MySQL数据库3 Python操作MySQL及用户权限管理

MySQL数据库3 Python操作MySQL及用户权限管理

通过PyMySQL库操作数据库 之前都是通过命令行窗口和Navicat这种交互界面进行操作,现在要开始使用Python操作MySQL来进行自动化操作了. Python需要第三方库PyMySQL来操作MySQL数据库. 通过查询数据来看基本使用方法: import pymysql # 创建连接 co

通过PyMySQL库操作数据库

之前都是通过命令行窗口和Navicat这种交互界面进行操作,现在要开始使用Python操作MySQL来进行自动化操作了. Python需要第三方库PyMySQL来操作MySQL数据库. 通过查询数据来看基本使用方法:
import pymysql

# 创建连接
conn = pymysql.connect(host='localhost', user='root', password='', database='mydb')
# 在建立连接之后,通过连接创建游标,之后对数据库的具体操作,都是通过游标进行
cursor = conn.cursor()

sid = input('id>>>')

# 写sql语句,是字符串,可以用占位符变量
sql = "select * from student where sid < {}".format(sid)

# 这里需要通过游标拿到结果,结果是一个大的元组,每一条数据是其中一个元组.执行结果是受影响的行数(不是行号)
effect_row = cursor.execute(sql)

# 用fetchall和fetchone都可以获得结果,每执行一次fetchone,游标位置会向下走1
res = cursor.fetchone()

# 关闭游标和连接
cursor.close()
conn.close()

print(effect_row)
print(res)

上边的程序实际上就是输入一个sid,然后在student表里查询所有sid小于这个数字的结果,然后通过cursor的fetchone方法来拿到了第一个结果.有点类似于文件句柄,在查询结束以后,应该将游标和数据库连接都关闭

结果发现显示了乱码,后来发现,需要在实例化连接对象的时候,指定charset关键字参数的值为'utf8',此外还可以修改PyMySQL的配置文件connections.py,将DEFAULT_CHARSET从latin1改为utf8就可以了.如果修改了后者,则无需每次实例化的时候指定charset参数了.

实例化连接对象

MySQL数据库是通过socket连入的.PyMySQL用connect方法来生成一个数据库连接.
connect的常用参数
host 字符串组成的ip地址,如果是本机,可以用'localhost'表示
user 用户名
password 密码
database 数据库名
port MySQL使用的端口,默认是3306的端口
charset 指定使用的字符集,一般使用utf-8,需要数据库也是按照utf-8编码

CURSOR

连接成功之后,通过连接的cursor方法创建一个游标,这个游标类似于文件操作里的文件指针,是数据库的指针.后边的fetch类方法实际上与这个指针相关.

cursor的方法
close() 关闭游标
mogrify(query, args=None) 返回刚刚执行的execute的sql语句,必须将自己定义的query语句作为参数,可以直接返回传输给MySQL的语句.如果没有执行过exe,则会报错
execute(query, args=None) 最重要的方法,执行query语句,query是自己编写的字符串SQL语句,后边args可以传入占位符列表或者字典,用以拼接字符串.注意,execute的占位符只支持C语言风格的%s,不支持python风格的{}
executemany(query, args) args是一个序列,每个元素是一组参数,会将每组参数使用到query上,可以一次性执行多个参数的操作,常用在一次性insert多个数据的时候
fetchone() fetch系列,拿当前游标的下一行,实际内部操作一个rownumber变量.从cursor类的初始化里可以看出,初始化cursor对象的时候rownumber被设置为0.fetchone执行之后,游标数会+1
fetchmany(size=None) 一次取多行,不指定size则取下一行并将游标下移一行,指定size则一次性取出等于size的行,并将游标下移size行
fetchall() 一次性取出全部行,并且将游标移动到数据结尾.注意,尽量少用此数据,会占用大量内存,最好是通过变量和limit语句来控制
scroll(value, mode='relative') 移动游标,默认为相对移动,value为负数则上移,正数为下移.如果mode设置为absolute,则表示绝对定位,此时value不能为负,也不能超过最大值.游标的起始位置是0.

此外,默认cursor对象返回的是元组,这个元组是不包含行名的,其实可以修改cursor返回的类型,让其返回字典类型,这需要在实例化cursor对象的时候指定,将上边程序修改一下:

