On May 21, 2010, at 10:04 PM, Yang Zhang wrote:
> On Fri, May 21, 2010 at 6:47 PM, Yang Zhang <[email protected]> wrote:
>> I'm trying to run the following:
>>
>> session.query(Activity).filter(Activity.blob == blob).one()
>>
>> where Activity.blob is a BLOB and blob is a Python bytes object
>> (equiv. to str in Python 2.x). But although I can insert Activity(blob
>> = blob) objects fine, the above query fails with:
>>
>> ProgrammingError: (ProgrammingError) You must not use 8-bit
>> bytestrings unless you use a text_factory that can interpret 8-bit
>> bytestrings (like text_factory = str).
>>
>> This is because blob has to be wrapped with sqlite3.Binary(blob). Why
>> doesn't sqlalchemy automatically do this casting, given that it has
>> schema awareness? Is there any way to avoid having to do this for
>> every such query?
>>
>> Thanks in advance.
>> --
>> Yang Zhang
>> http://yz.mit.edu/
>>
>
>
> Also, how do I do the wrapping portably? For sqlite3 I have to wrap
> with sqlite3.Binary, for postgresql I have to wrap with
> psycopg2.Binary, etc.
the SQLAlchemy LargeBinary type and subclasses handle the dbapi.Binary wrapping
for you. In the case of your comparison, you might want to say
literal(mydata, type_=LargeBinary), if LargeBinary is in fact not being pulled
in and is actually the cause of that error, however this should not be
necessary - in Python 3, the "bytes" type is detected and is coerced into
LargeBinary when passed as a value (you can see this on line 1798 of
sqlalchemy/types.py). I am not able to reproduce your error in py2k or py3k:
from sqlalchemy import *
engine = create_engine('sqlite://', echo=True)
m = MetaData()
t = Table('t', m, Column('data', LargeBinary))
m.create_all(engine)
engine.execute(t.insert(), {'data':b'abc'}, {'data':b'xyz'})
engine.execute(t.select().where(t.c.data==b'xyz'))
output:
200:sqlalchemy classic$ python3.1 test.py
2010-05-22 01:12:35,595 INFO sqlalchemy.engine.base.Engine.0x...27b0 PRAGMA
table_info("t")
2010-05-22 01:12:35,597 INFO sqlalchemy.engine.base.Engine.0x...27b0 ()
2010-05-22 01:12:35,598 INFO sqlalchemy.engine.base.Engine.0x...27b0
CREATE TABLE t (
data BLOB
)
2010-05-22 01:12:35,598 INFO sqlalchemy.engine.base.Engine.0x...27b0 ()
2010-05-22 01:12:35,598 INFO sqlalchemy.engine.base.Engine.0x...27b0 COMMIT
2010-05-22 01:12:35,598 INFO sqlalchemy.engine.base.Engine.0x...27b0 INSERT
INTO t (data) VALUES (?)
2010-05-22 01:12:35,599 INFO sqlalchemy.engine.base.Engine.0x...27b0 ((<memory
at 0x12a1850>,), (<memory at 0x12a18a0>,))
2010-05-22 01:12:35,599 INFO sqlalchemy.engine.base.Engine.0x...27b0 COMMIT
2010-05-22 01:12:35,599 INFO sqlalchemy.engine.base.Engine.0x...27b0 SELECT
t.data
FROM t
WHERE t.data = ?
2010-05-22 01:12:35,599 INFO sqlalchemy.engine.base.Engine.0x...27b0 (<memory
at 0x12a1850>,)
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
