sqlalchemy多外键关联教程

sqlalchemy\_many\_fk.py

from sqlalchemy import Integer, ForeignKey, String, Column,create\_engine
from sqlalchemy.ext.declarative import declarative\_base
from sqlalchemy.orm import relationship

Base = declarative\_base()

class Customer(Base):
\_\_tablename\_\_=**'customer'
id=Column(Integer,primary\_key=True)
name=Column(String(64))
billing\_address\_id=Column(Integer,ForeignKey("address.id"))
shipping\_address\_id=Column(Integer,ForeignKey("address.id"))

billing\_address=relationship("Address",foreign\_keys=[billing\_address\_id])*\#账单地址
shipping\_address=relationship("Address",foreign\_keys=[shipping\_address\_id])*\#邮寄地址

class Address(Base):
\_\_tablename\_\_=**"address"
id=Column(Integer,primary\_key=True)
street=Column(String(64))
city=Column(String(64))
state=Column(String(64))
def \_\_repr\_\_(self):
return "state:%s city:%s street:%s"%(self.state,self.city,self.street)

engine=create\_engine("mysql+pymysql://root:[email protected]/shop\_db",
encoding='utf-8',echo=False)*\#连接数据库 echo=TRUE输出执行过程
Base.metadata.create\_all(engine)\#创建表

sqlalchemy\_many\_fk\_api.py

from day12 import sqlalchemy\_many\_fk
from sqlalchemy.orm import sessionmaker

*\#连接mysql
Session\_class = sessionmaker(bind=sqlalchemy\_many\_fk.engine) *\# 创建与数据库的连接session class ,注意,这里返回给session的是个class,不是实例
session = Session\_class() *\# 生成session实例 session会话 类似cursor
**'''
\#插入数据
addr1=sqlalchemy\_many\_fk.Address(street="bantian",city='longgang',state="SZ")
addr2=sqlalchemy\_many\_fk.Address(street="yousong",city='longhua',state="SZ")
addr3=sqlalchemy\_many\_fk.Address(street="shayuanpu",city='changan',state="DG")
session.add\_all([addr1,addr2,addr3])

c1=sqlalchemy\_many\_fk.Customer(name="kobe",billing\_address=addr1,shipping\_address=addr2)
c2=sqlalchemy\_many\_fk.Customer(name='jack',billing\_address=addr3,shipping\_address=addr3)
session.add\_all([c1,c2])
'''
*\#查数据
obj=session.query(sqlalchemy\_many\_fk.Customer).filter(sqlalchemy\_many\_fk.Customer.name=="kobe").first()
print(obj.name,obj.billing\_address,obj.shipping\_address)

session.commit()