一对多表操作

一对多表操作

一、创建建表

# model.py
import datetime
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index
from sqlalchemy.orm import relationship

Base = declarative_base()

# make_declarative_base
class Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)
    caption = Column(String(50), default='篮球')

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    # hobby指的是tablename而不是类名，uselist=False
    hobby_id = Column(Integer, ForeignKey("hobby.id"))

    # 跟数据库无关，不会新增字段，只用于快速链表操作
    # 类名，backref用于反向查询
    hobby = relationship('Hobby', backref='perss')

    def __repr__(self):
        return self.name


def init_db():
    """
    根据类创建数据库表
    :return:
    """
    engine = create_engine(
        "mysql+pymysql://root:root@127.0.0.1:3306/db_flask?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.create_all(engine)


def drop_db():
    """
    根据类删除数据库表
    :return:
    """
    engine = create_engine(
        "mysql+pymysql://root:root@127.0.0.1:3306/db_flask?charset=utf8",
        max_overflow=0,  # 超过连接池大小外最多创建的连接
        pool_size=5,  # 连接池大小
        pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
        pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
    )

    Base.metadata.drop_all(engine)


if __name__ == '__main__':
    # 创建表
    init_db()

    # 删除表
    # drop_db()

二、添加数据

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from model import Users, Hobby, Person

engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/db_flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()

# 1. 添加数据

# 方式一
session.add_all([
    Hobby(caption='乒乓球'),
    Hobby(caption='羽毛球'),
    Person(name='张三', hobby_id=1),
    Person(name='李四', hobby_id=2),
])

# 方式二
# Hobby 爱好会自动插入一个爱好，即使存在也会插入数据
# 正向查询字段hoppy添加
person = Person(name='张三', hobby=Hobby(caption='跑腿'))
session.add(person)

# 方式三

hb = Hobby(caption='羽毛球')  
# 通过反向查询字段，添加数据perss 是关联字段
hb.perss = [Person(name='文飞'), Person(name='波波')]

session.add(hb)
session.commit()

三、正、反向查询

3.1 正向查询

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from model import Users, Hobby, Person

engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/db_flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()

# 2. relationship正向查询
person = session.query(Person).first()
print(person.name)

# 正向查询, 当前类中有外键成为正向查询，正向查询只会有一个与其对应的爱好（单条记录）
print(person.hobby)
print(person.hobby.caption)

3.1 反向查询

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from model import Users, Hobby, Person

engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/db_flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()

# 3. relationship 反向查询
hobby = session.query(Hobby).filter(Hobby.id == 1).first()
print(hobby.caption)
# 对象列表，反向查询的结果是一个类表对象（因为是一对多的关系，在person可能有多个爱好被被关联），所以要使用循坏一个个遍历
print(hobby.perss)
print(hobby.perss[0].nid, hobby.perss[0].name)

3.3 断关联连表查询

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from model import Users, Hobby, Person

engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/db_flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()

# 3. 断关联
# 断关联：是两张表中没有设置外键关联，所以只能通过逻辑进行关联，使用.join( Person.hobby_id == Hobby.id)
# Person.hobby_id == Hobby.id 断关联这个条件必须要写，通过join实现表之间的关联
# isouter=True 左关联,False为 inner join 等值关联
# query中的表和join表来调换表的查询关系
ret = session.query(Person).join(Hobby, Person.hobby_id == Hobby.id, isouter=False)
print(ret)

# 设置了外键关联，就不需要设置 Person.hobby_id == Hobby.id 条件
person_list = session.query(Hobby).join(Person, isouter=True)
print(person_list)

person_list = session.query(Person, Hobby).join(Hobby).all()
print(person_list)
for row in person_list:
    # print(row.name,row.caption)
    # 第一个为person对象, 第二位hobby对象
    print(row[0].name, row[1].caption)

3.4 通过relationship连表查询

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from model import Users, Hobby, Person

engine = create_engine("mysql+pymysql://root:root@127.0.0.1:3306/db_flask", max_overflow=0, pool_size=5)
Session = sessionmaker(bind=engine)
session = Session()
# 通过relationship
person_list = session.query(Person).all()

print(person_list)

for row in person_list:
    print(row.name, row.hobby.caption)
    
# obj = session.query(Hobby).filter(Hobby.id == 1).first()
# persons = obj.perss
# print(persons)
    

三、总结

• 添加数据：可以通过add_all添加数据并设置关联外键字段值，还可以通过正反向查询向添加表之间的关系，会自定设置关联的外键值
• 正向查询：当前表中有外键字段称为正向查询，正向查询单条数据对一个爱好数据
• 反向查询：当前表中没有外键字段称为反向查询，反向查询单条数据可会被多个人喜欢，所以查出来为列表对象，是一个集合
• 连表查询：可以通过两种方式进行查询，一种是通过表之间的关键关系通过 .join方法实现连表查询，需要注意的是表之间是否有级联关系，也就是是否设置了外键，如果没有则需要在.join中通过逻辑条件实现连表查询，通过调至query和.join中类名实现左右连接查询，第二种方式就是通过正反向查询实现连表查询