sqlalchemy 多对多关系

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]