Python 操作 MySQL：连接真正的"数据金库"

引言：从"记事本"升级到"银行金库"

但记事本有几个致命问题：

只有你能用：别人没法同时看你的记事本（不支持高并发）；
丢了就完了：文件删了数据就没了（没有完善的服务保障）；
容量有限：记到几百页就翻不动了。

如果开公司做生意，你需要的是银行金库——有专门的保管员（数据库服务进程）、支持多人同时存取（高并发）、有严格的安全制度（权限管理）、金库本身固若金汤（稳定性和事务保障）。

MySQL 就是互联网世界最流行的"数据金库"。淘宝、微博、Facebook、YouTube 的背后都有它的身影。

这一篇，我们从 MySQL 和 SQLite 的区别讲起，讲清楚安装配置、字符集选择、Python 驱动的使用，以及事务、占位符等核心概念，最后通过一个完整的实战案例让你掌握 Python 操作 MySQL 的全部要点。

一、MySQL vs SQLite：什么时候该用哪个？

1.1 核心区别

对比项	SQLite	MySQL
架构	嵌入式（库文件直接读写）	客户端-服务器（独立进程）
类比	个人记事本	银行金库
并发能力	弱（写操作会锁全库）	强（支持数千并发连接）
安装	Python 自带，零安装	需单独安装服务器
占用资源	极小（几 MB）	较大（几百 MB 起步）
网络访问	不支持	支持（跨机器访问数据）
适用场景	桌面软件、手机 App、小型工具	网站后台、企业系统、高并发应用

1.2 生活化理解

SQLite 像你家的保险柜：自己用很方便，但全家只有一把钥匙，别人想存取得等你开门；
MySQL 像银行：24 小时营业，几百个窗口同时服务，任何人凭账号密码都能办业务。

1.3 MySQL 的"引擎"：InnoDB

MySQL 内部有多种存储引擎（数据的实际存储和管理方式），最常用的是 InnoDB。

InnoDB 的核心优势：支持事务（Transaction）。

什么是事务？ 转账是经典例子：

从 A 账户扣 100 元
向 B 账户加 100 元

这两步必须要么都成功，要么都失败。如果扣完 A 的钱系统崩了，B 没收到，钱就凭空消失了。事务保证这种"一组操作不可分割"。

生活化理解：事务像搬家公司的"整车托运"——所有家具要么全部运到新家，要么一件不动原车拉回，不会出现"沙发到了、床丢了"的局面。

二、安装 MySQL：两种姿势

2.1 方式一：官方安装包（传统方式）

从 MySQL 官网下载 Community Server（社区版，免费）：

选择对应平台（Windows/macOS/Linux）；
安装过程中会提示设置 root 用户的密码——这是数据库的最高权限账号，务必记牢；
Windows 安装时记得选择 UTF-8 编码，否则中文会变乱码。

2.2 方式二：Docker 启动（推荐，干净快速）

不想在电脑上装一堆服务？用 Docker 一条命令搞定：

bash

docker run -e MYSQL_ROOT_PASSWORD=password \
  -p 3306:3306 \
  --name mysql-8.4 \
  -v ./mysql-data:/var/lib/mysql \
  mysql:8.4 \
  --mysql-native-password=ON \
  --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_unicode_ci

参数详解（别怕，一个个看）：

参数	作用	生活化类比
`-e MYSQL_ROOT_PASSWORD=password`	设置 root 密码为 `password`	给金库配钥匙
`-p 3306:3306`	本机 3306 端口映射到容器	金库对外开的窗口
`--name mysql-8.4`	容器起名	给这个金库挂牌子
`-v ./mysql-data:/var/lib/mysql`	数据存到本机目录	金库的账本放你抽屉里，容器删了数据还在
`mysql:8.4`	使用 MySQL 8.4 镜像	选金库的型号
`--character-set-server=utf8mb4`	字符集用 utf8mb4	金库支持中文和 emoji

看到 ready for connections 字样，说明 MySQL 启动成功。

2.3 关于 utf8mb4：字符集的血泪史

这是新手必踩的坑，务必重视！

