Python操作SQLAlchemy之连表操作-白红宇

Python操作SQLAlchemy之连表操作

阅读量：5117 次

发布时间：2019-06-13

本文共 6786 字，大约阅读时间需要 22 分钟。

多对一连表操作

首先有两个知识点:

改变数据输出的方式:可以在表的类中定义一个特殊成员:__repr__,return一个自定义的由字符串拼接的数据连接方式.

数据库中表关系之间除了MySQL中标准的外键(ForeignKey)之外,还可以创建一个虚拟的关系,比如group = relationship("Group",backref='uuu'),一般此虚拟关系与foreignkey一起使用.

需求:

用户组,有sa,dba组

用户,用户只能属于一个用户组

那么从需求可以看出来,是一个一对多的

遍历表中数据

接着,我们先来看下数据库中的表结构:

from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship engine = create_engine("mysql+pymysql://root:7ujm8ik,@192.168.4.193:3306/testsql", max_overflow=5) Base = declarative_base() #一对多 class Group(Base): __tablename__ = 'group' nid = Column(Integer,primary_key=True,autoincrement=True) caption = Column(String(32)) class User(Base): __tablename__ = 'user' nid = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(32)) group_id = Column(Integer,ForeignKey('group.nid')) ###虚拟创建关系,relationship 一般是跟foreginkey 在一起使用 group = relationship("Group",backref='uuu') #自定义输出方式 def __repr__(self): temp = '%s-%s:%s'%(self.nid,self.name,self.group_id) return temp # 生成数据库表 Base.metadata.create_all(engine) ##插入数据 # session.add_all([ # Group(caption='DBA'), # Group(caption='SA') # ]) # session.add_all([ # User(name='alex',group_id=1), # User(name='alex2',group_id=1), # User(name='cc',group_id=2) # ]) # # session.commit()

查询user表中的所有数据:

sql=session.query(User).join(Group,isouter=True) print(sql) ret =session.query(User).join(Group,isouter=True).all() print(ret)

结果:

SELECT "user".nid AS user_nid, "user".name AS user_name, "user".group_id AS user_group_id FROM "user" LEFT OUTER JOIN "group" ON "group".nid = "user".group_id [3-alex:1, 4-alex2:1, 5-cc:2]

无虚拟关系的原始查询方式

需求:查询user表中姓名并且显示各自的所属组

ret = session.query(User.name,Group.caption).join(Group,isouter=True).all() print(ret)

结果:

[('alex', 'DBA'), ('alex2', 'DBA'), ('cc', 'SA')]

虚拟关系的查询方式

正向查询

需求:查询user表中所有数据,并且显示对应的用户组表中的数据.

首先肯定要设定一个虚拟关系啦,group = relationship("Group",backref='uuu')

看下代码:

#正向查询ret = session.query(User).all() for obj in ret: # #obj 代指user表的每一行数据 # #obj.group 代指group对象 print(obj.nid,obj.name,obj.group_id,obj.group,obj.group.nid,obj.group.caption)

结果:

3 alex 1 <__main__.Group object at 0x10387eba8> 1 DBA 4 alex2 1 <__main__.Group object at 0x10387eba8> 1 DBA 5 cc 2 <__main__.Group object at 0x10387ed68> 2 SA

注意:多对一正向查询,一条命令即可,直接看对象中的属性即可

反向查询原始方式

需求:查询用户组表中属于DBA组的用户名

ret=session.query(User.name,Group.caption).join(Group,isouter=True).filter(Group.caption=='DBA').all() print(ret)

结果:

[('alex', 'DBA'), ('alex2', 'DBA')]

虚拟关系反向查询方式

需求:查询用户组中属于DBA组的用户名

#反向查询#group中得到一个对象 obj=session.query(Group).filter(Group.caption=='DBA').first() print(obj.nid) print(obj.caption) #连接到虚拟关系中backref设定的uuu print(obj.uuu)

结果:

1DBA[3-alex:1, 4-alex2:1]

注意:多对一反向查询,需要遍历对象属性

多对多连表操作

需求以及数据库结构

需求:

三张表:

主机表:包括nid hostname port ip

管理员表:包括:nid username

主机对应管理员表: nid 主机id,管理员id

一个管理员帐号(比如root),可以关联多台服务器,一个服务器也可以有多个管理员帐号

先来看下数据结构吧:

