004-sqlalchemy-Python_sqlalchemy实现简单的多对多数据表的创建

1 建立数据表

 1 import sqlalchemy
 2 from sqlalchemy import create_engine,Column,Integer,String,DATE,Table,ForeignKey
 3 from sqlalchemy.orm import sessionmaker,relationship,Mapper
 4 from sqlalchemy.ext.declarative import declarative_base
 5 
 6 # charset=utf8 支持中文
 7 engine = create_engine("mysql+pymysql://Jim:123456@localhost/testdb?charset=utf8",
 8                        encoding="utf-8", echo=True)
 9 Base = declarative_base()
10 
11 book_m2m_author = Table(
12     "book_m2m_author",Base.metadata,
13     Column("book_id",Integer,ForeignKey("books.id")),
14     Column("author_id",Integer,ForeignKey("authors.id"))
15 )
16 
17 class Author(Base):
18     __tablename__ = "authors"
19     name = Column(String(32),nullable=False)
20     id = Column(Integer,primary_key=True)
21     # books = relationship("Book",secondary=book_m2m_author,backref="authors")  #,back_ref="authors"
22 
23     def __repr__(self):
24         return "name:%s" %self.name
25 
26 class Book(Base):
27     __tablename__ = "books"
28     id = Column(Integer,primary_key=True)
29     name = Column(String(32),nullable=False)
30     pub_date = Column(DATE,nullable=False)
31     authors = relationship("Author",secondary=book_m2m_author,backref="books")
32 
33     def __repr__(self):
34         return "name:%s authors:%s" %(self.name,self.authors)
35 
36 # Mapper(Book,book_m2m_author)
37 Base.metadata.create_all(bind=engine)

2 添加数据并查看

 1 from sqlalchemy.orm import sessionmaker
 2 import mysql_m2m
 3 
 4 Session_class = sessionmaker(bind=mysql_m2m.engine)
 5 session = Session_class()
 6 
 7 b1 = mysql_m2m.Book(name="hello",pub_date="2011-02-9")
 8 b2 = mysql_m2m.Book(name="world",pub_date="2011-03-9")
 9 b3 = mysql_m2m.Book(name="roal",pub_date="2011-08-9")
10 
11 a1 = mysql_m2m.Author(name="Jim")
12 a2 = mysql_m2m.Author(name="Tom")
13 a3 = mysql_m2m.Author(name="Mary")
14 
15 b1.authors = [a1,a3]
16 b2.authors = [a2,a1,a3]
17 
18 # session.add_all([b1,b2])
19 
20 author_obj = session.query(mysql_m2m.Author).filter(mysql_m2m.Author.name=="Jim").first()
21 print(author_obj.name,author_obj.books)
22 book_obj = session.query(mysql_m2m.Book).filter(mysql_m2m.Book.id==3).first()
23 print(book_obj.authors,book_obj.name,book_obj.pub_date)
24 # book_obj.authors.remove(author_obj)
25 session.commit()