首先在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-8 from sqlalchemy import Column, ForeignKey, Integer, String, Table, text from sqlalchemy.orm import relationship from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() metadata = Base.metadata class 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 sessionmaker S=sessionmaker(bind=db) s=S() u=s.query(User).first() u.addr AttributeError: ‘User‘ object has no attribute ‘addr‘ u.Addr AttributeError: ‘User‘ object has no attribute ‘Addr‘a=s.query(Addr).first() a.user Out[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-8 from sqlalchemy import Column, ForeignKey, Integer, String, Table, text from sqlalchemy.orm import relationship,backref from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() metadata = Base.metadata class 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 sessionmaker In [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.user Out[10]: <b.User at 0xab3186c>