bulk_save_objects can't save updated object having a versioning field

[Adrin Jalali]

I've asked the question here (http://stackoverflow.com/questions/38969406/sqlalchemy-bulk-save-objects-cant-save-updated-object-having-a-versioning-field), but since I didn't receive answers, here's a copy.

I suspect it might be a bug, but as sqlalchemy newbie I'd like to hear your feedback.

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.

[Mike Bayer]

versioning is untested with the session.bulk() methods, a patch for the
update is at
https://bitbucket.org/zzzeek/sqlalchemy/issues/3781/implement-version_id-for-bulk_save.

Also, your test needs to read like this:

tmp = session.query(Test).filter(Test.id == 1).one()

session.close()

tmp.name = 'test'

session.bulk_save_objects([tmp])
session.commit()

otherwise autoflush tries to flush "tmp" again, and it's stale. Because
bulk operations do not update the attributes on the object, so it still
has the stale date.

> 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 =[]
> fori inrange(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-1609:44:00,710INFO sqlalchemy.engine.base.Engine

> SELECT default_schema_name FROM
> sys.database_principals
> WHERE principal_id=database_principal_id()
>
> 2016-08-1609:44:00,710INFO sqlalchemy.engine.base.Engine()
> 2016-08-1609:44:00,729INFO sqlalchemy.engine.base.EngineSELECT
> CAST('test plain returns'AS VARCHAR(60))AS anon_1
> 2016-08-1609:44:00,729INFO sqlalchemy.engine.base.Engine()
> 2016-08-1609:44:00,734INFO sqlalchemy.engine.base.EngineSELECT
> CAST('test unicode returns'AS NVARCHAR(60))AS anon_1
> 2016-08-1609:44:00,734INFO sqlalchemy.engine.base.Engine()
> 2016-08-1609:44:00,740INFO sqlalchemy.engine.base.EngineSELECT
> [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-1609:44:00,741INFO
> sqlalchemy.engine.base.Engine('gads_sqlalchemyTest','dbo')
> 2016-08-1609:44:00,966INFO sqlalchemy.engine.base.EngineBEGIN(implicit)
> 2016-08-1609:44:00,967INFO sqlalchemy.engine.base.EngineINSERT INTO
> [gads_sqlalchemyTest](id,id2,name,[lastUpdated])VALUES (?,?,?,?)
> 2016-08-1609:44:00,968INFO
> 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-1609:44:00,976INFO sqlalchemy.engine.base.EngineCOMMIT
> 2016-08-1609:44:00,984INFO sqlalchemy.engine.base.EngineBEGIN(implicit)
> 2016-08-1609:44:00,986INFO sqlalchemy.engine.base.EngineSELECT
> [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-1609:44:00,986INFO sqlalchemy.engine.base.Engine(1,)
> 2016-08-1609:44:00,992INFO sqlalchemy.engine.base.EngineROLLBACK

> Traceback(most recent call last):
> File"tmp.py",line 60,in<module>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.