数据备份与pymysql模块

阅读目录

• 一 IDE工具介绍
• 二 MySQL数据备份
• 三 pymysql模块

一、IDE工具（Navicat）介绍

生产环境还是推荐使用mysql命令行，但为了方便我们测试，可以使用IDE工具

下载链接：

链接：https://pan.baidu.com/s/13JmtWAyO2-36SJjsm4tb6Q
提取码：qcgg

掌握：
#1. 测试+链接数据库
#2. 新建库
#3. 新建表，新增字段+类型+约束
#4. 设计表：外键
#5. 新建查询
#6. 备份库/表

#注意：
批量加注释：ctrl+？键
批量去注释：ctrl+？键，ps：有的版本是ctrl+shift+？

二、MySQL数据备份

#1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。
#2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。
#3. 导出表： 将表导入到文本文件中。

使用mysqldump实现逻辑备份

#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql 

示例：(在windows上执行备份命令)

管理员身份进入cmd，进入到MySQL的安装目录下的bin目录，执行下图命令之后，就会在bin目录下生成备份文件alldb.sql

 

 注意：在命令行输入命令时，别在-p后跟密码，提示输入密码后手动输入，如果在命令行-p跟密码，有可能会报错如下：

 

恢复逻辑备份

#方法一：这个命令在linux执行正常
[root@egon backup]# mysql -uroot -p123 < /backup/all.sql
注意：
在windows上要先创建即将要备份的数据库，然后再恢复，恢复命令也要指定导入到创建的数据库
#mysql -uroot -p123 之前创建的数据库名 < /backup/all.sql


#方法二：进入mysql命令行
mysql> source /backup/all.sql

示例：在windows执行恢复备份

先进入mysql命令行，

注意，要先创建要导入的数据库，例如本次要导入的数据库名为userinfo_db

mysql> create database userinfo_db;
Query OK, 1 row affected (0.00 sec)

mysql> use userinfo_db;
Database changed

mysql> source D:\mysql5.6\mysql-5.6.37-winx64\bin\alldb.sql  #再执行source命令恢复

mysql> show tables;
+-----------------------+
| Tables_in_userinfo_db |
+-----------------------+
| baseinfo              |
| class                 |
| cmd                   |
| emp                   |
| errlog                |
| studnet               |
| user                  |
+-----------------------+
7 rows in set (0.00 sec)

mysql>

备份/恢复案例

#数据库备份/恢复实验一：数据库损坏
备份：
1. # mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. # mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog
3. 插入数据 //模拟服务器正常运行
4. mysql> set sql_log_bin=0; //模拟服务器损坏
mysql> drop database db;

恢复：
1. # mysqlbinlog 最后一个binlog > /backup/last_bin.log
2. mysql> set sql_log_bin=0; 
mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份 
mysql> source /backup/last_bin.log //恢复最后个binlog文件


#数据库备份/恢复实验二：如果有误删除
备份：
1. mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog
3. 插入数据 //模拟服务器正常运行
4. drop table db1.t1 //模拟误删除
5. 插入数据 //模拟服务器正常运行

恢复：
1. # mysqlbinlog 最后一个binlog --stop-position=260 > /tmp/1.sql 
# mysqlbinlog 最后一个binlog --start-position=900 > /tmp/2.sql 
2. mysql> set sql_log_bin=0; 
mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份
mysql> source /tmp/1.log //恢复最后个binlog文件
mysql> source /tmp/2.log //恢复最后个binlog文件

注意事项：
1. 完全恢复到一个干净的环境（例如新的数据库或删除原有的数据库）
2. 恢复期间所有SQL语句不应该记录到binlog中

实现自动化备份

备份计划：
1. 什么时间 2:00
2. 对哪些数据库备份
3. 备份文件放的位置

备份脚本：
[root@egon ~]# vim /mysql_back.sql
#!/bin/bash
back_dir=/backup
back_file=`date +%F`_all.sql
user=root
pass=123

if [ ! -d /backup ];then
mkdir -p /backup
fi

# 备份并截断日志
mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file}
mysql -u${user} -p${pass} -e 'flush logs'

