Python操作MySQL

一 用户管理

用户在数据库操作系统中，是一个设计到数据安全的非常重要的数据库对象，在MySQL系统的数据库中root用户作为超级管理员用户，通常情况下不会给普通用户使用，所以掌握数据库管理系统中的用户管理和维护，是非常重要且必须的技能，对于用户的操作。

（一）连接数据库

在命令行执行命令 mysql --help 查看操作命令和帮助，会出现大量的操作选项，其中较为常用的选项如
下：
● -h：host 连接数据库的主机名称，通常我们使用 ip 地址进行确定
● -P：Port 端口号，大写字符 P 指定连接数据库服务器使用的端口号
● -u：user 连接数据库的用户账号
● -p：password 连接数据库的用户密码
● -e：execute 执行一条指定的 sql 语句并退出命令行

mysql -u root -p -e "select user, authentication_string from mysql.user"

（二）创建用户

--基本格式：
create user 用户名@地址 identified by 'password';
--案例
create user lyh@localhost identified by '123456';
--查看所有用户
select user,host from mysql.user;

• CREATE USER：用于创建一个新用户的关键字
• user：创建的用户账号
• host：指定创建用户账号的数据库主机
• IDENTIFIED BY：用于设置用户登录数据库的密码
• ‘password’：设置的登录数据库的明文密码

( 三 ) 删除用户

--DROP删除用户基本语法：
DROP USER user@host;
--DELETE删除用户基本语法：因为用户也是在一个表中，可以从表中删除对应数据
DELETE FROM mysql.user WHERE host = 'hostname' and user = 'username';
-- 删除用户
drop user lyh@localhost;
delete from mysql.user where host='localhost' and user='lyh';

( 四 ) 修改密码

--登录mysql后操作
ALTER USER "root"@"localhost" IDENTIFIED BY "你的新密码";
--修改密码
alter user lyh@localhost identified by '111111';
--在命令行中修改密码
mysqladmin -u '用户' -p password '新密码';
Enter password：旧密码
mysqladmin -ulyh -p password 222222
enter password:111111

( 五 ) 忘记密码处理

方法1：
绕过密码登录：
-- 先停止mysql服务,在cmd中使用绕过权限认证
net stop mysql
mysqld --console --skip-grant-tables --shared-memory
-- 再另开cmd即可不用密码登录
mysql -ulyh -p
use mysql;
update user set authentication_string='' where user='lyh'and host='localhost';
flush privileges;
-- 先改成空,然后启动服务,再修改密码
alter user lyh@localhost identified by '111';
方法2：
--步骤一，通过系统服务停止MySQL服务，或者在命令窗口直接执行命令停止数据库服务
$ net stop mysql
--步骤二，创建一个sql文件，将修改密码的命令添加到文件中：
alter user 'lyh'@'localhost' identified by '123456';
--步骤三，启动服务器，使用mysqld命令附带--init-file参数执行命令，关闭服务窗口
$ mysqld --init-file=D:\mysql-8.0.17-winx64\passwd.sql --console
-- 步骤四，开启mysql服务
net start mysql
-- 步骤五，按照修改的密码登录
mysql -ulyh -p123456

二 权限管理

权限管理是针对MySQL中的用户对于数据库和数据表中的数据的管理操作，MySQL中的所有用户以及访问数据的操作权限都是包含在数据库表信息中，对于用户的合理的权限分配，能极大程度的区分不同用户在数据库中的数据操作边界，提高系统的安全性。

