pymysql

Python - pymysql

 

目录

• About pymysql
• Install
• 准备
  • 建立连接
  • 创建数据库
• 快速上手之增删改查
  • 增
  • 删
  • 改
  • 查
• SQL注入
• 事物
• 存储过程
• 批量录入数据

• 返回MySQL目录
• 返回Python目录
• 返回测试目录

About pymysql#

在Python2.x中，Python连接MySQL服务器使用mysqldb库，但是它只支持到Python2.x，在Python3.x中由pymysql模块代替。

PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。

Install#

pip install pymysql
pip install -i https://pypi.doubanio.com/simple pymysql==1.0.2

准备#

在正式操作前，这里默认你有了一个良好的环境，包括MySQL服务，Python环境。

建立连接#

import pymysql
conn = pymysql.connect(
    host='localhost',   # 连接的服务器ip
    user='username',   # 用户名
    password='password',  # 密码
    database='day31',   # 你想连接的数据库
    charset='utf8'   # 指定字符编码，不要加杠，如：utf-8
)
cursor = conn.cursor()  # 获取游标

# 一顿操作后......别忘了
cursor.close()   # 关闭游标
conn.close()    # 关闭连接对象

创建数据库#

首先，我们要手动在MySQL中创建表：

create table info(
	id int primary key auto_increment,
	user varchar(10) not null,
    pwd varchar(10)
);

快速上手之增删改查#

增#

法1

cursor = conn.cursor()  # 获取游标

cursor.execute('insert into info (user,pwd) values ("张开腿", "zhangkai1");')

conn.commit()
cursor.close()   # 关闭游标
conn.close()    # 关闭连接对象

相当于我们写原生的SQL语句。
法2

cursor = conn.cursor()  # 获取游标

sql = 'insert into info (user,pwd) values ("%s", "%s");' % ('张开2', 'zhangkai2')
cursor.execute(sql)

conn.commit()
cursor.close()   # 关闭游标
conn.close()    # 关闭连接对象

我们把sql语句提出来，用Python处理一下，相当于拼接字符串。
法3

cursor = conn.cursor()  # 获取游标

sql = 'insert into info (user,pwd) values (%s, %s);'
cursor.execute(sql, ('张开3', 'zhangkai3'))  # 第二个参数可以是元组也可以是列表

conn.commit()
cursor.close()   # 关闭游标
conn.close()    # 关闭连接对象

我们将值放到元组（或列表）中，交给execute帮我们处理，其实，execute本质上也是拼接字符串，然后再执行。

注意，在sql语句中，%s那里不要使用"%s"这种形式，因为MySQL会把引号当成普通的数据写入到数据库中。不信来看：

cursor = conn.cursor()  # 获取游标

sql = 'insert into info (user,pwd) values ("%s", "%s");'
cursor.execute(sql, ['张开4', 'zhangkai4'])  # 这里我们使用列表进行验证

conn.commit()
cursor.close()   # 关闭游标
conn.close()    # 关闭连接对象

来看输出结果：

mysql> select * from info where id = 4;
+----+-----------+------------+
| id | user      | pwd        |
+----+-----------+------------+
|  4 | '张开4'   | 'zhangkai4 |
+----+-----------+------------+

很明显，带着引号的不是我们想要的结果。

法4

不能总是一条一条插入，那岂不是费老劲了啊，能不能一次插入多条呢？答案是可以的：

cursor = conn.cursor()  # 获取游标

info_list = [('张开{}'.format(i), 'zhangkai{}'.format(i)) for i in range(5, 101)]

sql = 'insert into info (user,pwd) values (%s, %s);'
cursor.executemany(sql, info_list)

conn.commit()
cursor.close()   # 关闭游标
conn.close()    # 关闭连接对象

生成器可能是更优的选择：

cursor = conn.cursor()  # 获取游标

info_list = (('张开{}'.format(i), 'zhangkai{}'.format(i)) for i in range(101, 201))

sql = 'insert into info (user,pwd) values (%s, %s);'
cursor.executemany(sql, info_list)

conn.commit()
cursor.close()   # 关闭游标
conn.close()    # 关闭连接对象

删#

删改查没啥好说，都是写SQL语句就完了。

cursor = conn.cursor()

sql = 'delete from info where user = %s;'
cursor.execute(sql, '张开200')
conn.commit()
cursor.close()
conn.close()

改#

cursor = conn.cursor()

sql = 'update info set pwd = %s where user = %s'
cursor.execute(sql, ('张开一九九', '张开199'))

conn.commit()
cursor.close()
conn.close()

查#

cursor = conn.cursor()  # 获取游标

sql = 'select id, user, pwd from info;'
rows = cursor.execute(sql)
print(rows) 

conn.commit()
cursor.close()   # 关闭游标
conn.close()    # 关闭连接对象

直接打印返回值rows，得到的是所有记录的条数。

想要得到记录内容可以使用fetch系列：

