flask框架数据库部分(四)

1.数据库基本介绍

数据库特点

• 持久化存储
• 读写速度极高
• 保证数据的有效性
• 对程序支持性很好

数据库理解

• 数据行（记录）
• 数据列（字段）
• 数据表（数据行的集合）
• 数据库（数据表的集合）

MySQL

是一个关系型数据库管理系统。
特点

• 使用C、C++编写，使用了多种编译器测试，保证源代码的可移植性
• 支持多种操作系统，Linux、Windows、AIX、FreeBSD、HP-UX、MacOS、NovellNetware、OpenBSD、OS/2Wrap、Solaris
• 为多种编程语言提供了API，如C、C++、Python、Java、Perl、PHP、Eiffel、Ruby
• 支持多线程，充分利用CPU资源
• 优化的SQL查询算法，有效地提高查询速度
• 提供多种语言支持，常见的编码如GB2312、BIG5、UTF8
• 提供TCP/IP、ODBC和JDBC等多种数据库连接途径
• 提供用于管理、检查、优化数据库操作的管理工具
• 大型的数据库。可以处理拥有上万条的大型数据库
• 支持多种存储引擎
• 采用了双授权政策，分为社区版和商业版，体积小，速度快，总体拥有成本低，开放源码的特点。中小网站开发都选择使用MySQL作为网站数据库。
• MySQL使用标准的SQL数据语言形式
• MySQL是可以制定的，才用了GPL协议，可以修改源码来开发自己的MySQL系统
• 在线DDL更改功能
• 复制全局事务标识
• 复制无崩溃从机
• 复制多线程从机

2.SQLALchemy介绍和基本使用

使用准备
数据库是一个网站的基础。flask可以使用很多数据库，如MySQL、MongoDB、SQLite、PostgreSQL等。以MySQL为例，如果想要操作数据库，我们可以使用ORM来操作数据库。
flask中的数据库操作之前，先要安装的模块：

• mysql：Windows在官网下载。Ubuntu，通过sodu apt-get install mysql-server libmysqlclient-dev -yq。
• pymysql：是用Python来操作mysql的包。
• SQLALchemy：是一个数据库ORM框架。
  通过SQLALchemy连接数据库

from sqlalchemy import create_engine  #导入库 创建搜索引擎
#数据库的配置变量
HOSTNAME='127.0.0.1'   #连接数据库的域名
PORT='3306'  #数据库监听的端口号
DATABASE='XXXX'  #  数据库的名字
USERNAME='root'  #连接数据库的用户名
PASSWORD='root'  #连接数据库的密码

#固定格式
DB_URI='myql+pymysql://{}:{}@{}:{}/{}'.format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE)
#创建搜索引擎
engine=create_engine(DB_URL)
#创建连接
with engine.connect() as con:
	result = con.execute('select * from students;')
	print(result.fetchone())
	
#数据库配置选项单独放在一个constans.py的文件中

首先从sqlalchemy中导入create_engine，这个函数创建引擎，然后用engine.connect()来连接数据库。通过create_engine函数中的DB_URI，需要传递一个满足某种格式的字符串。
dialect+driver://username:password@host:port/database?charset=utf8

使用SQLALchemy执行原生SQL

from sqlalchemy import create_engine
from constants import DB_URI

#连接数据库
engine = create_engine(DB_URI,echo=True)
# 使用with语句连接数据库，如果发生异常会被捕获
with engine.connect() as con:
    # 先删除users表
    con.execute('drop table if exists authors')
    # 创建一个users表，有自增长的id和name
    con.execute('create table authors(id int primary key auto_increment,'name varchar(25))')
    # 插入两条数据到表中
    con.execute('insert into persons(name) values("abc")')
    con.execute('insert into persons(name) values("xiaotuo")')
    # 执行查询操作
    results = con.execute('select * from persons')
    # 从查找的结果中遍历
    for result in results:
        print(result)

3.ORM介绍

随着项目增大，采用原生SQL的方式在代码中会出现大量的SQL语句，对项目的进展非常不利

• SQL语句重复利用率不高，越复杂的SQL语句条件越多，代码越长。
• 很多SQL语句是在业务逻辑中拼出来的，如果数据库需要更改，就要去修改这些逻辑，很容易漏掉某些SQL语句的修改
• 写SQL时容易忽略web安全问题

ORM：Object Relationship Mapping，对象关系映射，通过ORM我们可以通过类的方式去操作数据库，而不是写原生的SQL语句。通过把表映射成类，把行作为实例，把字段作为属性，ORM在执行对象操作时候最终还是会把对应的操作转换为数据库原生语句。

