Python3-sqlalchemy-orm 多对多关系建表、插入数据、查询数据
现在来设计一个能描述“图书”与“作者”的关系的表结构，需求是
- 一本书可以有好几个作者一起出版
- 一个作者可以写好几本书
此时你会发现，用之前学的外键好像没办法实现上面的需求了，因为
当然你更不可以像下面这样干，因为这样就你就相当于有多条书的记录了，太low b了，改书名还得都改。。。
那怎么办呢？ 此时，我们可以再搞出一张中间表，就可以了
这样就相当于通过book_m2m_author表完成了book表和author表之前的多对多关联
#-*-coding:utf-8-*- #__author__ = "logan.xu" import sqlalchemy from sqlalchemy import create_engine,func from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker,relationship from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey from sqlalchemy.orm import relationship engine=create_engine("mysql+pymysql://root:12345678@localhost/news",encoding='utf-8',echo=True) Base=declarative_base() #一本书可以有多个作者，一个作者又可以出版多本书 book_m2m_author = Table('book_m2m_author', Base.metadata, Column('book_id',Integer,ForeignKey('books.id')), Column('author_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)
#-*-coding:utf-8-*- #__author__ = "logan.xu" from orm_m2m import orm_m2m from sqlalchemy.orm import sessionmaker Session_class=sessionmaker(bind=orm_many_fk.engine) #创建与数据库的链接 session= Session_class() #生成session实例 #cursor
/Users/drizzle/PycharmProjects/2018-12-03/venv/bin/python /Users/drizzle/PycharmProjects/2018-12-03/orm_m2m/orm_m2m.py 2019-01-08 19:35:48,687 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2019-01-08 19:35:48,687 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,689 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names' 2019-01-08 19:35:48,689 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,691 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2019-01-08 19:35:48,691 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,692 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin' 2019-01-08 19:35:48,692 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,694 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2019-01-08 19:35:48,695 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,695 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2019-01-08 19:35:48,695 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,696 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4)
COLLATE utf8mb4_bin AS anon_1 2019-01-08 19:35:48,696 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,697 INFO sqlalchemy.engine.base.Engine DESCRIBE `book_m2m_author` 2019-01-08 19:35:48,697 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,698 INFO sqlalchemy.engine.base.Engine ROLLBACK 2019-01-08 19:35:48,698 INFO sqlalchemy.engine.base.Engine DESCRIBE `books` 2019-01-08 19:35:48,698 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,699 INFO sqlalchemy.engine.base.Engine ROLLBACK 2019-01-08 19:35:48,699 INFO sqlalchemy.engine.base.Engine DESCRIBE `authors` 2019-01-08 19:35:48,699 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,699 INFO sqlalchemy.engine.base.Engine ROLLBACK 2019-01-08 19:35:48,700 INFO sqlalchemy.engine.base.Engine CREATE TABLE books ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(64), pub_date DATE, PRIMARY KEY (id) ) 2019-01-08 19:35:48,700 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,793 INFO sqlalchemy.engine.base.Engine COMMIT 2019-01-08 19:35:48,794 INFO sqlalchemy.engine.base.Engine CREATE TABLE authors ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(32), PRIMARY KEY (id) ) 2019-01-08 19:35:48,794 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,800 INFO sqlalchemy.engine.base.Engine COMMIT 2019-01-08 19:35:48,802 INFO sqlalchemy.engine.base.Engine CREATE TABLE book_m2m_author ( book_id INTEGER, author_id INTEGER, FOREIGN KEY(book_id) REFERENCES books (id), FOREIGN KEY(author_id) REFERENCES authors (id) ) 2019-01-08 19:35:48,802 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:35:48,811 INFO sqlalchemy.engine.base.Engine COMMIT Process finished with exit code 0
mysql> show tables; +-----------------+ | Tables_in_news | +-----------------+ | address | | authors | | book_m2m_author | | books | | customer | | student | | study_record | | user | +-----------------+ 8 rows in set (0.00 sec) mysql> show create table authors; +---------+--------------------------------------------------------------+ | Table | Create Table | +---------+--------------------------------------------------------------+ | authors | CREATE TABLE `authors` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +---------+-------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table book_m2m_author; +-----------------+--------------------------------------------------------------------------+ | Table | Create Table | +-----------------+--------------------------------------------------------------------------+ | book_m2m_author | CREATE TABLE `book_m2m_author` ( `book_id` int(11) DEFAULT NULL, `author_id` int(11) DEFAULT NULL, KEY `book_id` (`book_id`), KEY `author_id` (`author_id`), CONSTRAINT `book_m2m_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`), CONSTRAINT `book_m2m_author_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-----------------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table books; +-------+-------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------+ | books | CREATE TABLE `books` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) DEFAULT NULL, `pub_date` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +---------------------------------------------------------------------+
mysql> desc book_m2m_author;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| book_id | int(11) | YES | MUL | NULL | |
| author_id | int(11) | YES | MUL | 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 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)
#-*-coding:utf-8-*- #__author__ = "logan.xu" from m2m import orm_m2m from sqlalchemy.orm import sessionmaker Session_class=sessionmaker(bind=orm_m2m.engine) #创建与数据库的链接 session= Session_class() #生成session实例 #cursor #插入数据 b1= orm_m2m.Book(name="learn python with Alex", pub_date="2019-01-08") b2= orm_m2m.Book(name="learn JAVA with Alex", pub_date="2019-01-09") b3= orm_m2m.Book(name="learn LINUX with Alex", pub_date="2019-01-10") a1= orm_m2m.Author(name="Alex") a2= orm_m2m.Author(name="Jack") a3= orm_m2m.Author(name="Eric") b1.authors=[a1,a3] b3.authors=[a1,a2,a3] session.add_all([b1,b2,b3,a1,a2,a3]) session.commit()
/Users/drizzle/PycharmProjects/2018-12-03/venv/bin/python /Users/drizzle/PycharmProjects/2018-12-03/m2m/orm_m2m_api.py 2019-01-08 19:54:48,757 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2019-01-08 19:54:48,757 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:54:48,759 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names' 2019-01-08 19:54:48,759 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:54:48,760 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2019-01-08 19:54:48,761 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:54:48,761 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin' 2019-01-08 19:54:48,761 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:54:48,764 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2019-01-08 19:54:48,764 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:54:48,765 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2019-01-08 19:54:48,765 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:54:48,765 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4)
COLLATE utf8mb4_bin AS anon_1 2019-01-08 19:54:48,765 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:54:48,767 INFO sqlalchemy.engine.base.Engine DESCRIBE `book_m2m_author` 2019-01-08 19:54:48,767 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:54:48,768 INFO sqlalchemy.engine.base.Engine DESCRIBE `books` 2019-01-08 19:54:48,768 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:54:48,769 INFO sqlalchemy.engine.base.Engine DESCRIBE `authors` 2019-01-08 19:54:48,769 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:54:48,777 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-01-08 19:54:48,777 INFO sqlalchemy.engine.base.Engine INSERT INTO authors (name) VALUES (%(name)s) 2019-01-08 19:54:48,777 INFO sqlalchemy.engine.base.Engine {'name': 'Alex'} 2019-01-08 19:54:48,778 INFO sqlalchemy.engine.base.Engine INSERT INTO authors (name) VALUES (%(name)s) 2019-01-08 19:54:48,778 INFO sqlalchemy.engine.base.Engine {'name': 'Jack'} 2019-01-08 19:54:48,779 INFO sqlalchemy.engine.base.Engine INSERT INTO authors (name) VALUES (%(name)s) 2019-01-08 19:54:48,779 INFO sqlalchemy.engine.base.Engine {'name': 'Eric'} 2019-01-08 19:54:48,780 INFO sqlalchemy.engine.base.Engine INSERT INTO books (name, pub_date) VALUES (%(name)s, %(pub_date)s) 2019-01-08 19:54:48,780 INFO sqlalchemy.engine.base.Engine {'name': 'learn python with Alex', 'pub_date': '2019-01-08'} 2019-01-08 19:54:48,780 INFO sqlalchemy.engine.base.Engine INSERT INTO books (name, pub_date) VALUES (%(name)s, %(pub_date)s) 2019-01-08 19:54:48,781 INFO sqlalchemy.engine.base.Engine {'name': 'learn LINUX with Alex', 'pub_date': '2019-01-10'} 2019-01-08 19:54:48,781 INFO sqlalchemy.engine.base.Engine INSERT INTO books (name, pub_date) VALUES (%(name)s, %(pub_date)s) 2019-01-08 19:54:48,781 INFO sqlalchemy.engine.base.Engine {'name': 'learn JAVA with Alex', 'pub_date': '2019-01-09'} 2019-01-08 19:54:48,782 INFO sqlalchemy.engine.base.Engine INSERT INTO book_m2m_author (book_id, author_id) VALUES (%(book_id)s, %(author_id)s) 2019-01-08 19:54:48,782 INFO sqlalchemy.engine.base.Engine ({'book_id': 2, 'author_id': 1}, {'book_id': 2, 'author_id': 2},
{'book_id': 2, 'author_id': 3}, {'book_id': 1, 'author_id': 1}, {'book_id': 1, 'author_id': 3}) 2019-01-08 19:54:48,783 INFO sqlalchemy.engine.base.Engine COMMIT Process finished with exit code 0
mysql> select * from authors; +----+------+ | id | name | +----+------+ | 1 | Alex | | 2 | Jack | | 3 | Eric | +----+------+ 3 rows in set (0.00 sec) mysql> select * from book_m2m_author; +---------+-----------+ | book_id | author_id | +---------+-----------+ | 2 | 1 | | 2 | 2 | | 2 | 3 | | 1 | 1 | | 1 | 3 | +---------+-----------+ 5 rows in set (0.00 sec) mysql> select * from books; +----+------------------------+------------+ | id | name | pub_date | +----+------------------------+------------+ | 1 | learn python with Alex | 2019-01-08 | | 2 | learn LINUX with Alex | 2019-01-10 | | 3 | learn JAVA with Alex | 2019-01-09 | +----+------------------------+------------+ 3 rows in set (0.00 sec)
#查询 #-*-coding:utf-8-*- #__author__ = "logan.xu" from m2m import orm_m2m from sqlalchemy.orm import sessionmaker Session_class=sessionmaker(bind=orm_m2m.engine) #创建与数据库的链接 session= Session_class() #生成session实例 #cursor #插入数据 #b1= orm_m2m.Book(name="learn python with Alex", pub_date="2019-01-08")# #b2= orm_m2m.Book(name="learn JAVA with Alex", pub_date="2019-01-09") #b3= orm_m2m.Book(name="learn LINUX with Alex", pub_date="2019-01-10") # #a1= orm_m2m.Author(name="Alex") #a2= orm_m2m.Author(name="Jack") #a3= orm_m2m.Author(name="Eric") # #b1.authors=[a1,a3] #b3.authors=[a1,a2,a3] # #session.add_all([b1,b2,b3,a1,a2,a3]) #查询数据 author_obj=session.query(orm_m2m.Author).filter(orm_m2m.Author.name=="alex").first() print(author_obj.books) session.commit()
/Users/drizzle/PycharmProjects/2018-12-03/venv/bin/python /Users/drizzle/PycharmProjects/2018-12-03/m2m/orm_m2m_api.py 2019-01-08 19:59:34,860 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2019-01-08 19:59:34,860 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:59:34,862 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names' 2019-01-08 19:59:34,862 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:59:34,864 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2019-01-08 19:59:34,864 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:59:34,865 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin' 2019-01-08 19:59:34,865 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:59:34,868 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2019-01-08 19:59:34,868 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:59:34,869 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2019-01-08 19:59:34,869 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:59:34,870 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4)
COLLATE utf8mb4_bin AS anon_1 2019-01-08 19:59:34,870 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:59:34,871 INFO sqlalchemy.engine.base.Engine DESCRIBE `book_m2m_author` 2019-01-08 19:59:34,871 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:59:34,872 INFO sqlalchemy.engine.base.Engine DESCRIBE `books` 2019-01-08 19:59:34,872 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:59:34,873 INFO sqlalchemy.engine.base.Engine DESCRIBE `authors` 2019-01-08 19:59:34,873 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 19:59:34,879 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-01-08 19:59:34,880 INFO sqlalchemy.engine.base.Engine SELECT authors.id AS authors_id, authors.name AS authors_name FROM authors WHERE authors.name = %(name_1)s LIMIT %(param_1)s 2019-01-08 19:59:34,880 INFO sqlalchemy.engine.base.Engine {'name_1': 'alex', 'param_1': 1} 2019-01-08 19:59:34,882 INFO sqlalchemy.engine.base.Engine SELECT books.id AS books_id, books.name AS books_name,
books.pub_date AS books_pub_date FROM books, book_m2m_author WHERE %(param_1)s = book_m2m_author.author_id AND books.id = book_m2m_author.book_id 2019-01-08 19:59:34,882 INFO sqlalchemy.engine.base.Engine {'param_1': 1} [learn LINUX with Alex, learn python with Alex] 2019-01-08 19:59:34,883 INFO sqlalchemy.engine.base.Engine COMMIT Process finished with exit code 0
#-*-coding:utf-8-*- #__author__ = "logan.xu" from m2m import orm_m2m from sqlalchemy.orm import sessionmaker Session_class=sessionmaker(bind=orm_m2m.engine) #创建与数据库的链接 session= Session_class() #生成session实例 #cursor #插入数据 #b1= orm_m2m.Book(name="learn python with Alex", pub_date="2019-01-08")# #b2= orm_m2m.Book(name="learn JAVA with Alex", pub_date="2019-01-09") #b3= orm_m2m.Book(name="learn LINUX with Alex", pub_date="2019-01-10") # #a1= orm_m2m.Author(name="Alex") #a2= orm_m2m.Author(name="Jack") #a3= orm_m2m.Author(name="Eric") # #b1.authors=[a1,a3] #b3.authors=[a1,a2,a3] # #session.add_all([b1,b2,b3,a1,a2,a3]) #查询数据 author_obj=session.query(orm_m2m.Author).filter(orm_m2m.Author.name=="alex").first() print(author_obj.books) book_obj=session.query(orm_m2m.Book).filter(orm_m2m.Book.id==2).first() print(book_obj) session.commit()
/Users/drizzle/PycharmProjects/2018-12-03/venv/bin/python /Users/drizzle/PycharmProjects/2018-12-03/m2m/orm_m2m_api.py 2019-01-08 20:03:36,934 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2019-01-08 20:03:36,934 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:03:36,936 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names' 2019-01-08 20:03:36,936 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:03:36,937 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2019-01-08 20:03:36,937 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:03:36,938 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin' 2019-01-08 20:03:36,938 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:03:36,941 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2019-01-08 20:03:36,941 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:03:36,942 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2019-01-08 20:03:36,942 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:03:36,942 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4)
COLLATE utf8mb4_bin AS anon_1 2019-01-08 20:03:36,942 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:03:36,943 INFO sqlalchemy.engine.base.Engine DESCRIBE `book_m2m_author` 2019-01-08 20:03:36,943 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:03:36,945 INFO sqlalchemy.engine.base.Engine DESCRIBE `books` 2019-01-08 20:03:36,945 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:03:36,946 INFO sqlalchemy.engine.base.Engine DESCRIBE `authors` 2019-01-08 20:03:36,946 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:03:36,953 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-01-08 20:03:36,953 INFO sqlalchemy.engine.base.Engine SELECT authors.id AS authors_id, authors.name AS authors_name FROM authors WHERE authors.name = %(name_1)s LIMIT %(param_1)s 2019-01-08 20:03:36,953 INFO sqlalchemy.engine.base.Engine {'name_1': 'alex', 'param_1': 1} 2019-01-08 20:03:36,955 INFO sqlalchemy.engine.base.Engine SELECT books.id AS books_id, books.name AS books_name,
books.pub_date AS books_pub_date FROM books, book_m2m_author WHERE %(param_1)s = book_m2m_author.author_id AND books.id = book_m2m_author.book_id 2019-01-08 20:03:36,955 INFO sqlalchemy.engine.base.Engine {'param_1': 1} [learn LINUX with Alex, learn python with Alex] 2019-01-08 20:03:36,957 INFO sqlalchemy.engine.base.Engine SELECT books.id AS books_id, books.name AS books_name,
books.pub_date AS books_pub_date FROM books WHERE books.id = %(id_1)s LIMIT %(param_1)s 2019-01-08 20:03:36,957 INFO sqlalchemy.engine.base.Engine {'id_1': 2, 'param_1': 1} learn LINUX with Alex 2019-01-08 20:03:36,958 INFO sqlalchemy.engine.base.Engine COMMIT Process finished with exit code 0
#-*-coding:utf-8-*- #__author__ = "logan.xu" from m2m import orm_m2m from sqlalchemy.orm import sessionmaker Session_class=sessionmaker(bind=orm_m2m.engine) #创建与数据库的链接 session= Session_class() #生成session实例 #cursor #插入数据 #b1= orm_m2m.Book(name="learn python with Alex", pub_date="2019-01-08")# #b2= orm_m2m.Book(name="learn JAVA with Alex", pub_date="2019-01-09") #b3= orm_m2m.Book(name="learn LINUX with Alex", pub_date="2019-01-10") # #a1= orm_m2m.Author(name="Alex") #a2= orm_m2m.Author(name="Jack") #a3= orm_m2m.Author(name="Eric") # #b1.authors=[a1,a3] #b3.authors=[a1,a2,a3] # #session.add_all([b1,b2,b3,a1,a2,a3]) #查询数据 author_obj=session.query(orm_m2m.Author).filter(orm_m2m.Author.name=="alex").first() print(author_obj.books) book_obj=session.query(orm_m2m.Book).filter(orm_m2m.Book.id==2).first() print(book_obj.authors) session.commit()
/Users/drizzle/PycharmProjects/2018-12-03/venv/bin/python /Users/drizzle/PycharmProjects/2018-12-03/m2m/orm_m2m_api.py 2019-01-08 20:04:49,122 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2019-01-08 20:04:49,122 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:04:49,124 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names' 2019-01-08 20:04:49,124 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:04:49,126 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2019-01-08 20:04:49,126 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:04:49,127 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin' 2019-01-08 20:04:49,127 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:04:49,130 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2019-01-08 20:04:49,130 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:04:49,131 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2019-01-08 20:04:49,131 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:04:49,131 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4)
COLLATE utf8mb4_bin AS anon_1 2019-01-08 20:04:49,131 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:04:49,132 INFO sqlalchemy.engine.base.Engine DESCRIBE `book_m2m_author` 2019-01-08 20:04:49,132 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:04:49,133 INFO sqlalchemy.engine.base.Engine DESCRIBE `books` 2019-01-08 20:04:49,133 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:04:49,135 INFO sqlalchemy.engine.base.Engine DESCRIBE `authors` 2019-01-08 20:04:49,135 INFO sqlalchemy.engine.base.Engine {} 2019-01-08 20:04:49,141 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-01-08 20:04:49,141 INFO sqlalchemy.engine.base.Engine SELECT authors.id AS authors_id, authors.name AS authors_name FROM authors WHERE authors.name = %(name_1)s LIMIT %(param_1)s 2019-01-08 20:04:49,141 INFO sqlalchemy.engine.base.Engine {'name_1': 'alex', 'param_1': 1} 2019-01-08 20:04:49,143 INFO sqlalchemy.engine.base.Engine SELECT books.id AS books_id, books.name AS books_name,
books.pub_date AS books_pub_date FROM books, book_m2m_author WHERE %(param_1)s = book_m2m_author.author_id AND books.id = book_m2m_author.book_id 2019-01-08 20:04:49,143 INFO sqlalchemy.engine.base.Engine {'param_1': 1} [learn LINUX with Alex, learn python with Alex] 2019-01-08 20:04:49,145 INFO sqlalchemy.engine.base.Engine SELECT books.id AS books_id, books.name AS books_name,
books.pub_date AS books_pub_date FROM books WHERE books.id = %(id_1)s LIMIT %(param_1)s 2019-01-08 20:04:49,145 INFO sqlalchemy.engine.base.Engine {'id_1': 2, 'param_1': 1} 2019-01-08 20:04:49,146 INFO sqlalchemy.engine.base.Engine SELECT authors.id AS authors_id, authors.name AS authors_name FROM authors, book_m2m_author WHERE %(param_1)s = book_m2m_author.book_id AND authors.id = book_m2m_author.author_id 2019-01-08 20:04:49,146 INFO sqlalchemy.engine.base.Engine {'param_1': 2} [Alex, Jack, Eric] 2019-01-08 20:04:49,147 INFO sqlalchemy.engine.base.Engine COMMIT Process finished with exit code 0