MySQL 里有个著名的"假 UTF-8"问题：

字符集	每个字符最多字节	能存 emoji 吗
`utf8`（MySQL 里的）	3 字节	❌ 不能
`utf8mb4`	4 字节	✅ 能

MySQL 的 utf8 是阉割版，最多只支持 3 字节字符。而 emoji 表情（😀🎉）和一些生僻字需要 4 字节——用 utf8 存 emoji 会直接报错或存成 ???。

结论：新建 MySQL 数据库，永远用 utf8mb4，没有例外。

验证方法（进入 MySQL 命令行）：

bash

mysql -u root -p

sql

show variables like '%char%';

看到一堆 utf8mb4 就说明配置正确。

三、安装 Python 驱动：程序和金库之间的"翻译"

3.1 为什么需要驱动？

MySQL 是独立的服务器进程，说自己的"网络协议方言"。Python 程序不会说这种方言，需要一个翻译——这就是 MySQL 驱动。

官方推荐的驱动是 mysql-connector-python：

bash

pip install mysql-connector-python

社区还有一个流行的驱动叫 PyMySQL（纯 Python 实现），用法几乎一样。本文用官方驱动演示。

生活化理解：MySQL 服务器是一位只讲"MySQL 语"的银行柜员，mysql-connector-python 是你的随身翻译，把 Python 的指令翻译成柜员听得懂的话。

四、核心操作：连接、增删改查

4.1 DB-API：统一的操作套路

Python 官方定义了数据库操作的统一接口规范——DB-API。无论你操作 MySQL、SQLite 还是 PostgreSQL，套路都一模一样：

连接（connect）→ 拿游标（cursor）→ 执行 SQL（execute）→ 提交（commit）→ 关闭（close）

生活化理解：去银行办业务的流程永远是——取号（连接）→ 找柜员（游标）→ 办业务（执行 SQL）→ 签字确认（commit）→ 离开（close）。换了银行（换数据库），流程不变。

4.2 完整的增删改查示例

python

import mysql.connector

# ========== 1. 建立连接（取号进门）==========
conn = mysql.connector.connect(
    host='localhost',      # 数据库地址（本机）
    port=3306,             # 端口
    user='root',           # 用户名
    password='password',   # 密码
    database='test'        # 要操作的数据库
)

# ========== 2. 获取游标（找到柜员）==========
cursor = conn.cursor()

# ========== 3. 建表 ==========
cursor.execute('''
    CREATE TABLE IF NOT EXISTS user (
        id VARCHAR(20) PRIMARY KEY,
        name VARCHAR(20)
    )
''')

# ========== 4. 插入数据（增）==========
# 注意：MySQL 的占位符是 %s（不管字段类型，一律 %s）
cursor.execute(
    'INSERT INTO user (id, name) VALUES (%s, %s)',
    ['1', 'Michael']
)
print(f'影响了 {cursor.rowcount} 行')  # 1

# ========== 5. 提交事务（签字确认）==========
conn.commit()

# ========== 6. 查询数据（查）==========
cursor.execute('SELECT * FROM user WHERE id = %s', ('1',))
values = cursor.fetchall()
print(values)  # [('1', 'Michael')]

# ========== 7. 更新数据（改）==========
cursor.execute('UPDATE user SET name = %s WHERE id = %s', ('Mike', '1'))
conn.commit()

# ========== 8. 删除数据（删）==========
cursor.execute('DELETE FROM user WHERE id = %s', ('1',))
conn.commit()

# ========== 9. 关闭（离开银行）==========
cursor.close()
conn.close()

4.3 关键概念逐个击破

（1）连接 Connection

python

conn = mysql.connector.connect(user='root', password='password', database='test')

一次连接 = 一条程序和数据库之间的"电话线"。建立连接有开销，不要每个操作都新建连接（后面讲连接池）。

（2）游标 Cursor

python

cursor = conn.cursor()

游标是执行 SQL 的"手柄"。一个连接可以创建多个游标，就像一条电话线可以转接给不同柜员。

（3）占位符 %s：防 SQL 注入的关键

python

