23. Flask 操作Mysql数据库 - flask-sqlalchemy扩展

官网文档

https://flask-sqlalchemy.palletsprojects.com/en/master/quickstart/

数据库的设置

Web应用中普遍使用的是关系模型的数据库，关系型数据库把所有的数据都存储在表中，表用来给应用的实体建模，表的列数是固定的，行数是可变的。它使用结构化的查询语言。关系型数据库的列定义了表中表示的实体的数据属性。比如：商品表里有name、price、number等。 Flask本身不限定数据库的选择，你可以选择sql或NOsql的任何一种。也可以选择更方便的sqlALchemy，类似于Django的ORM。sqlALchemy实际上是对数据库的抽象，让开发者不用直接和sql语句打交道，而是通过Python对象来操作数据库，在舍弃一些性能开销的同时，换来的是开发效率的较大提升。

sqlAlchemy是一个关系型数据库框架，它提供了高层的ORM和底层的原生数据库的操作。flask-sqlalchemy是一个简化了sqlAlchemy操作的flask扩展。

下面使用MysqL作为示例进行说明。

创建MysqL数据库

1.登录数据库

MysqL -u root -p password

2.创建数据库，并设定编码

create database <数据库名> charset=utf8;

3.显示所有数据库

show databases;

4.执行如下

MysqL> create database flask_ex charset=utf8;
Query OK, 1 row affected (0.06 sec)

安装flask-sqlalchemy的扩展

pip install -U Flask-sqlAlchemy

python2：要连接MysqL数据库，仍需要安装flask-MysqLdb

pip install flask-MysqLdb

python3：要连接MysqL数据库，仍需要安装pyMysqL

pip install pyMysqL

本篇章内容以python3作为开讲。

使用Flask-sqlAlchemy连接MysqL数据库

使用Flask-sqlAlchemy扩展操作数据库，首先需要建立数据库连接。数据库连接通过URL指定，而且程序使用的数据库必须保存到Flask配置对象的sqlALCHEMY_DATABASE_URI键中。

对比下Django和Flask中的数据库设置：

Django的数据库设置：

DATABASES = {
'default': {
'ENGINE': 'django.db.backends.MysqL', # 修改后端数据库使用MysqL
'NAME': 'mydb', # 设置访问数据库名称
'USER': 'root', # 访问访问MysqL用户名
'PASSWORD': 'password', # 设置访问密码
'HOST': 'localhost', # 设置访问ip地址
'PORT': 3306, # 设置访问端口号
    }
}

Flask的数据库设置：

app.config['sqlALCHEMY_DATABASE_URI'] = 'MysqL://root:MysqL@127.0.0.1:3306/flask_ex'

常用的sqlAlchemy字段类型

上面看完了如何设置连接数据库，那么来看看，使用sqlAlchemy创建数据模型的时候，基本的字段类型如下：

常用的sqlAlchemy列选项

常用的sqlAlchemy关系选项

上面这些有很多基本选项的说明，下面来进行数据库的基本增删改等操作来加强理解。

数据库基本操作

在Flask-sqlAlchemy中，插入、修改、删除操作，均由数据库会话管理。会话用db.session表示。在准备把数据写入数据库前，要先将数据添加到会话中然后调用commit()方法提交会话。

数据库会话是为了保证数据的一致性，避免因部分更新导致数据不一致。提交操作把会话对象全部写入数据库，如果写入过程发生错误，整个会话都会失效。

数据库会话也可以回滚，通过db.session.rollback()方法，实现会话提交数据前的状态。

在Flask-sqlAlchemy中，查询操作是通过query对象操作数据。最基本的查询是返回表中所有数据，可以通过过滤器进行更精确的数据库查询。

下面先来创建两个表的数据模型：用户表和角色表。

在视图函数中定义模型类

看完了上面那么多的概念说明，下面来看看如何创建数据模型以及创建数据表，如下：

1.在脚本15_sqlAlchemy.py编写创建User和Role数据模型

from flask import Flask
from flask_sqlalchemy import sqlAlchemy
import pyMysqL
pyMysqL.install_as_MysqLdb()

app = Flask(__name__)

class Config(object):
"""配置参数"""
# 设置连接数据库的URL
    user = 'root'
    password = '********'
    database = 'flask_ex'
    app.config['sqlALCHEMY_DATABASE_URI'] = 'MysqL://%s:%s@127.0.0.1:3306/%s' % (user,password,database)

# 设置sqlalchemy自动更跟踪数据库
    sqlALCHEMY_TRACK_MODIFICATIONS = True

# 查询时会显示原始sql语句
    app.config['sqlALCHEMY_ECHO'] = True

# 禁止自动提交数据处理
    app.config['sqlALCHEMY_COMMIT_ON_TEARDOWN'] = False

# 读取配置
app.config.from_object(Config)

# 创建数据库sqlalchemy工具对象
db = sqlAlchemy(app)

class Role(db.Model):
# 定义表名
    __tablename__ = 'roles'
