pymysql Python与数据库的连接

简介

pymysql提供了Python与MySQL数据库进行交互的API，我们可以使用pymysql对数据库进行读写操作

安装

pip install pymysql

使用

连接数据库

使用pymysql.connect()函数建立与数据库的连接：

import pymysql

db = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    database='dbname',
    charset='utf8mb4' # 可选，
    cursorclass=pymysql.cursors.DictCursor # 可选
)

• host：连接的数据库地址
• user：数据库用户名
• password：数据库用户名的密码
• database：要使用的数据库
• charset：设置字符集（编码方式），如果不设置charset，pymysql会使用默认的字符集，这通常是服务器的默认字符集。为了确保数据的正确编码和解码，特别是处理非ASCII字符（如中文）时，建议显示设置charset参数。常用的字符集是utf8mb4，因为它完全兼容UTF-8并且支持存储4字节的unicode字符
• cursorclass：指定使用哪种游标来执行查询和获取结果。不同的游标提供不同的功能和使用方式。如果不设置cursorclass，pymysql会使用默认的游标类，这通常是pymysql.cursors.Cursor
  • pymysql.cursors.Cursor：默认的游标，返回元组格式的结果
  • pymysql.cursors.DictCursor：返回字典格式的结果，其中列名作为键
  • pymysql.cursors.SSCursor：服务器端游标，用于处理大量数据
  • pymysql.cursors.SSDictCursors：服务器端游标，返回字典格式的结果

创建游标对象

import pymysql

db = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    database='test'
)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

也可以使用with语句创建游标，with语句确保在代码块执行完成之后，无论是否发生异常，游标对象都能被正确关闭。这样可以避免资源泄露，防止数据库连接出现悬挂状态：

with db.cursor() as cursor:
    pass

执行SQL语句

使用execute()方法执行SQL语句：

import pymysql

db = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    database='test'
)

cursor = db.cursor()

# 使用 execute() 方法执行SQL语句
cursor.execute("SELECT VERSION()")

修改数据库

当我们在数据库中执行了修改操作（INSERT、UPDATE、DELETE等）时，这些操作默认情况下不会立即写入数据库，而是被存储在内存中的事务缓冲区。调用commit()方法会将这些修改永久性地写入数据库

在创建数据库表、删除数据库表、进行数据库查询时，无需使用commit()

import pymysql

db = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    database='test'
)

try:
    with db.cursor() as cursor:
        # 执行SQL修改操作
        cursor.execute('INSERT INTO some_table (column1, column2) VALUES (1, 2)')
        
        # 可以执行多个修改操作
        cursor.execute('UPDATE some_table SET column1 = 1 where column2 = 2')
    # 提交事务
    db.commit()
except Exception as e:
    # 如果发生异常，回滚事务
    db.rollback()
    print(f'An error occurred: {e}')
    
finally:
    # 关闭数据库连接
    db.close()

当事务提交失败时，可以使用rollback()来撤销操作，回滚事务。

一些数据库可能会设置自动提交模式，即每执行一条修改语句后自动调用commit()。在pymysql中，默认情况下自动提交是关闭的，我们需要显示的调用commit()

当我们想确保一系列操作要么全部成功，要么全部失败时，才需要使用事务。在这种情况下，我们会在所有操作完成后调用commit()。如果其中任何操作失败，我们应该调用rollback()来撤销所有操作

频繁地提交事务可能会影响性能，因此我们应该合理的组织事务，只在必要的时候提交

数据库查询

在使用execute()执行查询语句后，我们需要获取查询到的数据，可以使用以下几种方法：

fetchone()

从查询结果中返回第一条记录，通常返回一个元组，其中包含了一条记录的所有字段值。调用fetchone()后，游标会移动到结果的下一行，也就是说，每次调用fetchone()，得到的都是不同的结果

import pymysql

db = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    database='test'
)

with db.cursor() as cursor:
    cursor.execute('SELECT * FROM abc')
    print(cursor.fetchone()) # 以元组形式返回第一行结果，游标移动到下一行
    print(cursor.fetchone()) # 以元组形式返回第二行结果，游标移动到下一行
    
db.close()

fetchall()

fetchall()会从当前游标开始返回后面的所有记录，通常返回一个列表，其中每个元素是一个元组，代表一条记录中所有字段值。调用fetchall()后，游标会移动到结果集的末尾

import pymysql

db = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    database='test'
)

with db.cursor() as cursor:
    cursor.execute('select * from abc')
    print(cursor.fetchone()) # 以元组形式返回第一行结果，游标移动到下一行
    print(cursor.fetchall()) # 以列表形式返回第二行到最后一行的结果，游标移动到末尾

db.close()

fetchany()

fetchany(size)从查询结果中返回指定数量的记录，游标移动到下一批次记录的开始。返回的结果通常是一个列表，其中每个元素是一个元组，代表一条记录的所有字段值

import pymysql

db = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    database='test'
)

with db.cursor() as cursor:
    cursor.execute('select * from abc')
    print(cursor.fetchany(3)) # 以列表的形式返回第一行到第三行的结果，游标移动到第四行
    print(cursor.fetchany(2)) # 以列表的形式返回第四行到第五行的结果，游标移动到第六行

db.close()

fetchany()可以有效避免一次性加载过多数据导致的问题

关闭数据库

使用close()方法，具体使用参考上面案例