pymysql 如何返回字段名称 pymysql详解

1、前言

1.1 安装pymysql

python3 与MySQL 进行交互编程需要安装 pymysql 库

①、使用命令安装pymysql

pip install pymysql

②、pycharm内部安装

 

 

③、导入模块

import pymysql

1.2 pymysql流程及模块说明

①pymysql操作流程

• 导入pymysql；
• 建立数据库连接，使用pymysql的connect()方法连接数据库，返回连接对象；
• 使用连接对象创建游标对象（用于操作sql）；
• 准备写sql语句；
• 使用游标对象执行sql；
• 查询数据使用游标获取；
• 关闭游标（先）和数据库连接（后）。

②pymysql模块说明

¶ connection对象

表示：conn = connect(参数列表)

作用：用于创建与数据库的连接

创建对象：调用connect()方法

参数列表：

• host：连接的mysql主机，如本机是'localhost‘
• port：连接的mysql主机的端口，默认是3306
• database：数据库的名称
• user：连接的用户名
• password：连接的密码
• charset：通信采用的编码方式，推荐使用utf8

对象的方法：

• close()：关闭连接
• commit()：提交
• cursor()：返回cursor对象，用于执行sql语句并获得结果
• execute(operation[,parameters])：执行语句，返回受影响的行数
• fetchone()：执行查询语句，获取查询结果集的第一个行数据，返回一个元组
• fetchall()：执行查询语句，获取结果集的所有行，一行构成一个元组，再将这些元素装入一个元组返回

¶ cursor对象

游标就是游动的标识，通俗的说，一条sql取出对应n条结果资源的接口/句柄，就是游标，沿着游标可以一次取出一行

• 用于执行sql语句，使用频度最高的语句为select、uodate、insert、delete
• 获取cursor对象：调用connection对象的cursor方法：cursor = conn.cursor()

对象的属性：

• rowcount：只读属性，表示最近一次execute()执行后受影响的行数
• connection获得当前连接对象

¶ 语法总结

a. 连接数据库，需要host、port、user、password、database、charset等信息

b. 操作数据库要先创建游标

c. 执行指定的sql语句，如果涉及到增、删、改数据库必须要conn.commit()，提交事务

d. 查询获取数据条数有三种方法fetchone、fetchall、fetchmany

• cursor.fetchone()：默认获取查询结果的第一条数据
• cursor.fetchamany(2)：获取查询结果的指定条数，比如获取2条数据
• cursr.fetchall()：获取查询结果的所有数据

e.需要注意的是，fetch获取的数据默认是元组，如果想要字典类型：cursor = pymysql.cursors.DictCursor;

f. 先关闭游标，后关闭数据库连接

2、连接数据库

import pymysql
# 打开数据库连接
conn = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8')
print(conn)
输出：

3、获取游标

要想操作数据库，光连接数据是不够的，必须拿到操作数据库的游标，才能进行后续的操作，比如读取数据、添加数据。通过获取到的数据库连接实例conn下的cursor()方法来创建游标，游标用来接收返回结果。

import pymysql
# 打开数据库连接
conn = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8')
# 获取游标
cursor = conn.cursor()

说明：cursor返回的是一个游标实例对象，其中包含了很多操作数据库的方法，比如执行sql语句

python查询数据库返回类型默认是元组，使用cursorclass属性可以以字典形式返回操作结果

conn = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor)

4、执行sql语句

pymysql提供了两个执行语句的方法：一个是execute()，一个是executemany()

execute(sql)：

可接受一条语句从而执行

executemany(templet,args)：

能通知执行多条语句，执行同样多的语句可比execute()快很多，建议执行多条数据时使用executemany

templet：sql模板字符串。例如”insert into table(id,name) values(%s,%s)'

args：模板字符串的参数，是一个列表，列表中的每一个元素必须是元组，例如：[(1,'小明'),(2,'zeke'),(3,'琪琪'),(4,'香香')]

cursor.execute('insert into teacher(name,age,gender) values(%s,%s,%s)',('铁柱',25,'women'))    #插入单条多个数据
cursor.executemany('insert into teacher(name,age,gender) values(%s,%s,%s)',[('铁柱',25,'women'),('钢蛋',21,'women')]    #插入多条多个数据

5、创建表

import pymysql

# 打开数据库连接
db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor)
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 如果数据库已经存在使用execute()方法删除表
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 创建数据库表SQL语句
sql = """CREATE TABLE EMPLOYEE(
        FIRST_NAME CHAR(20) NOT NULL,
        LAST_NAME CHAR(20),
        AGE INT,
        SEX CHAR(1),
        SALARY FLOAT)"""
cursor.execute(sql)
cursor.close()  # 关闭游标
db.close()    # 关闭数据库连接

6、数据库插入操作

插入单条记录，示例如下：

import pymysql

# 打开数据库连接
db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor)
# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,SALARY)
         VALUES('Mac','Mohan', 20, 'M', 2500) """
try:
    cursor.execute(sql)   # 执行sql语句
    db.commit()   # 提交到数据库执行
except:
    db.rollback()  # 遇到错误时回滚
cursor.close()  # 关闭游标
db.close()    # 关闭数据库连接

插入多条记录，示例如下：

import pymysql

# 打开数据库连接
db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor)
# 使用cursor()方法获取操作游标
cursor = db.cursor()

# SQL插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,SALARY)
         VALUES(%s, %s, %s, %s, %s) """
try:
    cursor.executemany(sql, [('Mac','Mohan', 20, 'M', 2500),('hanhan','li', 24, 'F', 3500)])   # 执行sql语句
    db.commit()   # 提交到数据库执行
