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:[email protected]: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: