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

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默认为1print(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 会自动去处理。