Python之路第十三天，高级(7)-详述数据库一对多，多对多表关系的设计以及如何查询...

一对多表设计和查询方法

#!/usr/bin/env python3 # Author: Zhangxunan from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, Table from sqlalchemy.orm import sessionmaker, relationship # 连接数据库 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/test", max_overflow=5) # 创建基类，所有创建表的类都要继承这个基类 Base = declarative_base() # 创建会话，通过会话去操作数据库 Session = sessionmaker(bind=engine) session = Session() # 一对多关系（一个组可以有很多人，一个人只能属于一个组） class Group(Base): __tablename__ = 'groups' nid = Column(Integer, primary_key=True,autoincrement=True) caption = Column(String(32)) class User(Base): __tablename__ = 'users' nid = Column(Integer, primary_key=True,autoincrement=True) username = Column(String(32)) group_id = Column(Integer, ForeignKey('groups.nid')) group = relationship("Group", backref='user') def init_db(): """ 通过上面的类创建表 :return: None """ Base.metadata.create_all(engine) def drop_db(): """ 删除表 :return: None """ Base.metadata.drop_all(engine) # 插入数据 # session.add_all([ # Group(caption='SA'), # Group(caption='DEV'), # Group(caption='TEST'), # Group(caption='DBA') # ]) # session.commit() # session.add_all([ # User(username='tom', group_id=1), # User(username='jerry', group_id=1), # User(username='jack', group_id=2), # User(username='rose', group_id=3), # User(username='eric', group_id=4), # User(username='james', group_id=4) # ]) # # session.commit() # 输出原生sql sql = session.query(User.username, Group.caption).join(Group, isouter=True).filter(User.username == 'jack') print(sql) # 查询jack属于哪个组 ret = session.query(User.username, Group.caption).join(Group, isouter=True).filter(User.username == 'jack').first() print(ret) # 输出原生sql sql = session.query(User.username, Group.caption).join(Group, isouter=True) print(sql) # 查询所有用户分别属于哪个组 ret = session.query(User.username, Group.caption).join(Group, isouter=True).all() print(ret) # 输出原生sql sql = session.query(User.username, Group.caption).join(Group, isouter=True).filter(Group.caption == 'SA') print(sql) # 查询SA组有哪些人 ret = session.query(User.username, Group.caption).join(Group, isouter=True).filter(Group.caption == 'SA').all() print(ret) # 正向查询 (group = relationship("Group", backref='user'),通过这一句建立关系，然后可以通过这种关系查询更方便) # 查询jack用户属于哪个组 ret = session.query(User).filter(User.username == 'jack').first() print(ret.username, ret.group.caption) # 查询所有用户分别属于哪个组 ret = session.query(User).all() for obj in ret: # obj代指user表的每一行数据 # obj.group代指group对象， print(obj.nid, obj.username, obj.group.caption) # 反向查询 # 查询SA组有哪些人 obj = session.query(Group).filter(Group.caption == 'SA').first() # obj 指代groups表里组名为SA的那一行数据 # obj.user 指代users对象（组为SA的用户数据） for item in obj.user: print(item.username, end=' ')

多对多的表设计和查询方法

#!/usr/bin/env python3 # Author: Zhangxunan from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index, Table from sqlalchemy.orm import sessionmaker, relationship # 连接数据库 engine = create_engine("mysql+pymysql://root:123456@127.0.0.1:3306/test", max_overflow=5) # 创建基类，所有创建表的类都要继承这个基类 Base = declarative_base() # 创建会话，通过会话去操作数据库 Session = sessionmaker(bind=engine) session = Session() # 多对多(需要第三张表，专门用来存关系,一个用户可以登录多个服务器，一个服务器上可以有多个用户) 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')) 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='host') class HostUser(Base): __tablename__ = 'host_user' nid = Column(Integer, primary_key=True,autoincrement=True) username = Column(String(32)) def init_db(): """ 通过上面的类创建表 :return: None """ Base.metadata.create_all(engine) def drop_db(): """ 删除表 :return: None """ Base.metadata.drop_all(engine) # session.add_all([ # Host(hostname='web1', port='22', ip='192.168.1.65'), # Host(hostname='web2', port='22', ip='192.168.1.66'), # Host(hostname='web3', port='22', ip='192.168.1.67'), # Host(hostname='web4', port='22', ip='192.168.1.68'), # Host(hostname='web5', port='22', ip='192.168.1.69'), # ]) # session.commit() # session.add_all([ # HostUser(username='root'), # HostUser(username='tom'), # HostUser(username='jerry'), # HostUser(username='jack'), # HostUser(username='rose'), # ]) # 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() # 需求：获取web1服务器中的所有用户 # 原始方式需要经过三步： # 第一步：查询web1的服务器ID host_obj = session.query(Host).filter(Host.hostname == 'web1').first() print(host_obj.nid) # 第二步：查询第三张表（关系表）查询所有用户的ID host_id == host_obj.nid host_2_host_user = session.query(HostToHostUser.host_user_id).filter(HostToHostUser.host_id == host_obj.nid).all() uids = list(zip(*host_2_host_user))[0] print(uids) # 第三步：根据用户ID查找所有用户 users = session.query(HostUser.username).filter(HostUser.nid.in_(uids)).all() users = [x[0] for x in users] print(users) # 当然也可以把上面三步合成一个sql，但太长了 # 正向查询 # host_user = relationship('HostUser', secondary=lambda: HostToHostUser.__table__, backref='host') # 上面这个话的意思是说给通过第三张表HostToHostUser给HostUser表建立关系 # host_obj是一个对象，是表示hostname=='web1'的那一行数据 host_obj.nid为web1的nid host_obj = session.query(Host).filter(Host.hostname == 'web1').first() for item in host_obj.host_user: print(item.username, end=' ') # 需求2：获取tom用户可以登录哪些服务器 # 原始方式需要经过三步 # 第一步：查询tom用户的id user_obj = session.query(HostUser).filter(HostUser.username == 'tom').first() print(user_obj.nid) # 第二步：查询第三张表(关系表)，查询所有服务器的ID, 条件是 user_obj.nid == host_user_id host_ids = session.query(HostToHostUser.host_id).filter(HostToHostUser.host_user_id == user_obj.nid).all() host_ids = list(zip(*host_ids))[0] print(host_ids) # 第三步: 根据服务器ID查找服务器hostname hosts = session.query(Host).filter(Host.nid.in_(host_ids)).all() hosts = [x.hostname for x in hosts] print(hosts) # 反向查询 # host_user = relationship('HostUser', secondary=lambda: HostToHostUser.__table__, backref='host') # 上面这个话的意思是说给通过第三张表HostToHostUser给HostUser表建立关系 host_user_obj = session.query(HostUser).filter(HostUser.username == 'tom').first() for item in host_user_obj.host: print(item.hostname, end=' ') print()

转载于:https://www.cnblogs.com/zhangxunan/p/5728712.html

相关资源：JAVA上百实例源码以及开源项目