How does one use binary data (BLOB type column) in SQLAlchemy.
I just created a table with fields key, val, where val is BLOB and when I query the table, SQLAlchemy returns:
<read-only buffer for 0x83c3040, size -1, offset 0 at 0x83c3120>
How do I use this read-only buffer?
You can iterate over it (e.g. for streaming it) or convert it to a string/binary if you want to have the whole binary in memory (which shouldn't be a problem as long as you are not dealing with movies in the database...)
>>> from sqlalchemy.util import buffer
>>> var = buffer('foo')
>>> var
<read-only buffer for 0xb727fb00, size -1, offset 0 at 0xb727fa80>
>>> str(var)
'foo'
>>> for i in var:
... print i
...
f
o
o
Regards, Christoph
In SQLAlchemy 1.3, using Python2, binary data is returned as str, in Python 3 it is bytes:
# -*- coding: utf-8 -*-
import zlib
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import orm
Base = declarative_base()
class Blobby(Base):
__tablename__ = 'blobby'
id = sa.Column(sa.Integer, primary_key=True)
blob = sa.Column(sa.BLOB)
engine = sa.create_engine('mysql+pymysql:///test', echo=True)
Base.metadata.drop_all(bind=engine, checkfirst=True)
Base.metadata.create_all(bind=engine)
Session = orm.sessionmaker(bind=engine)
session = Session()
data = zlib.compress('Hello world'.encode('ascii'))
session.add(Blobby(blob=data))
session.commit()
blob, = session.query(Blobby.blob).first()
print(type(blob), blob)
session.close()
Python2 output
(<type 'str'>, 'x\x9c\xf3H\xcd\xc9\xc9W(\xcf/\xcaI\x01\x00\x18\xab\x04=')
Python3 output:
<class 'bytes'> b'x\x9c\xf3H\xcd\xc9\xc9W(\xcf/\xcaI\x01\x00\x18\xab\x04='