# 只保留最近一周的备份
cd $back_dir
find . -mtime +7 -exec rm -rf {} \;

手动测试：
[root@egon ~]# chmod a+x /mysql_back.sql 
[root@egon ~]# chattr +i /mysql_back.sql
[root@egon ~]# /mysql_back.sql

配置cron：
[root@egon ~]# crontab -l
2 * * * /mysql_back.sql

表的导出和导入

SELECT... INTO OUTFILE 导出文本文件
示例：
mysql> SELECT * FROM school.student1
INTO OUTFILE 'student1.txt'
FIELDS TERMINATED BY ',' //定义字段分隔符
OPTIONALLY ENCLOSED BY '”' //定义字符串使用什么符号括起来
LINES TERMINATED BY '\n' ; //定义换行符


mysql 命令导出文本文件
示例:
# mysql -u root -p123 -e 'select * from student1.school' > /tmp/student1.txt
# mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml
# mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html

LOAD DATA INFILE 导入文本文件
mysql> DELETE FROM student1;
mysql> LOAD DATA INFILE '/tmp/student1.txt'
INTO TABLE school.student1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '”'
LINES TERMINATED BY '\n';

#可能会报错
mysql> select * from db1.emp into outfile 'C:\\db1.emp.txt' fields terminated by ',' lines terminated by '\r\n';
ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable


#数据库最关键的是数据，一旦数据库权限泄露，那么通过上述语句就可以轻松将数据导出到文件中然后下载拿走，因而mysql对此作了限制，只能将文件导出到指定目录
在配置文件中
[mysqld]
secure_file_priv='C:\\' #只能将数据导出到C:\\下

重启mysql
重新执行上述语句

数据库迁移

务必保证在相同版本之间迁移
# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456

三、pymysql模块

#安装（管理员运行cmd）
pip3 install pymysql

3.1 链接、执行sql、关闭（游标）

import pymysql

# 链接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='atm_db',
    charset='utf8',
    # autocommit=True    # 自动提交,可以不加
)
# 生成游标对象
cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)   #执行完毕返回的结果集以字典显示

user=input('用户名: ').strip()
pwd=input('密码: ').strip()

# 执行sql语句
sql = 'select * from userinfo where username=%s and password=%s'
val=(user, pwd)  
res = cursor.execute(sql,val)  # 执行sql语句，返回sql查询成功的记录数目
#也可以写成
#res = cursor.execute(sql,(user, pwd))
print(res)

cursor.close()
conn.close()

if res:
    print('登录成功')
else:
    print('登录失败')

3.2 execute()之sql注入

注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符

根本原理：就根据程序的sql语句字符串拼接中name='%s'，我们输入一个xxx' -- haha,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'

#示例：
sql = 'select * from userinfo where username="%s" and password="%s"'  %(user,pwd)
res = cursor.execute(sql)  

#ps:有个注意的地方，就是sql语句拼接中%s如果用的双引号，那么我们输入xxx加"
#总之，看你的sql语句中%s用的什么引号，输的时候就跟什么

#不同注入方法
#1、sql注入之：用户存在，绕过密码
egon' -- 任意字符

#2、sql注入之：用户不存在，绕过用户与密码
xxx' or 1=1 -- 任意字符

示例（程序代码）

import pymysql

# 链接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='atm_db',
    charset='utf8'
)
# 游标
cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)   #执行完毕返回的结果集以字典显示

user=input('用户名: ').strip()
pwd=input('密码: ').strip()

# 执行sql语句
sql = 'select * from userinfo where username="%s" and password="%s"'  %(user,pwd)
print("sql语句",sql)
res = cursor.execute(sql)  # 执行sql语句，返回sql查询成功的记录数目
print(res)

cursor.close()
conn.close()

if res:
    print('登录成功')
else:
    print('登录失败')

不同输入效果（注意代码中sql语句用的是双引号）

用户存在绕过密码：

 用户不存在：

 解决方法：

# 原来代码中是对对sql进行字符串拼接
sql = 'select * from userinfo where username="%s" and password="%s"'  %(user,pwd)
print("sql语句",sql)
res = cursor.execute(sql) 