# 定义字段
    id = db.Column(db.Integer, primary_key=True,autoincrement=True)
    name = db.Column(db.String(64), unique=True)
    users = db.relationship('User',backref='role') # 反推与role关联的多个User模型对象

class User(db.Model):
# 定义表名
    __tablename__ = 'users'
# 定义字段
    id = db.Column(db.Integer, primary_key=True,autoincrement=True)
    name = db.Column(db.String(64), unique=True, index=True)
    email = db.Column(db.String(64),unique=True)
    pswd = db.Column(db.String(64))
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id')) # 设置外键

if __name__ == '__main__':

# 删除所有表
    db.drop_all()

# 创建所有表
    db.create_all()

2. 执行脚本，创建数据库

python3 15_sqlAlchemy.py

3.在MysqL查看已经创建的表结构

MysqL> show tables;
+--------------------+
| Tables_in_flask_ex |
+--------------------+
| roles              |
| users              |
+--------------------+
2 rows in set (0.00 sec)

MysqL> 
MysqL> desc users;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(64) | YES  | UNI | NULL    |                |
| email   | varchar(64) | YES  | UNI | NULL    |                |
| pswd    | varchar(64) | YES  |     | NULL    |                |
| role_id | int(11)     | YES  | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

MysqL> 
MysqL> desc roles;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64) | YES  | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

MysqL> 

创建好了数据表之后，下面来看看如何执行数据的增删查改的。

常用的sqlAlchemy查询过滤器

常用的sqlAlchemy查询执行器

创建表：

db.create_all()

删除表

db.drop_all()

每次插入单条数据

if __name__ == '__main__':

# 插入一条角色数据
    role1 = Role(name='admin')
    db.session.add(role1)
    db.session.commit()

# 再次插入一条数据
    role2 = Role(name='user')
    db.session.add(role2)
    db.session.commit()

执行脚本：

python3 15_sqlAlchemy.py

在MysqL中查看插入的数据，如下：

MysqL> select * from roles \G
*************************** 1. row ***************************
id: 1
name: admin
*************************** 2. row ***************************
id: 2
name: user
2 rows in set (0.00 sec)

一次插入多条数据

    # 一次性插入多条数据
    user1 = User(name='wang',email='wang@163.com',pswd='123456',role_id=role1.id)
    user2 = User(name='zhang',email='zhang@189.com',pswd='201512',role_id=role2.id)
    user3 = User(name='chen',email='chen@126.com',pswd='987654',role_id=role2.id)
    user4 = User(name='zhou',email='zhou@163.com',pswd='456789',role_id=role1.id)
    db.session.add_all([user1,user2,user3,user4])
    db.session.commit()

执行插入数据，如下：

python3 15_sqlAlchemy.py

在MysqL中查询插入的数据如下：

MysqL> select * from users \G
*************************** 1. row ***************************
id: 1
name: wang
  email: wang@163.com
   pswd: 123456
role_id: 1
*************************** 2. row ***************************
id: 2
name: zhang
  email: zhang@189.com
   pswd: 201512
role_id: 2
*************************** 3. row ***************************
id: 3
name: chen
  email: chen@126.com
   pswd: 987654
role_id: 2
*************************** 4. row ***************************
id: 4
name: zhou
  email: zhou@163.com
   pswd: 456789
role_id: 1
4 rows in set (0.00 sec)

MysqL> 

虽然这里在python中看上去是一次性插入多条数据，其实在MysqL也是执行多行插入的语句，通过MysqL的日志可以看到如下：

2019-11-23T16:48:56.984459Z	 9061 Query	INSERT INTO users (name, email, pswd, role_id) VALUES ('wang', 'wang@163.com', '123456', 1)
2019-11-23T16:48:56.997132Z	 9061 Query	INSERT INTO users (name, email, pswd, role_id) VALUES ('zhang', 'zhang@189.com', '201512', 2)
2019-11-23T16:48:57.010175Z	 9061 Query	INSERT INTO users (name, email, pswd, role_id) VALUES ('chen', 'chen@126.com', '987654', 2)
2019-11-23T16:48:57.024134Z	 9061 Query	INSERT INTO users (name, email, pswd, role_id) VALUES ('zhou', 'zhou@163.com', '456789', 1)

实际上并没有将多个values合并到一个insert语句，依然是多个insert语句逐个插入的。

查询:filter_by精确查询

返回名字等于wang的所有user

User.query.filter_by(name='wang').all()

在交互模型执行如下：

>python3 db_demo.py shell
In [1]: from db_demo import User

In [2]: User.query.filter_by(name='wang').all()
Out[2]: [<User 1>]

In [3]:

first()返回查询到的第一个对象

User.query.first()

执行如下：

In [3]: User.query.first()
Out[3]: <User 1>

all()返回查询到的所有对象

User.query.all()

执行如下：

In [4]: User.query.all()
Out[4]: [<User 1>, <User 2>, <User 3>, <User 4>]

In [5]:

filter模糊查询，返回名字结尾字符为g的所有数据。

User.query.filter(User.name.endswith('g')).all()

