SQLAlchemy bulk_save_objects can't save updated object having a versioning field

Question

I'm trying to have a combination of a versioning on my rows, and bulk_save_objects. Here's my code, and it fails when I try to give the function an updated object at the end of the code.

import datetime
import sqlalchemy as sqa
import sqlalchemy.ext
import sqlalchemy.ext.declarative
import sqlalchemy.orm

Base = sqa.ext.declarative.declarative_base()
class Test(Base):
    __tablename__ = 'gads_sqlalchemyTest'

    id = sqa.Column(sqa.Integer, primary_key = True)
    id2 = sqa.Column(sqa.String(50), primary_key = True)
    name = sqa.Column(sqa.String(200))
    lastUpdated = sqa.Column(sqa.DateTime)

    __mapper_args__ = {
        'version_id_col': lastUpdated,
        'version_id_generator': lambda version: datetime.datetime.now()
    }

    def __repr__(self):
        return('<Test(id: %d, name: %s)>' % (
            self.id, self.name))


if __name__ == '__main__':
    connection_string = ('mssql+pyodbc://'
                         'username:password@server:1433/'
                         'databasename'
                         '?driver=FreeTDS')
    engine = sqa.create_engine(connection_string, echo=True)

    Base.metadata.create_all(engine)
    Session = sqa.orm.sessionmaker(bind = engine)
    session = Session()

    objects = []
    for i in range(3):
        tmp = Test()
        tmp.id = i
        tmp.id2 = 'SE'
        tmp.name = 'name %d' % i
        objects.append(tmp)

    session.bulk_save_objects(objects)
    session.commit()

    tmp = session.query(Test).filter(Test.id == 1).one()
    tmp.name = 'test'
    session.bulk_save_objects([tmp])
    session.commit()

And here's the output:

2016-08-16 09:44:00,710 INFO sqlalchemy.engine.base.Engine 
            SELECT default_schema_name FROM
            sys.database_principals
            WHERE principal_id=database_principal_id()

2016-08-16 09:44:00,710 INFO sqlalchemy.engine.base.Engine ()
2016-08-16 09:44:00,729 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2016-08-16 09:44:00,729 INFO sqlalchemy.engine.base.Engine ()
2016-08-16 09:44:00,734 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS NVARCHAR(60)) AS anon_1
2016-08-16 09:44:00,734 INFO sqlalchemy.engine.base.Engine ()
2016-08-16 09:44:00,740 INFO sqlalchemy.engine.base.Engine SELECT [INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA], [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME], [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME], [INFORMATION_SCHEMA].[COLUMNS].[IS_NULLABLE], [INFORMATION_SCHEMA].[COLUMNS].[DATA_TYPE], [INFORMATION_SCHEMA].[COLUMNS].[ORDINAL_POSITION], [INFORMATION_SCHEMA].[COLUMNS].[CHARACTER_MAXIMUM_LENGTH], [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_PRECISION], [INFORMATION_SCHEMA].[COLUMNS].[NUMERIC_SCALE], [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_DEFAULT], [INFORMATION_SCHEMA].[COLUMNS].[COLLATION_NAME] 
FROM [INFORMATION_SCHEMA].[COLUMNS] 
WHERE [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[COLUMNS].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max))
2016-08-16 09:44:00,741 INFO sqlalchemy.engine.base.Engine ('gads_sqlalchemyTest', 'dbo')
2016-08-16 09:44:00,966 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-08-16 09:44:00,967 INFO sqlalchemy.engine.base.Engine INSERT INTO [gads_sqlalchemyTest] (id, id2, name, [lastUpdated]) VALUES (?, ?, ?, ?)
2016-08-16 09:44:00,968 INFO sqlalchemy.engine.base.Engine ((0, 'SE', 'as;dkljasdfl;kj 0 1', datetime.datetime(2016, 8, 16, 9, 44, 0, 967306)), (1, 'SE', 'as;dkljasdfl;kj 1 2', datetime.datetime(2016, 8, 16, 9, 44, 0, 967328)), (2, 'SE', 'as;dkljasdfl;kj 2 3', datetime.datetime(2016, 8, 16, 9, 44, 0, 967337)))
2016-08-16 09:44:00,976 INFO sqlalchemy.engine.base.Engine COMMIT
2016-08-16 09:44:00,984 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2016-08-16 09:44:00,986 INFO sqlalchemy.engine.base.Engine SELECT [gads_sqlalchemyTest].id AS [gads_sqlalchemyTest_id], [gads_sqlalchemyTest].id2 AS [gads_sqlalchemyTest_id2], [gads_sqlalchemyTest].name AS [gads_sqlalchemyTest_name], [gads_sqlalchemyTest].[lastUpdated] AS [gads_sqlalchemyTest_lastUpdated] 
FROM [gads_sqlalchemyTest] 
WHERE [gads_sqlalchemyTest].id = ?
2016-08-16 09:44:00,986 INFO sqlalchemy.engine.base.Engine (1,)
2016-08-16 09:44:00,992 INFO sqlalchemy.engine.base.Engine ROLLBACK
Traceback (most recent call last):
  File "tmp.py", line 60, in <module>
    session.bulk_save_objects([tmp])
  File "/home/adrin/Projects/venv/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2264, in bulk_save_objects
    return_defaults, update_changed_only, False)
  File "/home/adrin/Projects/venv/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2428, in _bulk_save_mappings
    transaction.rollback(_capture_exception=True)
  File "/home/adrin/Projects/venv/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/home/adrin/Projects/venv/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value
  File "/home/adrin/Projects/venv/lib/python3.5/site-packages/sqlalchemy/orm/session.py", line 2419, in _bulk_save_mappings
    isstates, update_changed_only)
  File "/home/adrin/Projects/venv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 123, in _bulk_update
    bookkeeping=False)
  File "/home/adrin/Projects/venv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 642, in _emit_update_statements
    lambda rec: (
  File "/home/adrin/Projects/venv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 439, in _collect_update_commands
    update_version_id in states_to_update:
  File "/home/adrin/Projects/venv/lib/python3.5/site-packages/sqlalchemy/orm/persistence.py", line 117, in <genexpr>
    for mapping in mappings
KeyError: 'lastUpdated'

The code runs smoothly if I simply completely remove the lastUpdated field.

EDIT: There's a patch to fix the error here

Answer 1

Bulk operations bypass a lot of SQLAlchemy's functionality in the name of speed. Every bulk operation has a large list of warnings and caveats in the docs. I would not be surprised if the versioning functionality is one of the things bypassed.