#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号也无需在sql后%(user,pwd)去指定，在）
sql="select * from userinfo where name=%s and password=%s" #注意%s需要去掉引号，因为execute方法会自动为我们加上
res=cursor.execute(sql,[user,pwd]) # execute自动识别sql里面的%s用后面元组里面的数据替换

修改过后的代码：

import pymysql

# 链接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='atm_db',
    charset='utf8'
)
# 游标
cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)   #执行完毕返回的结果集以字典显示

user=input('用户名: ').strip()
pwd=input('密码: ').strip()

# 执行sql语句
sql = 'select * from userinfo where username=%s and password=%s'
val=(user, pwd)  
res = cursor.execute(sql,val)  # 执行sql语句，返回sql查询成功的记录数目
#也可以写成
#res = cursor.execute(sql,(user, pwd))
print(res)

cursor.close()
conn.close()

if res:
    print('登录成功')
else:
    print('登录失败')

3.3 增、删、改：conn.commit()

import pymysql

# 链接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='school',
    charset='utf8',
    # autocommit=True    # 自动提交,可以不加
)
# 生成游标对象
# cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 执行完毕返回的结果集以字典显示

# 增加
"""
#part1（插入单条记录）
# sql='insert into userinfo(username,password) values("root","123456");'
# res=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数
# print(res)

#part2（插入单条记录）
#sql='insert into userinfo(username,password) values(%s,%s);'
#res=cursor.execute(sql,("root","123456")) #执行sql语句，返回sql影响成功的行数
#print(res)
#ps:
'''
add_name="root
passwd="123456"
sql='insert into userinfo(username,password) values(%s,%s);'
val=(add_name,passwd)
res=cursor.execute(sql,val)    #可以将元组提出了在外面通过变量给元组赋值
'''

#part3（插入多条记录）
sql='insert into userinfo(name,password) values(%s,%s);'
res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数
print(res)

conn.commit() #提交后插入记录才能成功
cursor.close()
conn.close()
"""

# =================修改====================
# sql = "update userinfo set username=%s where username=%s"
# res = cursor.execute(sql, ["alias", "root"])
# print(res)
#
# conn.commit() # 提交后插入记录才能成功
# cursor.close()
# conn.close()
# ====================================


# ===============删除==================
sql = "delete from userinfo where username=%s"
res = cursor.execute(sql, ["alias"])
print(res)

conn.commit() # 提交后插入记录才能成功
cursor.close()
conn.close()
# ===================================

查：fetchone，fetchmany，fetchall

import pymysql
# 链接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='atm_db',
    charset='utf8'
)
# 游标
cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)   #执行完毕返回的结果集以字典显示


#执行sql语句
sql='select * from userinfo;'
rows=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数rows,将结果放入一个队列，等待被查询

# cursor.scroll(3,mode='absolute') # 相对绝对位置移动，相对于返回结果队列队首移动3，括号内设置几就移动几,比如当前位置在结果队列的第五个结果，写了(2,mode="absolute")，那就是从结果起始位置移动2个结果开始 
# cursor.scroll(3,mode='relative') # 相对当前位置移动，相对于游标在返回结果队列当前所在位置
res1=cursor.fetchone()   #从命令返回的结果队列中当前位置取出一条记录
res2=cursor.fetchone()
res3=cursor.fetchone()
res4=cursor.fetchmany(2) #从命令返回的结果队列中当前位置取出2条记录，括号内设置几就取几条
res5=cursor.fetchall()      #从命令返回的结果队列中当前位置之后所有记录
print(res1)
print(res2)
print(res3)
print(res4)
print(res5)

cursor.close()
conn.close()

获取插入的最后一条数据的自增ID

import pymysql
# 链接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='atm_db',
    charset='utf8'
)
# 游标
cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)   #执行完毕返回的结果集以字典显示

sql='insert into userinfo(username,password) values(%s,%s);'
val=("jason","123456")
rows=cursor.execute(sql,val)
print(cursor.lastrowid) #在插入语句后查看

