1.建立数据表
# Author : Xuefeng
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DATE, func,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship
engine = create_engine("mysql+pymysql://Jim:123456@localhost/testdb",
encoding="utf-8",echo=True)
Base = declarative_base() # 生成orm基类
class Address(Base):
__tablename__ = "address"
id = Column(Integer,primary_key=True)
street = Column(String(32),nullable=False)
city = Column(String(32),nullable=False)
state = Column(String(32),nullable=False)
def __repr__(self):
return self.city
class Costumer(Base):
__tablename__ = "costumer"
id = Column(Integer,primary_key=True)
name = Column(String(32), nullable=False)
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])
def __repr__(self):
return self.name,self.billing_address_id,self.shipping_address
Base.metadata.create_all(bind=engine)
2.添加数据并查看
# Author : Xuefeng
from sqlalchemy.orm import sessionmaker
import mysql_createtable_costum
Session_class = sessionmaker(bind=mysql_createtable_costum.engine)
session = Session_class()
addr1 = mysql_createtable_costum.Address(street="taibainanlu",city="xian",state="xidian")
addr2 = mysql_createtable_costum.Address(street="mengzhongjiashu",city="songyuan",state="jia")
addr3 = mysql_createtable_costum.Address(street="xiaxie",city="daxian",state="gongsi")
session.add_all([addr1,addr2,addr3])
session.commit()
c1 = mysql_createtable_costum.Costumer(name="Jim",billing_address=addr2,shipping_address=addr3)
c2 = mysql_createtable_costum.Costumer(name="Tom",billing_address=addr1,shipping_address=addr3)
session.add_all([c1,c2])
session.commit()
obj = session.query(mysql_createtable_costum.Costumer).filter(mysql_createtable_costum.Costumer.name=="Jim").first()
print(obj.name,obj.billing_address,obj.shipping_address)