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
On Tuesday, October 29, 2013 12:13:15 AM UTC+8, Michael Bayer wrote:
>
>
> On Oct 28, 2013, at 11:14 AM, Jerry <jerry...@gmail.com <javascript:>>
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
