pythonのsqlalchemy多对多关系

时间：2023-03-10 04:36:00
pythonのsqlalchemy多对多关系

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

  1. 一本书可以有好几个作者一起出版
  2. 一个作者可以写好几本书
 #!/usr/bin/env python

 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

 from sqlalchemy.orm import sessionmaker

 engine = create_engine("mysql+pymysql://root:root@localhost/testuser?charset=utf8",encoding="utf-8",echo=True)

 # 生成orm基类

 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)
#!/usr/bin/env python

from day12 import orm_m2m_fk

from sqlalchemy.orm import sessionmaker

# 创建与数据库会话实例。

session_class = sessionmaker(bind=orm_m2m_fk.engine)

# 生成session实例

session = session_class()

# b1 = orm_m2m_fk.Book(name="Java从入门到精通",pub_date="2018-08-08")

# b2 = orm_m2m_fk.Book(name="Python从入门到精通",pub_date="2018-08-08")

# b3 = orm_m2m_fk.Book(name="JavaScript从入门到精通",pub_date="2018-08-08")

# b4 = orm_m2m_fk.Book(name=".Net从入门到精通",pub_date="2018-08-08")

#

#

# a1 = orm_m2m_fk.Author(name="Jerry")

# a2 = orm_m2m_fk.Author(name="Tom")

# a3 = orm_m2m_fk.Author(name="Jack")

#

# b1.authors = [a1,a3]

#

# b3.authors = [a1,a2,a3]

#

# b2.authors = [a2,a3]

#

# b4.authors = [a1]

#

# session.add_all([b1,b2,b3,b4,a1,a2,a3])

a = session.query(orm_m2m_fk.Author).filter(orm_m2m_fk.Author.name=="Jerry").first()

print(a.books)

session.commit()
 C:\Users\Administrator\AppData\Local\Programs\Python\Python37\python.exe D:/PythonStudy/charm/01/day12/orm_m2m_aip.py

 2018-09-20 09:22:14,652 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

 2018-09-20 09:22:14,652 INFO sqlalchemy.engine.base.Engine {}

 2018-09-20 09:22:14,656 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()

 2018-09-20 09:22:14,656 INFO sqlalchemy.engine.base.Engine {}

 2018-09-20 09:22:14,656 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'

 2018-09-20 09:22:14,656 INFO sqlalchemy.engine.base.Engine {}

 C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366, "Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA...' for column 'VARIABLE_VALUE' at row 484")

   result = self._query(query)

 2018-09-20 09:22:14,658 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1

 2018-09-20 09:22:14,658 INFO sqlalchemy.engine.base.Engine {}

 2018-09-20 09:22:14,658 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1

 2018-09-20 09:22:14,658 INFO sqlalchemy.engine.base.Engine {}

 2018-09-20 09:22:14,659 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1

 2018-09-20 09:22:14,659 INFO sqlalchemy.engine.base.Engine {}

 2018-09-20 09:22:14,660 INFO sqlalchemy.engine.base.Engine DESCRIBE `book_m2m_author`

 2018-09-20 09:22:14,660 INFO sqlalchemy.engine.base.Engine {}

 2018-09-20 09:22:14,661 INFO sqlalchemy.engine.base.Engine DESCRIBE `books`

 2018-09-20 09:22:14,661 INFO sqlalchemy.engine.base.Engine {}

 2018-09-20 09:22:14,663 INFO sqlalchemy.engine.base.Engine DESCRIBE `authors`

 2018-09-20 09:22:14,663 INFO sqlalchemy.engine.base.Engine {}

 2018-09-20 09:22:14,668 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)

 2018-09-20 09:22:14,668 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

 2018-09-20 09:22:14,669 INFO sqlalchemy.engine.base.Engine {'name_1': 'Jerry', 'param_1': 1}

 2018-09-20 09:22:14,670 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

 2018-09-20 09:22:14,670 INFO sqlalchemy.engine.base.Engine {'param_1': 1}

 [Java从入门到精通, JavaScript从入门到精通, .Net从入门到精通]

 2018-09-20 09:22:14,675 INFO sqlalchemy.engine.base.Engine COMMIT

 Process finished with exit code 0

相关文章