执行如下：

In [5]: User.query.filter(User.name.endswith('g')).all()
Out[5]: [<User 1>, <User 2>]

get()，参数为主键，如果主键不存在没有返回内容

User.query.get()

执行如下：

In [6]: User.query.get(2)
Out[6]: <User 2>

In [7]: user2 = User.query.get(2)

In [8]: user2.name
Out[8]: 'zhang'

逻辑非，返回名字不等于wang的所有数据。

User.query.filter(User.name!='wang').all()

执行如下：

In [9]: User.query.filter(User.name!='wang').all()
Out[9]: [<User 2>, <User 3>, <User 4>]

逻辑与，需要导入and*，返回and*()条件满足的所有数据。

from sqlalchemy import and_
User.query.filter(and_(User.name!='wang',User.email.endswith('163.com'))).all()

执行如下：

In [10]: from sqlalchemy import and_

In [15]: users = User.query.filter(and_(User.name!='wang',User.email.endswith('163.com'))).all()

In [16]: for user in users:
    ...:     print(user.email)
    ...:
zhou@163.com

逻辑或，需要导入or_

from sqlalchemy import or_
User.query.filter(or_(User.name!='wang',User.email.endswith('163.com'))).all()

执行如下：

In [17]: from sqlalchemy import or_

In [18]: users = User.query.filter(or_(User.name!='wang',User.email.endswith('163.com'))).all()

In [19]: for user in users:
    ...:     print(user.name, user.email)
    ...:
wang wang@163.com
zhang zhang@189.com
chen chen@126.com
zhou zhou@163.com

In [20]:

not_ 相当于取反

from sqlalchemy import not_
User.query.filter(not_(User.name=='chen')).all()

执行如下：

In [22]: from sqlalchemy import not_

In [25]: users = User.query.filter(not_(User.name=='chen')).all()

In [26]: for user in users:
    ...:     print(user.name, user.email)
    ...:
wang wang@163.com
zhang zhang@189.com
zhou zhou@163.com

查询数据后删除

user = User.query.first()
db.session.delete(user)
db.session.commit()
User.query.all()

执行如下：

(venv) $ >python3 db_demo.py shell

In [1]: from db_demo import User

In [3]: user = User.query.first()

In [5]: from db_demo import db

In [6]: db.session.delete(user)

In [7]: db.session.commit()

In [8]: User.query.all()
Out[8]: [<User 2>, <User 3>, <User 4>]

更新数据

user = User.query.first()
user.name = 'dong'
db.session.commit()
User.query.first()

执行如下：

In [1]: from db_demo import User

In [5]: from db_demo import db

In [9]: user = User.query.first()

In [10]: user
Out[10]: <User 2>

In [11]: user.name
Out[11]: 'zhang'

In [12]: user.name = 'dong'

In [13]: db.session.commit()

In [14]: user = User.query.first()

In [15]: user.name
Out[15]: 'dong'

使用update

User.query.filter_by(name='zhang').update({'name':'li'})

执行如下：

In [21]: User.query.filter_by(name='dong').update({'name':'li'})
Out[21]: 0

In [22]: User.query.get(2)
Out[22]: <User 2>

In [23]: user = User.query.get(2)

In [24]: user.name
Out[24]: 'li'

关联查询示例：角色和用户的关系是一对多的关系，一个角色可以有多个用户，一个用户只能属于一个角色。

关联查询角色的所有用户：

#查询roles表id为1的角色
role1 = Role.query.get(1)
#查询该角色的所有用户
role1.users

执行如下：

In [25]: from db_demo import Role

In [26]: role1 = Role.query.get(1)

In [27]: role1.users
Out[27]: [<User 4>]

In [28]: role2 = Role.query.get(2)

In [29]: role2.users
Out[29]: [<User 2>, <User 3>]

关联查询用户所属角色：

#查询users表id为3的用户
user1 = User.query.get(3)
#查询用户属于什么角色
user1.role

执行如下：

In [30]: user1 = User.query.get(3)

In [31]: user1.role
Out[31]: <Role 2>

Can''t connect to local MySQL server through socket ''/var/run/mysqld/mysqld.sock'' (2)

docker 容器安装 mysql 报错 Can''t connect to local MySQL server through socket ''/var/run/mysqld/mysqld.sock'' (2)

root@b04c9896b0ce:/# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can''t connect to local MySQL server through socket ''/var/run/mysqld/mysqld.sock'' (2)
root@b04c9896b0ce:/# docker stop mysql01
bash: docker: command not found
root@b04c9896b0ce:/# docker images mysql:8.0.28
bash: docker: command not found
root@b04c9896b0ce:/# exit

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) --Zabbix 安装

# MysqL -u root -p 
Enter password: 

# apt install zabbix-server-MysqL zabbix-frontend-PHP zabbix-apache-conf zabbix-sql-scripts zabbix-agent 

   # apt install MysqL-server
   # systemctl status MysqL
   # systemctl start MysqL 

   #apt install mysql-server
   #systemctl status mysql
   #systemctl start mysql