python开发【三十三篇】python操作mysql——pymsql

Python操作MySQL主要使用两种方式：

• 原生模块 pymsql
• ORM框架 SQLAchemy

本章主要介绍pymsql。orm框架将在后面单独讲解

pymsql

pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。

下载安装

1	pip3 install pymysql

使用操作

1、执行SQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql    # 创建连接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') # 创建游标 cursor = conn.cursor()    # 执行SQL，并返回收影响行数 effect_row = cursor.execute("update hosts set host = '1.1.1.2'")    # 执行SQL，并返回受影响行数 #effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (1,))    # 执行SQL，并返回受影响行数 #effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)])       # 提交，不然无法保存新建或者修改的数据 conn.commit()    # 关闭游标 cursor.close() # 关闭连接 conn.close()

2.何为游标

 游标是SQL的一个内存工作区，由系统或用户以变量形式定义。
 游标的作用是用于临时存储从数据库中提取的数据块。通俗理解就是将受影响的数据暂时放到了一个内存区域的虚表中，而这个虚表就是游标。
 为什么要用游标？

• 数据库的数据是存放在磁盘中的，游标是把数据从磁盘中调到计算机内存中进行处理，最后将处理结果显示出来或者最终写回数据库，这样可以提高数据处理的效率，因为频繁的磁盘数据交换会降低效率。
• 大家都知道数据库中的事物可以回滚，而游标在其中起着非常重要的作用，由于对数据库的操作我们会暂时放在游标中，只要不提交，我们就可以根据游标中内容进行回滚，在一定意义有利于数据库的安全。
• 另外，在Oracle中PL/SQL只能返回单行数据，而游标弥补了这个不足。相当于ADO.NET中的Data table吧。

游标有两种类型：隐式游标和显示游标。

• 隐式游标：对于SELECT…INTO…语句（一次只能从数据库中提取一行记录）和DML（数据操纵语言，也就是INSERT语句、UPDATE语句和DELETE语句），系统都会采用隐式游标。
• 显示游标：由开发人员通过程序显式控制,用于从表中取出多行数据,并将多行数据一行一行的单独进行处理.

游标简单认识了，我们来看一下简单的使用游标四步骤：

步骤	关键词	说明
1	在DECLARE中cursor	声明游标，创建一个命名的查询语句
2	Open	打开游标
3	Fetch	取出游标中的一条记录装入变量
4	Close	释放游标

3、获取新创建数据自增ID

1 2 3 4 5 6 7 8 9 10 11 12 13

#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') cursor = conn.cursor() cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)]) conn.commit() cursor.close() conn.close()    # 获取最新自增ID new_id = cursor.lastrowid

4、获取查询数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') cursor = conn.cursor() cursor.execute("select * from hosts")    # 获取第一行数据 row_1 = cursor.fetchone()    # 获取前n行数据 # row_2 = cursor.fetchmany(3) # 获取所有数据 # row_3 = cursor.fetchall()    conn.commit() cursor.close() conn.close()

注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：

• cursor.scroll(1,mode='relative')  # 相对当前位置移动
• cursor.scroll(2,mode='absolute') # 相对绝对位置移动

5、fetch数据类型

　　关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

　　关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')    # 游标设置为字典类型 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) r = cursor.execute("call p1()")    result = cursor.fetchone()    conn.commit() cursor.close() conn.close()