# cursor变成字典类型
import pymysql

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    database='mydb',
    charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sid = input('id>>>')
sql = "select * from student where sid < %s"

cursor.execute(sql,sid)
res = cursor.fetchmany(3) # 游标索引开始是0,取前三行内容,游标指向了3
print(res)

cursor.scroll(2)  # 游标下移2,指向了5
res2 = cursor.fetchone()  # 取出来的是第6行数据

print(res2)
cursor.close()
conn.close()
取多行的时候,得到的是一个字典列表,取单行的时候,得到的就是一个字典,键是列名,数据是值.
# print(res)的结果
[{'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '理解'}, 
{'sid': 2, 'gender': '女', 'class_id': 1, 'sname': '钢蛋'}, 
{'sid': 3, 'gender': '男', 'class_id': 1, 'sname': '张三'}]
# print(res2)的结果
res1的结果是{'sid': 6, 'gender': '男', 'class_id': 1, 'sname': '张四'}
如果对数据库执行了增加,删除,修改,在cursor所有的操作完成以后,必须执行连接对象conn.commit()方法提交修改,之后再关闭游标和连接,否则不会生效.查询则无需commit.

获得新插入数据的自增ID

经过查询从MySQL中获得数据之后,就可以对其操作了.剩下的主要是修改数据.修改的语句不再赘述,但是修改之后经常需要得到修改后最后的id用于定位.

import pymysql

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='',
    database='mydb',
    charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
gender = input('gender>>>')
classid = int(input('classid>>>'))
name = input('name>>>')

sql = "insert into student (gender,class_id,sname) values (%s,%s,%s)"
cursor.execute(sql, (gender, classid, name))
conn.commit()
print(cursor.lastrowid)
cursor.close()
conn.close()
这个时候cursor.lastrowid就会返回最后一行的对应的自增id.注意,这个自增id并不是表里总共有多少行,也不是游标的索引.在不截断数据表的情况下,这个表里删除过数据,再增加,自增id依然会按照原来的继续增加. 在没有删除过任何记录的情况下,这个id才等于总行数.

用户权限管理

无论是在命令行窗口还是python,目前使用的都是无密码的root用户,这个用户是MySQL初始化生成的.root是MySQL的管理员账户,有全部的权限.在生产环境中,DBA才有数据库的管理权限,一般用户大部分只能查询,少部分可以增加,很少会发放删除和修改权限.用户权限的设置,是通过建立用户,然后给用户赋予权限实现的.

MySQL的用户数据全部保存在Mysql数据库的user表内,创建用户就会导致MySQL修改此表,由于该表的数据和相关结构非常重要,不建议直接修改此表,最好通过命令操作.

新建和修改用户名和密码
新增用户 CREATE USER username 建立一个叫username的用户,可以不指定密码
CREATE USER username IDENTIFIED BY 'string' 可以加上IDENTIFIED BY子句创建密码,后边为纯文本密码,MysQL会将密码的hash值存储到user表内的authenticating_string列内
username@host 所有对用户的操作,用户名后边都可以加上@地址,MySQL的权限是结合用户和地址的,如果不给出地址,则默认为%,即所有地址都可以.
删除用户 DROP USER username 删除一个用户,会将这个用户所有的权限和用户名本身都删除
修改用户 RENAME USER old_name TO new_name 重命名用户
SET PASSWORD FOR username ='new_password' 修改用户的密码

新建的用户没有任何权限,登陆MySQL之后,仅能看到information_schema这个数据库,里边存放的是MySQL的各种帮助信息.而且也不能建立数据库,为了让用户能够使用数据库,必须给用户授予权限

用户权限设置
SHOW GRANTS FOR username 显示用户的权限,如果只有USAGE ON *.* 表示没有任何权限
GRANT privilege ON database_name.table.name TO username MySQL的权限是动作结合数据库以及表的,可以详细到某个数据库的某个表的权限,privilege部分可以用逗号隔开多个权限
REVOKE privilege ON database_name.table.name FROM username REVOKE FROM 用来撤销权限,被撤销的权限必须存在,否则报错.privilege部分可以用逗号隔开多个权限
权限的具体清单不再列出.通过控制用户权限,即可有效的管理数据库.
LICENSED UNDER CC BY-NC-SA 4.0
Comment