(一） 权限描述(了解)

MySQL数据库中提供了user、db、host、tables_priv、columns_priv、procs_priv的数据表，规范了用户对于数据库、数据表以及数据的各种操作权限。
权限操作主要通过grant和revoke命令进行操作，命令可操作的权限表列名称和每个权限有关的操作对象的映射关系如下表：

• create、drop权限，创建数据库和数据表，可以删除数据库和数据表。
• insert、delete、update、select权限，用于对数据表中的数据进行操作。
• index权限，适用于对已有数据包的索引添加和删除操作。
• alter权限，适用于alter table改变数据表结构的操作。
• create routing、alter routing、executor权限，用于创建、修改或者执行创建的函数或者存储程序。
• grant权限，用于进行授权操作。

上述不同的权限用于在数据库维护过程中，针对临时操作用户或者已有项目访问用户的数据操作边界，进行全新的认定和授权操作，提高数据库中数据的安全性。

（二） 授权操作

授权操作通过GRANT命令，完成对单个用户的权限的详细定义，根据具体的用户操作级别，可以控制授权的力度。区分为如下不同几种权限：

• 全局层级
• 数据库层级
• 表层级
• 列层级
• 子程序层级

1 全局层级

全局层级权限，主要包含在mysql.user数据表中，项目中使用较多
grant all on *.* ; 授权
revoke all on *.* ; 收回权限

2 数据库层级

数据库层级权限，主要针对某个数据库的操作权限，包含在mysql.host和mysql.db数据表中项目中使用较多
grant all on db_name. ;* 授权
revoke all on db_name. ;* 收回权限

3 表层级

数据表层级权限，主要针对某个和数据表中的数据进行操作的权限，包含在mysql.tables_priv数据表中
grant all on db_name.table_name ; 授权
revoke all on db_name.table_name ; 收回权限

4 列层级

数据列、字段层级权限，主要针对某个数据列设置操作权限，包含在mysql.columns_priv数据表中

--针对lyh账号在db数据库中的user表下的id和name字段授予select/update权限
grant select(id,name),update(age) on db1.t1 to 'lyh'@'localhost';

5 刷新权限

flush privileges

--案例操作：创建一个新用户，并授权用户对所有数据的插入、修改和查询的权限,新版本已经将用户管理和
权限管理分开了
GRANT INSERT, UPDATE, SELECT ON *.* TO 'lyh'@'localhost' IDENTIFIED BY '123456'
WITH GRANT OPTION; -- 语法错误
--1创建用户
create user lyh2@localhost identified by '654321';
--2分配权限
grant select,create on *.* to lyh2@localhost with grant option;

发现该用户无法删除表中数据

（三）收回权限

收回权限就是回收权限，将用户已有权限进行取消的操作， 使用REVOKE命令回收权限。
基本操作语法：

REVOKE priv_type [(clumuns)], [, priv_type [(columns)]]...
ON table1, table2...
FROM 'user'@'host' [, 'user'@'host'...]

案例操作：回收用户lyh2的查看权限

--1登录root用户
mysql -uroot -p
--2收回权限
REVOKE select ON *.* FROM 'lyh2'@'localhost';
--3刷新权限
flush privileges;
--4 退出root用户，lyh2登录，进行select操作
mysql -ulyh2 -p654321
use my_db;
select * from emp;

( 四 ) 查看权限

SHOW GRANTS FOR ‘user’@‘host’;
案例：查看lyh2权限

show grants for lyh2@localhost;  

三 python操作数据库

（一） 安装mysql: pip install pymysql

国内镜像:https://pypi.tuna.tsinghua.edu.cn/simple

（二）python 连接数据库方法

1 引入模块

from pymysql import *

2 创建Connection 对象

• 用于建立与数据库的连接
• 创建对象：调用connect()方法
• connect = Connection = Connect

conn=connect(参数列表)

connect参数很多，有32个，但是常用的只有如下

• 参数host：连接的mysql主机，如果本机是’localhost’
• 参数port：连接的mysql主机的端口，默认是3306
• 参数database：数据库的名称
• 参数user：连接的用户名
• 参数password：连接的密码
• 参数charset：通信采用的编码方式，推荐使用utf8

3 对象的方法

• close()：关闭连接
• commit()：提交
• cursor()：返回Cursor对象，用于执行sql语句并获得结果

4 Cursor对象作用：

• 用于执行sql语句，使用频度最高的语句为select、insert、update、delete

5 cursor对象的方法

• close()关闭
• execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
• fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
• fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回

6 cursor对象的属性

• rowcount只读属性，表示最近一次execute()执行后受影响的行数
• connection获得当前连接对象

7 python操作数据库步骤

• 连接数据库
• 获取一个访问数据库的操作对象conn
• 获取cursor对象
• 定义SQL语句
• 通过cursor对象执行SQL语句
• 查询结果
• 通过conn提交结果：有修改时候需要
• 关闭cursor对象和conn对象

（三）案例

1 准备工作

# 创建数据库
CREATE DATABASE py_user;
# 指定使用数据库
USE py_user;
# 创建用户表
CREATE TABLE users(
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL COMMENT '用户账号',
userpass VARCHAR(50) NOT NULL COMMENT '登录密码',
nickname VARCHAR(50) COMMENT '昵称',
    age INT COMMENT '年龄',
gender VARCHAR(5) COMMENT '性别',
phone VARCHAR(15) COMMENT '联系方式',
email VARCHAR(50) COMMENT '邮箱',
createTime DATETIME COMMENT '账号创建时间',
updateTime DATETIME COMMENT '账号最后修改时间',
lastLogin DATETIME COMMENT '账号最后登录时间',
usersFlag INT COMMENT '账号状态：0 正常 1 锁定 2 删除',
remark TEXT COMMENT '备注'
) DEFAULT CHARSET "utf8";
# 增加测试数据
INSERT INTO users(username, userpass, nickname, age, gender, phone, email,
createTime, updateTime, lastLogin, usersFlag, remark)
VALUES("tom", "123", "凯特", 48, "男", "13868686868", "cat@163.com", "2017-06-
01","2017-06-02","2017-06-05",0,"tom and jerry 管理员"),
("jerry", "111", "杰瑞", 46, "女", "15688888888", "mouse@163.com", "2017-06-
01","2017-06-03","2017-06-04",0,"tom and jerry 管理员");

2 python操作mysql

# 导入包
import pymysql
# 创建conn对象
conn = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='mysql',
database='py_user',
charset='utf8'
) #
获得cursor对象
curs = conn.cursor()
# 创建SQL语句
# 查询操作
# sql = 'select * from users'
# 新增操作
# sql = "insert into users values (0,'张三','123','zs',20,'男',
'18888888888','zs@163.com','2020-01-01','2020-05-10','2020-06-01',0,'普通用户')"
# sql = "insert into users values (0,'李四','123','zs',20,'男',
'18888888888','zs@163.com','2020-01-01','2020-05-10','2020-06-01',0,'普通用户')"
# 修改操作
# sql = "update users set nickname = '凯特大叔' where id = 1 "
# 执行SQL语句,
# 删除操作
sql ="delete from users where id = 4 "
count = curs.execute(sql)
# 查询受影响的行数,即查询到的数量
print(count)
# 提交修改
conn.commit()
# 获得具体数据
# for _ in range(count):
# res = curs.fetchone() # 获取一条信息
# print(res) # 打印具体信息
# res2 = curs.fetchall() # 获取全部信息
# print(res2) # 一次性打印全部信息
# 关闭cursor对象
curs.close()
conn.close()

