sqlalchemy--外键
一、简介
SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上，使用关系对象映射进行数据库操作，简言之便是：将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果
数据库的连接方式：
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] sqlite sqlite:///test_s1.db
以pymysql为例：
create_engine(
"mysql+pymysql://root:123@127.0.0.1:3306/t1?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置）)二、实战
1、创建表, users和depart
# -*- coding:utf-8 -*- from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import create_engine, Column, String, Integer, ForeignKey engine = create_engine("sqlite:///test_s1.db") # 创建表 Base = declarative_base() class Depart(Base): __tablename__ = 'depart' id = Column(Integer, primary_key=True) title = Column(String(32), index=True, nullable=False) class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(32), index=True, nullable=False) depart_id = Column(Integer, ForeignKey('depart.id')) def create_all(): Base.metadata.create_all(engine) def drop_all(): Base.metadata.drop_all(engine) if __name__ == '__main__': # 创建所有表 create_all() # 删除表 # drop_all()
# users
1,孔扎根,1
2,李小龙,2
3,黄飞鸿,3
4,孙阿瞒,3
# depart
1,管理员
2,普通用户
3,匿名用户
2、单表查询
需要修改原代码， 加入数据库连接功能
…… # 创建数据库连接 session =sessionmaker(bind=engine)() if __name__ == '__main__': ……
# query中填写查询对像 ret = session.query(Users).all() for r in ret: print(r.id, r.name, r.depart_id)
session.close() 结果： 1 孔扎根 1 2 李小龙 2 3 黄飞鸿 3 4 孙阿瞒 3
3、多表查询
query中罗列查询对象，不分先后顺序。
join表示被关联对象， 之后紧随关联条件。默认是inner join, 可以使用isouter参数改为左连接
if __name__ == '__main__': ret = session.query(Users.id, Users.name, Depart.title).join(Depart, Users.depart_id == Depart.id, isouter=True).all() for r in ret: # print(r) print(r.id, r.name, r.title) session.close()
结果：
1 孔扎根 管理员
2 李小龙 普通用户
3 黄飞鸿 匿名用户
4 孙阿瞒 匿名用户
4、relationship
可以实现查询某表时带出关联表的信息
from sqlalchemy.orm import relationship # user表做如下修改 class Users(Base): …… dp = relationship('Depart', backref='pers') if __name__ == '__main__': ret = session.query(Users).all() for r in ret: print(r.id, r.name,r.dp) 结果： 1 孔扎根 <__main__.Depart object at 0x00000209ECD3A6C8> 2 李小龙 <__main__.Depart object at 0x00000209ECD3AF48> 3 黄飞鸿 <__main__.Depart object at 0x00000209ECD3F388> 4 孙阿瞒 <__main__.Depart object at 0x00000209ECD3F388>
查询Users表的dp字段时，由于relationship的原因，自动关联了指定的表Depart(两表自动关联默认使用的是外键)，可以使用r.dp.title获取Depart中实际的数据。
问题来了， relationship中的backref是个什么梗？ 它是个被关联，即查询Depart表时，如果查询了pers字段，则Users表会被自动关联
if __name__ == '__main__': ret = session.query(Depart).all() for r in ret: print(r.title, r.pers)
# 结果 管理员 [<__main__.Users object at 0x000001EFB3EF6A88>] 普通用户 [<__main__.Users object at 0x000001EFB3F03288>] 匿名用户 [<__main__.Users object at 0x000001EFB3F03688>, <__main__.Users object at 0x000001EFB3F03708>]
结论： 在Depart中指定r.pers时，Depart与Users表自动关联， 此时pers为外键关联后的Users对象。
5、relationship高级用法
1) 在 Depart 表中添加一个权限， 在User表中添加多个人员
先创建一个权限， 再创建一个用户
if __name__ == '__main__': ……
# 提交之后 part对像p1便产生了新的 id p1 = Depart(title='路人') session.add(p1) session.commit() # 新建的user对象中的外键 depart_id但可引用它 u1 = Users(name='田小鸭', depart_id=d1.id) session.add(u1) session.commit() session.close()
2） 先创建一个权限， 再创建多个用户。 利用depart的反向关系pers， 同时补充定义带外键的user数据， 同时提交到库中。
if __name__ == '__main__': …… d1 = Depart(title='路人') d1.pers = [Users(name='田小鸭'), Users(name='杨小路')] session.add(d1) session.commit() session.close()
3）先定义用户， 利用其relationship的依赖， 分别提交dp和u1
if __name__ == '__main__': u1 = Users(name='王小猛', dp=Depart(title='路人')) session.add(u1) session.commit() session.close()
