Query binary data using sqlalchemy with PostgreSQL

Question

I have a simple database storing an attachment as blob.

CREATE TABLE public.attachment
(
  id integer NOT NULL,
  attachdata oid,
  CONSTRAINT attachment_pkey PRIMARY KEY (id)
)

-- Import a file
INSERT INTO attachment (id, attachdata) VALUES (1, lo_import('C:\\temp\blob_import.txt'))
-- Export back as file. 
SELECT lo_export(attachdata, 'C:\temp\blob_export_postgres.txt') FROM attachment WHERE id = 1

I'm able to read this file back using psycopg2 directly.

from psycopg2 import connect
con = connect(dbname="blobtest", user="postgres", password="postgres", host="localhost")
cur = con.cursor()
cur.execute("SELECT attachdata FROM attachment WHERE id = 1")
oid = cur.fetchone()[0]
obj = con.lobject(oid)
obj.export('C:\\temp\\blob_export_psycopg.txt')

When I try the same using sqlalchemy, the attachdata is a bytestring of zeros. I've tested the following code with types like BLOB, LargeBinary and BINARY. The size of attachdata bytstring seems to be the OIDs value.

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, Binary
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
Session = sessionmaker()

engine = create_engine('postgresql://postgres:postgres@localhost:5432/blobtest', echo=True)
Base.metadata.create_all(engine)
Session.configure(bind=engine)

class Attachment(Base):
    __tablename__ ="attachment"
    id = Column(Integer, primary_key=True)
    attachdata = Column(Binary)

session = Session()
attachment = session.query(Attachment).get(1)
with open('C:\\temp\\blob_export_sqlalchemy.txt', 'wb') as f:
    f.write(attachment.attachdata)

I've searched the sqlalchemy documentation and various sources and couldn't find a solution how to export the binary data using sqlalchemy.

Answer 1

I had the same problem. There seems to be no way to get the large object data via the ORM. So I combined the ORM and the psycopg2 engine like this:

from sqlalchemy import create_engine
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.dialects.postgresql import OID

Base = declarative_base()
session_factory = sessionmaker()

engine = create_engine('postgresql+psycopg2://postgres:postgres@localhost:5432/postgres', echo=True)
Base.metadata.create_all(engine)
session_factory.configure(bind=engine)
Session = scoped_session(session_factory)


class Attachment(Base):
    __tablename__ ="attachment"
    id = Column(Integer, primary_key=True)
    oid = Column(OID)

    @classmethod
    def insert_file(cls, filename):
        conn = engine.raw_connection()
        l_obj = conn.lobject(0, 'wb', 0)
        with open(filename, 'rb') as f:
            l_obj.write(f.read())
        conn.commit()
        conn.close()
        session = Session()
        attachment = cls(oid=l_obj.oid)
        session.add(attachment)
        session.commit()
        return attachment.id

    @classmethod
    def get_file(cls, attachment_id, filename):
        session = Session()
        attachment = session.query(Attachment).get(attachment_id)
        conn = engine.raw_connection()
        l_obj = conn.lobject(attachment.oid, 'rb')
        with open(filename, 'wb') as f:
            f.write(l_obj.read())
        conn.close()


if __name__ == '__main__':
    my_id = Attachment.insert_file(r'C:\path\to\file')
    Attachment.get_file(my_id, r'C:\path\to\file_out')

Not very elegant but it seems to work.

Update:

I am using events now

from sqlalchemy import create_engine, event
from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.dialects.postgresql import OID

Base = declarative_base()
session_factory = sessionmaker()

engine = create_engine('postgresql+psycopg2://postgres:postgres@localhost:5432/postgres', echo=True)
Base.metadata.create_all(engine)
session_factory.configure(bind=engine)
Session = scoped_session(session_factory)

class Data(Base):
    __tablename__ = "attachment"
    id = Column(Integer, primary_key=True)
    oid = Column(OID)


@event.listens_for(Data, 'after_delete')
def remove_large_object_after_delete(_, connection, target):
    raw_connection = connection.connection
    l_obj = raw_connection.lobject(target.oid, 'n')
    l_obj.unlink()
    raw_connection.commit()


@event.listens_for(Data, 'before_insert')
def add_large_object_before_insert(_, connection, target):
    raw_connection = connection.connection
    l_obj = raw_connection.lobject(0, 'wb', 0)
    target.oid = l_obj.oid
    l_obj.write(target.ldata)
    raw_connection.commit()


@event.listens_for(Data, 'load')
def inject_large_object_after_load(target, _):
    session = object_session(target)
    conn = session.get_bind().raw_connection()
    l_obj = conn.lobject(target.oid, 'rb')
    target.ldata = l_obj.read()

if __name__ == '__main__':
   session = Session()  
   # Put
   data = Data()
   data.ldata = 'your large data'
   session.add(data)
   session.commit()

   id = data.id

   # Get
   data2 = session.query(Data).get(id)
   print(data.ldata) # Your large data is here

   # Delete
   session.delete(data)
   session.delete(data2)
   session.commit()   

   session.flush()
   session.close()

Works good so far.

I don't understand why postgres large objects get so neglected these days. I use them a ton. Or let's say I want to but it's challenging especially in asyncio....