SQLAlchemy exception and transaction sample code in core

The content of this department is relatively simple and is deposited.

#Coding=utf-8 fromDatetimeImportdatetime fromSQLAlchemyImport(MetaData, Table, Column, Integer, Numeric, String, Boolean, DateTime, ForeignKey, ForeignKey , Create_engine, Checkconstraint) fromSQLAlchemyImport(INSERT, select, Update, delete, text, desc, cast, And_, Or_, Not_) fromSqlalchemy.sqlImportfunc fromSqlalchemy.excImportIntegrityerrormetadata=MetaData () Cookies= Table ('Cookies', metadata, Column ('cookie_id', Integer (), primary_key=True), Column ('Cookie_name', String (in), index=True), Column ('Cookie_recipe_url', String (255)), Column ('Cookie_sku', String (55)), Column ('Quantity', Integer ()), Column ('Unit_cost', Numeric (12, 2)), Checkconstraint ('Quantity >= 0', name='quantity_positive')) Users= Table ('Users', metadata, Column ('user_id', Integer (), primary_key=True), Column ('username', String (+), Nullable=false, unique=True), Column ('email_address', String (255), nullable=False), Column ('Phone', String (nullable=),False), Column ('Password', String (+), nullable=False), Column ('created_on', DateTime (), default=DateTime.Now), Column ('updated_on', DateTime (), Default=datetime.now, onupdate=datetime.now)) orders= Table ('orders', metadata, Column ('order_id', Integer (), primary_key=True), Column ('user_id', ForeignKey ('users.user_id')), Column ('shipped', Boolean (), default=False)) Line_items= Table ('Line_items', metadata, Column ('line_items_id', Integer (), primary_key=True), Column ('order_id', ForeignKey ('orders.order_id')), Column ('cookie_id', ForeignKey ('cookies.cookie_id')), Column ('Quantity', Integer ()), Column ('Extended_cost', Numeric (12, 2)) engine= Create_engine ('Mysql+pymysql://u:[email protected]:3306/cookies') Metadata.create_all (engine) connection=Engine.connect ()defship_it (order_id): s=Select ([line_items.c.cookie_id, line_items.c.quantity]) s= S.where (line_items.c.order_id = =order_id) Transaction=connection.begin () cookies_to_ship=Connection.Execute (s)Try:         forCookiesinchCookies_to_ship:u= Update (cookies). WHERE (cookies.c.cookie_id==cookie.cookie_id) U= u.values (quantity = cookies.c.quantity-cookie.quantity) Result=connection.execute (U) u= Update (orders). WHERE (orders.c.order_id = =order_id) U= U.values (shipped=True) Result=connection.execute (U)Print("Shipped order ID: {}". Format (order_id)) Transaction.commit ()exceptIntegrityerror as Error:transaction.rollback ()Print(Error)" "INS = insert (users). VALUES (username= "Cookiemon", email_address= "[email protected]", phone= "111-111-1111", password= "Password") Try:result = Connection.Execute (INS) except Integrityerror as Error:passins = Cookies.ins ERT () inventory_list = [{' Cookie_name ': ' Chocolate Chip ', ' cookie_recipe_url ': ' Http://some.aweso.me/coo Kie/recipe.html ', ' cookie_sku ': ' CC01 ', ' Quantity ': ' A ', ' unit_cost ': ' 0.50 '}, {' Cook Ie_name ': ' Dark chocolate Chip ', ' cookie_recipe_url ': ' http://some.aweso.me/cookie/recipe_dark.html ', ' Cooki E_sku ': ' CC02 ', ' Quantity ': ' 1 ', ' unit_cost ': ' 0.75 '}]result = Connection.Execute (INS, inventory_lis t) INS = insert (orders). VALUES (user_id=1, order_id= ' 1 ') result = Connection.Execute (INS) ins = insert (Line_items) Order_ Items = [{' order_id ': 1, ' cookie_id ': 1, ' Quantity ': 9, ' Extended_cost ': 4.50}]re Sult = Connection.Execute (INS, order_items) ins = insert (orders). VALUES (user_id=1, order_id= ' 2 ') result = Connection.Execute (INS) ins = insert (line_     Items) Order_items = [{' order_id ': 2, ' cookie_id ': 1, ' Quantity ': 4, ' extended_cost ': 1.50 }, {' order_id ': 2, ' cookie_id ': 2, ' Quantity ': 1, ' Extended_cost ': 4.50}]resul t = Connection.Execute (INS, Order_items) ship_it (1) s = select ([Cookies.c.cookie_name, cookies.c.quantity]) Connection.Execute (s). Fetchall ()" "Ship_it (20)

SQLAlchemy exception and transaction sample code in core