SQLAlchemy bulk_save_objects无法保存具有版本控制字段的更新对象

Question

我正试图对我的行和bulk_save_objects进行版本控制的组合。这是我的代码，当我试图在代码末尾给函数一个更新的对象时，它失败了。

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()

输出如下:

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'

如果我简单地完全删除lastUpdated字段，代码将运行顺利。

编辑:这里有一个补丁来修复这个错误

Answer 1

批量操作以速度的名义绕过了SQLAlchemy的许多功能。每个批量操作在文档中都有一个大的警告和警告列表。如果版本控制功能被忽略，我不会感到惊讶。