sqlalchemy 多对多关系

内容简介：sqlalchemy 多对多关系

一、前言

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

现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是

1. 一本书可以有好几个作者一起出版
2. 一个作者可以写好几本书

二、表结构和数据

book_m2m_author表由author表和book表生成

三、事例

# -*- coding: UTF-8 -*-

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)

创建3张表

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)

# 这是直接将作者从表中删除了

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网

查看所有标签

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。