本程序主要是参考金角大王博客
做了一个ORM可增可查的外键关联查询实验
#-*- coding:utf-8 -*-
from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship,sessionmaker
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:ABC@192.168.1.3/test?charset=utf8",
encoding='utf-8', echo=True,convert_unicode=True)#?charset=utf8才使ORM真正能存中文数据
Base = declarative_base()
class Customer(Base):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True)
name = Column(String(32))
__table_args__ = {
"mysql_charset" : "utf8"
}
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(32))
state = Column(String(32))
__table_args__ = {
"mysql_charset" : "utf8"
}
def __repr__(self):
return "<Address(street='%s', city='%s', state=%s)>" % (self.street, self.city, self.state)
Base.metadata.create_all(engine) #创建表结构
Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
Session = Session_class() #生成session实例
#-------------------增----------------------
# ad1=Address(street="中山北路",city="闸北区",state="上海")
# ad2=Address(street="南京路",city="黄浦区",state="上海")
# ad3=Address(street="北京西路",city="静安区",state="上海")
# ad4=Address(street="嘉松中路",city="青浦区",state="上海")
# Session.add_all([ad1,ad2,ad3,ad4]) #把要创建的数据对象添加到这个session里, 一会统一创建
# c1=Customer(name="jack",billing_address_id=1,shipping_address_id=1)
# c2=Customer(name="cici",billing_address_id=2,shipping_address_id=3)
# c3=Customer(name="bob",billing_address_id=4,shipping_address_id=4)
# Session.add_all([c1,c2,c3])
#-------------------查----------------------
info=Session.query(Customer).filter(Customer.name=="cici").first()
print(info.name,info.billing_address,info.shipping_address)
Session.commit() #现此才统一提交,创建数据