一对多或多对多的情况下,需要新建一张表来进行关联
from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:godme@localhost/godme', encoding='utf-8', echo=False)
Base = declarative_base()
book2author = Table(
'book2author', Base.metadata,
Column('book_id', Integer, ForeignKey('book.id')),
Column('author_id', Integer, ForeignKey('author.id'))
)
class Book(Base):
__tablename__ = 'book'
id = Column(Integer, primary_key=True)
name = Column(String(64))
author = relationship('Author', secondary=book2author, backref='book')
def __repr__(self):
return 'book:{}'.format(self.name)
class Author(Base):
__tablename__ = 'author'
id = Column(Integer, primary_key=True)
name = Column(String(64), nullable=False)
def __repr__(self):
return 'author:{}'.format(self.name)
sessionclass = sessionmaker(engine)
session = sessionclass()
b1 = Book(name='one')
b2 = Book(name='two')
b3 = Book(name='three')
a1 = Author(name='godme')
a2 = Author(name='judas')
a3 = Author(name='foreva')
b1.author = [a1, a2]
b2.author = [a2, a3]
b2.author = [a3, a1]
session.add_all([b1, b2, b3,a1, a2, a3])
session.commit()
b = session.query(Book).filter_by(name='one').first()
print(b)
print(b.author)
session.close()
关联删除
b = session.query(Book).filter_by(name='one').first()
print(b)
print(b.author)
a = session.query(Author).filter_by(name='godme').first()
b.author.remove(a)
session.commit()
b = session.query(Book).filter_by(name='one').first()
print(b)
print(b.author)
session.close()
直接在对象中移除,表中数据也会自动管理移除
同样的,如果基本表中数据被删除,中间表的对应对象也会被删除
a = session.query(Author).filter_by(name='godme').first()
session.delete(a)
其中新增的知识点不过两点
1. 表结构的关联声明
2. 数据的关联插入
表结构的声明关联
author = relationship('Author', secondary=book2author, backref='book')
在声明关联中,secondary关键字表示中间关联表
数据的关联插入
b1.author = [a1,a2]
数据表对象直接对relationship声明对象进行赋值即可
注意:
1. 有基本的表对象
2. 必须要有反查的关联表
3. 有声明两表的关联表
这样这张关联表的维护就会自动交给系统自己管理
总的来说,就是三方面的综合
1. 表的两种创建方式
2. 表的两种不同关联
3. 关联表的数据操作
不过其中有几点值得注意,避免遇坑
1. relationship关联,默认参数是本表可反查表,backref是当前表
2. 中间表直接传对象而不是字符串
3. 表名不论类声明,对象传递或者是字符串传递,一律保证一直并首字母大写
3. 除去中间表,基本表最好实现一些方法,查出对象方便操作,尤其是repr方法,方便确认查询对象
4. 想在数据库中存入中文,最好采取以下方式
engine = create_engine('mysql+pymysql://root:godme@localhost/godme?charset=utf-8', echo=False)
事实证明,encoding并没有什么卵用