python操作MySQL数据库
python操作MySQL
python中支持操作MySQL的模块很多,最常见是pymysql,pymysql属于第三方模块,需要自行下载。
pip install pymysql
基本使用
现有如下数据:
通过python代码获取数据:
# 1.导入模块
import pymysql
# 2.连接数据库
db_obj = pymysql.connect(
host='127.0.0.1', # MySQL服务端的IP地址
port=3306, # MySQL默认PORT地址(端口号)
user='root', # 用户名
password='123456', # 密码,也可以简写passwd=
database='school', # 库名称,也可以简写db=
charset='utf8' # 字符编码,千万不要加杠utf-8
)
# 3.产生获取命令的游标对象
cursor = db_obj.cursor() # 不写参数返回的是元组
# 4.编写SQL语句
sql = 'select * from student;'
# 5.执行SQL语句
affect_rows = cursor.execute(sql) # 返回的是受影响的行数
print(affect_rows)
# 6.获取全部结果
res = cursor.fetchall()
print(res)
对于游标对象,不写参数时每一个记录默认返回的是元组类型,如果想要返回其他类型,比如字典类型,可以在产出游标对象时加上参数:
cursor = db_obj.cursor(
cursor=pymysql.cursors.DictCursor
)
补充说明
在获取SQL语句执行的结果时,跟读取文件内容的read方法几乎一致(光标)。
- fetchall()可以获取当前光标到结尾的全部数据,并把光标移到末尾。
- fetchone()可以获取当前光标往后一个的数据,并把光标往后移动一位。
- fetchmany()可以获取当前光标往后任意个数据,并把光标往后移动相应的位数,比如fetchmany(3)就是获取三个数据。
此外,还有可以控制光标移动的方法:
- 游标对象.scroll(m, 'relative'),相对于当前位置往后移动m个单位
- 游标对象.scroll(m, 'absolute'),相对于起始位置往后移动m个单位
SQL注入问题
先看一个例子:
数据:
代码:
import pymysql
db_obj = pymysql.connect(
host='127.0.0.1', port=3306,
user='root', password='332525',
db='userinfo'
)
cursor = db_obj.cursor(
cursor=pymysql.cursors.DictCursor
)
# 获取输入
username = input('请输入用户名:')
password = input('请输入密码:')
sql = "select * from user where username='%s' and password='%s'" % (username, password)
print(sql)
cursor.execute(sql)
res = cursor.fetchall()
if res:
print('登录成功!')
else:
print('用户名或密码错误')
运行后输入,tom,123,登录成功!这是没有问题的,但是如果你这么输入(只输入用户名):
请输入用户名:tom' -- abc
密码:
这时还是登录成功。
我们可以把sql语句输出看一看:
select * from user where username='tom' -- abc' and password=''
我们发现,后面的密码比对的条件被注释了,这就是典型的SQL注入问题。
解决方法
execute方法自带校验SQL注入问题,自动处理特殊符号。
把执行SQL语句这么写就行了:
# 获取输入
username = input('请输入用户名:')
password = input('请输入密码:')
sql = "select * from user where username=%s and password=%s"
res = cursor.execute(sql, (username, password))
execute还可以批量写入数据,如:
sql = 'insert into userinfo(name,password) values(%s,%s)'
cursor.executemany(sql,[('tom',123),('lavin',321),('pony',333)])
二次确认
在使用刚刚所学的python代码修改数据库数据时,你会发现明明没有报错,确无法把数据库里的数据修改。
这是因为pymysql针对增、改、删三个操作,都设置了二次确认,如果不确认则不会真正影响数据库。
方法一:手动提交
cursor.execute(sql)
db_obj.commit() # 手动二次确认
方法二:连接数据库时配置固定参数
db_obj = pymysql.connect(
autocommit=True # 自动二次确认
)