cursor = conn.cursor()  # 获取游标

sql = 'select id, user, pwd from info;'
cursor.execute(sql)
print(cursor.fetchone())
print(cursor.fetchone())

conn.commit()
cursor.close()   # 关闭游标
conn.close()    # 关闭连接对象

fetchone一条一条取。

cursor = conn.cursor()  # 获取游标

sql = 'select id, user, pwd from info;'
cursor.execute(sql)
# print(cursor.fetchmany())   # 默认取第一条
print(cursor.fetchmany(3))  # 默认从开始取指定条数

conn.commit()
cursor.close()   # 关闭游标
conn.close()    # 关闭连接对象

fetchmany默认从开始取指定条数。

cursor = conn.cursor()  # 获取游标

sql = 'select id, user, pwd from info;'
cursor.execute(sql)
print(cursor.fetchall())

conn.commit()
cursor.close()   # 关闭游标
conn.close()    # 关闭连接对象

fetchall取所有。

pymysql.cursors.DictCursor

如果你每次看打印结果的话，结果都是以元组套元组的形式返回。

cursor = conn.cursor()
cursor.execute('select * from info;')
print(cursor.fetchmany(2))  # ((1, '张开腿', 'zhangkai1'), (2, '张开2', 'zhangkai2'))

我们也可以控制返回形式，比如以列表套字典的形式：

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 需要在实例化游标对象的时候，传个参数
cursor.execute('select * from info;')
print(cursor.fetchmany(2))  # [{'id': 1, 'user': '张开腿', 'pwd': 'zhangkai1'}, {'id': 2, 'user': '张开2', 'pwd': 'zhangkai2'}]
conn.commit()
cursor.close()
conn.close()

scroll

先来看相对定位，根据当前的游标位置移动。

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

cursor.execute('select * from info;')
print(cursor.fetchone())  # 此时游标在第一行
cursor.scroll(1, 'relative')  # 光标按照相对位置移动一位，此时在2
print(cursor.fetchone())  # 取第3行记录

conn.commit()
cursor.close()
conn.close()

接下来看绝对定位：

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

cursor.execute('select * from info;')
print(cursor.fetchone())  # 此时游标在第一行
cursor.scroll(1, 'absolute')  # 光标按照绝对位置移动一位，此时在1
print(cursor.fetchone())  # 取第2行记录

conn.commit()
cursor.close()
conn.close()

SQL注入#

什么是SQL注入呢？先来看个示例：

import pymysql

def connection(user=None, password=None, database=None, host='localhost', charset='utf8'):
    """ 建立连接并将游标返回 """
    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        database=database,
        charset=charset
    )
    return conn.cursor()  


def login():
    user = input('username: ').strip()
    pwd = input('password: ').strip()
    sql = 'select user, pwd from info where user= "%s" and pwd = "%s";' % (user, pwd)
    print(sql)
    result = cursor.execute(sql)  # 查询结果自带布尔值，查询成功返回1，查询失败返回0
    if result:
        print('login successful')
    else:
        print('login error')


if __name__ == '__main__':
    cursor = connection(user='root', password='root!admin', database='day31')
    login()
    conn.commit()
    cursor.close()
    conn.close()

在上述的登录示例中了，我们输入正确的用户名和密码肯定都没问题：

username: 张开2
password: zhangkai2
select user, pwd from info where user="张开2" and pwd = "zhangkai2";
login successful

但是，如果有人在输入用户名和密码时，做了手脚：

username: 张开2"; -- aswesasa
password: 
select user, pwd from info where user="张开2"; -- aswesasa" and pwd = "";
login successful

可以看到，再输入用户名的时候，在用户名后面跟了"; -- aswesasa这些东西，再看打印的SQL语句，不难发现。判断语句现在变成了，只要是用户名对了就算成功。后面的密码部分，被--注释掉了，你写啥都无所谓了。

这就是SQL注入的方式之一。另外一种情况，就是用户名和密码都有问题，也能登录成功：

username: xxx" or 1 -- xxx
password: 
select user, pwd from info where user="xxx" or 1 -- xxx" and pwd = "";
login successful

当用户名错误和密码错误时，依然登录成功。什么原因呢？由打印的SQL语句可以看到，用户名错了不要紧，我们使用or语句，后跟一个真值，这样用户名无论如何都会成立。当然，后面的密码部分已经被注释掉了。

以上就是SQL注入的两种方式，那么怎么解决呢？解决办法，就是我们不手动的拼接SQL字符串，而是交给pymysql来完成：

def login():
    user = input('username: ').strip()
    pwd = input('password: ').strip()
    sql = 'select user, pwd from info where user = %s and pwd = %s;' # 注意，%s这里要去掉引号，因为pymysql会帮我们加上的
    result = cursor.execute(sql, (user, pwd))
    if result:
        print('login successful')
    else:
        print('login error')

