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()