使用ORM的优点

• 易用性：使用ORM做数据库开发可以有效减少SQL语句，写出来的模型也更见直观
• 性能损耗小
• 设计灵活：可以轻松写出来复杂的查询
• 可移植性：SQLALchemy封装了底层的数据库实现，支持多个关系型数据库，包括MySQL，SQLite

使用SQLALchemy
要使用ORM来操作数据库，首先需要创建一个类来与对应的表进行映射。现在User表来作为例子，它有自增长的id、name、fullname、password这些字段

from sqlalchemy import create_engine,Column,Integer,String
from constants import DB_URL  #在constants文件中 导入DB_URL
from sqlalchemy.ext.declarative import declarative_base

engine=create_engine(DB_URL,echo=True)

#所有类都要继承'declarative_base'这个函数生成的基类
Base=declarative_base(engine)
class User(Base):
	#定义表名为users
	__tablename__='user'
	#将id设置为主键，并且默认是增长的
	id=Column(Integer,primary_key=True)
	#name字段，字符类型，最大的长度是50个字符
	name=Column(String(50))
	fullname=Column(String(50))
	password=Column(String(100))
	#让打印出来的数据更好看，可选的
	def __repr__(self):
		return '<User(id='%s', name='%s', fullname='%s', password='%s')>'%(self.id, self.name, self.fullname, self.password)

sqlalchemy会自动的设置第一个Integer的主键并且没有被标记为外键的字段添加自增长的属性。因此以上例子中id自动变成自增长的。以上创建完表和表映射的类后，还没有真正的映射到数据库中，执行以下代码将类映射到数据库中。

Base.metadata.create_all()

在创建完数据表，并且做完和数据库的映射后，接下来让我们添加数据进去

ed_user=User(name='haha',fullname='liang haha', password='edspassword')
#打印名字，密码， id 来检查
print(en_user.name,ed_user.password, ed_user.id)

name和password可以正常打印，id为None，这是因为id是一个自增长的主键，还未插入到数据库中，id不存在。

我们把创建的数据插入到数据库中。
用Session对象。

from sqlalchemy.orm import sessionmaker
Session=sessionmaker(bind=engine)
#或者使用下面这种
#Session=sessionmaker()
#Session.conffigure(bind=engine)

session=Session()
ed_user=User(name='haha',fullname='lianghaah',password='edspassword')
session.add(ed_user)

只把数据添加到session中，但是并没有真正的把数据存储到数据库中。如果需要把数据存储到数据库中，还要做一次commit操作。

session.commit()
#打印ed_userd的id
print(ed_user.id)

ed_user就已经有id。说明已经插入到数据库中。在sqlalchemy的ORM实现中，在做commit操作之前，所有的操作都是在事务中进行的，因此如果你要将事务中的操作真正的映射到数据库中，还需要做commit操作。既然用到了事务，这里就并不能避免的提到一个回滚操作了，一下代码展示了如何使用回滚。

#修改ed_user的用户名
ed_user.name='Edwaro'
#创建一个新用户
fake_user=User(name='fakeuser', fullname='Invalid', password='123456')
#将创建的fake_user添加到session中
session.add(fake_user)

#判断'fake_user'是否在 'session'中存在
print(fake_user in session)

#从数据库中查找name=Ed用户
tmp_user=session.query(User).filter_by(name='Edwardo')
#打印tmp_user的name
print(temp)
#打印出查找到的tmp_user对象，注意这个对象的name属性已经在事务中被修改为Edwardo
><User(name='Edwardo', fullname='Ed', password='edspassword')>
#刚刚所有的操作都是在事务中进行的，现在来做回滚操作
session.rollback()
#在打印出tmp_user
print(tmp_user)


#再看fake_user是否还在session中
print(fake_user in session)

进行查找操作，查找操作是通过session.query()方法实现的，这个方法会返回一个Query对象，Query对象相当于一个数组，装载了查找出来的数据，并且可以进行迭代。具体里面装的什么数据，就要看向session.query()方法传的什么参数了，如果只是传一个ORM的类名作为参数，那么提取出来的数据就是都是这个类的实例了

for instance in session.query(User).order_by(User.id):
	print(instance)

如果传递了两个及其以上的对象，或者是传递的是ORM类的属性，那么查找出来的就是元组

for instance in session.query(User.name):
	print(inistance)

for instance in session.query(User.name,User.fullname):
    print(instance)

