Python 操作 MySql

pymysql 介绍

该模块属于第三方模块，用于操作mysql，需要手动下载，使用语句pip3 install pymysql或者直接在pycharm直接下载。

基本使用

创建 mysql 服务器的套接字对象

1. 语法：  pymysql.connect()

2. 创建对象常用参数
（详细内容参阅https://pymysql.readthedocs.io/en/latest/modules/connections.html#pymysql.connections.Connection.begin）

	host – 数据库服务器所在的主机。
	user – 登录的用户名。
	password- 要使用的密码。
	database – 要使用的数据库，None 表示不使用特定的数据库。
	port - 要使用的 MySQL 端口，默认值通常是可以的。（默认：3306）
	charset -- 要使用的字符集。
	sql_mode – 要使用的默认 SQL_MODE。
	db –已弃用的数据库别名。
	passwd –已弃用的密码别名。

3. 示例

		import pymysql
		
		conn_obj = pymysql.connect(
		    host='127.0.0.1',
		    port=3306,
		    user='root',
		    password='12345',
		    database='d1',
		    charset='utf8'
		)

常见套接字对象方法

close()			发送退出消息并关闭套接字。
commit()		提交对稳定存储的更改。
rollback()		回滚当前事务
cursor()		创建一个新的游标来执行查询
ping()			检查服务器是否处于活动状态。
select_db		设置当前数据库

创建游标对象

1. 语法：	服务器套接字对象.cursor(Cursor, SSCursor, DictCursor, or SSDictCursor)

参数为光标类型

2. 示例

		cursor = conn_obj.cursor(
		      cursor=pymysql.cursors.DictCursor
		      # 括号内不写参数时展示的数据是元组类型，不够精确，可以添加参数使其将数据处理成字典
		)

常见游标方法

close()			立即关闭光标
execute（）		准备并执行数据库操作
executemany（）  针对一个查询运行多个数据
fetchall()		获取查询结果的所有（剩余）行
fetchmany（）	获取查询结果的下一组行，获取的行数由参数指定
fetchone()      获取查询结果集的下一行

执行sql语句

1. 查询操作

		# 编写sql语句	
		sql1 = 'show tables;'  	

		# 执行SQL语句，其中返回值 affect_rows 代表的是受影响的行数
		affect_rows = cursor.execute(sql1)
		print(affect_rows)

		# 获取结果集第一行
		print(cursor.fetchone())
  		print(cursor.fetchone())
  		

2. 增删改操作
由于增、改、删操作都会影响真正的数据其重要程度较高，所以需要使用 commit 进行二次确认，如下示例

'手动确认'
		import pymysql
		
		conn_obj = pymysql.connect(
		    host='127.0.0.1',
		    port=3306,
		    user='root',
		    password='12345',
		    database='d3',
		    charset='utf8'
		)
		
		cursor = conn_obj.cursor(
		    cursor=pymysql.cursors.DictCursor
		)
		
		user_name = input('新名字:   ').strip()
		user_pwd = input('学号:   ').strip()
		
		sql1 = "update student set sname=%s where sid=%s;"
		
		affect_rows = cursor.execute(sql1, (user_name, user_pwd))
		print(affect_rows)
		# 手动保存
		conn_obj.commit()


'自动保存'
		import pymysql
		
		conn_obj = pymysql.connect(
		    host='127.0.0.1',
		    port=3306,
		    user='root',
		    password='12345',
		    database='d3',
		    charset='utf8',
		    # 设置自动保存
		    autocommit=True
		)
		
		cursor = conn_obj.cursor(
		    cursor=pymysql.cursors.DictCursor
		)
		
		user_name = input('新名字:   ').strip()
		user_pwd = input('学号:   ').strip()
		
		sql1 = "update student set sname=%s where sid=%s;"
		
		affect_rows = cursor.execute(sql1, (user_name, user_pwd))
		print(affect_rows)
		
		print(sql1)

光标移动

光标在读取完结果集的内容后在继续读取会读取出空数据，因为光标在最后了，我们可以改变光标的位置

1. 使用的方法

		套接字对象.scroll()

第一个参数是移动的单位
第二个参数是 relative、absolute
relative 表示的是当前位置往后移动
absolute 表示的是相对于起始位置往后移动

2. 示例

		import pymysql
		
		conn_obj = pymysql.connect(
		    host='127.0.0.1',
		    port=3306,
		    user='root',
		    password='12345',
		    database='d3',
		    charset='utf8'
		)
		
		cursor = conn_obj.cursor(
		    cursor=pymysql.cursors.DictCursor
		)
		
		sql1 = 'select * from teacher'
		
		affect_rows = cursor.execute(sql1)
		print(affect_rows)
		
		print(cursor.fetchall())
		# 改变光标位置
		cursor.scroll(2, 'absolute')
		print(cursor.fetchone())

SQL注入问题

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作

示例

		import pymysql
		
		conn_obj = pymysql.connect(
		    host='127.0.0.1',
		    port=3306,
		    user='root',
		    password='xwx668428',
		    database='d3',
		    charset='utf8'
		)
		
		cursor = conn_obj.cursor(
		    cursor=pymysql.cursors.DictCursor
		)
		
		user_name = input('用户名:   ').strip()
		user_pwd = input('密码:   ').strip()
		
		sql1 = "select * from student where sname='%s' and sid='%s';"%(user_name,user_pwd)
		
		affect_rows = cursor.execute(sql1)
		print(affect_rows)
		
		print(sql1)
		res = cursor.fetchall()
		if res:
		    print('登录成功')
		else:
		    print('登陆失败')


在示例中正确输入值是可以登录的，但是有以下俩中形式会出问题
	
		# 只需要提供用户名
		用户名:张三' -- jhahsdjasdjasd		
		密码:直接回车
	
		# 什么都不需要
		用户名:xxx' or 1=1 -- asdjasjdkajsd
		密码:直接回车

上述情况利用的是MySQL注释语法及逻辑运算符

解决方法
其实execute方法自带校验SQL注入问题，自动处理特殊符号，只要将执行语句改成以下所示
		
		sql1 = "select * from student where sname=%s and sid=%s;"
		affect_rows = cursor.execute(sql1, (user_name, user_pwd))


此方法处理还能批量插入数据如下示例

		sql = 'insert into userinfo(name,password) values(%s,%s)'
		cursor.executemany(sql,[('tom',123),('lavin',321),('pony',333)])