# ✅ 正确：参数化查询
cursor.execute('SELECT * FROM user WHERE id = %s', (user_id,))

# ❌ 危险：字符串拼接 SQL
cursor.execute('SELECT * FROM user WHERE id = ' + user_id)

为什么字符串拼接危险？ 看这个故事：

python

user_id = "1' OR '1'='1"   # 黑客输入的内容
sql = 'SELECT * FROM user WHERE id = ' + user_id
# 拼接后变成：SELECT * FROM user WHERE id = 1' OR '1'='1
# 结果：所有用户数据都被查出来了！

这就是著名的 SQL 注入攻击。用占位符 %s 时，驱动会把参数当"纯数据"处理，黑客的恶意代码不会被执行。

注意：MySQL 占位符是 %s（即使是数字也用 %s），而 SQLite 用 ?，别搞混。

（4）事务与 commit()

python

cursor.execute('INSERT INTO ...')
conn.commit()  # 签字确认，真正写入

增、删、改（INSERT/UPDATE/DELETE）必须 commit，否则数据不会真正落盘；
查询（SELECT）不需要 commit；
如果中途出错想反悔，可以 conn.rollback() 回滚，撤销本次事务的所有操作。

生活化理解：你在银行柜台办完业务，柜员打印回执单，你签字（commit）才生效；你觉得办错了，撕掉回执单（rollback）就当没发生。

（5）读取查询结果

方法	作用	适用
`fetchone()`	取一条	确定只有一条结果
`fetchall()`	取全部（返回列表）	结果集小
`fetchmany(n)`	取 n 条	分页读取大结果集

python

cursor.execute('SELECT * FROM user')
row = cursor.fetchone()
while row:
    print(row)
    row = cursor.fetchone()

五、更安全的写法：with 语句自动管理资源

手动 close() 容易忘，用 with 自动收尾：

python

import mysql.connector

def query_users():
    conn = mysql.connector.connect(
        user='root', password='password', database='test'
    )
    try:
        with conn.cursor() as cursor:  # 自动关闭游标
            cursor.execute('SELECT * FROM user')
            return cursor.fetchall()
    finally:
        conn.close()  # 确保连接关闭

注意：mysql-connector 的 connection 本身不支持 with 自动关闭（这点和文件不同），所以用 try...finally 兜底。

六、实战案例：用户管理系统

把上面的知识整合成一个可复用的小模块：

python

import mysql.connector
from contextlib import contextmanager

DB_CONFIG = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': 'password',
    'database': 'test',
}


@contextmanager
def get_cursor():
    """上下文管理器：自动管理连接和游标"""
    conn = mysql.connector.connect(**DB_CONFIG)
    cursor = conn.cursor()
    try:
        yield cursor, conn
    finally:
        cursor.close()
        conn.close()


