sqlalchemy多对多关系

⼀、前⾔

　　多对多的关系是⼀张表可以关联多张表。　

　　现在来设计⼀个能描述“图书”与“作者”的关系的表结构，需求是1. ⼀本书可以有好⼏个作者⼀起出版2. ⼀个作者可以写好⼏本书

⼆、表结构和数据

book_m2m_author表由author表和book表⽣成

三、事例

from sqlalchemy import Table, Column, Integer, String, DATE, ForeignKeyfrom sqlalchemy.orm import relationship

from sqlalchemy.ext.declarative import declarative_basefrom 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()) 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() | 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 Authorfrom 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 Authorfrom 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)# 这是直接将作者从表中删除了