测试需求平台9：数据持久化与PyMySQL使用

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号自增',
  `keyCode` varchar(200) NOT NULL COMMENT '项目唯一编号',
  `title` varchar(200) NOT NULL COMMENT '中文项目名',
  `desc` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '描述',
  `operator` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '操作者',
  `update` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '操作时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='项目产品表';

-- ----------------------------
-- Records of products
-- ----------------------------
BEGIN;
INSERT INTO `products` VALUES (1, 'data', '数据大盘', '内部一个数据技术分析的项目，用于分析各种数据聚合平台', 'daqi', '2021-07-17 20:38:37');
INSERT INTO `products` VALUES (2, 'payment', '收银台', '支付聚合收银台', 'lili', '2021-07-17 20:40:29');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

# 安装依赖包
python3 -m pip install PyMySQL

# -*- coding:utf-8 -*-
from flask import Blueprint
import pymysql.cursors

app_product = Blueprint("app_product", __name__)

# 使用用户名密码创建数据库链接
connection = pymysql.connect(host='localhost',   # 数据库IP地址或链接域名
                             user='root',     # 设置的具有增改查权限的用户
                             password='******', # 用户对应的密码
                             database='TPMStore',# 数据表
                             charset='utf8mb4',  # 字符编码
                             cursorclass=pymysql.cursors.DictCursor) # 结果作为字典返回游标

@app_product.route("/api/product/list",methods=['GET'])
def product_list():
    # 使用python的with..as控制流语句（相当于简化的try except finally）
    with connection.cursor() as cursor:
        # 查询产品信息表-按更新时间新旧排序
        sql = "SELECT * FROM `Products` ORDER BY `Update` DESC"
        cursor.execute(sql)
        data = cursor.fetchall()

    # 按返回模版格式进行json结果返回
    resp_data = {
        "code": 20000,
        "data": data
    }
    return resp_data

# 不兼容加密方式连接报错如下
# RuntimeError: 'cryptography' package is required for sha256_password or caching_sha2_password auth methods
$ python3 -m pip install PyMySQL[rsa]

# 执行创建数据库
cursor.execute("CREATE DATABASE QiDBTest character SET utf8mb4;") 

# 切库或使用 db.select_db("数据库名")
cursor.execute("use QiDBTest;") 

# #可以通过以下语句进行创建查询
# cursor.execute("SHOW DATABASES;")
# print(cursor.fetchall())

import pymysql
 
# 创建数据库连接
db = pymysql.connect(host='localhost',
                     user='mrzcode',
                     password='mrzcode',
                     database='QiDBTest')
 
# 使用cursor()方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用execute()方法执行 SQL，如果表存在则删除
cursor.execute("DROP TABLE IF EXISTS QiTableDemo")

# 创建表语句
sqlCreateTable = """
    CREATE TABLE `QiTableDemo` (
        `id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID',
        `name` varchar(100) NOT NULL COMMENT '名称',
        `desc` varchar(500) COMMENT '描述',
         PRIMARY KEY (`id`)
    )"""

# 执行SQL语句创建表
cursor.execute(sqlCreateTable)

# 验证查询下表情况
cursor.execute("SHOW TABLES;")
print(cursor.fetchall())  # (('qitabledemo',),) 默认元组查询表列表


# 查询QiTableDemo表所有数据
sqlSelect = "SELECT * FROM QiTableDemo;" 

# 执行表查询语句
cursor.execute(sqlSelect)
print(cursor.fetchall())  # () 新表返回一个空的tuples

import pymysql

# 创建数据库连接
db = pymysql.connect(host='127.0.0.1',
                     user='root',
                     password='rox@123',
                     database='QiDBTest',
                     cursorclass=pymysql.cursors.DictCursor)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

sqlInsert = '''INSERT INTO qitabledemo(`name`,`desc`) VALUES ('插入测试名称', '插入测试描述');'''

# 执行表查询语句
cursor.execute(sqlInsert)

# 对执行提交，这里可以尝试注释掉验证不进行提交数据能否插入
db.commit()

# 查询数据是否正确插入
cursor.execute("select * from qitabledemo;")
print(cursor.fetchall()) # [{'id': 6, 'name': '插入测试名称', 'desc': '插入测试描述'}]

# 关闭数据库连接
db.close()

# 方式一：占位拼接字符串
sqlMethod1 = "INSERT INTO qitabledemo(`name`,`desc`) VALUES ('%s', '%s')" % ("占位名称","占位描述")
cursor.execute(sqlMethod1)

# 方式二：通过execute传参
sqlMethod2 = "INSERT INTO qitabledemo(`name`,`desc`) VALUES ('%s', '%s')"
cursor.execute(sqlMethod1，(变量1,变量2))

sql = "数据库操作的增删改查语句"
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 向数据库提交
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()