def init_table():
    """建表"""
    with get_cursor() as (cursor, conn):
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS user (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL UNIQUE,
                email VARCHAR(100),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ''')
        conn.commit()
    print('数据表就绪')


def add_user(username, email):
    """新增用户"""
    with get_cursor() as (cursor, conn):
        cursor.execute(
            'INSERT INTO user (username, email) VALUES (%s, %s)',
            (username, email)
        )
        conn.commit()
        return cursor.lastrowid  # 返回自增 ID


def find_user(username):
    """按用户名查询"""
    with get_cursor() as (cursor, conn):
        cursor.execute(
            'SELECT id, username, email, created_at FROM user WHERE username = %s',
            (username,)
        )
        return cursor.fetchone()


def list_users():
    """列出所有用户"""
    with get_cursor() as (cursor, conn):
        cursor.execute('SELECT id, username, email FROM user ORDER BY id')
        return cursor.fetchall()


def delete_user(user_id):
    """删除用户"""
    with get_cursor() as (cursor, conn):
        cursor.execute('DELETE FROM user WHERE id = %s', (user_id,))
        conn.commit()
        return cursor.rowcount  # 返回删除的行数


# ========== 使用演示 ==========
if __name__ == '__main__':
    init_table()

    new_id = add_user('alice', 'alice@example.com')
    print(f'新增用户，ID = {new_id}')

    user = find_user('alice')
    print(f'查询结果: {user}')

    print('所有用户:', list_users())

    deleted = delete_user(new_id)
    print(f'删除了 {deleted} 条记录')

亮点：

@contextmanager 封装连接管理，业务代码再也不用关心 close()；
lastrowid 获取自增 ID（插入后立刻知道新记录的主键）；
建表语句显式指定 ENGINE=InnoDB 和 CHARSET=utf8mb4，好习惯。

七、进阶知识

7.1 连接池：别每次都"重新打电话"

建立数据库连接是耗时操作（TCP 握手 + 认证，可能几十毫秒）。高并发场景下，每个请求都新建连接会把数据库拖垮。

连接池的思路：预先建好一批连接放着，用完归还而不是关闭，下一个人接着用。

生活化理解：像公司的公务用车池——不用每人买一辆车，出车回来还钥匙，下个人继续开。

常用的连接池方案：mysql-connector-python 自带的 pooling 模块，或第三方 ORM（SQLAlchemy）内置的连接池：

python

from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(
    pool_name='mypool',
    pool_size=5,          # 池里保持 5 条连接
    **DB_CONFIG
)

# 从池中取连接，用完自动归还
conn = pool.get_connection()
cursor = conn.cursor()
cursor.execute('SELECT 1')
cursor.close()
conn.close()  # 注意：这里的 close 是"归还到池"，不是真关闭

7.2 事务的完整姿势

python

conn = mysql.connector.connect(**DB_CONFIG)
cursor = conn.cursor()
try:
    # 转账：A 扣钱，B 加钱，必须同生共死
    cursor.execute('UPDATE account SET balance = balance - 100 WHERE id = %s', ('A',))
    cursor.execute('UPDATE account SET balance = balance + 100 WHERE id = %s', ('B',))
    conn.commit()      # 都成功，一起生效
except Exception:
    conn.rollback()    # 任何一步失败，全部撤销
    raise
finally:
    cursor.close()
    conn.close()

八、常见误区解析

误区 1：忘记 commit，数据"丢了"

现象：程序跑完没报错，但数据库里查不到刚插入的数据。

真相：INSERT/UPDATE/DELETE 默认在事务中，不 commit() 就不会落盘。程序退出时未提交的事务被自动回滚。

误区 2：用字符串拼接 SQL

现象：用户输入特殊字符后报错，或者更糟——数据泄露。

真相：这是 SQL 注入漏洞。永远用 %s 占位符传参，没有例外。哪怕你确定参数"安全"，也要养成习惯。

误区 3：字符集用 utf8 而不是 utf8mb4

现象：存 emoji 或生僻字时报错 Incorrect string value。

真相：MySQL 的 utf8 只支持 3 字节字符。建库建表一律 utf8mb4。

误区 4：每个函数都新建连接

现象：并发一高，数据库报 Too many connections。

真相：连接是稀缺资源（MySQL 默认最大连接数 151）。应该用连接池复用连接，用完归还。

误区 5：fetchall() 读取百万行数据

现象：查询大表时程序内存爆掉。

真相：fetchall() 会把所有结果一次性读进内存。大数据量应该用 fetchmany() 分批读取，或者直接用游标迭代：

python

cursor.execute('SELECT * FROM huge_table')
for row in cursor:  # 逐行迭代，内存友好
    process(row)

误区 6：把密码硬编码在代码里

真相：和邮件授权码一样，数据库密码要放环境变量或配置文件：

python

import os
DB_CONFIG = {
    'user': os.environ.get('DB_USER'),
    'password': os.environ.get('DB_PASSWORD'),
    # ...
}

九、MySQL vs SQLite vs 其他：选型速查

场景	推荐
学习 SQL、本地小工具、手机 App	SQLite
网站、Web 应用、企业系统	MySQL
超复杂查询、数据分析	PostgreSQL
缓存、会话存储	Redis（非关系型）
日志、文档存储	MongoDB（非关系型）

学完 MySQL 的下一步：手写 SQL 拼字符串还是太繁琐，实际项目常用 ORM（对象关系映射）——比如 SQLAlchemy，把数据库表映射成 Python 类，操作数据像操作对象一样自然。那是下一篇的故事。

十、小结

核心知识点	一句话总结
MySQL 定位	服务端数据库，支持高并发，互联网应用标配
InnoDB 引擎	支持事务的存储引擎，建表默认选它
utf8mb4	真正的 UTF-8，支持 emoji，建库必选
驱动	`pip install mysql-connector-python`
操作流程	connect → cursor → execute → commit → close
占位符	MySQL 用 `%s`，防 SQL 注入的生命线
commit	增删改必须提交，查询不用
连接池	高并发必备，连接用完归还而不是新建

记住三句话：

SQL 参数永远用 %s 占位符，字符串拼接等于开门揖盗；
增删改必须 commit，字符集必须 utf8mb4；
连接是稀缺资源，小项目 try...finally 关连接，大项目上连接池。

Python 操作 MySQL：连接真正的"数据金库" ​

引言：从"记事本"升级到"银行金库" ​

一、MySQL vs SQLite：什么时候该用哪个？ ​

1.1 核心区别 ​

1.2 生活化理解 ​

1.3 MySQL 的"引擎"：InnoDB ​

二、安装 MySQL：两种姿势 ​

2.1 方式一：官方安装包（传统方式） ​

2.2 方式二：Docker 启动（推荐，干净快速） ​

2.3 关于 utf8mb4：字符集的血泪史 ​

三、安装 Python 驱动：程序和金库之间的"翻译" ​

3.1 为什么需要驱动？ ​

四、核心操作：连接、增删改查 ​

4.1 DB-API：统一的操作套路 ​

4.2 完整的增删改查示例 ​

4.3 关键概念逐个击破 ​

（1）连接 Connection ​

（2）游标 Cursor ​

（3）占位符 %s：防 SQL 注入的关键 ​

（4）事务与 commit() ​

（5）读取查询结果 ​

五、更安全的写法：with 语句自动管理资源 ​

六、实战案例：用户管理系统 ​

七、进阶知识 ​

7.1 连接池：别每次都"重新打电话" ​

7.2 事务的完整姿势 ​

八、常见误区解析 ​

误区 1：忘记 commit，数据"丢了" ​

误区 2：用字符串拼接 SQL ​

误区 3：字符集用 utf8 而不是 utf8mb4 ​

误区 4：每个函数都新建连接 ​

误区 5：fetchall() 读取百万行数据 ​

误区 6：把密码硬编码在代码里 ​

九、MySQL vs SQLite vs 其他：选型速查 ​

十、小结 ​

Python 操作 MySQL：连接真正的"数据金库"

引言：从"记事本"升级到"银行金库"

一、MySQL vs SQLite：什么时候该用哪个？

1.1 核心区别

1.2 生活化理解

1.3 MySQL 的"引擎"：InnoDB

二、安装 MySQL：两种姿势

2.1 方式一：官方安装包（传统方式）

2.2 方式二：Docker 启动（推荐，干净快速）

2.3 关于 utf8mb4：字符集的血泪史

三、安装 Python 驱动：程序和金库之间的"翻译"

3.1 为什么需要驱动？

四、核心操作：连接、增删改查

4.1 DB-API：统一的操作套路

4.2 完整的增删改查示例

4.3 关键概念逐个击破

（1）连接 Connection

（2）游标 Cursor

（3）占位符 %s：防 SQL 注入的关键

（4）事务与 commit()

（5）读取查询结果

五、更安全的写法：with 语句自动管理资源

六、实战案例：用户管理系统

七、进阶知识

7.1 连接池：别每次都"重新打电话"

7.2 事务的完整姿势

八、常见误区解析

误区 1：忘记 commit，数据"丢了"

误区 2：用字符串拼接 SQL

误区 3：字符集用 utf8 而不是 utf8mb4

误区 4：每个函数都新建连接

误区 5：fetchall() 读取百万行数据

误区 6：把密码硬编码在代码里

九、MySQL vs SQLite vs 其他：选型速查

十、小结