Alex Rivera | Logout

How use transaction in sql alchemy while using db session?

Asked 2012-04-08T13:55:03.193
13

I am using sql alchemy in my project, I used db session,

engine = create_engine(configuration)
db_session = scoped_session(sessionmaker(autocommit=False,
                                     autoflush=False,
                                     bind=engine))

Base = declarative_base()
Base.query = db_session.query_property()

def init_db():    
    import models
    Base.metadata.create_all(bind=engine)

DB session used as:

db_session.merge(order)      #order(model) in object
db_session.commit()

Now I want to insert data in to two tables order and order line item, so I need transaction, as: 1. In first insert I want inserted order's id to use in second insert query 2. If second insert query failed then first query should be rollback

Try:
    #begin transaction/How to begin transaction?
    order=db_session.add(order)      #insert into order
    #is need to commit db_session here as I need inserted orders id
    #here actually db_session.commit() needed to get order's id(auto generated) 
    #if db_session committed here then sql alchemy starts new session       

    order_line_item.id = order.id
    db_session.add(order_line_item)    #insert into order line line item

    db_session.commit()
    #check transaction status if failed then rollback, How to check status?

except:
    db_session.rollback()

How to use trasaction?

transactions sqlalchemy
Edit
Report

2 Answers

18

Nested transaction, as suggested by Lafada, is not intended for such situation. A flush() will do just fine, e.g.

db_session.begin()
try:
    db_session.add(order)

    db_session.flush()

    order_line_item.id = order.id
    db_session.add(order_line_item)

    db_session.commit()
except:
    db_session.rollback()

Or better still, if you have relationship setup properly between order and line item, you shouldn't even bother to assign id manually.

answered 2012-04-09T07:06:15.333
6

You should use the relationship feature of SQLAlchemy, so you don't have to mess around with foreign keys. So for example your order item could look like (I assume you have a many-one relationship):

class OrderLineItem(Base):
    id = Column(Integer, primary_key=True)
    order_id = Column(Integer, ForeignKey('orders.id'))
    order = relationship('Order', backref='order_line_items')

And on insert you would just assign the order instance:

order_line_item.order = order
session.add(order)  # you even don't have to insert the item!

See more details in the tutorial: http://docs.sqlalchemy.org/en/latest/orm/relationships.html

answered 2013-01-14T14:47:50.533

Your Answer