On Oct 28, 2013, at 11:14 AM, Jerry <[email protected]> wrote: > 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?
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. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
