SQLAlchemy学习

SQLAlchemy学习"/>

SQLAlchemy学习

前言

relationship 函数是 sqlalchemy对关系之间提供的一种便利的调用方式, backref参数则对关系提供反向引用的声明。
在最新版本的sqlalchemy中对relationship引进了back_populates参数， 两个参数的效果完全一致。

backref 和 back_populates 两个参数的区别

• backref 只需要在 Parent 类中声明 children，Child.parent 会被动态创建。
• back_populates 必须在两个类中显式地使用 back_populates，更显繁琐，理解更直观

relationship 使用

relationship 函数是 sqlalchemy对关系之间提供的一种便利的调用方式, backref参数则对关系提供反向引用的声明。

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationshipclass Parent(Base):__tablename__ = 'parent'id = Column(Integer, primary_key=True)children = relationship("Child",  uselist=False, backref='parent')# 在父表类中通过 relationship() 方法来引用子表的类集合class Child(Base):__tablename__ = 'child'id = Column(Integer, primary_key=True)parent_id = Column(Integer, ForeignKey('parent.id'))# 在子表类中通过 foreign key (外键)引用父表的参考字段

如上代码可以通过Parent.children 访问到Child 对象，那么如果得到了Child 对象，如何获取Parent对象呢？
这时候就通过backref参数反向引用到Parent 类。

backref 方法定义相关源码部分

def backref(name, **kwargs):"""Create a back reference with explicit keyword arguments, which are     the same arguments one can send to :func:`relationship`.Used with the ``backref`` keyword argument to :func:`relationship` inplace of a string argument, e.g.::'items':relationship(SomeItem, backref=backref('parent', lazy='subquery')).. seealso:::ref:`relationships_backref`"""return (name, kwargs)

backref 参数相关解释

:param backref:indicates the string name of a property to be placed on the relatedmapper's class that will handle this relationship in the otherdirection. The other property will be created automaticallywhen the mappersare configured.  Can also be passed as a:func:`.backref` object to control the configuration of thenew relationship.

backref 参数

使用示例

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationshipBase = declarative_base()class Card(Base):"""银行卡基本信息"""__tablename__ = 'card'  # 数据库表名id = Column(Integer, primary_key=True, autoincrement=True)card_id = Column(String(30))card_user = Column(String(10))tel = Column(String(30))card_detail = relationship("CardDetail",uselist=False,backref='card')class CardDetail(Base):"""银行卡 详情信息"""__tablename__ = 'carddetail'  # 数据库表名id = Column(Integer, primary_key=True, autoincrement=True)mail = Column(String(30))city = Column(String(10))address = Column(String(30))card_id = Column(Integer, ForeignKey('card.id'))if __name__ == '__main__':DB_URI = 'mysql+pymysql://root:123456@localhost:3306/web'engine = create_engine(DB_URI)Base.metadata.create_all(engine)  # 将模型映射到数据库中

还有另外一种方法 从 sqlalcehmy.orm 中导入 backref方法

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import backrefBase = declarative_base()class Card(Base):"""银行卡基本信息"""__tablename__ = 'card'  # 数据库表名id = Column(Integer, primary_key=True, autoincrement=True)card_id = Column(String(30))card_user = Column(String(10))tel = Column(String(30))card_detail = relationship("CardDetail", backref=backref("card", uselist=False))def __repr__(self):return f"<Card(id={self.id}, card_id={self.card_id}, card_user={self.card_user} ...)>"class CardDetail(Base):"""银行卡 详情信息"""__tablename__ = 'carddetail'  # 数据库表名id = Column(Integer, primary_key=True, autoincrement=True)mail = Column(String(30))city = Column(String(10))address = Column(String(30))card_id = Column(Integer, ForeignKey('card.id'))def __repr__(self):return f"<CardDetail(id={self.id}, mail={self.mail}, city={self.city} ...)>"

跟上面作用是等价的。

正向查询

# 查询
# query()
r1 = session.query(Card).filter(Card.card_user == 'yoyo').first()
print(r1)# 正向查询，主表查副表
print(r1.card_detail)
print(r1.card_detail.mail)

反向查询

# 先查关联表数据
r2 = session.query(CardDetail).filter(CardDetail.mail == '123@qq').first()
print(r2)
# 反向查询主表
print(r2.card)     # relationship 通过 backref='detail'属性 副表关联到主表
print(r2.card.card_user)

back_populates 参数

在最新版本的sqlalchemy中对relationship引进了back_populates参数， 两个参数的效果完全一致。

backref 和 back_populates 两个参数的区别

• backref 只需要在 Parent 类中声明 children，Child.parent 会被动态创建。
• back_populates 必须在两个类中显式地使用 back_populates，更显繁琐，理解更直观

使用示例

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import backrefBase = declarative_base()class Card(Base):"""银行卡基本信息"""__tablename__ = 'card'  # 数据库表名id = Column(Integer, primary_key=True, autoincrement=True)card_id = Column(String(30))card_user = Column(String(10))tel = Column(String(30))detail = relationship("CardDetail",uselist=False,back_populates='card')def __repr__(self):return f"<Card(id={self.id}, card_id={self.card_id}, card_user={self.card_user} ...)>"class CardDetail(Base):"""银行卡 详情信息"""__tablename__ = 'carddetail'  # 数据库表名id = Column(Integer, primary_key=True, autoincrement=True)mail = Column(String(30))city = Column(String(10))address = Column(String(30))card_id = Column(Integer, ForeignKey('card.id'))card = relationship("Card",uselist=False,back_populates='detail')def __repr__(self):return f"<CardDetail(id={self.id}, mail={self.mail}, city={self.city} ...)>"if __name__ == '__main__':DB_URI = 'mysql+pymysql://root:123456@localhost:3306/web'engine = create_engine(DB_URI)Base.metadata.create_all(engine)  # 将模型映射到数据库中

正向查询，通过主表查询关联表

from xuexi.a5 import Card, CardDetail
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engineengine = create_engine('mysql+pymysql://root:123456@localhost:3306/web')
# 把当前的引擎绑定给这个会话
Session = sessionmaker(bind=engine)
# 实例化
session = Session()# 查询
# query()
r1 = session.query(Card).filter(Card.card_user == 'yoyo').first()
print(r1)# 正向查询，主表查副表
print(r1.detail)
print(r1.detail.mail)

运行结果

<Card(id=1, card_id=3211222001, card_user=yoyo ...)>
<CardDetail(id=1, mail=123@qq, city=上海市 ...)>
123@qq

反向查询，通过关联表查询主表

# 先查关联表数据
r2 = session.query(CardDetail).filter(CardDetail.mail == '123@qq').first()
print(r2)
# 反向查询主表
print(r2.card)     # relationship 通过 backref='detail'属性 副表关联到主表
print(r2.card.card_user)

运行结果

<CardDetail(id=1, mail=123@qq, city=上海市 ...)>
<Card(id=1, card_id=3211222001, card_user=yoyo ...)>
yoyo