CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
tsauerwein
tsauerwein

Reputation: 6011

SQLAlchemy bulk insertion with null colums

I am using Session.bulk_insert_mappings() to insert a number of entities, but I am having a problem when entities have None values.

The following bulk insert works fine, a single insert statement is used to insert all entities:

session.bulk_insert_mappings(Document, [
    dict(document_id=1, version=1, colA=True, colB='a'),
    dict(document_id=2, version=1, colA=True, colB='b'),
    dict(document_id=3, version=1, colA=True, colB='c')
])

But when one of the fields is None, SQLAlchemy will split up the insert in multiple statements:

session.bulk_insert_mappings(Document, [
    dict(document_id=1, version=1, colA=True, colB='a'),
    dict(document_id=2, version=1, colA=True, colB=None),
    dict(document_id=3, version=1, colA=True, colB='c')
])

Log:

INFO  [...] INSERT INTO api.documents (...) VALUES (...)
INFO  [...] {'colA': True, 'colB': 'a', 'version': 1, 'document_id': 1}
INFO  [...] INSERT INTO api.documents (...) VALUES (...)
INFO  [...] {'colA': True, 'version': 1, 'document_id': 2}
INFO  [...] INSERT INTO api.documents (...) VALUES (...)
INFO  [...] {'colA': True, 'colB': 'c', 'version': 1, 'document_id': 3}

I tried to replace None with null(), but then I am getting the following error "can't adapt type 'Null'":

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'Null' [SQL: 'INSERT INTO api.documents (...) VALUES (...)'] [parameters: ({'colB': 'a', 'colA': True, 'version': 1, 'document_id': 1}, {'colB': <sqlalchemy.sql.elements.Null object at 0x7f74679c0190>, 'colA': True, 'version': 1, 'document_id': 2}, {'colB': 'c', 'colA': True, 'version': 1, 'document_id': 3})]

How can I make sure that a single insert statement is used even when some entities have None values?

Edit: The mapping looks like this:

class Document(Base):
    __tablename__ = 'documents'

    document_id = Column(Integer, primary_key=True)
    version = Column(Integer, nullable=False, server_default='1')
    colA = Column(Boolean)
    colB = Column(Integer)

Upvotes: 4

Views: 3302

Answers (1)

Tomasz Jakub Rup
Tomasz Jakub Rup

Reputation: 10680

Following the SQLAlchemy docs http://docs.sqlalchemy.org/en/latest/orm/persistence_techniques.html:

If we’d like to be able to use the Python value None and have this also be persisted as NULL despite the presence of column defaults, we can configure this for the ORM using a Core-level modifier TypeEngine.evaluates_none(), which indicates a type where the ORM should treat the value None the same as any other value and pass it through, rather than omitting it as a “missing” value:

class MyObject(Base):
    __tablename__ = 'my_table'
    id = Column(Integer, primary_key=True)
    data = Column(
      String(50).evaluates_none(),  # indicate that None should always be passed
      nullable=True, server_default="default")

obj = MyObject(id=1, data=None)
session.add(obj)
session.commit()  # INSERT with the 'data' column explicitly set to None;
                  # the ORM uses this directly, bypassing all client-
                  # and server-side defaults, and the database will
                  # persist this as the NULL value

This probably solve Your problem.

Upvotes: 4

Related Questions