Python_sqlalchemy实现简单的一对多数据表创建

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)