Flask框架八：ORM操作MySQL(二)

1、外键以及它的四种约束

①前提：只有innodb数据库才支持外键，如果是myisam引擎它是在MySQL5.5之前默认的引擎不支持事务以及外键，是无法切换外键的，可以通过修改配置文件改变创建的默认引擎，点击查看详情
②使用：SQL alchemy也支持创建外键，通过ForeignKey来实现

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, TEXT, ForeignKey
from sqlalchemy.orm import sessionmaker

USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
BD_URL = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(BD_URL)
Base = declarative_base(engine)
session = sessionmaker(bind=engine)()


# user/article
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50))


class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50))
    context = Column(TEXT, nullable=False)
    uid = Column(Integer, ForeignKey('user.id',[ondelete='外键约束']))  # 外键里面传的参数是想要关联的哪个字段


# Base.metadata.create_all()

user = User(username='wchao')
session.add(user)
session.commit()
article = Article(title='python', context='人生苦短我用python', uid=1)
session.add(article)
session.commit()
res = session.query(User).first()
session.delete(res)
session.commit()

③外键约束
restrict：父表数据被删除，会阻止删除，默认约束就是它
no action：在MySQL中和restrict一样
cascade：级联删除，父表删除数据的时候跟它相关的记录也删除掉
set null：父表数据被删除，子表数据会设置为null(会经常使用到)
在创建外键的时候可以添加外键的约束uid=Column(Integer,ForeignKey('user.id',ondelete='RESTRICT'))
④外键的查询

article=session.query(Article).first()
uid = article.uid
user = session.query(User).get(uid)
print(user)
# 或者这样写
user = session.query(User).filter(User.id==Article.uid).first()
print(user)

2、表关系

2.1一对多

要通过外键才能引用，比如A模型中有relationship(B模型)那么就可以可以通过B模型来访问A模型中的属性，反之也一样，如果加上反向访问backref=（名称）则两者都可以通过字段名互相访问，详情看代码

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, TEXT, ForeignKey
from sqlalchemy.orm import sessionmaker,relationship

USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
BD_URL = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(BD_URL)
Base = declarative_base(engine)
session = sessionmaker(bind=engine)()

# 一个用户对应多篇文章
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50))
    # 反向访问属性，通过一句话关联两个表
    article = relationship('Article',backref='author')  # 通过这个字段连接两个表使得article表可以和user表通信，不会映射到数据库中
    def __str__(self):
        return 'username:%s'%self.username


class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(50))
    context = Column(TEXT, nullable=False)
    uid = Column(Integer, ForeignKey('user.id',ondelete='set null'))
    author = relationship('User')  # 通过这个字段连接两个表使得user表可以和article表通信，不会映射到数据库中
    def __str__(self):
        return 'title:%s'%self.title
Base.metadata.drop_all()
Base.metadata.create_all()
user = User(username='wchao')
session.add(user)
session.commit()
article = Article(title='python', context='人生苦短我用python', uid=1)
session.add(article)
session.commit()
# 根据article查询user中的username
res = session.query(Article).first()
print(res.author)
# 根据user查询article中的title
rs = session.query(User).first()
print(rs.article[0])

# 添加单条数据
user = User(username='beiy')
article = Article(title='python',context='何来人间惊鸿客')
article.author = user
session.add(article)
session.commit()
# 添加多条数据
user = User(username='beiy')
article1 = Article(title='python1',context='何来人间惊鸿客')
article2 = Article(title='python2',context='何来人间惊鸿客')
article3 = Article(title='python3',context='何来人间惊鸿客')
article1.author = user
article2.author = user
article3.author = user
session.add(article1)
session.add(article2)
session.add(article3)
session.commit()

2.2一对一

只需要在关系映射中添加uselist=false字段就可以在数据库中插入一对一的字段，它相当于将大表划分为小表，实际应用上将常用查询和不常用查询分开为两个表。

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Integer,ForeignKey
from sqlalchemy.orm import relationship,sessionmaker


USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
BD_URL = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(BD_URL)
Base = declarative_base(engine)
session = sessionmaker(bind=engine)()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String(50))
    password = Column(String(100))
    addresses = relationship("Address", backref='user', uselist=False)


class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(50))
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship('User', backref='address')
    
user = User(username='wchao')   
address1 = Address(email_address='sx')
address2 = Address(email_address='sx')
address1.user=user
address2.user=user
session.add(address1)
session.add(address2)
session.commit()

2.3多对多

多对多需要一个中间表作为连接，首先通过table来创建一个中间表，table要传四个参数，第一个是中间表的名称，第二个是Base的元类，第三个和第四个是要连接的两个表，这里面的参数分别是链接表的外键名称，外键的类型，外键的表名对应的外键字段，详情看代码(多个老师对应多个课程)

from sqlalchemy import create_engine,Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,String,Integer,ForeignKey
from sqlalchemy.orm import relationship,sessionmaker


USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
BD_URL = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(BD_URL)
Base = declarative_base(engine)
session = sessionmaker(bind=engine)()
teacher_class = Table(
    'teacher_class',
    Base.metadata,
    Column('teacher_id',Integer,ForeignKey('teacher.id')),
    Column('class_id',Integer,ForeignKey('class.id'))
)
class Teacher(Base):
    __tablename__ = 'teacher'
    id = Column(Integer, primary_key=True)
    username = Column(String(50))
    classes = relationship("Class", backref='teacher', secondary=teacher_class)
    def __str__(self):
        return 'teacher(name:%s)'%self.username


class Class(Base):
    __tablename__ = 'class'
    id = Column(Integer, primary_key=True)
    class_name = Column(String(50))
    def __str__(self):
        return 'teacher(name:%s)'%self.class_name

# Base.metadata.drop_all()
Base.metadata.create_all()
teacher1=Teacher(name='wchao')
teacher2=Teacher(name='jya')
class1=Class(class_name='chinese')
class2=Class(class_name='english')
teacher1.classes.append(class1)
teacher1.classes.append(class2)
teacher2.classes.append(class1)
teacher2.classes.append(class2)
session.add(teacher1)
session.add(teacher2)
session.commit()
# 查询老师对应所教的班级有哪些班
teacher = session.query(Teacher).first()
print(teacher)
for i in teacher.classes:
    print(i)
# 查询班级对用的老师
classes = session.query(Class).first()
for i in classes.teacher:
    print(i)

3、排序

可以在定义模型的时候让他排序好，或者在写查询语句的时候给他排序，都是通过order_by来实现额，在模型中使用到了__mapper_args__实现排序

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column,String,Integer,ForeignKey


USERNAME = 'root'
PASSWORD = 'root'
HOSTNAME = '127.0.0.1'
PORT = '3306'
DATABASE = 'test'
BD_URL = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(USERNAME, PASSWORD, HOSTNAME, PORT, DATABASE)
engine = create_engine(BD_URL)
Base = declarative_base(engine)
session = sessionmaker(bind=engine)()

class Teacher(Base):
    __tablename__ = 'teacher'
    id = Column(Integer, primary_key=True)
    username = Column(String(50))
    def __str__(self):
        return 'teacher(name:%s)'%self.username
    __mapper_args__ = {
        # 'order_by':id  # 升序
        'order_by':id.desc()  # 降序
    }
Base.metadata.drop_all()
Base.metadata.create_all()
for i in range(10):
    teacher = Teacher(username='iam%s'%i)
    session.add(teacher)
session.commit()
# 默认是升序
teacher = session.query(Teacher).oreder_by(Teacher.id).all()
# 给想要查询的字段加上desc就会变成降序
teacher = session.query(Teacher).oreder_by(Teacher.id.desc()).all()
# teacher = session.query(Teacher).oreder_by(-Teacher.id).all()
for i in teacher:
    print(i)

4、实现翻页

①limit：可以限制每次查询显示有几条数据
②offset：可以限制查找数据的时候过滤掉前面的数据有多少条(只查想要的数据)
③切片：对query对象使用切片操作可以获得想要的数据(查询所有在切片)
③的查询结果虽然也可以做到②的效果，但是，②的查询量更小

# 查询前三条数据
teacher1 = session.query(Teacher).limit(3).all()
# 查询3-5条数据，offset是从0开始的,偏移量
teacher2 = session.query(Teacher).offset(3).limit(3).all()
teacher3 = session.query(Teacher)[2:5]
for i in teacher1:
    print(i)

5、高级查询

①group_by分组查询：根据某个字段进行分组，比如想要根据性别进行分组，分别统计每个分组有多少个人，聚合函数一般和分组配合使用

session.query(User.gender,func.count(User.id)).group_by(User.gender).all()
-- query中放入映射的字段，如果只传入模型，那么会将模型中的所有字段都查询出来，如果查询所有数据，是会在数据库层面报错的，因为分完组后，sql就不知道怎么显示所有的数据了

②having对分组后的人过滤：having是对查找结果进一步的过滤，比如想要查看未成年人的数量，可以先对年龄进行分组统计人数，然后再对分组进行having过滤

result = session.query(User.age,func.count(User.id)).group_by(User.age).having(User.age>=18)

③join方法：一种是inner join内连接另外一种是outer join，默认的是inner join，如果指定left join 或者right join则为outer join，如果想要查询user以及其对应的address

for u,a in session.query(User,Address).filter(User.id==Address.user_id).all()
	print(u,a)
for u,a in session.query(User,Address).join(Address).all()
	print(u,a)
# 取别名
from sqlalchemy.orm import aliased
adalias1 = aliased(Address)
adalias2 = aliased(Address)
for username,email1,email2 in session.query(User.name,adalias1.email_address,adalias2.email_address).join(adalias1).join(adalias2).all():
    print(username,email1,email2)

④子查询
通过subquery()方法将普通查询变为子查询，通过lable取个别名，通过子查询.c.属性取访问子查询出来的列，相当于column

from sqlalchemy.sql import func
# 构造子查询
stmt = session.query(Address.user_id.label('user_id'),func.count(*).label('address_count')).group_by(Address.user_id).subquery()
# 将子查询放到父查询中
for u,count in session.query(User,stmt.c.address_count).outerjoin(stmt,User.id==stmt.c.user_id).order_by(User.id):
    print u,count