数据库--用户管理, pymysql
1.用户管理
- 主要是为了控制权限，让不同开发者，仅能操作属于自己的业务范围内的数据
创建MySQL账户
账户中涉及的三个数据：
- 账户名
- 密码
- 主机地址：用于限制某个账户只能在哪些机器上登录，'%'代表所有地址，但不包括本机，localhost指向127.0.0.1
# 创建用户
# 密码可以为空
create user '用户名'@'主机地址' identified by '密码';
# 删除用户
drop user '用户名'@'主机地址';
# 注意：操作用户只能由root账户来进行
# 刚注册的用户没有任何权限，包括登录权限也没有
可以通过查看user表查看用户，\G列查看
权限管理
涉及到的表（在mysql库下）
user 与用户相关的信息
db 用户的数据库权限信息
talbes_priv 用户的表权限
columns_priv 用户的字段权限
语法：
# 语法
# 这个写法在8.0以前可以用
GRANT <privileges> ON <dbname>.<tablename> TO '<username>'@'<host>' identified by "密码";
# 这个写法在8.0用
GRANT <privileges> ON <dbname>.<tablename> TO '<username>'@'<host>';
# privileges：用户的操作权限，如SELECT，INSERT，UPDATE等,多个权限用','分割，所有权限则使用all
# dbname.tablename：授权给用户的库表，所有库的所有表用*.*
# 创建在所有库有所有权限的用户，但是并没有操作用户的权限
grant all on *.* to 'haha'@'localhost'; # 可以在user表中查看
# 创建在某个库有所有权限的用户
grant all on day42 to 'haha1'@'localhost'; # 可以在user,db中查看
# 创建在某个表有所有权限的用户
grant all on day42.table1 to 'haha2'@'localhost'; # 可以在user,tables_priv中查看
# 创建在某个字段有读，更新权限的用户
grant select(name), update(name) on day42.table1 to 'haha3'@'localhost'; # 可以在user,talbes_priv,columns_priv表中查看
# with grant option 表示可以将他拥有的权限授予其他用户
grant all on *.* to 'root1'@'localhost' with grant option;
# 收回权限
revoke <privileges> on <dbname>.<tablename> from '<username>'@'<host>' ;
# 授予某个用户，可以在任何主机上登录
grant all on *.* to haha4@"%"; # 本机无法登陆
# 再创建一个本机的，并不是给上面的加权限，而是又新建了一个用户也叫haha4，可以在user表中看到
group all on *.* to haha4@'localhost';
注意：
这些操作在敲完之后一定要刷新
flush privileges
2.可视化客户端
navicat
mysql workbench
3.pymysql
- 是一个第三方模块，帮我们封装了，建立连接，用户认证，sql的执行以及数据的获取
基本使用
import pymysql
# 1. 连接服务器，获取连接对象（本质上就是封装好的socket）
conn = pymysql.connect(
host = '127.0.0.1', # 如果是本机，可以不填
port = 3306, # 如果没改过默认的端口，可以不填
user = 'root', # 必填，不会走mysql的配置文件
password = '123', # 必填
database = 'day42' # 必填
)
# 2. 通过连接拿到游标对象
# 默认的游标返回的是元祖类型，不方便查看使用，需要更换字典类型的游标
c = conn.cursor() # 元祖
c = conn.cursor(pymysql.cursors.DictCursor) # 字典
# 3. 执行sql语句
sql = 'select * from table1'
res = c.execute(sql)
# 各种语句返回的都是结果的数量，比如查看到了一条就返回1，修改了一条返回1之类
# 4. 提取结果，类似于迭代，只能提取一次
print(res)
print(c.fetchall()) # 提取所有 返回格式 [{}]
# print(c.fetchmany(2)) # 提取多条 返回格式 [{}]
# print(c.fetchone()) # 提取一条 返回格式 {}
# 5. 关闭连接
c.close() # 先关闭游标
conn.close()
# 如果想要再重复读取，需要移动指针，参数1是移动的位置，mode指定相对或绝对，默认是相对
c.scroll(1, mode='absolute') # 绝对，从头部开始计算
sql注入攻击
一些程序员，在输入数据时，按照sql的语法规范，提交了用于攻击性目的的数据
案例：登录
import pymysql
# 这些固定用法可以写到python配置文件中
conn = pymysql.connect(
host = "127.0.0.1",
port = 3306,
user = "root",
password = "111",
database = "day42",
)
c = conn.curssor(pymysql.cursors.DictCursor)
name = input('name:')
pwd = input('pwd:')
sql = 'select * from user where name = "%s" and pwd = %s' % (name, pwd)
res = c.execute(sql)
if res:
print('登录成功')
else:
print('登录失败')
c.close()
conn.close()
# 这个程序看上去没什么问题，但是当有程序员恶意输入时就会出现问题，比如:
# 帐号输入 " or 1 = 1; -- 密码随意输入比如1，也一样可以登录
# 这是因为实际上给mysql输入的内容为
select * from user where name = "" or 1 = 1; -- " and pwd = 1
# 而--就是注释的意思，后面的代码不会执行，所以前面的语句因为有1=1所以会输出数据，导致错误登录
# 注意：注释-- 后面需要有一个空格
如何避免这个问题
在服务器段执行sql语句以前做sql的验证，判断有没有一些关键的代码，比如drop 等等
之所以不在客户端做验证，是因为在传输过程中程序员依然可以抓取数据进行更改
pymysql已经封装了这个过程，只需要将参数交给pymysql来做拼接即可
需要修改的代码为：
# 将拼接参数交给pymysql
sql = 'select * from user where name = %s and pwd = %s'
res = c.execute(sql, (name, pwd))
事务
- pymysql默认执行了
start transaction开启了事务
import pymysql
conn = pymysql.connect(
...# 重复的配置代码就不写了，上同
# autocommit = True # 这是开启自动提交，默认是False，一般不要改这个，自己手动提交就好了，更加灵活
)
c = conn.cursor(pymysql, coursors.DictCursor)
name = input('name:')
pwd = input('pwd:')
sql1 = 'select * from user where name = %s'
if c.execute(sql1, (name,)): # 一定要提交元组
print('用户名已存在！')
else:
sql2 = 'insert into user values(%s, %s)'
if c.execute(sql2, (name, pwd)):
print('注册成功')
conn.commit() # 调用pymysql封装的连接对象的提交函数，也可以在配置文件中进行修改
else:
print('注册失败')
c.close()
conn.close()
调用储存过程
# mysql中创建名为add1的储存过程
delimiter |
create procedure add1(in a int, in b int, out c int)
begin
set c = a + b;
end|
delimiter;
# 配置文件不变
c.callproc('add1', (30, 40, 12138))# @_add1_0, @_add1_1, @_add1_2
c.execute('select @_add1_2')
print(c.fetchone())
# 调用储存过程时，传入的参数会自动定义成变量
# 变量的命名方式是 @_过程的名称_参数的索引 索引从0开始