3 参数化和SQL注入(了解)

from pymysql import *
def main():
find_name = input("请输入要查询的人物名字：")
# 创建Connection连接
conn =
connect(host='localhost',port=3306,user='root',password='mysql',database='py_use
r',charset='utf8')
# 获得Cursor对象
cs1 = conn.cursor()
# 非安全的方式
# 正常sql语句
# sql = 'select * from users where username="%s"' % find_name
"""
恶意sql语句:通过用户输入内容来改变
用户输入：张三" or "1=1,则sql语句则为：
select * from users where username="张三" or "1=1" 是恒成立的条件,则查询所有内容
"""
sql = 'select * from users where username="%s"' % find_name
print("""sql===>%s<====""" % sql)
# 执行select语句，并返回受影响的行数：查询所有数据
count = cs1.execute(sql)
# 安全的方式
# 构造参数列表
# params = [find_name]
# 执行select语句，并返回受影响的行数：查询所有数据
# count = cs1.execute('select * from users where username="%s"', params)
# 注意：
# 如果要是有多个参数，需要进行参数化
# 那么params = [数值1, 数值2....]，此时sql语句中有多个%s即可
# 打印受影响的行数
print(count)
# 获取查询的结果
# result = cs1.fetchone()
result = cs1.fetchall()
# 打印查询的结果
print(result)
# 关闭Cursor对象
cs1.close()
# 关闭Connection对象
conn.close()
if __name__ == '__main__':
main()

SQL注入

防止SQL注入