一,前言
如果有张表 A 的多个字段关联另一张表 B 的一个字段,就如同一个客户表的账单地址和发货地址,同时关联地址表中的 id 字段.
二,事例
# -*- coding: UTF-8 -*-
from sqlalchemy import create_engine
from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
engine = create_engine("mysql+pymysql://bigberg:111111@172.16.200.49:3306/study",
encoding="utf-8", ) # 连接数据库,echo=True =>把所有的信息都打印出来
Base = declarative_base() # 生成orm基类
class Customer(Base):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True)
name = Column(String(32), nullable=False)
# 多个外键关联
billing_address_id = Column(Integer, ForeignKey("address.id"))
shopping_address_id = Column(Integer, ForeignKey("address.id"))
# foreign_keys 一定要加,否则会报错
billing_address = relationship("Address",foreign_keys=[billing_address_id])
shopping_address = relationship("Address",foreign_keys=[shopping_address_id])
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
street = Column(String(64), nullable=False)
city = Column(String(64), nullable=False)
state = Column(String(64), nullable=False)
def __repr__(self):
return "省份:%s 城市:%s 街区:%s" %(self.state, self.city, self.street)
# 创建表
Base.metadata.create_all(engine)
multi_fk
插入数据,为了整体的简洁,数据操作在另一张表进行
# -*- coding: UTF-8 -*-
import multi_fk
from multi_fk import Customer
from multi_fk import Address
from sqlalchemy.orm import sessionmaker
# 创建session会话
Session_class = sessionmaker(bind=multi_fk.engine)
# 生成session实例
session = Session_class()
# 数据
address_obj1 = Address(street='daguanlu', city='hz', state='zj')
address_obj2 = Address(street='gudunlu', city='hz', state='zj')
address_obj3 = Address(street='xinjiekou', city='nj', state='js')
session.add_all([address_obj1,address_obj2,address_obj3])
customer_obj1 = Customer(name="bigberg", billing_address=address_obj1,
shopping_address=address_obj2)
customer_obj2 = Customer(name="Jack", billing_address=address_obj3,
shopping_address=address_obj3)
session.add_all([customer_obj1,customer_obj2])
session.commit()
multi_fk_data
数据和表结构
mysql> select * from address;
+----+-----------+------+-------+
| id | street | city | state |
+----+-----------+------+-------+
| 1 | daguanlu | hz | zj |
| 2 | gudunlu | hz | zj |
| 3 | xinjiekou | nj | js |
+----+-----------+------+-------+
3 rows in set (0.00 sec)
mysql> select * from customer;
+----+---------+--------------------+---------------------+
| id | name | billing_address_id | shopping_address_id |
+----+---------+--------------------+---------------------+
| 1 | bigberg | 1 | 2 |
| 2 | Jack | 3 | 3 |
+----+---------+--------------------+---------------------+
2 rows in set (0.00 sec)
mysql> desc address;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| street | varchar(64) | NO | | NULL | |
| city | varchar(64) | NO | | NULL | |
| state | varchar(64) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc customer;
+---------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | NO | | NULL | |
| billing_address_id | int(11) | YES | MUL | NULL | |
| shopping_address_id | int(11) | YES | MUL | NULL | |
+---------------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
View Code
查询
# -*- coding: UTF-8 -*-
import multi_fk
from multi_fk import Customer
from multi_fk import Address
from sqlalchemy.orm import sessionmaker
# 创建session会话
Session_class = sessionmaker(bind=multi_fk.engine)
# 生成session实例
session = Session_class()
obj = session.query(Customer).filter(Customer.name=='bigberg').first()
print(obj.name,'\n','bill_address:',obj.billing_address,'\n',
'shopping_address:', obj.shopping_address)
session.commit()
#输出
bigberg
bill_address: 省份:zj 城市:hz 街区:daguanlu
shopping_address: 省份:zj 城市:hz 街区:gudunlu
multi_fk_query
来源: http://www.bubuko.com/infodetail-2470896.html