上述代码修改后，无论我们输入什么，pymysql都会把输入内容拼成普通的字符串，然后校验。所以，以后碰到这种拼接SQL语句的事情，都交给pymysql来做，而不是我们手动的拼接。

事物#

也就是回滚机制，将一连串的执行当成一个原子性的操作，要么全部执行成功，要么全部执行失败。

我们演示执行两条命令，一条执行成功，一条执行失败，执行失败的话，就回滚到之前最开始的状态。

先来看正常的：

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql1 = 'insert into info(user, pwd) values(%s, %s);'
sql2 = 'insert into info(user, pwd) values(%s, %s);'

try:
    cursor.execute(sql1, (("小王", "123")))
    cursor.execute(sql2, (("小李", "123")))
except Exception as e:
    print(e)
    conn.rollback()

cursor.execute('select * from info where user like %s;', '小%')
print(cursor.fetchall())  # [{'id': 210, 'user': '小王', 'pwd': '123'}, {'id': 211, 'user': '小李', 'pwd': '123'}]

conn.commit()
cursor.close()
conn.close()

如果try语句中的两个SQL语句都执行成功，则最终执行成功。

我们把刚才插入的删掉再测试。

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql1 = 'insert into info(user, pwd) values(%s, %s);'
sql2 = 'insert into info(user, pwd) values(%s, %s);'

cursor.execute('delete from info where user like "小_"')
conn.commit()
try:
    cursor.execute(sql1, (("小王", "123")))
    cursor.execute(sql2, (("小李", "123")))
    raise 1
except Exception as e:
    print(e)  # exceptions must derive from BaseException
    conn.rollback()

cursor.execute('select * from info where user like %s;', '小%')
print(cursor.fetchall())  # ()

conn.commit()
cursor.close()
conn.close()

可以看到，在一串的执行中，遇到了错误，就回滚到之前的状态。

存储过程#

接下来来看在pymysql中使用存储过程。

创建存储过程

delimiter\
CREATE PROCEDURE t1 ()
BEGIN
    SELECT * FROM t1;
END\
delimiter ;

使用存储过程

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

cursor.callproc('t1')
conn.commit()
print(cursor.fetchall())
cursor.close()
conn.close()

批量录入数据#

pymysql提供executemany方法用于批量插入。
基本用法：

# data必须是列表套列表，或者列表套元组的形式
data = [(1, 2, 3), (1, 2, 3), (1, 2, 3)]
cursor.execute(sql, data)

上示例：

import time
import faker
import pymysql
from pymysql.connections import CLIENT

fk = faker.Faker(locale='zh_CN')

conn = pymysql.Connect(
    host='10.0.0.200', user='root', password='123',
    database='school', charset='utf8', client_flag=CLIENT.MULTI_STATEMENTS)
cursor = conn.cursor()


def timmer(func):
    def wrapper(*args, **kwargs):
        start = time.time()
        res = func(*args, **kwargs)
        print('{} running: {}'.format(func.__name__, time.time() - start))
        return res

    return wrapper


def create_table():
    """ 创建表 """
    sql = """
    DROP DATABASE IF EXISTS temp_db;
    CREATE DATABASE temp_db CHARSET utf8;
    USE temp_db;
    DROP TABLE IF EXISTS temp_tb;
    CREATE TABLE temp_tb1(
      id int not null primary key auto_increment,
      name varchar(32) not null default "张开",
      addr varchar(128) not null default "",
      phone varchar(32) not null,
      email varchar(64) not null 
    ) ENGINE=INNODB CHARSET=utf8;
    CREATE TABLE temp_tb2 LIKE temp_tb1;
    """
    # 注意，一次性执行多行sql，必须在连接时，指定client_flag=CLIENT.MULTI_STATEMENTS
    cursor.execute(sql)
    conn.commit()


@timmer
def insert_one(num):
    """ 每次插入一条数据 """
    for i in range(1, num + 1):
        id, name, addr, phone, email = i, fk.name(), fk.address(), fk.phone_number(), fk.email()
        sql = "insert into temp_tb1(id, name, addr, phone, email) values(%s, %s, %s, %s, %s);"
        cursor.execute(sql, (id, name, addr, phone, email))
        conn.commit()


@timmer
def insert_many(num):
    """ 批量插入 """
    gen = ((i, fk.name(), fk.address(), fk.phone_number(), fk.email()) for i in range(1, num + 1))
    # print(gen)  # <generator object insert_many.<locals>.<genexpr> at 0x000001A68843C360>
    sql = "insert into temp_tb2(id, name, addr, phone, email) values(%s, %s, %s, %s, %s);"
    cursor.executemany(sql, gen)
    conn.commit()


if __name__ == '__main__':
    num = 10000
    create_table()
    insert_one(num)  # insert_one running: 12.135478019714355
    insert_many(num)  # insert_many running: 2.909210443496704

    cursor.close()
    conn.close()

由测试结果发现，批量插入的性能还是很高的。

---

欢迎斧正，that's all