1.建立数据表
# Author : Xuefeng
import sqlalchemy
from sqlalchemy import create_engine,Column,Integer,String,DATE,Table,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship,Mapper
from sqlalchemy.ext.declarative import declarative_base
# charset=utf8 支持中文
engine = create_engine("mysql+pymysql://Jim:123456@localhost/testdb?charset=utf8",
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 Author(Base):
__tablename__ = "authors"
name = Column(String(32),nullable=False)
id = Column(Integer,primary_key=True)
# books = relationship("Book",secondary=book_m2m_author,backref="authors") #,back_ref="authors"
def __repr__(self):
return "name:%s" %self.name
class Book(Base):
__tablename__ = "books"
id = Column(Integer,primary_key=True)
name = Column(String(32),nullable=False)
pub_date = Column(DATE,nullable=False)
authors = relationship("Author",secondary=book_m2m_author,backref="books")
def __repr__(self):
return "name:%s authors:%s" %(self.name,self.authors)
# Mapper(Book,book_m2m_author)
Base.metadata.create_all(bind=engine)
2.添加数据并查看
# Author : Xuefeng
from sqlalchemy.orm import sessionmaker
import mysql_m2m
Session_class = sessionmaker(bind=mysql_m2m.engine)
session = Session_class()
b1 = mysql_m2m.Book(name="hello",pub_date="2011-02-9")
b2 = mysql_m2m.Book(name="world",pub_date="2011-03-9")
b3 = mysql_m2m.Book(name="roal",pub_date="2011-08-9")
a1 = mysql_m2m.Author(name="Jim")
a2 = mysql_m2m.Author(name="Tom")
a3 = mysql_m2m.Author(name="Mary")
b1.authors = [a1,a3]
b2.authors = [a2,a1,a3]
# session.add_all([b1,b2])
author_obj = session.query(mysql_m2m.Author).filter(mysql_m2m.Author.name=="Jim").first()
print(author_obj.name,author_obj.books)
book_obj = session.query(mysql_m2m.Book).filter(mysql_m2m.Book.id==3).first()
print(book_obj.authors,book_obj.name,book_obj.pub_date)
# book_obj.authors.remove(author_obj)
session.commit()