Python3中sqlalchemy模块学习之正查反查

起步

#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""表结构"""

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import Column, Integer, String, ForeignKey

engine = create_engine('mysql+pymysql://root:123@localhost:3306/sqlalchemy_db?charset=utf8mb4', max_overflow=10, echo=True)

Base = declarative_base()


class Dep(Base):
    __tablename__ = 'dep'
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)


class Emp(Base):
    __tablename__ = 'emp'
    id = Column(Integer, primary_key=True, autoincrement=True)
    ename = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey('dep.id'))

    # 1. 在ForeignKey所在的类内添加relationship字段，注意：
    # 2. Dep是类名
    # 3. depart字段不会在数据库表中生成字段
    # 4. depart用于Emp表查询Dep表(正向查询)，
    #    而to_emp用于Dep表查询Emp表(反向查询)
    depart = relationship('Dep', backref='to_emp')


def init_db():
    Base.metadata.create_all(bind=engine)


def drop_db():
    Base.metadata.drop_all(bind=engine)


# drop_db()
# init_db()

Session = sessionmaker(bind=engine)
session = Session()
#
# 准备数据
# session.add_all([
#     Dep(dname='技术'),
#     Dep(dname='销售'),
#     Dep(dname='运营'),
#     Dep(dname='人事'),
# ])
#
# session.add_all([
#     Emp(ename='林海峰', dep_id=1),
#     Emp(ename='李杰', dep_id=1),
#     Emp(ename='武配齐', dep_id=1),
#     Emp(ename='元昊', dep_id=2),
#     Emp(ename='李钢弹', dep_id=3),
#     Emp(ename='张二丫', dep_id=4),
#     Emp(ename='李坦克', dep_id=2),
#     Emp(ename='王大炮', dep_id=4),
#     Emp(ename='牛榴弹', dep_id=3)
# ])
#
# session.commit()

前提

1. 在 ForeignKey 所在的类内添加 relationship 字段，注意：
2. Dep 是类名
3. depart 字段不会在数据库表中生成字段
4. depart 用于 Emp 表查询Dep表(正向查询)，
5. 而 to_emp 用于Dep表查询Emp表(反向查询)

正查

多个对一个

rows = session.query(Emp)
for each_line in rows:
    print(
        each_line.id,
        each_line.ename,
        each_line.dep_id,
        each_line.depart,
        each_line.depart.dname,
        each_line.depart.id
    )

反查

一个对多个

rows = session.query(Dep)
for each_line in rows:
    print(each_line.to_emp)
    # print(
    #     each_line.id,
    #     each_line.dname,
    #     [(r.id, r.ename) for r in each_line.to_emp]
    #
    # )
#

整体代码

#!/usr/bin/python3
# -*- coding: utf-8 -*-
"""表结构"""

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import Column, Integer, String, ForeignKey

engine = create_engine('mysql+pymysql://root:123@localhost:3306/sqlalchemy_db?charset=utf8mb4', max_overflow=10, echo=True)

Base = declarative_base()


class Dep(Base):
    __tablename__ = 'dep'
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)


class Emp(Base):
    __tablename__ = 'emp'
    id = Column(Integer, primary_key=True, autoincrement=True)
    ename = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey('dep.id'))

    # 1. 在ForeignKey所在的类内添加relationship字段，注意：
    # 2. Dep是类名
    # 3. depart字段不会在数据库表中生成字段
    # 4. depart用于Emp表查询Dep表(正向查询)，
    #    而to_emp用于Dep表查询Emp表(反向查询)
    depart = relationship('Dep', backref='to_emp')


def init_db():
    Base.metadata.create_all(bind=engine)


def drop_db():
    Base.metadata.drop_all(bind=engine)


# drop_db()
# init_db()

Session = sessionmaker(bind=engine)
session = Session()
#
# 准备数据
# session.add_all([
#     Dep(dname='技术'),
#     Dep(dname='销售'),
#     Dep(dname='运营'),
#     Dep(dname='人事'),
# ])
#
# session.add_all([
#     Emp(ename='林海峰', dep_id=1),
#     Emp(ename='李杰', dep_id=1),
#     Emp(ename='武配齐', dep_id=1),
#     Emp(ename='元昊', dep_id=2),
#     Emp(ename='李钢弹', dep_id=3),
#     Emp(ename='张二丫', dep_id=4),
#     Emp(ename='李坦克', dep_id=2),
#     Emp(ename='王大炮', dep_id=4),
#     Emp(ename='牛榴弹', dep_id=3)
# ])
#
# session.commit()

# 1. 正查
# # 多个对一个
# # sqlalchemy的relationship在内部帮我们做好表的连接
rows = session.query(Emp)
for each_line in rows:
    print(
        each_line.id,
        each_line.ename,
        each_line.dep_id,
        each_line.depart,
        each_line.depart.dname,
        each_line.depart.id
    )
#
# # 2. 反查
# # 一个对多个
rows = session.query(Dep)
for each_line in rows:
    print(each_line.to_emp)
    # print(
    #     each_line.id,
    #     each_line.dname,
    #     [(r.id, r.ename) for r in each_line.to_emp]
    #
    # )
#
session.commit()
session.close()