11-3 数据库的用户管理和pymysql模块的使用
1 用户管理
对数据库的管理工作一般是由专门的DBA人员处理,但是小公司没有DBA的情况很常见,所以我们有时也需要承担数据库的维护.
数据库最重要的就是数据,所以对用户的权限管理就显得很重要.不然哪一天,就可能出现丢失数据和删库的情况.
1.1 创建mysql用户
首先创建一个mysql用户,涉及到的3个数据:
用户名 密码 IP地址
# 其中IP地址适用于限制某个账户只能在某台机器上登陆,也可以限制在某个IP段
创建用户的语法:
# 创建一个用户yaco,可以在本地localhost上登陆,密码为123.
create user yaco@localhost identified by '123';
# 注意 : 操作用户 只能由 root账户来进行
# 如果想要让某个账户也可以创建账户,可以在授权的时候,指定
# 这是一种创建用户的方式,还有一张方式可以同时创建账户和授权,建议使用接下来的方式.
删除用户: 将同时删除所有权限
drop user 用户名@主机地址;
1.2 权限管理
1.2.1 授权
# 语法:
grant all on *.* to 用户名@主机地址 identified by '密码';
flush privileges; # 刷新权限
# all 表示的是对所有字段的增删改查
# *.* 表示的是 所有库的所有表
# 这是给这个用户 所有的库表的所有操作权限
# 如果用户不存在,则自动创建这个新用户.
# 如果 用户存在,后面的identified by '密码' 就可以不用写了
# 我们也可以限制用户只能操作哪个数据库
# 下面的是表示yaco用户只能操作day042这个数据库.通过show databases 可以发现,只显示对应的数据库.
grant all on day042.* to yaco@localhost ;
# 这样创建的用户,我们可以在 mysql库中的user表查看是否已经生成这个账户.同时,在db表中也会有该用户的信息.
# 我们也就可以限制用户只能操作哪张表
# 下面的是表示 yaco用户只能操作day041数据库下的table1表,其他表对这个用户不可见,也无权限查询.
grant all on day041.table1 to yaco@localhost ;
# 这样创建的用户,我们可以在user表总查看到用户信息,同时也可在table_priv 表中查看到用户的信息.
# 甚至 我们也可以限制用户只能操作某张表的某个字段
# 下面的yaco用户只能操作table1表的name字段的查询和更新操作.其他操作无权限
grant select(name),update(name) on day042.table1 to yaco@localhost;
# 这样创建的用户,我们可以在user表总查看到用户信息,同时也可以在columns_priv中查看
# 如果想要让某个用户也可以创建用户或者授权,可以使用with grant option
# 表示 可以将它拥有的权限授予其他的用户
grant all on *.* to yaco@localhost identified by '123' with grant option;
# 限制IP段访问
# % 表示任意IP都可以连接到数据库
grant all on *.* to yaco@'%' identified by '123';
1.2.2 取消权限
取消权限可以使用revoke
# 语法:
# 取消yaco的所有权限
revoke all on *.* from yaco@localhost
# 取消yaco table1的权限
revoke all on day42.table1 from yaco@localhost;
2 pymysql 模块
2.1 基本使用
import pymysql
"""
1. 连接服务器
2. 用户认证
3. 发送指令
4. 提取结果
"""
# 1 获得一个连接对象(本质上就是封装好的socket)
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '111',
database = 'day42'
)
# 2 通过连接拿到游标对象
# 默认的游标返回的是元组类型 不方便使用,需要更换成字典类型的游标
cur = conn.cursor(pymysql.cursor.DictCursor)
# 3 执行sql语句
# execute执行后返回的是受影响的行数,
# 这个返回的行数一般没什么使用场景
sql = 'select * from table1'
cur.execute(sql)
# 4 提取结果
print(cur.fetchone()) # 获取1个记录
print(cur.fetchmany(个数)) # 获取指定个数的记录
print(cur.fetchall())# 获取全部记录
# 5 关闭连接
cur.close()
conn.close()
# 光标
# 调整光标位置,可以改变我们读取的记录顺序
# 参数1表示移动的位置,向后移一位,1,向前一位就是-1
# mode 指定相对或者是 绝对
cur.scroll(1,mode = 'absolutie')
2.2 sql注入攻击
指的是 一些程序员 在输入数据时,按照sql的语法规范,提交了带有攻击性目的的数据
如何避免
- 我们可以在服务器端执行sql之前做sql语句的验证
- pymysql已经封装了sql的验证,我们只需要将参数交给pymysql来做拼接即可
2.3 修改数据
注意: pymysql 自动开启了事务,所以我们要自己决定在合适的位置进行提交操作.
import pymysql
conn = pymysql.connect(
host = "127.0.0.1", #如果是本机 可以忽略
port = 3306, # 如果没改过 可以忽略
user = "root", #必填
password = "111", #必填
database = "day42", #必填,
#autocommit=False # 开启自动提交 不常用....
)
c = conn.cursor(pymysql.cursors.DictCursor)
name = input("name:")
pwd = input("pwd:")
sql = "select *from user where name = %s"
if c.execute(sql,(name,)):
print("用户名已存在!")
else:
sql2 = "insert into user values(%s,%s)"
if c.execute(sql2,(name,pwd)):
print("注册成功!")
conn.commit() # 调用连接对象的提交函数
else:
print("注册失败!")
c.close()
conn.close()
2.4 调用存储过程
# 我们先在数据库中创建一个add1过程
delimiter |
create procedure add1(in a int,in b int,out c int)
begin
set c = a + b;
end |
delimiter ;
# 在pymysql中调用这个add1过程
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
user = 'root',
password = '123',
port = 3306,
database = 'day41'
)
cur = conn.cursor(pymysql.cursors.DictCursor)
cur.callproc('add1',(1,2,_))
# 调用存储过程时,传入参数,会自动定义成变量
# 命名方式 @_过程名_索引 ,注意从0开始
# @_add_0 @_add_1 @_add_2
cur.execute('select @_add1_2')
print(cur.fetchone())
