mysql——sqlAlchemy&ORM：一对多

一对多：

（一）表的创建

步骤1、创建外键，建立一对多关系，建在多里

步骤2、创建关联，建在一或多里均可，与表结构无关，仅方便添加和查询

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/chouti?charset=utf8")

Base = declarative_base()

class Favor(Base):
    __tablename__ = 'favor'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    caption = Column(String(50),default='red',unique=True)
    # pers = relationship("Person",backref='favor')       # 创建关联，与下同
    def __repr__(self):
        return "%s-%s"%(self.nid,self.caption)

class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer,primary_key=True,autoincrement=True)
    name = Column(String(32),index=True,nullable=True)
    favor_id = Column(Integer,ForeignKey("favor.nid"))    # 创建外键
    favor = relationship("Favor",backref='pers')          # 创建关联，与表结构无关，只是方便查询

Base.metadata.create_all(engine)
# Base.metadata.drop_all(engine)

（二）数据添加

方式1、直接对外键列进行赋值　　

方式2、指定关联项

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

from one_2_many_create import Favor,Person

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/chouti?charset=utf8")

Session = sessionmaker(bind=engine)
session = Session()

# 1、添加数据方式一：直接给外键列赋值
f1=Favor(caption='blue')
f2=Favor(caption='orange')
f3=Favor(caption='yellow')
p1=Person(name='lucy',favor_id=1)
p2=Person(name='jack',favor_id=3)
p3=Person(name='even',favor_id=2)
p4=Person(name='luna',favor_id=1)
session.add_all([f1,f2,f3,p1,p2,p3,p4])
session.commit()

# 2、添加数据方式二：指定关联
f2_1 = Favor(caption='white')
f2_2 = Favor(caption='black')
f2_3 = Favor(caption='pink')
p2_1 = Person(name='elen')
p2_2 = Person(name='leo')
p2_3 = Person(name='haha')
p2_4 = Person(name='lala')

f2_1.pers = [p2_1,p2_3]
f2_2.pers = [p2_2]
f2_3.pers = [p2_4]
session.add_all([f2_1,f2_2,f2_3,p2_1,p2_2,p2_3,p2_4])
session.commit()

注意：在已有数据中新添加数据，应该append，不能直接赋值，

# 在已有数据中新添加数据，应该append，不能直接赋值，
f = session.query(Favor).filter(Favor.caption=='blue').first()
print(f.pers)
p_new = Person(name='dodo')
f.pers.append(p_new)

session.add(f)
session.commit()

（三）数据查询

1、单表查询

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

from one_2_many_create import Favor,Person

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/chouti?charset=utf8")

Session = sessionmaker(bind=engine)
session = Session()

# # 单表查询
# ret = session.query(Person).filter(Person.nid==4).first()
# print(ret.name)

2、跨表查询，session（）.query（）.join（）

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

from one_2_many_create import Favor,Person

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/chouti?charset=utf8")

Session = sessionmaker(bind=engine)
session = Session()

# 跨表查询
# FROM person INNER JOIN favor ON favor.nid = person.favor_id
ret = session.query(Person.name,Favor.caption).join(Favor)
print(ret)
ret = session.query(Person.name,Favor.caption).join(Favor).all()
print(ret)

# FROM favor INNER JOIN person ON favor.nid = person.favor_id
ret = session.query(Favor.caption,Person.name).join(Person)
print(ret)
ret = session.query(Favor.caption,Person.name).join(Person).all()
print(ret)

# 为字段重命名 .lable（"..."）
ret = session.query(Favor.caption.label('kkk'),Person.name.label('ggg')).join(Person)
print(ret)
# SELECT favor.caption AS kkk, person.name AS ggg FROM favor INNER JOIN person ON favor.nid = person.favor_id

3、联表查询，relationship（）,

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

from one_2_many_create import Favor,Person

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/chouti?charset=utf8")

Session = sessionmaker(bind=engine)
session = Session()

# 联表查询
# 1、一查多，从表查主表
f1 = session.query(Favor).filter(Favor.caption=='blue').first()
print(f1)                   # 单表查询，返回Favor.__repr__()设置的返回值
print(f1.pers)              # 关联查询，返回符合条件的从表对象列表
for i in f1.pers:
    print(i.name)           # 提取从表对象中的具体字段信息
# 2、多查一，主表查从表
p1 = session.query(Person).filter(Person.name=='lucy').first()
print(p1)
print(p1.favor)
print(p1.name, p1.favor.caption)

　　注意：

　　（1）使用联表查询之前，需要在表类中创建relationship关联，指明关联字段

　　（2）一查多，即从表查主表，获取具体字段信息时，需循环遍历，

　　（3）多查一，即主表查从表，获取字段时可以直接使用，