使用 SQLAlchemy 进行多表关联查询

2023-04-05 00:00:00 sqlalchemy 查询关联

在 SQLAlchemy 中，多表关联查询可以通过 join() 方法或者 relationships（关系）来实现。

join() 实现多表关联查询

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

engine = create_engine('mysql+pymysql://username:password@localhost:3306/dbname')
Base = declarative_base()


class Author(Base):
    __tablename__ = 'authors'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    books = relationship('Book', back_populates='author')


class Book(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)
    title = Column(String(100))
    author_id = Column(Integer, ForeignKey('authors.id'))
    author = relationship('Author', back_populates='books')


Session = sessionmaker(bind=engine)
session = Session()

# 查询作者名字为“pidancode.com”的所有书籍标题
query = session.query(Book.title).join(Author).filter(Author.name == 'pidancode.com')
print(query.all())

relationships（关系）实现多表关联查询

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

engine = create_engine('mysql+pymysql://username:password@localhost:3306/dbname')
Base = declarative_base()


class Author(Base):
    __tablename__ = 'authors'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))


class Book(Base):
    __tablename__ = 'books'

    id = Column(Integer, primary_key=True)
    title = Column(String(100))
    author_id = Column(Integer, ForeignKey('authors.id'))
    author = relationship('Author', backref='books')


Session = sessionmaker(bind=engine)
session = Session()

# 查询作者名字为“pidancode.com”的所有书籍标题
query = session.query(Book.title).join(Book.author).filter(Author.name == 'pidancode.com')
print(query.all())

相关文章