conn.commit()
cursor.close()
conn.close()

 3.4 pymysql 执行事务操作

import pymysql

# 链接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='school',
    charset='utf8',
    # autocommit=True    # 自动提交,可以不加
)
# 游标
cursor = conn.cursor()  # 执行完毕返回的结果集默认以元组显示
# cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)   #执行完毕返回的结果集以字典显示

sql_1 = "update emp set age=11 where name='jason'"
sql_2 = "update emp set age=22 where name='egon'"
sql_3 = "dele from emp where id=100"

try:
    cursor.execute(sql_1)
    cursor.execute(sql_2)
    cursor.execute(sql_3) # 语法错误

except Exception as e:
    conn.rollback()  # 事务回滚
    print('事务处理失败', e)
else:
    conn.commit()  # 事务提交
    print('事务处理成功', cursor.rowcount)

# 关闭连接
cursor.close()
conn.close()

执行结果：

# 事务处理失败 (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dele from emp where id=100' at line 1")

3.5 pymysql-普通的数据库连接

初级阶段

import pymysql
from flask import Flask

app = Flask(__name__)


def fetchall(sql):
    conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='router_auth')
    # 游标
    cursor = conn.cursor()
    rows = cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()

    return result


@app.route('/login')
def login():
    result = fetchall('select * from app01_user')
    print(result)
    return 'login'


@app.route('/index')
def index():
    result = fetchall('select * from app01_user')
    print(result)

    return 'index'


@app.route('/order')
def order():
    result = fetchall('select * from app01_user')
    print(result)

    return 'order'


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

 修改版，全局共用一个conn

import pymysql
from flask import Flask

app = Flask(__name__)
CONN = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='router_auth')


def fetchall(sql):
    # conn = pymysql.connect(host='127.0.0.1', user='root', password='123456', database='router_auth')
    # 游标
    cursor = CONN.cursor()
    rows = cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    # conn.close()

    return result


@app.route('/login')
def login():
    result = fetchall('select * from app01_user')
    print(result)
    return 'login'


@app.route('/index')
def index():
    result = fetchall('select * from app01_user')
    print(result)

    return 'index'


@app.route('/order')
def order():
    result = fetchall('select * from app01_user')
    print(result)

    return 'order'


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

3.6 数据库连接池

1）安装

# pip3 install dbutils
# pip3 install pymysql

版本：Successfully installed dbutils-3.0.2

2）使用

 DBUtils是Python的一个用于实现数据库连接池的模块。

#模块导入
# 注意这里PersistentDB是在persistent_db Module当中
from dbutils.persistent_db import PersistentDB
from dbutils.pooled_db import PooledDB, SharedDBConnection

此连接池有两种连接模式：

• 模式一：为每个线程创建一个连接，线程即使调用了close方法，也不会关闭，只是把连接重新放到连接池，供自己线程再次使用。当线程终止时，连接自动关闭。

POOL = PersistentDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    closeable=False,
    # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
    threadlocal=None,  # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    database='pooldb',
    charset='utf8'
)

def func():
    conn = POOL.connection(shareable=False)
    cursor = conn.cursor()
    cursor.execute('select * from tb1')
    result = cursor.fetchall()
    cursor.close()
    conn.close()

func()

• 模式二：创建一批连接到连接池，供所有线程共享使用。

         PS：由于pymysql、MySQLdb等threadsafety值为1，所以该模式连接池中的线程会被所有线程共享。

import pymysql
from dbutils.persistent_db import PersistentDB
from dbutils.pooled_db import PooledDB, SharedDBConnection
import threading
import time

# 连接池
POOL = PooledDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxconnections=6,  # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=5,  # 链接池中最多闲置的链接，0和None不限制
    maxshared=3,
    # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='router_auth',
    charset='utf8'
)


