Example using BLOB in SQLAlchemy

Question

Does anybody have example on how to use BLOB in SQLAlchemy?

I'd like to see an example, that works without the orm. I guess in that case you need to somehow wrap the blob value passed to Connection.execute. — Helmut Grohne, Jul 24 '13 at 11:40

score 26 · Answer 1 · edited Sep 18 '13 at 09:08

from sqlalchemy import *
from sqlalchemy.orm import mapper, sessionmaker
import os

engine = create_engine('sqlite://', echo=True)
metadata = MetaData(engine)

sample = Table(
    'sample', metadata,
    Column('id', Integer, primary_key=True),
    Column('lob', Binary),
)

class Sample(object):

    def __init__(self, lob):
        self.lob = lob

mapper(Sample, sample)

metadata.create_all()

session = sessionmaker(engine)()

# Creating new object
blob = os.urandom(100000)
obj = Sample(lob=blob)
session.add(obj)
session.commit()
obj_id = obj.id
session.expunge_all()

# Retrieving existing object
obj = session.query(Sample).get(obj_id)
assert obj.lob==blob

score 12 · Answer 2 · edited Aug 10 '17 at 02:19

12

from sqlalchemy import *
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

from struct import *

_DeclarativeBase = declarative_base()

class MyTable(_DeclarativeBase):
    __tablename__ = 'mytable'
    id = Column(Integer, Sequence('my_table_id_seq'), primary_key=True)
    my_blob = Column(BLOB)

DB_NAME = 'sqlite:///C:/BlobbingTest.db'
db = create_engine(DB_NAME)
#self.__db.echo = True
_DeclarativeBase.metadata.create_all(db)
Session = sessionmaker(bind=db)
session = Session()

session.add(MyTable(my_blob=pack('H', 365)))
l = [n + 1 for n in xrange(10)]
session.add(MyTable(my_blob=pack('H'*len(l), *l)))
session.commit()

query = session.query(MyTable)
for mt in query.all():
    print unpack('H'*(len(mt.my_blob)/2), mt.my_blob)

edited Aug 10 '17 at 02:19

Luxaritas

23
9

answered Jan 16 '13 at 20:39

Jakub Koszuliński

321
3
4

Advantages of using pack. +1 – pylover Oct 28 '14 at 04:41
18

Consider avoiding using import * so people don't have to guess/hunt where objects come from like pack. – superbeck May 04 '18 at 00:45
1

Where are those pack / unpack coming from? – Dawid Laszuk Apr 09 '21 at 22:36

score 5 · Answer 3 · answered Nov 16 '20 at 08:52

Why don't you use LargeBinary?

Extract from: https://docs.sqlalchemy.org/en/13/core/type_basics.html#sqlalchemy.types.LargeBinary

class sqlalchemy.types.LargeBinary(length=None)
A type for large binary byte data.

The LargeBinary type corresponds to a large and/or unlengthed binary type for the target platform, such as BLOB on MySQL and BYTEA for PostgreSQL. It also handles the necessary conversions for the DBAPI.

I believe this might assist you.

score 1 · Answer 4 · edited Sep 12 '19 at 11:15

From the documentation BINARY seems the way to go: http://docs.sqlalchemy.org/en/latest/dialects/mysql.html

class sqlalchemy.dialects.mysql.BLOB(length=None) Bases: sqlalchemy.types.LargeBinary

The SQL BLOB type.

__init__(length=None) Construct a LargeBinary type.

Parameters: length – optional, a length for the column for use in DDL statements, for those BLOB types that accept a length (i.e. MySQL). It does not produce a lengthed BINARY/VARBINARY type - use the BINARY/VARBINARY types specifically for those. May be safely omitted if no CREATE TABLE will be issued. Certain databases may require a length for use in DDL, and will raise an exception when the CREATE TABLE DDL is issued.

Example using BLOB in SQLAlchemy

4 Answers4