SQLAlchemy Obtain Primary Key With Autoincrement Before Commit

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.

From stackoverflow

• You can use multiple transactions and manage it within scope.

• I don't know anything about SQL Alchemy.. but if the database is SQl Server then you simply do your insert statement and record the value of @@IDENTITY... example:

  DECLARE @ID BIGINT

  BEGIN TRANSACTION INSERT INTO table (col1, col2, col3) VALUES (val1, val2, val3) SET @ID = @@IDENTITY

  INSERT INTO table2 (Fk1, col1, col2) VALUES (@ID, val1, val2)

  COMMIT TRANSACTION

  HLGEM : Never ever use @@identity in SQL Server!!!! It will cause data integrity problems if you ever put a trigger on the table that inserts into another table with an identity. Use scope_identity() instead.

  Boo : @@IDENTITY is perfectly acceptable in most cases. It's triggers that should be avoided.

• 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 SA 0.4.8.

  from sqlalchemy import *
  from sqlalchemy.databases.mysql 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.save(user)
              session.flush()
              print 'user_id', user.user_id
              session.commit()
              session.close()
  
      unittest.main()
  

  Bill Karwin : Wow a hearty +1 for a good code example!