【Python】Python操作MySQL详解——PyMySQL

一，Python操作数据库简介

Python标准数据库规范为 DB-API， DB-API定义了一系列必须的对象和数据库操作方式，以便为各种数据库系统和数据库访问程序提供一致的访问接口。

开发人员将接口封装成不同的数据库操作模块，不同的数据库需要不同数据库操作模块，例如，MySQL数据库，它对应以下操作模块：https://wiki.python.org/moin/MySQL

其中，最常用的应该是
1，MySQL-python：也就是MySQLdb，底层是通过C操作MySQL，效率高，但是只支持py2，不支持py3。
2，mysqlclient：是MySQL-python的一个分支。它增加了Python 3支持，并修复了许多错误。Django文档推荐的MySQL依赖库。
3，PyMySQL：纯Python实现的模块，可以与Python代码兼容衔接，并也几乎兼容MySQL-python。
4，MySQL Connector/Python：MySQL官方推出的纯Python实现的模块。

二，Python操作MySQL——PyMySQL

（一）PyMySQL模块简介

纯Python实现的模块，可以与Python代码兼容衔接，并也几乎兼容MySQL-python。遵循 Python 数据库 API v2.0 规范。安装PyMySQL需要满足以下需求。

（二）PyMySQL使用

1，连接数据库
使用connect函数创建连接对象，此连接对象提供关闭数据库、事务提交、事务回滚等操作。
传入参数有很多，具体参考文档，一般参数基本连接信息 host, user, password, port(默认为3306), database。

import pymysql
conn = pymysql.connect(
    host='127.0.0.1',
    user='xxx',
    password='xxxx',
    port=3306,
    database='nba_data'
)

主要方法

方法	功能
cursor()	获取游标对象，操作数据库，如执行DML操作，调用存储过程等
commit()	提交事务
rollback()	回滚事务
close()	关闭数据库连接

2，操作数据库
使用Cursor对象与数据库进行交互。具体方法参考：https://pymysql.readthedocs.io/en/latest/modules/cursors.html

2.1，查询操作

import pymysql
conn = pymysql.connect(
    host='127.0.0.1',
    user='python_link',
    password='python_link',
    port=3306,
    database='nba_data'
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()

# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)

sql = 'select * from player_info_test limit 10'
try:
    cursor.execute(sql)
    # result = cursor.fetchall()  # 返回所有数据
    # result = cursor.fetchone()  # 返回一行数据
    result = cursor.fetchmany(2)  # fetchmany(size) 获取查询结果集中指定数量的记录，size默认为1
    print(result)
except Exception as e:
    conn.rollback()
    print(e)
finally:
    cursor.close()
    conn.close()

2.2，插入操作
插入操作中参数可以以元组、列表和字典形式传入，需要使用到占位符 “%s”，注意这只是个占位符，不同于Python 中字符串格式化中的转换说明符。

# 元组、列表形式传参
sql = 'insert into player_info_test(PERSON_ID,PERSON_NAME,AGE) VALUES(%s, %s, %s)'
try:
    cursor.execute(sql, ("999999", "kenny", 28))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(e)
finally:
    cursor.close()
    conn.close()


# 字典形式传参
sql = 'insert into player_info_test(PERSON_ID,PERSON_NAME,AGE) VALUES(%(person_id)s, %(person_name)s, %(age)s)'
try:
    cursor.execute(sql, {"person_id": "999998", "person_name": "kenny", "age": 28})
    conn.commit()
except Exception as e:
    conn.rollback()
    print(e)
finally:
    cursor.close()
    conn.close()

2.3，更新操作

sql = 'update  player_info_test set PERSON_NAME=%s where PERSON_ID = %s'
try:
    cursor.execute(sql, ['liu', '999998'])
    conn.commit()
except Exception as e:
    conn.rollback()
    print(e)
finally:
    cursor.close()
    conn.close()

2.4，删除操作

sql = 'delete from  player_info_test where PERSON_ID = %s'
try:
    cursor.execute(sql, ['999999'])
    conn.commit()
except Exception as e:
    conn.rollback()
    print(e)
finally:
    cursor.close()
    conn.close()

增删改需要有提交事务的操作，除了execute方法，还有批量操作方法executemany()。

2.5，批量插入操作

sql = 'insert into player_info_test(PERSON_ID,PERSON_NAME,AGE) VALUES(%s, %s, %s)'
param = [("999999", "kenny", 28), (["999998", "liu", 28)]   # 元组列表作为传入参数
try:
    cursor.executemany(sql, param)
    conn.commit()
except Exception as e:
    conn.rollback()
    print(e)
finally:
    cursor.close()
    conn.close()

（三）SQL防注入

SQL注入是一种常见的网络攻击手法，它利用sql的语法特性和程序员编写程序时产生的漏洞，用一些特殊符号的组合产生特殊的含义，使得正常的sql语句失效，从而逃脱正常的业务逻辑，完成一些如跳过密码验证等的非法操作。

产生原因：SQL语句使用了动态拼接的方式。

比如，登录时，使用以下SQL查询验证用户信息

'SELECT username FROM user WHERE username = %s AND password = %s' % (username ,password)

并且，没有对用户的输入做任何处理，直接放到了SQL语句中。那么，当黑客输入了’jjyang’ OR 1=1 – jjyang 作为用户名时，原来的SQL语句就会变成下面的样子：

SELECT username FROM user WHERE username = 'jjyang' OR 1=1 -- jjyang AND password=''

WHERE username = ‘jjyang’ OR 1=1 是一个恒成立的条件，所以无论输入什么用户名都会返回True；而–后面的语句被当作注释忽略掉了，密码验证也被跳过。最终，绕过验证，成功登录。

针对参数不要采用拼接处理，交给pymysql中的方法（execute）自动处理，并对输入数据进行检查校验

# 元组,列表形式
param = ('jjyangs','123445')
sql = 'SELECT username FROM user WHERE username = %s AND password = %s'
cursor.execute(sql, param)

# 字典形式
param = {'name':'jjyangs','pwd':'123445'}
sql = 'SELECT username FROM user WHERE username = %(name)s AND password = %(pwd)s'
cursor.execute(sql, param)

需要注意的是，不要因为参数是其他类型而换掉 %s，pymysql 的占位符并不是 python 的通用格式化转换说明符。同时，也不要因为参数是 string 就在 %s 两边加引号，mysql 会自动去处理。