通过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部分可以用逗号隔开多个权限 |
权限的具体清单不再列出.通过控制用户权限,即可有效的管理数据库.
