sqlalchemy 多对多关系
一、前言
多对多的关系是一张表可以关联多张表。
现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是
- 一本书可以有好几个作者一起出版
- 一个作者可以写好几本书
二、表结构和数据
book_m2m_author表由author表和book表生成
三、事例
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 # 如果插入数据有中文，需要指定 charset=utf8 engine = create_engine("mysql+pymysql://bigberg:111111@172.16.200.49:3306/study?charset=utf8", encoding='utf-8') Base = declarative_base() # 创建orm基类 Base.metadata.create_all(engine) # 这个表的创建后，不需要维护 book_m2m_author = Table("book_m2m_author", Base.metadata, Column("id", Integer, primary_key=True), Column('books_id', Integer, ForeignKey("books.id")), Column('authors_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 # 创建表 Base.metadata.create_all(engine)
mysql> desc authors; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> desc books; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(64) | YES | | NULL | | | pub_date | date | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> desc book_m2m_author; +------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | books_id | int(11) | YES | MUL | NULL | | | authors_id | int(11) | YES | MUL | NULL | | +------------+---------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
四、插入数据
# -*- coding: UTF-8 -*- import m2m_orm from m2m_orm import Author from m2m_orm import Book from sqlalchemy.orm import sessionmaker # 创建session会话 Session_class = sessionmaker(bind=m2m_orm.engine) # 生成session实例 session = Session_class() b1 = Book(name="python学习", pub_date="2018-01-01") b2 = Book(name="linux学习", pub_date="2018-02-01") b3 = Book(name="mysql学习", pub_date="2018-03-01") a1 = Author(name="Jack") a2 = Author(name="Jerru") a3 = Author(name="Marry") b1.authors = [a1,a2] b2.authors = [a2,a3] b3.authors = [a1,a2,a3] session.add_all([b1,b2,b3,a1,a2,a3]) session.commit()
mysql> select * from books; +----+--------------+------------+ | id | name | pub_date | +----+--------------+------------+ | 1 | python学习 | 2018-01-01 | | 2 | mysql学习 | 2018-03-01 | | 3 | linux学习 | 2018-02-01 | +----+--------------+------------+ 3 rows in set (0.00 sec) mysql> select * from authors; +----+-------+ | id | name | +----+-------+ | 1 | Jack | | 2 | Marry | | 3 | Jerru | +----+-------+ 3 rows in set (0.00 sec) mysql> select * from book_m2m_author; +----+----------+------------+ | id | books_id | authors_id | +----+----------+------------+ | 1 | 2 | 1 | | 2 | 2 | 3 | | 3 | 2 | 2 | | 4 | 3 | 3 | | 5 | 3 | 2 | | 6 | 1 | 1 | | 7 | 1 | 3 | +----+----------+------------+ 7 rows in set (0.00 sec)
五、查询数据
# -*- coding: UTF-8 -*- import m2m_orm from m2m_orm import Author from m2m_orm import Book from sqlalchemy.orm import sessionmaker # 创建session会话 Session_class = sessionmaker(bind=m2m_orm.engine) # 生成session实例 session = Session_class() print("通过作者表查关联书".center(30, '-')) author_obj = session.query(Author).filter(Author.name=='Jack').first() print(author_obj.name, author_obj.books, author_obj.books[0].pub_date) print("通过书表查关联作者".center(30, '-')) book_obj = session.query(Book).filter(Book.id==2).first() print(book_obj.name, book_obj.authors) # 输出 ----------通过作者表查关联书----------- Jack [python学习, mysql学习] 2018-01-01 ----------通过书表查关联作者----------- mysql学习 [Jack, Marry, Jerru]
六、删除数据
删除数据时不用管boo_m2m_authors ， sqlalchemy会自动帮你把对应的数据删除
6.1 通过书删除作者
author_obj = session.query(Author).filter(Author.name=='Jack').first() book_obj = session.query(Book).filter(Book.id==2).first() print(author_obj.name) print(book_obj.authors) book_obj.authors.remove(author_obj) print(book_obj.authors) session.commit() # 输出 Jack [Jack, Marry, Jerru] [Marry, Jerru]
6.2 直接删除作者
author_obj = session.query(Author).filter(Author.name=='Jack').first() print(author_obj.name) session.delete(author_obj) session.commit()
mysql> select * from authors; +----+-------+ | id | name | +----+-------+ | 2 | Marry | | 3 | Jerru | +----+-------+ 2 rows in set (0.00 sec) mysql> select * from books; +----+--------------+------------+ | id | name | pub_date | +----+--------------+------------+ | 1 | python学习 | 2018-01-01 | | 2 | mysql学习 | 2018-03-01 | | 3 | linux学习 | 2018-02-01 | +----+--------------+------------+ 3 rows in set (0.00 sec) mysql> select * from book_m2m_author; +----+----------+------------+ | id | books_id | authors_id | +----+----------+------------+ | 2 | 2 | 3 | | 3 | 2 | 2 | | 4 | 3 | 3 | | 5 | 3 | 2 | | 7 | 1 | 3 | +----+----------+------------+ 5 rows in set (0.00 sec) # 这是直接将作者从表中删除了
