外键关联
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String(32), nullable=False)
user_id = Column(Integer, ForeignKey('user.id'))
user = relationship("User", backref="addresses") #这个nb，允许你在user表里通过backref字段反向查出所有它在addresses表里的关联项
def __repr__(self):
return "<Address(email_address='%s')>" % self.email_address
user = relationship("User", backref="addresses")这里到底是什么意思呢？在大学最起码学过一门关系型数据库，就是数据库里面的表与表之间的关联关系。
表创建好后，我们可以这样反查试试
obj = Session.query(User).first()
for i in obj.addresses: #通过user对象反查关联的addresses记录
print(i)
addr_obj = Session.query(Address).first()
print(addr_obj.user.name) #在addr_obj里直接查关联的user表
如果没有关联的内容，那么就会打印出空的列表[ ]
添加关联项
obj = Session.query(User).filter(User.name=='rain').all()[0]
print(obj.addresses)
obj.addresses = [Address(email_address="r1@126.com"), #添加关联对象
Address(email_address="r2@126.com")]
Session.commit()
多外键关联
from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
Base = declarative_base()
class Customer(Base):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True)
name = Column(String)
billing_address_id = Column(Integer, ForeignKey("address.id"))
shipping_address_id = Column(Integer, ForeignKey("address.id"))
billing_address = relationship("Address")
shipping_address = relationship("Address")
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
street = Column(String)
city = Column(String)
state = Column(String)
多对多关系
现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是
- 一本书可以有好几个作者一起出版
- 一个作者可以写好几本书
一本书可以有多个作者，一个作者又可以出版多本书
from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
book_m2m_author = Table('book_m2m_author', Base.metadata,
Column('book_id',Integer,ForeignKey('books.id')),
Column('author_id',Integer,ForeignKey('authors.id')),
)
class Book(Base):
__tablename__ = 'books'
id = Column(Integer,primary_key=True)
name = Column(String(64))
pub_date = Column(DATE)
authors = relationship('Author',secondary=book_m2m_author,backref='books')
def __repr__(self):
return self.name
class Author(Base):
__tablename__ = 'authors'
id = Column(Integer, primary_key=True)
name = Column(String(32))
def __repr__(self):
return self.name
orm 多对多
创建书和作者
Session_class = sessionmaker(bind=engine) #创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例
s = Session_class() #生成session实例
b1 = Book(name="跟Alex学Python")
b2 = Book(name="跟Alex学把妹")
b3 = Book(name="跟Alex学装逼")
b4 = Book(name="跟Alex学开车")
a1 = Author(name="Alex")
a2 = Author(name="Jack")
a3 = Author(name="Rain")
b1.authors = [a1,a2]
b2.authors = [a1,a2,a3]
s.add_all([b1,b2,b3,b4,a1,a2,a3])
s.commit()
此时，手动连上mysql，分别查看这3张表，你会发现，book_m2m_author中自动创建了多条纪录用来连接book和author表
mysql> select * from books;
+----+------------------+----------+
| id | name | pub_date |
+----+------------------+----------+
| 1 | 跟Alex学Python | NULL |
| 2 | 跟Alex学把妹 | NULL |
| 3 | 跟Alex学装逼 | NULL |
| 4 | 跟Alex学开车 | NULL |
+----+------------------+----------+
4 rows in set (0.00 sec)
mysql> select * from authors;
+----+------+
| id | name |
+----+------+
| 10 | Alex |
| 11 | Jack |
| 12 | Rain |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from book_m2m_author;
+---------+-----------+
| book_id | author_id |
+---------+-----------+
| 2 | 10 |
| 2 | 11 |
| 2 | 12 |
| 1 | 10 |
| 1 | 11 |
+---------+-----------+
5 rows in set (0.00 sec
此时，我们去用orm查一下数据
print('--------通过书表查关联的作者---------')
book_obj = s.query(Book).filter_by(name="跟Alex学Python").first()
print(book_obj.name, book_obj.authors)
print('--------通过作者表查关联的书---------')
author_obj =s.query(Author).filter_by(name="Alex").first()
print(author_obj.name , author_obj.books)
s.commit()
输出如下
--------通过书表查关联的作者---------
跟Alex学Python [Alex, Jack]
--------通过作者表查关联的书---------
Alex [跟Alex学把妹, 跟Alex学Python]