except:
    db.rollback()  # 遇到错误时回滚
cursor.close()  # 关闭游标
db.close()    # 关闭数据库连接

7、查询操作

python查询mysql使用fetchone()方法获取单条数据，使用fetchall()方法获取多条数据

• fetchone()：获取单条数据
• fetchall()：获取多条数据
• fetchmany()：获取指定条数，例如fetchmany(2)
• rowcount：只读属性，返回执行execute()方法后影响的行数

示例：查询EMPLOYEE表中salary（工资）字段大于1000的所有数据：

import pymysql

# 打开数据库连接
db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor)
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL查询语句
sql = "SELECT * FROM EMPLOYEE WHERE SALARY > 1000"
try:
    cursor.execute(sql)  # 执行SQL语句
    result = cursor.fetchall()  # 获取所有记录
except:
    print("Error:unable to fetch data")
cursor.close()  # 关闭游标
db.close()    # 关闭数据库连接

8、fetchone与fetchall的区别

fetchone：

不管查询结果是多条数据还是单条数据，使用fetchone得到的始终是一个元组。

如果查询结果是单条数据：fetchone得到的单条数据的元组；

如果查询结果是多条数据：fetchone默认是结果中的第一条数据构成的元组。

fetchall:

不管查询结果是多条数据还是单条数据，使用fetchall得到的始终是一个由元组组成的列表（不懂为什么往上教程都说是元组的列表，实际操作得到的是一条数据构成一个元组，然后这些元组再装入一个元组）。

如果查询结果是单条数据：fetchall得到的是由单个元组组成的列表，列表内是有单条数据组成的元组;

如果查询结果是多条数据：fetchall得到的是由多个元组组成的列表。

使用场景：

一般来说，查询结果集是单条数据的，使用fetchone获取数据

一般来说，查询结果集是多条数据的，使用fetchall获取数据

注意：

对于使用fetchone和fetchall获取到的结果，最好使用之前先判断非空，否则在存在空值的情况下获取元组内的数据时，会报“超出索引”的异常。

9、更新操作

示例：更新单条数据

import pymysql

# 打开数据库连接
db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor)
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL更新语句
sql = "UPDATE EMPLOYEE SET SALARY = 2000 WHERE NAME = 'JACK'"
try:
    cursor.execute(sql)  # 执行SQL语句
    db.commit()    # 提交到数据库执行
except:
    db.rollback()  # 发生错误时回滚
cursor.close()  # 关闭游标
db.close()    # 关闭数据库连接

示例：更新多条数据

import pymysql

# 打开数据库连接
db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor)
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL更新语句
sql = "UPDATE EMPLOYEE SET SALARY = '%s' WHERE NAME = '%s'"
try:
    cursor.executemany(sql, [(3000,'Amy'),(1500,'Jone')])  # 执行SQL语句
    db.commit()    # 提交到数据库执行
except:
    db.rollback()  # 发生错误时回滚
cursor.close()  # 关闭游标
db.close()    # 关闭数据库连接

10、删除操作

示例：删除一条数据

import pymysql

# 打开数据库连接
db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor)
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL删除语句
sql = "DELETE FROM EMPLOYEE WHERE NAME = 'Jack'"
try:
    cursor.execute(sql)  # 执行SQL语句
    db.commit()    # 提交到数据库执行
except:
    db.rollback()  # 发生错误时回滚
cursor.close()  # 关闭游标
db.close()    # 关闭数据库连接

示例：删除多条数据

import pymysql

# 打开数据库连接
db = pymysql.connect(host='', port='', user='', passwd='', db='', charset='utf8', cursorclass=pymysql.cursors.DictCursor)
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# SQL删除
sql = "DELETE FROM EMPLOYEE WHERE NAME = '%s'"
try:
    cursor.executemany(sql, [('Jack'),('Tom')])  # 执行SQL语句
    db.commit()    # 提交到数据库执行
except:
    db.rollback()  # 发生错误时回滚
cursor.close()  # 关闭游标
db.close()    # 关闭数据库连接

11、封装数据库类

在实际项目中，很多地方都有用到数据库的操作，所以需要将数据库相关操作进行封装，方便其他模块调用。

如下，在public目录下新建文件db.py，用于封装数据库操作

 

 简单示例代码

class Database:
    def __init__(self, host, port, user, password,
                 database, charset):
        # 连接数据库服务器
        self.conn = pymysql.connect(host=host, port=port, user=user, password=password,
                                    database=database, cursorclass=pymysql.cursors.DictCursor,
                                    charset=charset)
        # 获取游标
        self.cursor = self.conn.cursor()

    def query(self, sql, args=None, one=True):
        self.cursor.execute(sql, args)
        # 提交事务
        self.conn.commit()
        if one:
            return self.cursor.fetchone()
        else:
            return self.cursor.fetchall()

    def close(self):
        self.cursor.close()
        self.conn.close()


if __name__ == "__main__":
    db = Database(host='', port=, user='', password='', database='', charset='utf8')
    sql = ''
    result = db.query(sql)

12、使用pymysql的executemany方法时，需要注意的几个问题

1、在写sql语句时，不管字段为什么类型，占位符统一使用%s,且不能加上引号。例如：

sql = "insert into table (id, name) values(%s, %s)"

2、添加的数据格式必须为list[tuple(), tuple(), tuple()]或者tuple(tuple(), tuple(), tuple())，例如：

values = [(1, "zhangsan"), (2, 'lisi")]
或者
values = ((1, "zhangsan"), (2, 'lisi"))