Python操作数据库表时常用SQLAlchemy做ORM，把关系数据库的表结构映射到对象上，并通过relationship做外键关联，方便进一步处理。
假设需要实现某个“一对多”的场景，从“一”这头需要取出“多”那头的全部对象，并按某个字段做排序，如何实现呢?
话不多说，show code。
本例使用sqlite3做数据库，创建Project和User两张表，两者是一对多关系。
核心是在User表中通过backref做反向引用时声明order_by即可。
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker, backref, foreign, relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Project(Base):
__tablename__ = "project"
id = Column(Integer, primary_key=True)
name = Column(String(20))
def __repr__(self):
return "<Project(id='%d', name='%s')>" % (self.id, self.name)
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
name = Column(String(20))
project_id = Column(Integer)
project = relationship(Project, primaryjoin=foreign(project_id) == Project.id,
backref=backref("users", order_by=id.desc()))
def __repr__(self):
return "<User(id='%d', name='%s', project_id='%d')>" % (self.id, self.name, self.project_id)
# init database and create tables
def init_db():
engine = create_engine('sqlite:///test.db')
smaker = sessionmaker(bind=engine)
session = smaker()
Base.metadata.create_all(engine)
return engine, smaker, session
# prepare data
def init_data(session):
project = Project(name='Project_A')
session.add(project)
session.commit()
user_a = User(name='user_a', project_id=project.id)
user_b = User(name='user_b', project_id=project.id)
session.add_all([user_a, user_b])
session.commit()
def main():
engine, smaker, session = init_db()
init_data(session)
# test
projects = session.query(Project).all()
for p in projects:
print(p.users)
if __name__ == '__main__':
main()
结果如图，可以看到User对象是按照id倒序排列。
1619770765752.jpg
参考：
