#!/usr/bin/env python # -*- encoding:utf-8 -*- import sqlalchemy from sqlalchemy import DATE from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker from sqlalchemy import ForeignKey from sqlalchemy.orm import relationship engine = create_engine("mysql+pymysql://root:@127.0.0.1/db01?charset=utf8") Base = declarative_base() # 创建表结构 class User(Base): __tablename__ = ‘user‘ id = Column(Integer, primary_key=True) u_id = Column(Integer, nullable=False) name = Column(String(32), nullable=False) age = Column(Integer) favor_id = Column(Integer, ForeignKey(‘favor.id‘)) # favor 为表名，表示外键约束，真实存放在数据库中 favor = relationship(‘Favor‘, backref=‘xxx‘) # Favor 为类名，表示虚拟关系，存放于内存中 class Favor(Base): __tablename__ = ‘favor‘ id = Column(Integer, primary_key=True) type = Column(String(32), nullable=False) # Base.metadata.create_all(bind=engine) # Base.metadata.drop_all(bind=engine) Session_cls = sessionmaker(bind=engine) session = Session_cls() # 创建表数据 session.add_all([ Favor(type=‘favor-01‘), Favor(type=‘favor-02‘), Favor(type=‘favor-03‘), Favor(type=‘favor-04‘), ]) session.add_all([ User(u_id=2001, name=‘user1‘, age=15, favor_id=1), User(u_id=2002, name=‘user2‘, age=20, favor_id=3), User(u_id=2003, name=‘user3‘, age=30, favor_id=2), User(u_id=2004, name=‘user4‘, age=20, favor_id=3), User(u_id=2005, name=‘user5‘, age=22, favor_id=2), User(u_id=2006, name=‘user6‘, age=28, favor_id=3), User(u_id=2007, name=‘user7‘, age=19, favor_id=1), User(u_id=2008, name=‘user8‘, age=32, favor_id=4), User(u_id=2009, name=‘user9‘, age=129, favor_id=3), ]) session.commit()
ret = session.query(User).join(Favor).filter(User.name == ‘user3‘).first() # SELECT user.id AS user_id, user.u_id AS user_u_id, user.name AS user_name, user.age AS user_age, user.favor_id AS user_favor_id # FROM user INNER JOIN favor ON favor.id = user.favor_id # WHERE user.name = %(name_1)s ret2 = session.query(Favor).filter(Favor.id == ret.favor_id).first() print(ret2.type)
ret = session.query(User).filter(User.name == ‘user3‘).first().favor.type # first() 获取到一个User对象，.favor获取到对应表中的Favor对象，.type 获取到该对象的type字段 print(ret)
f_id = session.query(Favor).filter(Favor.type == ‘favor-03‘).first() print(f_id.id) ret_lst = session.query(User.name).filter(User.favor_id == f_id.id).all() print(ret_lst)
ret_lst = session.query(Favor).filter(Favor.type == ‘favor-03‘).first().xxx # .first() 获取到一个Favor对象，.xxx获取到对应表中的User对象列表，.name 获取该对象的name字段 for ret in ret_lst: print(ret.name)
- 重点：
#!/usr/bin/env python # -*- encoding:utf-8 -*- import sqlalchemy from sqlalchemy import DATE from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker from sqlalchemy import ForeignKey from sqlalchemy.orm import relationship engine = create_engine("mysql+pymysql://root:@127.0.0.1/db01?charset=utf8") Base = declarative_base() # 创建表结构 # 重点解释 # 因为此时，同一张表的2个字段（收件地址，付款地址），都对应到了另外一张表中（地址表） # 那么在给地址表插入数据时会报错：不清楚该条数据到底应该对应到“收件地址”还是“付款地址” # foreign_keys=[billing_address_id] # 告诉orm ，若要正向查询虚拟字段 billing_address， 则去billing_address_id字段对应的外键去查 # 告诉orm ，若要正向查询虚拟字段 shipping_address， 则去shipping_address_id字段对应的外键去查 class Customer(Base): __tablename__ = ‘customer‘ id = Column(Integer, primary_key=True) name = Column(String(32)) 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], backref=‘xxx‘) shipping_address = relationship("Address", foreign_keys=[shipping_address_id], backref=‘yyy‘) class Address(Base): __tablename__ = ‘address‘ id = Column(Integer, primary_key=True) street = Column(String(32)) city = Column(String(32)) state = Column(String(32)) # Base.metadata.drop_all(bind=engine) # Base.metadata.create_all(bind=engine) # 插入数据 Session_cls = sessionmaker(bind=engine) session = Session_cls() session.add_all([ Address(street=‘street-bbb‘, city=‘city-bbb‘, state=‘state-bbb‘), Address(street=‘street-ccc‘, city=‘city-ccc‘, state=‘state-ccc‘), Address(street=‘street-ddd‘, city=‘city-ddd‘, state=‘state-ddd‘) ]) session.add_all([ Customer(name=‘user1‘, billing_address_id=1, shipping_address_id=2), Customer(name=‘user2‘, billing_address_id=2, shipping_address_id=3), ]) session.commit() # 正向查询，user2 的付款地址是多少 ret = session.query(Customer).filter(Customer.name == ‘user2‘).first().billing_address.street print(ret) # 反向查询，付款地址(billing_address)为street-ccc 有那些人 ret_lst1 = session.query(Address).filter(Address.street == ‘street-ccc‘).first().xxx for ret in ret_lst1: print(ret.name) # 反向查询，邮寄地址(shipping_address)为street-ccc 有那些人 ret_lst2 = session.query(Address).filter(Address.street == ‘street-ccc‘).first().yyy for ret in ret_lst2: print(ret.name)
原文：http://www.cnblogs.com/qiaogy/p/6295700.html