def task(num):
    # 检测当前正在运行连接数的是否小于最大链接数，如果不小于则：等待或报raise TooManyConnections异常
    # 否则
    # 则优先去初始化时创建的链接中获取链接 SteadyDBConnection。
    # 然后将SteadyDBConnection对象封装到PooledDedicatedDBConnection中并返回。
    # 如果最开始创建的链接没有链接，则去创建一个SteadyDBConnection对象，再封装到PooledDedicatedDBConnection中并返回。
    # 一旦关闭链接后，连接就返回到连接池让后续线程继续使用。
    # 去连接池获取一个连接
    conn = POOL.connection()
    # print(th, '链接被拿走了', conn1._con)
    # print(th, '池子里目前有', pool._idle_cache, '\r\n')
    # 游标
    cursor = conn.cursor()
    rows = cursor.execute('select * from app01_user')
    result = cursor.fetchall()
    cursor.close()
    # conn.close()只是将连接放回连接池
    conn.close()
    print(num, '----->', result)

from threading import Thread

for i in range(57):
    t = Thread(target=task, args=(i, ))
    t.start()

 如果没有连接池，使用pymysql来连接数据库时，单线程应用完全没有问题，但如果涉及到多线程应用那么就需要加锁，一旦加锁那么连接势必就会排队等待，当请求比较多时，性能就会降低了。

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
import threading
from threading import RLock

LOCK = RLock()
CONN = pymysql.connect(host='127.0.0.1',
                       port=3306,
                       user='root',
                       password='123',
                       database='pooldb',
                       charset='utf8')


def task(arg):
    with LOCK:
        cursor = CONN.cursor()
        cursor.execute('select * from tb1')
        result = cursor.fetchall()
        cursor.close()

        print(result)


for i in range(10):
    t = threading.Thread(target=task, args=(i,))
    t.start()

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
import threading
CONN = pymysql.connect(host='127.0.0.1',
                       port=3306,
                       user='root',
                       password='123',
                       database='pooldb',
                       charset='utf8')


def task(arg):
    cursor = CONN.cursor()
    cursor.execute('select * from tb1')
    result = cursor.fetchall()
    cursor.close()

    print(result)


for i in range(10):
    t = threading.Thread(target=task, args=(i,))
    t.start()

PS: 查看连接 show status like 'Threads%';

3）修改3.5的代码

模式一方式

import pymysql
from flask import Flask
from dbutils.persistent_db import PersistentDB
from dbutils.pooled_db import PooledDB

app = Flask(__name__)
# 连接池
POOL = PersistentDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    closeable=False,
    # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
    threadlocal=None,  # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='router_auth',
    charset='utf8'
)


def fetchall(sql):
    conn = POOL.connection(shareable=False)
    # 游标
    cursor = conn.cursor()
    rows = cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()

    return result


@app.route('/login')
def login():
    result = fetchall('select * from app01_user')
    print(result)
    return 'login'


@app.route('/index')
def index():
    result = fetchall('select * from app01_user')
    print(result)

    return 'index'


@app.route('/order')
def order():
    result = fetchall('select * from app01_user')
    print(result)

    return 'order'


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

模式一进阶版用法：基于函数

sqlhelper.py

import pymysql
from dbutils.persistent_db import PersistentDB
from dbutils.pooled_db import PooledDB

# 连接池
POOL = PersistentDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    closeable=False,
    # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
    threadlocal=None,  # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='router_auth',
    charset='utf8'
)


def fetchall(sql, val):
    """获取多条数据"""
    conn = POOL.connection(shareable=False)
    # 游标
    cursor = conn.cursor()
    rows = cursor.execute(sql, val)
    result = cursor.fetchall()
    cursor.close()
    conn.close()

    return result


def fetchone():
    """获取单条数据"""
    pass

普通的数据库连接.py

from flask import Flask
from sqlhelper import fetchall

app = Flask(__name__)


@app.route('/login')
def login():
    result = fetchall('select * from app01_user where username=%s', "alias")
    print(result)
    return 'login'


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

 

模式一进阶版用法：单例

sqlhelper.py

import pymysql
from dbutils.persistent_db import PersistentDB
from dbutils.pooled_db import PooledDB


