Each Answer to this Q is separated by one/two green lines.
When I have created a table with an auto-incrementing primary key, is there a way to obtain what the primary key would be (that is, do something like reserve the primary key) without actually committing?
I would like to place two operations inside a transaction however one of the operations will depend on what primary key was assigned in the previous operation.
You don’t need to
commit, you just need to
flush. Here’s some sample code. After the call to flush you can access the primary key that was assigned. Note this is with SQLAlchemy v1.3.6 and Python 3.7.4.
from sqlalchemy import * import sqlalchemy.ext.declarative Base = sqlalchemy.ext.declarative.declarative_base() class User(Base): __tablename__ = 'user' user_id = Column('user_id', Integer, primary_key=True) name = Column('name', String) if __name__ == '__main__': import unittest from sqlalchemy.orm import * import datetime class Blah(unittest.TestCase): def setUp(self): self.engine = create_engine('sqlite:///:memory:', echo=True) self.sessionmaker = scoped_session(sessionmaker(bind=self.engine)) Base.metadata.bind = self.engine Base.metadata.create_all() self.now = datetime.datetime.now() def test_pkid(self): user = User(name="Joe") session = self.sessionmaker() session.add(user) session.flush() print('user_id', user.user_id) session.commit() session.close() unittest.main()
You can use multiple transactions and manage it within scope.