sqlalchemy 多对多关系

2018-06-18 00:48:39来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

一、前言

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

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

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

二、表结构和数据

  

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)

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

  

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:Python杂谈: 集合中union和update的区别(Python3.x)

下一篇:学习django各种奇葩错误日志