for instance in session.query(User,User.name).all():
    print(instance)

如果想对结果进行过滤，可以使用filter_by和filter两个方法，这两个方法都是用来做过滤的，区别在于，filter_by是传入关键字参数，filter是传入条件判断，并且filter能够传入的条件更多更灵活。

# 第一种：使用filter_by过滤：
for name in session.query(User.name).filter_by(fullname='Ed Jones'):
    print(name)

# 第二种：使用filter过滤：
for name in session.query(User.name).filter(User.fullname=='Ed Jones'):
    print(name)

4.SQLALchemy属性常用数据类型

sqlalchemy常用数据类型

• Integer：整型
• Float：浮点整型
• Boolean：传递True/False进去
• DECIMAL：定点类型
• enum：枚举类型
• Date：传递datetime.date()进去
• DateTime：传递datetime.datetime()进去
• Time：传递datetime.time()进去
• String：字符类型，使用时需要指定长度，区别于Text类型
• Text：文本类型
• LONGTEXT：长文本类型

Column常用参数

• default：默认值
• nullable：是否为空
• primary_key：是否为主键
• unique：是否唯一
• autoincrement：是否自动增长
• onupdate：更新的时候执行的函数
• name：该属性在数据库中的字段映射

query可用参数
1.模型对象。指定查找这个模型中所有的对象
2.模型中的属性。可以指定只查找某个模型的其中几个属性
3.聚合函数。

• func.count：统计行的数量
• func.avg：求平均值
• func.max：求最大值
• func.min：求最小值
• func.sum：求和

过滤对象
过滤是数据提取的一个很重要的功能，过滤条件都是只能通过filter方法实现的。
equals

query.filter(User.name=='ed')

not equals

query.filter(User.name != 'ed')

like

query.filter(User.name.like('%ed%'))

in

query.filter(User.name.in_(['ed','wendy','jack']))
# 同时，in也可以作用于一个Query
query.filter(User.name.in_(session.query(User.name).filter(User.name.like('%ed%'))))

not in

query.filter(~User.name.in_(['ed','wendy','jack']))

is null

query.filter(User.name==None)
#或者是
query.filter(User.name.is_(None))

is not null

query.filter(User.name != None)
# 或者是
query.filter(User.name.isnot(None))

and

from sqlalchemy import and_
query.filter(and_(User.name=='ed',User.fullname=='Ed Jones'))
# 或者是传递多个参数
query.filter(User.name=='ed',User.fullname=='Ed Jones')
# 或者是通过多次filter操作
query.filter(User.name=='ed').filter(User.fullname=='Ed Jones')

or

from sqlalchemy import or_
query.filter(or_(User.name=='ed', User.name=='wendy'))

1.外键及其四种约束

外键
子啊MySQL中，外键可以让表之间的关系更加紧密。而sqlalchemy也支持外键。通过ForeignKey类来实现，并且可以指定表的外键约束

class Article(Base):
    __tablename__ = 'article'
    id = Column(Integer,primary_key=True,autoincrement=True)
    title = Column(String(50),nullable=False)
    content = Column(Text,nullable=False)
    uid = Column(Integer,ForeignKey('user.id'))
    def __repr__(self):
        return "<Article(title:%s)>" % self.title
class User(Base):
    __tablename__ = 'user'
    id = Column(Integer,primary_key=True,autoincrement=True)
    username = Column(String(50),nullable=False)

外键约束有以下几项：
1.PESTRICT：父表数据被删除，会阻止删除。默认就是这一项。
2.NO ACTION：在MySQL中，同RESTRICT
3.CASCADE：级联删除
4.SET NULL：父表数据被删除，子表数据会设置为NULL

表关系
表之间的关系存在三种：一对一、一对多、多对多。而sqlalchemy中的ORM也可可以模拟这三种关系。因为一对一其实在SQLAlchemy中底层是通过一对多的方式模拟的。

一对多
拿之前的user表为例，假如现在要添加一个功能，要保存用户的邮箱帐号，并且邮箱帐号可以有多个，这时候就必须创建一个新的表，用来存储用户的邮箱，然后通过user.id来作为外键进行引用

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship
class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer,primary_key=True)
    email_address = Column(String,nullable=False)
    user_id = Column(Integer,ForeignKey('users.id'))
    user = relationship('User',backref="addresses")
    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer,primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(100))
    addresses = relationship("Address",backref="user")

