Python操作SQLAlchemy之连表操作
多对一连表操作
首先有两个知识点:
- 改变数据输出的方式:可以在表的类中定义一个特殊成员:__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
##插入数据
# 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)
结果:
1
DBA
[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的nid
host_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的信息,会得到一个对象,对象中存在一个已经设置好的虚拟关系:h
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
#2.正向查找,遍历对象属性
for item in host_obj.h:
print(item.host_user.username)
结果:
root
db
nb
注意:多对多的话,正反查询都是遍历对象中的属性
同一需求最简单的方式
需求还是同上:查询主机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
