Part01、sqlalchemy 使用
一、ORM
连表
一对多
1、创建表,主动指定外键约束。
2、操作。
类:repr
一对多:
单表
连表
session.query(表1).join(表2).all( )
多对多:
1、创建表,额外的关系表。
2、filter( ) _in( ) 把结果可传进去。
3、relationship
A
A_B ==> 【关系表】 relationship 字段 和 primary_key 字段常放在这里。
B PS:通过A 找到 A_B 关系的时候,是反向找;
而通过关系找到某一个,是正向找。
提示:1、这个关系表不一定要放在A_B 关系表中,其他地方也能放。比如、可以放在A中。
2、关系表不影响具体的代码。
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:123qwe@192.168.1.100:3306/s08day11", 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)
username =Column(String(32))
group_id = Column(Integer,ForeignKey('group.nid')) #设置外键约束
#与生成表结构无关,仅用于查询方便
_group_ = relationship("Favor", backref='pers')
# (2)通过对象方式
#这样看起来不方便,可以自己进行自定制,只是让 print 时候好看些。
def __repr__(self): #这里定义什么就可以返回并看到什么。因为在sqlalchemy 中使用了 __repr__()内部规定的方法。
temp = '%s -- %s: %s' % (self.nid,self.username,self.group_id)
return temp
def init_db():
Base.metadata.create_all(engine)
def drop_db():
Base.metadata.drop_all(engine)
# init_db() #创建数据库
Session=sessionmaker(bind=engine) #绑定元信息
session=Session()
####### 添加 插入 数据
session.add(Group(caption='DBA'))
session.add(Group(caption='SA'))
session.add(Group(caption='SA'))
#session.commit()
session.add_all([
User(username='alex',group_id=1),
User(username='eric',group_id=2),
User(username='jack',group_id=3)
])
#session.commit()
############# 单表查询 #################
#1、只是获取 user 表的 用户信息。(1)通过对象方式
ret= session.query(User).filter(User.username == 'alex').all()
print(ret) #ret 有多个对象
obj=ret[0] #获取到了对象
print(obj.nid)
print(obj.username)
print(obj.group.id)
print('================================')
#2、获取所有 user 表用户信息。(2) 通过映射方式
ret=session.query(User.username).all()
print(ret)
############## 连表查询 ###############
sql=session.query(User).join(Group,isouter=True) #isouter 为True 打印出sql语句.
print(sql)
print('=================================')
ret=session.query(User).join(Group,isouter=True).all() #join 联表操作。
print(ret)
print('=================================')
#如果想把两个【表】的内容都拿到的话。
ret=session.query(User,Group).join(Group,isouter=True).all()
print(ret)
ret=session.query(User.username,Group.caption).join(Group,isouter=True).all()
print(ret) #在列表中,有一个元祖,元祖中存放着 User表对象和 Group 表
执行结果:
通过对象的方式展示出来了,如果不想通过对象的话,
[1 -- alex: 1]
1
============================================
[('alex',), ('eric',), ('jack',)]
SELECT "user".nid AS user_nid, "user".username AS user_username, "user".group_id AS user_group_id
FROM "user" LEFT OUTER JOIN "group" ON "group".nid = "user".group_id
============================================
[1 -- alex: 1, 2 -- eric: 2, 3 -- jack: 3]
============================================
[(1 -- alex: 1, <__main__.Group object at 0x000001A19CD9A6D8>), (2 -- eric: 2, <__main__.Group object at 0x000001A19CD9A780>), (3 -- jack: 3, <__main__.Group object at 0x000001A19CD9A828>)]
[('alex', 'DBA'), ('eric', 'SA'), ('jack', 'SA')]
上述中, 通过对象的方式展示出来了,如果不想通过对象的话,
这样的方式很麻烦, 所以 与生成表结构无关,仅用于查询方便
group = relationship("Favor", backref='pers')
于是使用 group=relationship('Group',backref='uuu') 就可以了。
2、使用 relationship 方式不用联表,sqlalchemy 内部帮你联表操作了。
【一对多】数据库数据如下:
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:123qwe@10.10.31.99:3306/s08day11", 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)
username =Column(String(32))
group_id = Column(Integer,ForeignKey('group.nid')) #设置外键约束
group=relationship('Group',backref='uuu')
# 释义:这种 backref='uuu' 会在Group表中加上一个 uuu,直接 Group.uuu 表示找到跟这个组对应的所有的用户。
def __repr__(self):
temp = '%s -- %s: %s' % (self.nid,self.username,self.group_id)
return temp
Session=sessionmaker(bind=engine)
session=Session()
Base.metadata.create_all(engine) #创建表
#1、新方式【正向查询】
ret = session.query(User).all()
for obj in ret:
# obj 代表user 表的每一行数据
# obj.group 代指 group 对象。
print(obj.nid, obj.username,'======', obj.group_id ,obj.group_id, obj.group.caption,':=',obj.group )
print('=================','\n')
# 2、【原始方式】; 查询所有DBA的信息。
ret = session.query(User.username,Group.caption).join(Group, isouter=True).filter(Group.caption == 'DBA').all()
print(ret)
print('=================','\n')
# 3、新方式 【返向查询】
obj= session.query(Group).filter(Group.caption=='DBA').first()
print(obj.nid)
print(obj.caption)
print(obj.uuu) #通过反向查找Group表中。找到了关于DBA的组的所有user成员,
# 并以列表返回 [1 -- alex: 1, 2 -- eric: 2, 3 -- jack: 3]
执行结果:
1 alex ====== 1 1 DBA := <__main__.Group object at 0x0000019AB50FA898>
2 eric ====== 2 2 SA := <__main__.Group object at 0x0000019AB50FAA58>
3 jack ====== 3 3 SA := <__main__.Group object at 0x0000019AB50FAC18>
4 alex1 ====== 1 1 DBA := <__main__.Group object at 0x0000019AB50FA898>
=================
[('alex', 'DBA'), ('alex1', 'DBA')]
=================
1
DBA
[1 -- alex: 1, 4 -- alex1: 1]
- 正向查找和反向查找
一般情况下,foreignKey和relationship 是在一起的。
当我们通过 Group 表找User表的时候,backref=uuu,没有在此表中定义,所以通过Group 查找User表的时候,为反向查找。
如下图展示:
- 写成 Table对象也可以。
过程:由 class类转换为 Table对象 ,下面例子是没有写类 直接写 Table对象也可以,一模一样的。
from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey
metadata = MetaData()
user = Table('user', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(20)),
)
Host= Table('host', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(20)),
)
engine = create_engine("mysql+mysqldb://root:123@127.0.0.1:3306/s11", max_overflow=5)
metadata.create_all(engine)
3、多对多查询
想要操作多对多,就要有第三张表的介入。
流程如图:
数据库展示:
######## 多表查询 ###########
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:123qwe@192.168.1.100:3306/day13", max_overflow=5)
Base = declarative_base()
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))
class HostUser(Base):
__tablename__ = 'host_user'
nid = Column(Integer, primary_key=True,autoincrement=True)
username = Column(String(32))
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'))
##### 生产 库表 #######
def init_db():
Base.metadata.create_all(engine)
# init_db()
'''
######生成数据 #######
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() #生成数据按钮
'''
Session=sessionmaker(bind=engine)
session=Session()
#>>>需求来了, 获取c1主机中所有的用户??
1、使用【原始方式】获取 。
host_obj=session.query(Host).filter(Host.hostname == 'c1').first()
print(host_obj.nid) #第一步要找的唯一的【主机的ID】。
#得到了结果 ———>ID为1的值。
2、在第三张关系表中--> 利用【主机host的ID】找到了所有的对应的【用户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) #在关系表中拿到了C1【主机的ID】对应的 【全部用户ID】。
#执行结果:————> [(1,),(2,),(3),]的列表。
3、根据【用户user的ID】找到所有用户。
r=zip(*host_2_host_user)
# print(list(r)[0]) #目的:转换 [(1,),(2,),(3),] --->为[1,2,3]
# [1,2,3]
users=session.query(HostUser.username).filter(HostUser.nid.in_(list(r)[0])).all()
print(users)
#执行结果:
#[('root',), ('db',), ('nb',)]
########################################
以上的查询方法太low了,其实可以一句话进行才查找。
- 多表查询(三)、
############## 多表查询【高逼格】方法 ###################
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:123qwe@192.168.1.100:3306/day13", max_overflow=5)
Base = declarative_base()
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))
class HostUser(Base):
__tablename__ = 'host_user'
nid = Column(Integer, primary_key=True,autoincrement=True)
username = Column(String(32))
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')
Session=sessionmaker(bind=engine)
session=Session()
########查询结果#######:
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
print(host_obj)
print(host_obj.nid)
print(host_obj.hostname)
print('=========================','\n')
print(host_obj.h) #第三张表对应的对象。
print('=========================','\n')
for item in host_obj.h:
print(item.host_user,item.host_user.nid,item.host_user.username)
执行结果:
<__main__.Host object at 0x000001447FC99B70>
1
c1
=========================
[<__main__.HostToHostUser object at 0x000001447FC99828>, <__main__.HostToHostUser object at 0x000001447FC99EB8>, <__main__.HostToHostUser object at 0x000001447FC99F28>]
=========================
<__main__.HostUser object at 0x000001447FCAE4E0> 1 root
<__main__.HostUser object at 0x000001447FC64C50> 2 db
<__main__.HostUser object at 0x000001447FC99080> 3 nb
4、更简单的方式:多对多查询
A
AB ==》关系表 foreign 和 relationship
B
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:123qwe@192.168.1.100:3306/day13", max_overflow=5)
Base = declarative_base()
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))
class HostUser(Base):
__tablename__ = 'host_user'
nid = Column(Integer, primary_key=True,autoincrement=True)
username = Column(String(32))
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')
Session=sessionmaker(bind=engine)
session=Session()
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
print(host_obj)
print(host_obj.nid)
print('==============================')
print(host_obj.h) #第三张表对应的对象。
print('===============================','\n')
for item in host_obj.h:
print(item.host_user,item.host_user.nid,item.host_user.username)
执行结果:
<__main__.Host object at 0x000001FDBBA4E630>
1
==============================
[<__main__.HostToHostUser object at 0x000001FDBBA4E2B0>, <__main__.HostToHostUser object at 0x000001FDBBA4E940>, <__main__.HostToHostUser object at 0x000001FDBBA4E9B0>]
===============================
<__main__.HostUser object at 0x000001FDBBA6C048> 1 root
<__main__.HostUser object at 0x000001FDBBA6C208> 2 db
<__main__.HostUser object at 0x000001FDBBA6C3C8> 3 nb
5、更更 简单的方式:多对多查询
A ==》foreign 和 relationship
AB关系表 只需要进行简单的定义。
B
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:123qwe@192.168.1.100:3306/day13", max_overflow=5)
Base = declarative_base()
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=lambda :HostToHostUser.__table__,backref='h')
# 如果 有lambda的话,则可以关系对应表可以不用 放在上面,可以自由放置。放下面就会报错的;无lambda 的话 ,关系对应表应该放在上面。
无lambda 的话 ,关系对应表应该放在上面。
class HostUser(Base):
__tablename__ = 'host_user'
nid = Column(Integer, primary_key=True,autoincrement=True)
username = Column(String(32))
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'))
Session=sessionmaker(bind=engine)
session=Session()
host_obj = session.query(Host).filter(Host.hostname == 'c1').first()
print(host_obj)
print(host_obj.nid)
print('==============================')
print(host_obj.host_user)
# print(host_obj.h) #第三张表对应的对象。
print('===============================')
for item in host_obj.host_user:
print(item.username)
执行结果:
<__main__.Host object at 0x000002E592B8F860>
1
==============================
[<__main__.HostUser object at 0x000002E592B8F550>, <__main__.HostUser object at 0x000002E592B8F518>, <__main__.HostUser object at 0x000002E592B8F7B8>]
===============================
root
db
nb
GitHub:https://github.com/ju-do-it
个人博客:https://zhangju.lookdoc.cn
博客园:https://www.cnblogs.com/zhangju
Copyright ©2020 Ju-do-it
【转载文章务必保留出处和署名,谢谢!】
个人博客:https://zhangju.lookdoc.cn
博客园:https://www.cnblogs.com/zhangju
Copyright ©2020 Ju-do-it
【转载文章务必保留出处和署名,谢谢!】