一对一
是一对多的特殊情况，一对应的是User表，而多对应的是Address，也就是说一个User对象有多个Address。因此要将一对多转换成一对一，要设置一个User对象对应一个Address对象即可

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer,primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(100))

    addresses = relationship("Address",backref='addresses',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('Address',backref='user')

只要在User表中的addresses字段上添加uselist=False就可以达到一对一效果。

多对多
多对多需要一个中间表来作为连接，在sqlalchemy中的orm也需要一个中间表。

association_table = Table(
    'teacher_classes',
    Base.metadata,
  	Column('teacher_id',Integer,ForeignKey('teacher.id')),
  	Column('classes_id',Integer,ForeignKey('classes.id'))
  )

class Teacher(Base):
    __tablename__ = 'teacher'
    id = Column(Integer,primary_key=True)
    tno = Column(String(10))
    name = Column(String(50))
    age = Column(Integer)
    classes = relationship('Classes',secondary=association_table,backref='teachers')

 class Classes(Base):
    __tablename__ = 'classes'
    id = Column(Integer,primary_key=True)
    cno = Column(String(10))
    name = Column(String(50))
    teachers = relationship('Teacher',secondary=association_table,backref='classes')

要创建一个多对多的关系表，首先需要一个中间表，通过table来创建一个中间表。

6.排序和查询高级

排序
1.order_by：可以指定根据这个表中的某个字段进行排序，如果在前面加了一个，代表的是降序排序。
2.在模型定义的时候指定默认排序：有些时候，不想每次在查询的时候都指定排序的方式，可以在定义模型的时候就指定排序的方式。

• 在模型中定义，添加以下代码

__mapper_args__={
		"order_by":title
}

• 即可让文章使用标题来进行排序
  3.正向排序和反向排序：默认情况是从小到大排序，从前到后排序的，如果想要反向排序，可以调用排序的字段的desc方法

limit、offset和切片

• limit：可以限制每次查询的时候只查询几条数据
• offset：可以限制查找数据的时候过滤前面多少条
• 切片：可以对Query对象使用切片操作，来获取想要的数据

查询高级
group_by
根据某个字段进行分组。比如想要根据性别进行分组，来统计每个分组分别有多少人

session.query(User.gender,func.count(User.id)).group_by(User.gender).all()

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

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

join方法
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)
    print(a)

通过join的方式来实现

for u,a in session.query(User,Address).join(Address).all():
    print(u)
    print(a)

采用outerjoin，可以获取所有user，而不用在乎这个user是否有address对象，并且outerjoin默认为左外查询：

for instance in session.query(User,Address).outerjoin(Address).all():
	print(instance)

别名
当多表查询的时候，有时候同一个表要用到多次，这个时候别名就可以方便的解决命名冲突的问题

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)

子查询
sqlalchemy也支持子查询，比如现在要查找一个用户的用户名以及该用户的邮箱地址数量。要满足这个需求，可以在子查询中找到所有用户的邮箱数〈(通过group by合并同一用户)，然后再将结果放在父查询中进行使用:

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

一个查询如果想要变为子查询，则是通过subquery()方法实现，变成子查询后，通过子查询.c属性来访问查询出来的列。如果查找整个实体，则需要通过aliased方法

stmt = session.query(Address)
adalias = aliased(Address,stmt)
for user,address in session.query(User,stmt).join(stmt,User.addresses):
print user,address

7.Flask-SQLALchemy插件

另外一个框架，叫做Flask-SQLAlchemy，Flask-SQLAlchemy是对SQLAlchemy进行了一个简单的封装，使得我们在flask中使用sqlalchemy更加的简单。可以通过pipinstall flask-sqlalchemy 。

• 数据库初始化

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from constants import DB_URI
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
db = SQLAlchemy(app)

• ORM类

class User(db.Model):
  id = db.Column(db.Integer,primary_key=True)
  username = db.Column(db.String(80),unique=True)
  email = db.Column(db.String(120),unique=True)
  
  def __repr__(self):
      return '<User %s>' % self.username

• 映射模型到数据库表:使用Flask-SQLAlchemy所有的类都是继承自db.Model，并且所有的column和数据类型也都成为db的一个属性。但是有个好处是不用写表名了，Flask-SQLAlchemy会自动将类名小写化，然后映射成表名。
  写完类模型后，要将模型映射到数据库的表中，使用以下代码创建所有的表:

db.create_all()

• 添加数据：可以在数据库中看到生成的user表

admin = User('admin','admin@example.com')
guest = User('guest','guest@example.com')
db.session.add(admin)
db.session.add(guest)
db.session.commit()

• 添加数据之前的没有区别，只是session成为了一个db的属性

