SQLAlchemy Auto Converts BLOB To Str?

[Jerry]

Hi,

I'm using SQLAlchemy 0.8 connecting to an existing SQL Server database via ODBC.

JobSpec is the BLOB --

class ScheduledJob(Base):
    __tablename__ = 'ScheduledJob'
    __table_args__ = {'useexisting': True}
    JobSpec = Column(BLOB)

When I query in ORM, I get it as a str --

engine = create_engine('mssql+pyodbc://%s:%s@%s/%s' % (my_uid, my_pwd, my_server, my_db))
DBSession = scoped_session(sessionmaker(bind=engine))
for scheduledjob in DBSession.query(ScheduledJob).all():
    type(scheduledjob.JobSpec) # == type(str())
    print scheduledjob.JobSpec # '\xfa\xff\xff\xff\xff\xff\x01\x00\xff\xff\x0f\' \x00!\x00"\x00'...


But if I execute SQL in pyodbc, it's correctly returned as a bytearray --

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=my_server;DATABASE=my_db;UID=my_uid;PWD=my_pwd')
cursor = cnxn.cursor()
cursor.execute("select JobSpec from ScheduledJob")
for row in cursor.fetchall():
    type(row.JobSpec) # == type(bytearray())


It seems SQLAlchemy auto converts BLOB to str -- if it's true, it'd be a bug, isn't it?

Or am I using SQLAlchemy correctly? Is there a config to turn off this behavior?

Thanks.

Jerry

[Michael Bayer]

the bytearray() is a new type that was only introduced in Python 2.6, and this behavior is specific to pyodbc. psycopg2 for example returns a value called a “memoryview”, cx_oracle returns a special LOB type.

Binary types in Python are typically plain strings (e.g. str), in Python 3 this type is called “bytes”. The Binary types in SQLAlchemy standardize the return of bytes to be bytestring/bytes for all backends, converting from the various arbitrary objects the DBAPIs specify (as the DBAPI spec itself says nothing about Python types). The sqlite3 driver that’s included with Python uses this convention as well for binary types.

if you want the raw pyodbc behavior, quickest route is to just use NullType instead of BLOB for your type, or subclass UserDefinedType or BLOB if you still need some of the type-specific behaviors other than result conversion.

[Jerry]

Hi Mike,

It works -- bytearray is now returned after changing JobSpec from BLOB to NullType.

Thank you very much for the wonderful library and great support.

Jerry