from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, joinedload, selectinload
from sqlalchemy.future import select
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
Base = declarative_base()classUser(Base):
__tablename__ ='users'id= Column(Integer, primary_key=True)
name = Column(String(50))
role_id = Column(Integer, ForeignKey('roles.id'))
role = relationship('Role')
work_id = Column(Integer, ForeignKey('works.id'))
works = relationship('Works')classRole(Base):
__tablename__ ='roles'id= Column(Integer, primary_key=True)
name = Column(String(50))classWorks(Base):
__tablename__ ='works'id= Column(Integer, primary_key=True)
name = Column(String(50))asyncdefmain():
engine = create_async_engine('sqlite+aiosqlite:///example.db', echo=True)asyncwith engine.begin()as conn:await conn.run_sync(Base.metadata.create_all)asyncwith AsyncSession(engine)as session:asyncwith session.begin():# 1. options 与 join 顺序可以随便加,可以加最后面或者select后面,SQLAlchemy 会自动解析# 2. 使用 options 加载外键属性后,可以通过`.外键属性`获取到外键对象,使用 join 加载外键模型不可以获取,会报错# 3. 使用 options 或 join 加载后都可以通过 where 查询外键,但是只使用 options 加载后查询会报异常,会出现笛卡尔积# 建议:# 1. 只加载外键属性时,使用 options 加载# 2. 只需要用到外键关联查询时,使用 join 加载# 3. 需要加载外键属性的同时又需要外键关联查询,则 options 和 join 同时使用
sql = select(User).where(Role.name =='a').options(joinedload(User.role), joinedload(User.works)).join(Role)
queryset =await session.execute(sql)
datas = queryset.scalars().unique().all()for user in datas:print("user: {}".format(user.name))print("user.role: {}".format(user.role))print("user.works: {}".format(user.works))if __name__ =="__main__":import asyncio
asyncio.run(main())
只使用 options 查询的 SQL:
SELECT
users.id,
users.name,
users.role_id,
users.work_id,
roles_1.id AS id_1,
roles_1.name AS name_1,
works_1.id AS id_2,
works_1.name AS name_2
FROM
roles,
users
LEFTOUTERJOIN roles AS roles_1 ON roles_1.id = users.role_id
LEFTOUTERJOIN works AS works_1 ON works_1.id = users.work_id
WHERE
roles.name ='a'
会报警告:
sys:1: SAWarning: SELECT statement has a cartesian product betweenFROM element(s)"roles"andFROM element "users".Applyjoin condition(s)between each element to resolve.
会将users的所有数据都查询出来
使用 options 加 join 查询的SQL:
SELECT
users.id,
users.name,
users.role_id,
users.work_id,
roles_1.id AS id_1,
roles_1.name AS name_1,
works_1.id AS id_2,
works_1.name AS name_2
FROM
users
JOIN roles ON roles.id = users.role_id
LEFTOUTERJOIN roles AS roles_1 ON roles_1.id = users.role_id
LEFTOUTERJOIN works AS works_1 ON works_1.id = users.work_id
WHERE
roles.name ='a'
不会报警告
只会查询出来 roles.name ='a' 的数据