sqlalchemy many to many relationship

1, Foreword

A many to many relationship is a table that can be associated with multiple tables. 　

Now let's design a table structure that can describe the relationship between "book" and "author". The requirements are

A book can be published by several authors
A writer can write several books

2, Table structure and data

The book > M2M > author table is generated by the author table and the book table

3, Examples

# -*- 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

# If the inserted data has Chinese, you need to specify charset=utf8
engine = create_engine("mysql+pymysql://bigberg:111111@172.16.200.49:3306/study?charset=utf8",
                       encoding='utf-8')

Base = declarative_base()  # Establish orm Base class
Base.metadata.create_all(engine)



# After this table is created, it does not need to be maintained
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

# Create table
Base.metadata.create_all(engine)

Create 3 tables

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)

Table structure

4, Insert data

# -*- coding: UTF-8 -*-
import m2m_orm
from m2m_orm import Author
from m2m_orm import Book
from sqlalchemy.orm import sessionmaker

# Establish session Conversation
Session_class = sessionmaker(bind=m2m_orm.engine)
# generate session Example
session = Session_class()

b1 = Book(name="python Study", pub_date="2018-01-01")
b2 = Book(name="linux Study", pub_date="2018-02-01")
b3 = Book(name="mysql Study", 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 Study | 2018-01-01 | | 2 | mysql Study | 2018-03-01 | | 3 | linux Study | 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)

5, Query data

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

import m2m_orm
from m2m_orm import Author
from m2m_orm import Book
from sqlalchemy.orm import sessionmaker

# Establish session Conversation
Session_class = sessionmaker(bind=m2m_orm.engine)
# generate session Example
session = Session_class()

print("Check related books through the author table".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("Check related authors through book list".center(30, '-'))
book_obj = session.query(Book).filter(Book.id==2).first()
print(book_obj.name, book_obj.authors)


# output
----------Check related books through the author table-----------
Jack [python Study, mysql Study] 2018-01-01
----------Check related authors through book list-----------
mysql Study [Jack, Marry, Jerru]

6, Delete data

When deleting data, do not worry about boo M2M authors. sqlalchemy will automatically delete the corresponding data for you

6.1 delete the author through the book

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()

# output
Jack
[Jack, Marry, Jerru]
[Marry, Jerru]

6.2 delete the author directly

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 Study   | 2018-01-01 |
|  2 | mysql Study    | 2018-03-01 |
|  3 | linux Study    | 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)

# This is to delete the author directly from the table

Posted on Mon, 04 May 2020 13:47:47 -0400 by NorthWestSimulations

Programmer Help