|
|
Python操作MySQL与SQLite：从零到壹的底层数据库实操指南
1. 为什么我把MySQL和Sqlite放在一起讲
接触Python有一段时间的朋友，迟早会撞上"数据库"这道坎。爬虫爬到一定量级需要存数据，Web项目需要用户表和会话记录，量化策略需要跑历史行情——这些场景绕不开数据库。很多入门教程一上来就丢给你一堆ORM（对象关系映射）框架，比如SQLAlchemy、Django ORM，把SQL语句藏在底层，看似方便，但一旦遇到性能瓶颈或者复杂查询，你就抓瞎了。反过来，直接从最基础的数据库操作入手，用Python原生的方式连接MySQL和Sqlite，把SQL语言本身搞明白，后面再用什么框架都只是套壳的事情。
这篇博文的核心思路就一句话：用最笨、最直接的方式，把Python操作MySQL和Sqlite的底层逻辑打通。我会从零开始，带你完成环境搭建、建库建表、增删改查、事务处理、常见坑位排查这整个流程。整个过程不需要安装任何重量级框架，最多用到
pymysql
和Python自带的
sqlite3
标准库。适合三种人：刚学完Python基础语法、想了解数据库怎么用的初学者；会用ORM但没搞懂SQL本质的"半吊子"；以及需要快速在本地搭建测试数据库、不想折腾重型数据库服务的开发人员。
顺便说一句，标题里的"从零到壹"不是虚的。我把每一步需要执行什么命令、每个参数是什么意思、每个模块为什么存在都拆开讲清楚。MySQL和Sqlite两套系统同时对比着学，你会发现很多概念是相通的——学会一个，另一个基本就是换皮。
2. 数据库选型：什么时候用MySQL，什么时候用Sqlite
2.1 两个数据库的核心差异
先说明一个容易混淆的点：MySQL和Sqlite虽然都叫SQL数据库，但它们的工作方式完全不同。MySQL是典型的客户端-服务器架构，数据库引擎作为一个独立的服务进程跑在机器上，你的Python程序通过网络协议（默认3306端口）和它通信。Sqlite则是一个嵌入式数据库，它不是一个独立服务，而是一个库，直接在你的进程内读写文件——你的Python程序就是数据库引擎本体。
这个本质差异决定了几乎所有使用场景：
MySQL Sqlite用生活化的类比来说，把MySQL想象成一家银行——它有柜台、有金库、有安保系统，你只需要拿着银行卡（账号密码）去窗口办业务，银行内部怎么运转你不用管。Sqlite则是你家里的保险箱——保险箱就摆在你卧室，钥匙在你手里，打开就能存取东西，但你想让远在外地的朋友也来存取物品，这就不太现实了。
2.2 初学者怎么选
我给初学者的建议分三步走：第一步，先在Sqlite上练手，把所有SQL语法、增删改查逻辑跑通，因为它零配置、无网络依赖、报错信息直白，最适合当练习场。第二步，当你的项目需要被多个程序访问、需要长期稳定运行、需要账号权限管理时，迁移到MySQL。第三步，如果你要做Web开发，直接用MySQL起步也没问题——毕竟生产环境十有八九是它。
从学习路径来看，先学Sqlite再学MySQL还有一个隐藏好处：你会更清楚地知道哪些是SQL标准语法（两个数据库通用），哪些是特定数据库的方言。比如MySQL有
AUTO_INCREMENT
自增关键字，Sqlite也有同样的
AUTOINCREMENT
，但细节用法不一样；MySQL有
REPLACE INTO
这种方言，Sqlite就没有。把这些差异搞清楚，以后换数据库心里才有底。
3. 环境准备：从零搭建MySQL和Sqlite开发环境
3.1 MySQL的安装与初始化
很多人卡在第一步——MySQL装不上。我倒不是要说教，而是想分享一个最省心的路径。Windows用户直接去MySQL官网下载MySQL Community Server安装包，选MSI Installer版本，安装时勾选"Developer Default"会附带装好MySQL Workbench图形工具。这里有个关键选择：安装过程中会要求设置root密码，一定要记住，后面Python连接全靠它。Linux用户则简单一些，Ubuntu/Debian系执行
sudo apt install mysql-server
，CentOS/RHEL系用
sudo yum install mysql-server
，装完执行
sudo systemctl start mysqld
启动服务。
装完之后做两件事：第一，用
mysql -u root -p
进入命令行客户端，执行
SELECT VERSION();
确认安装版本；第二，创建一个专门给Python程序用的账号，而不是直接用root。在MySQL命令行里执行：
CREATE
USER
'pyuser'
@
'localhost'
IDENTIFIED
BY
'your_password'
;
GRANT
ALL
PRIVILEGES
ON
*
.
*
TO
'pyuser'
@
'localhost'
;
FLUSH PRIVILEGES;
生产环境肯定不该用
ALL PRIVILEGES ON *.*
这么宽松的权限，但本地学习阶段这样最省事，省得老是因为权限不足报错。
3.2 Python端依赖安装
Python操作MySQL需要单独的驱动库，主流选择是
pymysql
——它纯Python实现，不需要编译，支持Python 3全版本。一条命令装完：
pip install pymysql
如果你用的是
mysql-connector-python
也可以，那是MySQL官方出的驱动，但实测下来
pymysql
在易用性和错误提示方面更友好一些。至于Sqlite，完全不用装任何东西——Python标准库里的
sqlite3
模块直接就能用。不需要额外下载DB Browser for SQLite之类的工具，当然如果你喜欢图形化查看数据，后面我会提一嘴。
3.3 验证环境是否就绪
写个最简单的脚本验证环境。创建一个
test_db.py
文件，内容如下：
import
pymysql
import
sqlite3
# 测试MySQL连接
try
:
conn = pymysql.connect(
host=
'localhost'
,
user=
'pyuser'
,
password=
'your_password'
,
database=
'mysql'
,
# 先连到系统自带的数据库
charset=
'utf8mb4'
print
(
'MySQL连接成功，版本信息：'
)
cursor = conn.cursor()
cursor.execute(
'SELECT VERSION()'
)
row = cursor.fetchone()
print
(
f'MySQL版本:
{row[
0
]}
'
)
cursor.close()
conn.close()
except
Exception
as
e:
print
(
f'MySQL连接失败:
{e}
'
)
# 测试Sqlite连接
try
:
sqlite_conn = sqlite3.connect(
'test.db'
)
cursor = sqlite_conn.cursor()
cursor.execute(
'SELECT SQLITE_VERSION()'
)
row = cursor.fetchone()
print
(
f'Sqlite版本:
{row[
0
]}
'
)
cursor.close()
sqlite_conn.close()
print
(
'Sqlite连接成功，数据库文件已生成'
)
except
Exception
as
e:
print
(
f'Sqlite连接失败:
{e}
'
)
这里有个细节值得注意：
sqlite3.connect('test.db')
这个操作会在当前目录下生成一个
test.db
文件，里面是空的。Sqlite的连接就是"文件是否存在+我能不能打开"这么简单。如果传的是
:memory:
字符串，则会创建内存数据库，进程结束数据就没了——这个后面会细说。
4. 建库建表：第一个数据库和第一张表
4.1 MySQL端：先建库再建表
连接上MySQL之后，第一步通常是创建自己的业务数据库。MySQL默认有几个系统数据库（
information_schema
、
mysql
、
performance_schema
等），不建议直接在那里建业务表。新建一个独立的库：
CREATE
DATABASE IF
NOT
EXISTS
py_demo
DEFAULT
CHARACTER
SET
utf8mb4
COLLATE
utf8mb4_general_ci;
USE py_demo;
字符集这块是头号大坑。如果漏了
CHARACTER SET utf8mb4
，默认是
latin1
，到时候存中文就是乱码。很多老教程还在用
utf8
，但在MySQL 5.7以上版本，展示全量字符的
utf8mb4
才是唯一正确的选择——它兼容emoji表情，也兼容各种生僻字。
建表语句直接看，我用一个经典的学生信息表做例子：
CREATE
TABLE
IF
NOT
EXISTS
students (
id
INT
AUTO_INCREMENT
PRIMARY
KEY,
name
VARCHAR
(
50
)
NOT
NULL
,
age
INT
,
gender ENUM(
'男'
,
'女'
)
DEFAULT
'男'
,
email
VARCHAR
(
100
)
UNIQUE
,
created_at
TIMESTAMP
DEFAULT
CURRENT_TIMESTAMP
解释几个关键设计：
id
字段用
INT AUTO_INCREMENT PRIMARY KEY
，让数据库自动生成自增主键，避免业务层去管"下一条id是多少"这种脏活；
name
字段给
NOT NULL
约束，防止空数据混进表里；
email
加
UNIQUE
唯一约束，重复邮箱直接写入报错；
created_at
用
TIMESTAMP DEFAULT CURRENT_TIMESTAMP
，插入记录时自动打时间戳，不需要应用层传时间。这些约束和默认值的设计思路贯穿整个数据库开发——尽量让数据库替你完成数据校验，应用层少操心。
4.2 Sqlite端：文件即数据库
Sqlite建表和MySQL非常相似，关键差异在于数据类型。Sqlite对数据类型不敏感，叫"动态类型"——你可以在任何类型的列里存任何类型的值，它不会强校验。但为了可维护性和跨库兼容性，还是建议按标准类型声明。
PYTHON
import
sqlite3
# 连接并创建表
conn = sqlite3.connect(
'py_demo.db'
)
cursor = conn.cursor()
cursor.execute(
'''
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
gender TEXT DEFAULT '男',
email TEXT UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
'''
)
conn.commit()
# Sqlite必须手动提交事务
cursor.close()
conn.close()