from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship engine = create_engine("mysql+pymysql://root:7ujm8ik,@192.168.4.193:3306/testsql", max_overflow=5) Base = declarative_base() #多对多 class HostToHostUser(Base): __tablename__ = 'host_to_host_user' nid = Column(Integer, primary_key=True,autoincrement=True) host_id = Column(Integer,ForeignKey('host.nid')) host_user_id = Column(Integer,ForeignKey('host_user.nid')) #多对多操作 host = relationship('Host',backref='h') host_user = relationship('HostUser',backref='u') class Host(Base): __tablename__ = 'host' nid = Column(Integer, primary_key=True,autoincrement=True) hostname = Column(String(32)) port = Column(String(32)) ip = Column(String(32)) ####最简单的方式,添加此行就行: host_user=relationship('HostUser',secondary=HostToHostUser.__table__,backref='h') class HostUser(Base): __tablename__ = 'host_user' nid = Column(Integer, primary_key=True,autoincrement=True) username = Column(String(32)) def init_db(): Base.metadata.create_all(engine) # init_db() def drop_db(): Base.metadata.drop_all(engine) Session = sessionmaker(bind=engine) session = Session() #======多对多操作 # session.add_all([ # Host(hostname='c1',port='22',ip='1.1.1.1'), # Host(hostname='c2',port='22',ip='1.1.1.2'), # Host(hostname='c3',port='22',ip='1.1.1.3'), # Host(hostname='c4',port='22',ip='1.1.1.4'), # Host(hostname='c5',port='22',ip='1.1.1.5'), # ]) # session.commit() # session.add_all([ # HostUser(username='root'), # HostUser(username='db'), # HostUser(username='nb'), # HostUser(username='sb'), # ]) # session.commit() # session.add_all([ # HostToHostUser(host_id=1,host_user_id=1), # HostToHostUser(host_id=1,host_user_id=2), # HostToHostUser(host_id=1,host_user_id=3), # HostToHostUser(host_id=2,host_user_id=2), # HostToHostUser(host_id=2,host_user_id=4), # HostToHostUser(host_id=2,host_user_id=3), # ]) # session.commit()

无虚拟关系的原始方式

需求:查询主机C1的管理员帐号

#1.先在host表中查询c1的nidhost_obj = session.query(Host).filter(Host.hostname=='c1').first() #2.查询hosttohostuer表中的所有host_id等于c1的nid的对应的host_user_id host_2_host_user = session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_id==host_obj.nid).all() # print(host_2_host_user) r=zip(*host_2_host_user) # print(list(list(r)[0])) #通过查到的host_user_id查询hostuser表中的对应的管理员用户名 users = session.query(HostUser.username).filter(HostUser.nid.in_(list(list(r)[0]))).all() print(users)

结果:

[('root',), ('db',), ('nb',)]

是不是很麻烦?

虚拟关系的查询

需求:同上,查询主机C1的管理员帐号

# 1.反向查找,查询host表中c1的信息,会得到一个对象,对象中存在一个已经设置好的虚拟关系:hhost_obj = session.query(Host).filter(Host.hostname == 'c1').first() #2.正向查找,遍历对象属性 for item in host_obj.h: print(item.host_user.username)

结果:

rootdbnb

注意:多对多的话,正反查询都是遍历对象中的属性

同一需求最简单的方式

需求还是同上:查询主机C1的管理员帐号

需要在两张表的一张表中加一条host_user=relationship('HostUser',secondary=HostToHostUser.__table__,backref='h'),我加到了host表中

#最简单的查询方式:host_obj = session.query(Host).filter(Host.hostname == 'c1').first() print(host_obj.host_user) for item in host_obj.host_user: print(item.username)

结果:

[<__main__.HostUser object at 0x103778710>, <__main__.HostUser object at 0x103778d68>, <__main__.HostUser object at 0x103778e10>] root db nb

多表查询没有外键

from sqlalchemy import and_

两张表

q = db.session.query(User, UserIn).join(UserIn, and_(User.id==UserIn.user_id, User.id == 4)).all()

三张表

q = db.session.query(User, UserIn, Supplier).join(UserIn, and_(User.id==UserIn.user_id, User.id == 4)).join(Supplier, and_(UserIn.supplier_id==Supplier.id)).all()

SELECT tb_supplier., tb_user., tb_user_belonging.* FROM tb_supplier, tb_user left JOIN tb_user_belonging ON tb_user.id = tb_user_belonging.user_id AND tb_user_belonging.supplier_id = tb_supplier.id AND tb_user.id = 4