from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base
# 创建数据库引擎
engine = create_engine('postgresql://user:password@localhost/mydb')
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()
# 定义orders表
class Order(Base):
__tablename__ = 'orders'
id = Column(Integer, primary_key=True)
order_number = Column(String)
order_items = relationship('OrderItem', backref='order')
# 定义order_items表
class OrderItem(Base):
__tablename__ = 'order_items'
id = Column(Integer, primary_key=True)
order_id = Column(Integer, ForeignKey('orders.id'))
product_name = Column(String)
try:
# 创建表结构
Base.metadata.create_all(engine)
# 开始事务
new_order = Order(order_number='12345')
session.add(new_order)
item1 = OrderItem(product_name='Product 1', order=new_order)
item2 = OrderItem(product_name='Product 2', order=new_order)
session.add_all([item1, item2])
# 提交事务
session.commit()
except Exception as e:
# 回滚事务
session.rollback()
print(f"事务处理失败: {e}")
finally:
session.close()