How use transaction in sql alchemy while using db session?

Question

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?

Answer 1

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