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

  1. A book can be published by several authors
  2. 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()
insert data
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)
Data content

 

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]
Query data

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

Tags: Python Session MySQL Linux

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