issue while committing data using SQLAlchemy Session object in a Loop

Question

I am having a database committing problem with SQLalchemy. Here is my scenario I have a list of records to be added into a table. Once a records is added into a table I need to get the inserted records Id and do another insert into a second table. I am doing these operation under single session. The code structure is given below as I am not allowed to give the code

creates a session object

loop the records list to be inserted:

 do the session.add(obj)

 session.commit()

 get obj.id

 do the session.add(obj2) # with obj2 having the id from the obj

 session.commit()

here only the last record data is getting committed properly if we have multiple records

Could any one please help me correct this issue.

impossible to tell, write some sample code, which has the same problem — knitti, Sep 16 '11 at 16:59

score 8 · Answer 1 · answered Sep 16 '11 at 22:25

There's a very good chance that you don't need to issue two commits; One of the main reasons for using sqlalchemy sessions is that it understands how objects can interrelate, and will order its inserts in such a way that data gets properly inserted and correctly represents the desired structure. This works principally through the relationship construct. Here's a simple example:

>>> from sqlalchemy import *
>>> from sqlalchemy.orm import *
>>> from sqlalchemy.ext.declarative import declarative_base
>>> Base = declarative_base()

>>> class A(Base):
...     __tablename__ = "a_table"
...     id = Column(Integer, primary_key=True)
... 
>>> class B(Base):
...     __tablename__ = "b_table"
...     id = Column(Integer, primary_key=True)
...     a_id = Column(Integer, ForeignKey(A.id))
...     a = relationship(A)
... 
>>> my_a = A()
>>> my_b = B()
>>> my_b.a = my_a
>>>

The most important part is that we are declaring a relationship between A and B through B.a. To take best advantage of this, it's important to express the relationship between instances of each through this relationship property, and let sqlalchemy take care of setting the a_id column itself.

>>> engine = create_engine("sqlite:///:memory:")
>>> Base.metadata.create_all(engine)
>>> engine.echo = True
>>> Session = sessionmaker(engine)
>>> 
>>> session = Session()
>>> session.add(my_a)
>>> session.add(my_b)
>>> 
>>> session.commit()

With echo=True, the output looks a bit like this:

2011-09-16 17:19:22,367 INFO sqlalchemy.engine.base.Engine.0x...ed50 BEGIN (implicit)
2011-09-16 17:19:22,368 INFO sqlalchemy.engine.base.Engine.0x...ed50 INSERT INTO a_table DEFAULT VALUES
2011-09-16 17:19:22,368 INFO sqlalchemy.engine.base.Engine.0x...ed50 ()
2011-09-16 17:19:22,369 INFO sqlalchemy.engine.base.Engine.0x...ed50 INSERT INTO b_table (a_id) VALUES (?)
2011-09-16 17:19:22,369 INFO sqlalchemy.engine.base.Engine.0x...ed50 (1,)
2011-09-16 17:19:22,369 INFO sqlalchemy.engine.base.Engine.0x...ed50 COMMIT

Notice that the my_a object is inserted, and sqlalchemy reads the assigned primary key and uses that for the insert for my_b.

score 4 · Accepted Answer · answered Sep 16 '11 at 17:21

You might try restructuring your code to use this:

Session = sqlalchemy.orm.sessionmaker(...)
def transaction(self, callback):
  session = sqlalchemy.orm.scoped_session(Session)
  try:
    result = callback(session)
  except:
    session.rollback()
    raise
  else:
    session.commit()
  finally:
    session.close()
  return result

Then each transaction goes in its own function, like so:

def updatetxn(pk, newvalue):
  def txn(session):
    obj = session.query(myclass).filter_by(id=pk).one()
    obj.field = newvalue
    session.add(obj)
  return txn

transaction(updatetxn(4, 'abc'))

Handling the commit/rollback logic in one place, and relying on function scope to scope a unit of work, may reduce the complexity of your application and eliminate bugs you haven't found yet.

issue while committing data using SQLAlchemy Session object in a Loop

2 Answers2

Linked