Python全栈之旅：SQLAlchemyORM中的外键与关系

 1 import sqlalchemy
 2 from sqlalchemy import create_engine
 3 from sqlalchemy import Column, String, Integer, ForeignKey
 4 from sqlalchemy.orm import sessionmaker, relationship
 5 from sqlalchemy.ext.declarative import declarative_base
 6 
 7 engine = create_engine("mysql+pymysql://root:root@127.0.0.1/t1")
 8 
 9 Base = declarative_base()
10 
11 class Father(Base):
12     __tablename__ = "father"
13 
14     id = Column(Integer, primary_key=True, autoincrement=True)
15     name = Column(String(40), unique=True)
16     age = Column(Integer)
17     sOns= relationship('Son', backref='father')
18 
19 class Son(Base):
20     __tablename__ = 'son'
21 
22     id = Column(Integer, primary_key=True, autoincrement=True)
23     name = Column(String(40), unique=True)
24     age = Column(Integer)
25     father_id = Column(Integer, ForeignKey('father.id'))
26 
27 Base.metadata.create_all(engine)
28 
29 Session = sessionmaker(bind=engine)
30 session = Session()
31 
32 # 示例代码
33 father = Father(name='John Doe', age=35)
34 son1 = Son(name='Jane Doe', age=10, father=father)
35 son2 = Son(name='Jack Doe', age=8, father=father)
36 session.add_all([father, son1, son2])
37 session.commit()
38 
39 # 查询父亲及其子女
40 father_query = session.query(Father).filter_by(id=1).first()
41 for son in father_query.sons:
42     print(f'Name: {son.name}, Age: {son.age}')
43 
44 # 查询特定子的父
45 son_query = session.query(Son).filter_by(id=1).first()
46 print(f'Father Name: {son_query.father.name}, Father Age: {son_query.father.age}')