class SqlHelper(object):
    def __init__(self):
        self.pool = PersistentDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            closeable=False,
            # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
            threadlocal=None,  # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
            host='127.0.0.1',
            port=3306,
            user='root',
            password='123456',
            database='router_auth',
            charset='utf8'
        )

    def open(self):
        conn = self.pool.connection(shareable=False)
        # 游标
        cursor = conn.cursor()

        return conn, cursor

    def close(self, conn, cursor):
        cursor.close()
        conn.close()

    def fetchall(self, sql, val):
        """获取多条数据"""
        conn, cursor = self.open()
        rows = cursor.execute(sql, val)
        result = cursor.fetchall()
        self.close(conn, cursor)

        return result

    def fetchone(self):
        """获取单条数据"""
        pass


db = SqlHelper()

普通的数据库连接.py

from flask import Flask
from sqlhelper import db

app = Flask(__name__)


@app.route('/login')
def login():
    result = db.fetchall('select * from app01_user where username=%s', "alias")
    print(result)
    return 'login'


@app.route('/index')
def index():
    result = db.fetchall('select * from app01_user')
    print(result)

    return 'index'


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

 

 

模式二进阶版：

sqlhelper.py

import pymysql
from dbutils.persistent_db import PersistentDB
from dbutils.pooled_db import PooledDB


class SqlHelper(object):
    def __init__(self):
        self.pool = PersistentDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            closeable=False,
            # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
            threadlocal=None,  # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
            host='127.0.0.1',
            port=3306,
            user='root',
            password='123456',
            database='router_auth',
            charset='utf8'
        )

    def open(self):
        conn = self.pool.connection(shareable=False)
        # 游标
        cursor = conn.cursor()

        return conn, cursor

    def close(self, conn, cursor):
        cursor.close()
        conn.close()

    def fetchall(self, sql, val):
        """获取多条数据"""
        conn, cursor = self.open()
        rows = cursor.execute(sql, val)
        result = cursor.fetchall()
        self.close(conn, cursor)

        return result

    def fetchone(self):
        """获取单条数据"""
        pass


db = SqlHelper()

普通的数据库连接.py

from flask import Flask
from sqlhelper import db

app = Flask(__name__)


@app.route('/login')
def login():
    result = db.fetchall('select * from app01_user where username=%s', "alias")
    print(result)
    return 'login'


@app.route('/index')
def index():
    result = db.fetchall('select * from app01_user')
    print(result)

    return 'index'


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

 

再进一步，通过with上下文管理

sqlhelper.py

import pymysql
import threading
from dbutils.persistent_db import PersistentDB
from dbutils.pooled_db import PooledDB

"""
storage = {
    1111:{'stack': [(conn, cursor), ]}
}
"""


class SqlHelper(object):
    def __init__(self):
        self.pool = PersistentDB(
            creator=pymysql,  # 使用链接数据库的模块
            maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
            setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            closeable=False,
            # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
            threadlocal=None,  # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
            host='127.0.0.1',
            port=3306,
            user='root',
            password='123456',
            database='router_auth',
            charset='utf8'
        )
        self.local = threading.local()

    def open(self):
        conn = self.pool.connection(shareable=False)
        # 游标
        cursor = conn.cursor()

        return conn, cursor

    def close(self, conn, cursor):
        cursor.close()
        conn.close()

    def fetchall(self, sql, val):
        """获取多条数据"""
        conn, cursor = self.open()
        rows = cursor.execute(sql, val)
        result = cursor.fetchall()
        self.close(conn, cursor)

        return result

    def fetchone(self):
        """获取单条数据"""
        pass

    def __enter__(self):
        conn, cursor = self.open()

        rv = getattr(self.local, 'stack', None)
        if not rv:
            self.local.stack = [(conn, cursor), ]
        else:
            rv.append((conn, cursor))
            self.local.stack = rv
        return cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        rv = getattr(self.local, 'stack', None)
        if not rv:
            return
        conn, cursor = self.local.stack.pop()
        cursor.close()
        conn.close()


db = SqlHelper()

普通的数据库连接.py

from flask import Flask
from sqlhelper import db

app = Flask(__name__)


@app.route('/index')
def index():
    with db as cursor:
        rows = cursor.execute('select * from app01_user where username=%s', "alias")
        result = cursor.fetchall()
        print(result)

    return 'index'


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