首页 > 代码库 > sqlalchemy foreign key查询和backref
sqlalchemy foreign key查询和backref
首先在mysql中创建两个表如下：
mysql> create table user( id int,name varchar(8) , primary key(id));Query OK, 0 rows affected (0.01 sec)mysql> create table addr(id int,val varchar(100),user_id int, primary key(id),foreign key(user_id) references user(id) );Query OK, 0 rows affected (0.00 sec)mysql> insert into user values(8,‘kramer‘);Query OK, 1 row affected (0.00 sec)mysql> insert into user values (18,‘Tom‘);Query OK, 1 row affected (0.00 sec)mysql> insert into addr values(1,‘peking‘,8);Query OK, 1 row affected (0.00 sec)
然后我们用 sqlacodegen 来生成对应的 class。
root@rijx:/opt# sqlacodegen --schema rdb mysql://root:passw0rd@localhost:3306# coding: utf-8from sqlalchemy import Column, ForeignKey, Integer, String, Table, textfrom sqlalchemy.orm import relationshipfrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()metadata = Base.metadataclass Addr(Base): __tablename__ = ‘addr‘ __table_args__ = {u‘schema‘: ‘rdb‘} id = Column(Integer, primary_key=True, server_default=text("‘0‘")) val = Column(String(100)) user_id = Column(ForeignKey(u‘rdb.user.id‘), index=True) user = relationship(u‘User‘)t_mgr = Table( ‘mgr‘, metadata, Column(‘id‘, Integer, nullable=False), Column(‘name‘, String(18)), schema=‘rdb‘)class User(Base): __tablename__ = ‘user‘ __table_args__ = {u‘schema‘: ‘rdb‘} id = Column(Integer, primary_key=True, server_default=text("‘0‘")) name = Column(String(8))
要注意的是原来该数据库中还有个表mgr，但是没有生成class而是生成一个table。这是因为它没有primary key。
接下来我们把生成的代码保存成models.py文件然后操作。
from models import *from sqlalchemy import *db=create_engine(‘mysql://root:passw0rd@localhost:3306/rdb?charset=utf8‘,encoding = "utf-8",echo =True)from sqlalchemy.orm import sessionmakerS=sessionmaker(bind=db)s=S()u=s.query(User).first()u.addrAttributeError: ‘User‘ object has no attribute ‘addr‘u.AddrAttributeError: ‘User‘ object has no attribute ‘Addr‘a=s.query(Addr).first()a.userOut[11]: <models.User at 0xa12e88c>
可以看见通过user来获取addr获取不到，但是通过addr获取user可以。这是因为 addr 下面的代码
user = relationship(u‘User‘)这段代码说明addr可以通过这个函数来找到对应的user
我们改一下models.py 。把这行代码改成user = relationship(u‘User‘,backref=backref(‘addr‘))就可以通过user来找addr了。新的代码说明，user可以通过backref找到addr
要注意得import sqlalchemy.orm.backref
root@rijx:/opt/temp# cat b.py# coding: utf-8from sqlalchemy import Column, ForeignKey, Integer, String, Table, textfrom sqlalchemy.orm import relationship,backreffrom sqlalchemy.ext.declarative import declarative_baseBase = declarative_base()metadata = Base.metadataclass Addr(Base): __tablename__ = ‘addr‘ __table_args__ = {u‘schema‘: ‘rdb‘} id = Column(Integer, primary_key=True, server_default=text("‘0‘")) val = Column(String(100)) user_id = Column(ForeignKey(u‘rdb.user.id‘), index=True) #user = relationship(u‘User‘) user = relationship(u‘User‘,backref=backref(‘addr‘))t_mgr = Table( ‘mgr‘, metadata, Column(‘id‘, Integer, nullable=False), Column(‘name‘, String(18)), schema=‘rdb‘)class User(Base): __tablename__ = ‘user‘ __table_args__ = {u‘schema‘: ‘rdb‘} id = Column(Integer, primary_key=True, server_default=text("‘0‘")) name = Column(String(8))
红色部分是改过的代码，注意有两处
下面用python调用
In [1]: from b import *In [2]: from sqlalchemy import *In [3]: db=create_engine(‘mysql://root:passw0rd@localhost:3306/rdb?charset=utf8‘,encoding = "utf-8",echo =True)In [4]: from sqlalchemy.orm import sessionmakerIn [5]: S=sessionmaker(bind=db)In [6]: s=S()In [7]: u=s.query(User).first()In [8]: u.addr
Out[8]: [<b.Addr at 0xab31c6c>]
In [10]: a.userOut[10]: <b.User at 0xab3186c>
sqlalchemy foreign key查询和backref
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。