1. PyMySQL介绍与安装
-
介绍：PyMySQL是在Python3.x版本中用于连接MySQL服务器的一个第三方库(Django中也可以使用PyMySQL连接MySQL数据库)
-
安装：
-
pip安装：在命令行执行pip install PyMySQL
-
pycharm安装：Settings -> Project -> Project Interpreter
-
-
注意：
-
有一个能连接的MySQL服务，此服务处于启动状态，使用正确的add和port可以连接
-
有一个被授权的帐号和密码可以登录的
-
登录的帐号有权限操作数据库
2. PyMySQL的参数与方法
-
pymysql.connect()参数：
-
host：MySQL服务器地址
-
port：MySQL服务器的端口(指定端口时使用int类型)
-
user：用户名
-
password：密码
-
database或db：数据库名
-
charset：MySQL数据库编码(指定utf8编码时，中间一定不能加'-')
-
connection对象的方法：
-
cursor()：创建光标对象，cursor(pymysql.cursors.DictCursor)表示创建光标对象时把数据转为字典格式
-
commit()：提交当前事务
-
rollback()：回滚当前事务
-
close()：关闭连接
-
cursor对象的方法：
-
execute()：执行SQL语句，返回当前受影响的行数
-
fetchone()：获取当前结果集的第一条数据
-
fetchmany(N)：获取结果集的指定N条数据
-
fetchall()：获取结果集的所有数据，以元组的形式返回
-
lastrowid()：新增数据时获取数据的主键ID
-
-
close()：关闭光标对象
3. PyMySQL连接数据库
-
1) 导入pymysql模块
-
2) 连接数据库，创建连接对象
-
3) 创建光标对象
-
4) 定义sql语句
-
5) 执行sql语句(接受返回值并输出)
-
6) 关闭光标对象
-
7) 关闭连接对象
# 导入pymysql模块
import pymysql
# 连接数据库，创建连接对象
conn = pymysql.connect(host='localhost', port=3306, user='root', password='root_pwd', database='database', charset='utf8')
# 创建光标对象
cursor = conn.curson()
# 定义sql语句
sql = 'show databases;'
# 执行sql语句
res = cursor.execute(sql)
print(ret)
# 关闭光标对象
cursor.close()
# 关闭连接对象
conn.close()
4. PyMySQL防止SQL注入
-
SQL注入简介：SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编程时的疏忽，通过SQL语句，实现无帐号登录，甚至篡改数据库
-
SQL注入攻击的总体思路：
-
1) 寻找到SQL注入的位置
-
2) 判断服务器类型和后台数据库类型
-
3) 针对不通的服务器和数据库特点进行SQL注入攻击
-
SQL注入产生的原因：程序中sql语句由字符串拼接查询，并没有做特殊的处理
-
SQL注入的解决方法：
-
使用pymysql提供的参数化语句防止注入(execute()函数本身就有接受SQL语句变量的参数位，只要正确的使用就可以对传入的值进行correctly转义，从而避免SQL注入的发生，execute()函数内部执行参数化生成的SQL语句，对特殊字符进行了加\转义，避免注入语句生成。execute()函数第一个参数接收需要执行的sql语句，第二个参数就是传入的参数值，如果参数值有多个，可以使用元组或列表的方式传入)
-
使用mysql存储过程动态执行SQL防注入(数据库的存储过程已经逐渐的不再使用)
-
总结：使用PyMySQL执行sql语句时需要传入参数时，都使用execute()传参的方式来执行
5. PyMySQL的增删改查
准备sql信息
-- MySQL服务器地址：localhost
-- MySQL服务器端口：3306
-- 授权用户名：yy
-- 授权用户名的密码：123
--
-- 创建数据库
create database day60 default charset='utf-8';
use day60;
-- 创建表结构
create table user (
`id` int not null auto_increment primary key,
`username` char(50) not null,
`password` char(50) not null,
`age` int
)engine=innodb default charset=utf8;
-- 创建数据
insert into user values
(null, 'aaa', '123456', '18'),
(null, 'bbb', '123456', '20'),
(null, 'ccc', '123456', '22'),
(null, 'ddd', '123456', '25'),
(null, 'eee', '123456', '28');
增
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='yy', password='123', db='day60', charset='utf8')
cursor = conn.cursor()
sql = 'insert into user values (null, %s, %s, %s);'
# 添加单条数据
res1 = cursor.execute(sql, ('yy', '123', 18))
print(res1)
# 添加多条数据
data = [
('yange1', 123, 21),
('yange2', 456, 22),
('yange3', 789, 23),
]
res2 = cursor.executemany(sql, data)
print(res2)
# 循环添加数据
username = 'yan'
password = 'yan_pwd'
res_sum = 0
for i in range(10):
res = cursor.execute(sql, ('{0}{1}'.format(username, i), '{0}{1}'.format(password, i), i + 10))
res_sum += res
print(res_sum)
# 获取新插入数据的主键ID
cursor.execute(sql, ('ididid', '111', 11))
new_id = cursor.lastrowid
print(new_id)
# 在此数据库中无法查询到添加的数据
conn.commit()
# 此时可以在数据库中查询到添加的数据
cursor.close()
conn.close()
删
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='yy', password='123', db='day60', charset='utf8')
cursor = conn.cursor()
sql = 'delete from user where username like %s;'
res = cursor.execute(sql, ('yange_',))
print(res)
conn.commit()
cursor.close()
conn.close()
改
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='yy', password='123', db='day60', charset='utf8')
cursor= conn.cursor()
sql = 'update user set password = %s where username like %s;'
res = cursor.execute(sql, ('1234567890', 'yan%'))
print(res)
conn.commit()
cursor.close()
conn.close()
查
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='yy', password='123', db='day60', charset='utf8')
cursor = conn.cursor()
sql = 'select * from user;'
cursor.execute(sql)
# 查看单条数据，所有字段数据存储在元组中返回(取一条数据，光标会往后移动一次)
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
# 查看多条数据，一条数据的所有字段数据存储在小元组中，多条数据存储在外层的大元组中返回(同时取多条数据，光标会根据数据的条数往后移动)
print(cursor.fetchmany(5))
# 循环查看数据
for i in range(10):
print(cursor.fetchone())
# 查看所有数据，所有字段使用元组保存，所有数据使用元组嵌套的方式返回，(把剩余的数据都取完，光标移动到最后)
print(cursor.fetchall())
# 数据已经被取完，当前已经没有了数据，仍查看单条数据返回None
print(cursor.fetchone())
# 数据已经被取完，当前已经没有了数据，仍查看多条数据返回空元组
print(cursor.fetchmany())
6. PyMySQL的提交与回滚
-
commit()：默认情况下pymysql执行的增、删、改的操作是不会直接写进数据库的，会先把修改添加到事务中，直到执行commit()方法，才会从事务中写到数据库
-
rollback()：在没有执行commit()方法之前，增、删、改操作的数据是保存在事务中，如果此期间有错误的操作或者需要再次进行操作的数据可以使用rollback()方法来回滚事务
-
注意：只有数据的增(insert)、删(select)、改(update)的操作才会被写进事务，查询操作(select)是不会被事务记录的
-
总结：为了保证代码的完整性与安全性，可以使用异常处理的方式来实现
-
以上增删改的操作可以做一下优化与调整：
# 增
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='yy', password='123', db='day60', charset='utf8')
cursor = conn.cursor()
sql = 'insert into user values (null, %s, %s, %s);'
try:
res1 = cursor.execute(sql, ('yy', '123', 18))
print(res1)
conn.commit()
except Exception as e:
conn.rollback()
try:
data = [
('yange1', 123, 21),
('yange2', 456, 22),
('yange3', 789, 23),
]
res2 = cursor.executemany(sql, data)
print(res2)
conn.commit()
except Exception as e:
conn.rollback()
try:
username = 'yan'
password = 'yan_pwd'
res_sum = 0
for i in range(10):
res = cursor.execute(sql, ('{0}{1}'.format(username, i), '{0}{1}'.format(password, i), i + 10))
res_sum += res
print(res_sum)
except Exception as e:
conn.rollback()
try:
cursor.execute(sql, ('ididid', '111', 11))
new_id = cursor.lastrowid
print(new_id)
conn.commit()
except Exception as e:
conn.rollback()
cursor.close()
conn.close()
#
# 删
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='yy', password='123', db='day60', charset='utf8')
cursor = conn.cursor()
sql = 'delete from user where username like %s;'
try:
res = cursor.execute(sql, ('yange_',))
print(res)
conn.commit()
except Exception as e:
conn.rollback()
cursor.close()
conn.close()
#
# 改
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='yy', password='123', db='day60', charset='utf8')
cursor= conn.cursor()
sql = 'update user set password = %s where username like %s;'
try:
res = cursor.execute(sql, ('1234567890', 'yan%'))
print(res)
conn.commit()
except Exception as e:
conn.rollback()
cursor.close()
conn.close()
7. PyMySQL的其他方法应用
import pymysql
conn = pymysql.connect(host='localhost', port=3306, user='yy', password='123', db='day60', charset='utf8')
cursor = conn.cursor()
sql = 'select * from user;'
cursor.execute(sql)
print(cursor.fetchone())
cursor.scroll(3, mode='relative')
# 把光标按照相对位置往后移动3行(scroll默认模式是relative，可以不用写)
print(cursor.fetchone())
cursor.scroll(-2)
# 把光标按照相对位置往前移动2行
print(cursor.fetchone())
cursor.scroll(1, mode='absolute')
# 把光标按照绝对位置往后移动1行
print(cursor.fetchone())
浙公网安备 33010602011771号