PyMySQL数据库操作指南
- 数据库接口规范 ==============
1.1 Python数据库标准
Python数据库API（DB-API）定义了统一的数据库操作接口规范，使不同数据库系统能通过一致的编程方式访问。该规范包含连接管理、SQL执行、结果处理等核心功能。
1.2 数据库操作模块
PyMySQL是Python语言实现的MySQL数据库驱动程序，遵循DB-API 2.0标准。其核心功能包括连接管理、SQL执行及结果集处理。
官方文档地址： https://pypi.org/project/PyMySQL/
- 安装配置 ============
通过pip安装即可直接使用，无需特殊环境配置。建议在虚拟环境中进行安装以管理依赖。
- 使用流程 ============
3.1 初始化连接
import pymysql
from pymysql.cursors import DictCursor
# 创建数据库连接
db_connection = pymysql.connect(
host='localhost',
user='admin',
password='secure123',
database='company_db',
port=3306,
charset='utf8mb4',
cursorclass=DictCursor
)
# 创建游标对象
cursor = db_connection.cursor()
3.2 数据查询
全量查询
query = "SELECT * FROM departments;"
cursor.execute(query)
all_data = cursor.fetchall()
print(all_data)
单条查询
query = "SELECT * FROM departments;"
cursor.execute(query)
first_row = cursor.fetchone()
print(first_row)
分页查询
query = "SELECT * FROM departments;"
cursor.execute(query)
batch_data = cursor.fetchmany(5)
print(batch_data)
光标控制
query = "SELECT * FROM departments;"
cursor.execute(query)
cursor.scroll(2, 'relative') # 相对移动
cursor.scroll(0, 'absolute') # 绝对定位
3.3 数据操作
插入数据
query = "INSERT INTO departments (id, name) VALUES (%s, %s)"
values = (201, 'IT')
cursor.execute(query, values)
db_connection.commit()
参数化查询
# 位置参数
query = "INSERT INTO employees (emp_id, emp_name) VALUES (%s, %s)"
cursor.execute(query, (1001, 'Alice'))
# 关键字参数
query = "INSERT INTO employees (emp_id, emp_name) VALUES (%(id)s, %(name)s)"
cursor.execute(query, {'id': 1002, 'name': 'Bob'})
批量操作
query = "INSERT INTO departments (id, name) VALUES (%s, %s)"
data = [(202, 'HR'), (203, 'Finance')]
cursor.executemany(query, data)
db_connection.commit()
3.4 数据更新
query = "UPDATE departments SET name=%s WHERE id=%s"
cursor.execute(query, ('Research', 201))
db_connection.commit()
3.5 数据删除
query = "DELETE FROM departments WHERE id=%s"
cursor.execute(query, (202,))
db_connection.commit()
3.6 安全查询实践
# 防止SQL注入的正确方式
username = input("请输入用户名: ")
password = input("请输入密码: ")
query = "SELECT * FROM users WHERE username=%s AND password=%s"
cursor.execute(query, (username, password))
result = cursor.fetchall()