• 查询数据:查询数据不再是之前的session.query了，而是将query属性放在了db. Model上，所以查询就是通过Model.query的方式进行查询了

users = User.query.all()

• 删除数据：删除数据跟添加数据类似，只不过session是db的一个属性

db.session.delete(admin)
db.session.commit()

8.Flask-Script

介绍
Flask-Script的作用是可以通过命令行的形式来操作Flask。例如通过命令跑一个开发版本的服务器、设置数据库，定时任务等。使用Flask-Script，通过pip install flask-script安装

from flask_script import Manager
from your_app import app
manager = Manager(app)
@manager.command
def hello():
    print('hello')
if __name__ == '__main__':
    manager.run()

我们把脚本命令代码放在一个叫做manage.py文件中，然后在终端运行python manage.pyhello命令，就可以看到输出hello了

定义命令的三种方法
1.使用@command装饰器
2.使用类继承Command类

from flask_script import Command,Manager
from your_app import app
manager = Manager(app)
class Hello(Command):
    "prints hello world"
    def run(self):
        print("hello world")

manager.add_command('hello',Hello())

使用类的方式

• 必须继承自Command基类
• 必须实现run方法
• 必须通过add_command方法添加命令

3.使用option装饰器：想要在使用命令的时候还传递参数进去，使用@option装饰器更加方便。

@manager.option('-n','--name',dest='name')
def hello(name):
    print('hello ',name)

调用hello命令

python manage.py -n juran
python manage.py --name juran

添加参数到命令中

• option装饰器

@manager.option('-n', '--name', dest='name', default='joe')
@manager.option('-u', '--url', dest='url', default=None) 
def hello(name, url): 
  if url is None: 
     print("hello", name)
  else: 
      print("hello", name, "from", url)

• command装饰器：可以添加参数，不灵活

@manager.command 
def hello(name="Fred") 
  print("hello", name)

• 类继承：可以添加参数

from flask_Flask import Comman,Manager,Option

class Hello(Command):
  option_list = (
      Option('--name','-n',dest='name'),
  )

  def run(self,name):
      print("hello %s" % name)

要在指定参数的时候，动态的做一些事情，可以使用get_options方法

class Hello(Command):
  def __init__(self,default_name='Joe'):
self.default_name = default_name

  def get_options(self):
      return [
          Option('-n','--name',dest='name',default=self.default_name),
      ]
  def run(self,name):
      print('hello',name)

9.Flask-Migrate

简要介绍
在实际的开发环境中，经常会发生数据库修改的行为。一般我们修改数据库不会直接手动的去修改，而是去修改ORM对应的模型，然后再把模型映射到数据库中。这时候如果有一个工具能专门做这种事情，就显得非常有用了，而flask-migrate就是做这个事情的。flask-migrate是基于Alembic进行的一个封装，并集成到Flask中，而所有的迁移操作其实都是Alembic做的，他能跟踪模型的变化，并将变化映射到数据库中。安装pip install flask-migrate

flask-migrate
要让Flask-Migrate能够管理app中的数据库，需要使用Migrate(app,db)来绑定app和数据库

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from constants import DB_URI
from flask_migrate import Migrate

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)
# 绑定app和数据库
migrate = Migrate(app,db)
class User(db.Model):
    id = db.Column(db.Integer,primary_key=True)
    username = db.Column(db.String(20))
    addresses = db.relationship('Address',backref='user')
class Address(db.Model):
    id = db.Column(db.Integer,primary_key=True)
    email_address = db.Column(db.String(50))
    user_id = db.Column(db.Integer,db.ForeignKey('user.id'))
db.create_all()
@app.route('/')
def hello_world():
    return 'Hello World!'
if __name__ == '__main__':
    app.run()

初始化一个迁移文件夹

flask db init

然后再把当前的模型添加到迁移文件中

flask db migrate

最后再把迁移文件中对应的数据库操作，真正的映射到数据库

flask db upgrade

manage.py文件
这个文件用来存放映射数据库的命令，MigrateCommand是flask-migrate集成的一个命令，因此想要添加到脚本命令中，需要采用manager.add_command(‘db’,MigrateCommand)的方式，以后运行python manage.py dbxxx的命令，其实就是执行MigrateCommand

from flask_script import Manager
from flask_migrate import MigrateCommand, Migrate
from exts import db
from demo import app
from models import User

manage = Manager(app)
Migrate(app, db)
manage.add_command("db", MigrateCommand)

if __name__ == '__main__':
    manage.run()