SQLAlchemy foreign key query and Backref

First create two tables in MySQL as follows:

Mysql> Create Table User(IDint, namevarchar(8) ,Primary Key(ID)); Query OK,0Rows Affected (0.01sec) MySQL> Create TableAddr (idint, Valvarchar( -),user_id int,Primary Key(ID),Foreign Key(user_id)References User(ID)); Query OK,0Rows Affected (0.00sec)MySQL> Insert  into User Values(8,'Kramer'); Query OK,1Row affected (0.00sec) MySQL> Insert  into User Values( -,'Tom'); Query OK,1Row affected (0.00sec) MySQL> Insert  intoAddrValues(1,'Peking',8); Query OK,1Row affected (0.00Sec

Then we use Sqlacodegen to generate the corresponding class.

[Email protected]:/opt#Sqlacodegen--schema rdb mysql://root:[email protected]:3306#Coding:utf-8 fromSQLAlchemyImportColumn, ForeignKey, Integer, String, Table, text fromSqlalchemy.ormImportRelationship fromSqlalchemy.ext.declarativeImportDeclarative_basebase=declarative_base () metadata=Base.metadataclassAddr (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')classUser (Base):__tablename__='User'    __table_args__= {u'Schema':'Rdb'} ID= Column (Integer, Primary_key=true, Server_default=text ("' 0 '")) name= Column (String (8))

Note that there is also a table Mgr in the database, but instead of generating a class, it generates a table. This is because it does not have primary key.

Next we save the generated code as a models.py file and then manipulate it.

 fromModelsImport* fromSQLAlchemyImport*db=create_engine ('Mysql://root:[email Protected]:3306/rdb?charset=utf8', encoding ="Utf-8", echo =True) fromSqlalchemy.ormImportSessionmakerS=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[One]: <models. User at 0xa12e88c>

You can see that getting addr through the user is not available, but getting the user via addr is possible. This is because addr the following code

user = Relationship (U ' User ')

This code shows that addr can use this function to find the corresponding user

Let's change the models.py. Change this line of code to user = relationship (U ' user ', Backref=backref (' addr ')) to find addr via user. New code Description, user can find addr through Backref

Pay attention to import Sqlalchemy.orm.backref

[Email protected]:/opt/temp#Cat b.py#Coding:utf-8 fromSQLAlchemyImportColumn, ForeignKey, Integer, String, Table, text fromSqlalchemy.ormImportrelationship,backref  fromSqlalchemy.ext.declarativeImportDeclarative_basebase=declarative_base () metadata=Base.metadataclassAddr (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')classUser (Base):__tablename__='User'    __table_args__= {u'Schema':'Rdb'} ID= Column (Integer, Primary_key=true, Server_default=text ("' 0 '")) name= Column (String (8))

The red part is the changed code, notice there are two places

The following is called with Python

In [1]: fromBImport*In [2]: fromSQLAlchemyImport*In [3]: Db=create_engine ('Mysql://root:[email Protected]:3306/rdb?charset=utf8', encoding ="Utf-8", echo =True)In [4]: fromSqlalchemy.ormImportSessionmakerIn [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 [ten]: a.userout[]: <b.user at 0xab3186c>

SQLAlchemy foreign key query and Backref