每天学习一点pandas——Python操作SQLite数据库的完整指南
大家好！今天我们来学习如何使用pandas与SQLite数据库进行数据交互。无论是数据分析师还是开发人员，掌握数据库操作都是必备技能。通过本文，你将学会如何轻松地将数据从数据库读取到pandas中，以及如何将数据写入数据库。
一、准备工作：导入必要的库
首先，我们需要导入两个重要的Python库：
import pandas as pd
import sqlite3
- • pandas：Python中最强大的数据分析库，提供了DataFrame这种灵活的数据结构
- • sqlite3：Python内置的SQLite数据库操作模块，无需额外安装
二、读取数据库数据：最简单的开始
让我们从一个最简单的例子开始——从数据库中读取数据：
con = sqlite3.connect("./data/weather_2012.sqlite")
df = pd.read_sql("SELECT * from weather_2012 LIMIT 3", con)
df
代码详解：
第1行：con = sqlite3.connect("./data/weather_2012.sqlite")
- •
sqlite3.connect()：建立数据库连接 - •
"./data/weather_2012.sqlite"：数据库文件路径（当前目录下的data文件夹） - • 重要提醒：此时con连接的数据库是
weather_2012.sqlite。后面我们会换到其他数据库，一定要注意区分con当前连接的是哪个数据库！
第2行：df = pd.read_sql("SELECT * from weather_2012 LIMIT 3", con)
- •
pd.read_sql()：pandas提供的数据库查询函数 - •
"SELECT * from weather_2012 LIMIT 3"：SQL查询语句 - •
from weather_2012：从weather_2012表中读取
- •
df：返回的DataFrame，可以像处理Excel表格一样操作它
执行结果：
id date_time temp
0 1 2012-01-01 00:00:00 -1.8
1 2 2012-01-01 01:00:00 -1.8
2 3 2012-01-01 02:00:00 -1.8
看！我们成功地从数据库中读取了3行数据。这是一个包含3列（id、date_time、temp）的表格。
三、指定索引列：让数据更有意义
在上面的结果中，左边的0、1、2是pandas自动生成的索引。但有时我们希望使用数据库中的某列作为索引：
df = pd.read_sql("SELECT * from weather_2012 LIMIT 3", con, index_col=['id'])
df
新增参数解释：
- •
index_col=['id']：指定使用id列作为DataFrame的索引
执行结果：
date_time temp
id
1 2012-01-01 00:00:00 -1.8
2 2012-01-01 01:00:00 -1.8
3 2012-01-01 02:00:00 -1.8
注意三个变化：
- 1. 索引值变了：从0、1、2变成了1、2、3（数据库中的实际id值）
- 2. 数据结构变了：id列不再作为数据列显示，而是变成了索引
- 3. 索引有了名称：索引栏显示"id"作为索引名称
四、从CSV到数据库：完整的数据迁移过程
很多时候，我们的原始数据是从CSV文件开始的。如何将CSV数据存入数据库呢？
weather_df = pd.read_csv('./data/weather_2012.csv')
con = sqlite3.connect('./data/test_db.sqlite')
con.execute('DROP TABLE IF EXISTS weather_2012')
weather_df.to_sql('weather_2012', con)
代码详解，一步一个脚印：
第1行：weather_df = pd.read_csv('./data/weather_2012.csv')
- • 从
weather_2012.csv文件中读取数据到DataFrame - • 此时数据在内存中，存储在
weather_df变量里
第2行：con = sqlite3.connect('./data/test_db.sqlite')
- • 连接（或创建）新的数据库文件
test_db.sqlite - • 注意：这里换数据库了！从之前的
weather_2012.sqlite换成了test_db.sqlite
第3行：con.execute('DROP TABLE IF EXISTS weather_2012')
- • 如果weather_2012表已存在，则删除它
第4行：weather_df.to_sql('weather_2012', con)
这是最关键的一行，让我们详细拆解：
| | |
|---|
weather_df | 源数据 | |
.to_sql() | 写入方法 | |
'weather_2012' | 目标表名 | |
con | 数据库连接 | |
完整的数据流向图：
磁盘上的CSV文件 (weather_2012.csv)
↓
pd.read_csv()
↓
内存中的DataFrame (weather_df变量)
↓
.to_sql()方法
↓
SQLite数据库文件 (test_db.sqlite)
↓
数据库中的表 (weather_2012表)
实际发生的操作：
- 1. 自动创建表结构：
根据weather_df的列名和数据类型，自动生成SQL语句创建表：CREATE TABLE weather_2012 (
"index" INTEGER,
"Date/Time" TEXT,
"Temp (C)" REAL,
"Dew Point Temp (C)" REAL,
"Rel Hum (%)" INTEGER,
"Wind Spd (km/h)" INTEGER,
"Visibility (km)" REAL,
"Stn Press (kPa)" REAL,
"Weather" TEXT
)
- 2. 批量插入所有数据：
将DataFrame中的8784行数据逐行插入到新创建的表中
数据验证：
执行后返回8784，这表示成功写入了8784行数据。为什么是8784这个特殊数字？
因为2012年是闰年，有366天，每天24小时记录：
366天 × 24小时/天 = 8784小时
这说明我们的数据包含了2012年全年的每小时天气记录，非常完整！
五、查询写入的数据：验证成果
让我们验证一下刚才写入的数据：
df = pd.read_sql('SELECT * from weather_2012 LIMIT 3', con)
df
执行结果：
成功了！ 我们的数据已经完整地从CSV文件迁移到了数据库中。
六、处理特殊列名：避免SQL语法错误
注意上面的列名包含特殊字符（空格、括号、斜杠）。如果直接查询会报错，需要特殊处理：
# ❌ 错误写法（会报错）：
# df1 = pd.read_sql('''
# SELECT Date/Time, Temp (C), Wind Spd (km/h)
# FROM weather_2012
# WHERE Temp (C) < 0
# LIMIT 10
# ''', con)
# ✅ 正确写法：
df1 = pd.read_sql('''
SELECT "Date/Time", "Temp (C)", "Wind Spd (km/h)"
FROM weather_2012
WHERE "Temp (C)" < 0
LIMIT 10
''', con)
df1
为什么需要引号？
SQL解析器会有误解：
- •
Date/Time → 理解为"Date除以Time"（数学运算） - •
Temp (C) → 理解为"调用Temp函数，参数是C"（函数调用）
用双引号括起来告诉SQL："这是一个完整的列名，不要拆解！"
执行结果：
这里我们查询了温度低于0℃的前10条记录。可以看到2012年元旦的凌晨确实很冷！
七、复杂查询：展示SQL的强大功能
SQL的真正威力在于复杂的条件查询，让我们看一个综合例子：
query = '''
SELECT *
FROM weather_2012
WHERE "Date/Time" >= '2012-07-01'
AND "Date/Time" <= '2012-07-10'
AND "Temp (C)" < 15
ORDER BY "Temp (C)" DESC
'''
pd.read_sql(query, con)
查询逻辑解析：
| | |
|---|
"Date/Time" >= '2012-07-01' | | |
"Date/Time" <= '2012-07-10' | | |
"Temp (C)" < 15 | | |
ORDER BY "Temp (C)" DESC | | |
执行结果（前6行）：
看，7月份的夜晚温度都在15℃以下，还是挺凉爽的！
八、探索数据库：了解数据全貌
掌握了基本操作后，我们还需要学会如何"探索"数据库，了解数据的整体情况。
8.1 查看所有表：数据库里有什么？
tables_df = pd.read_sql("SELECT name FROM sqlite_master WHERE type='table'", con)
tables_df
代码解释：
- •
sqlite_master：SQLite的系统表，存储数据库的所有结构信息 - •
type='table'：只查询表类型（排除索引、视图等其他对象）
执行结果：
| name |
|--------------|
| weather_2012 |
结果显示数据库中只有一个表：weather_2012。
8.2 查看表结构：这个表长什么样？
schema_df = pd.read_sql("PRAGMA table_info(weather_2012)", con)
schema_df
代码解释：
- •
PRAGMA table_info()：SQLite特有的命令，专门用于获取表的详细结构信息
执行结果：
| cid | name | type | notnull | dflt_value | pk |
|-----|-------------------|---------|---------|------------|----|
| 0 | index | INTEGER | 0 | None | 0 |
| 1 | Date/Time | TEXT | 0 | None | 0 |
| 2 | Temp (C) | REAL | 0 | None | 0 |
| 3 | Dew Point Temp (C)| REAL | 0 | None | 0 |
| 4 | Rel Hum (%) | INTEGER | 0 | None | 0 |
| 5 | Wind Spd (km/h) | INTEGER | 0 | None | 0 |
| 6 | Visibility (km) | REAL | 0 | None | 0 |
| 7 | Stn Press (kPa) | REAL | 0 | None | 0 |
| 8 | Weather | TEXT | 0 | None | 0 |
字段详细解释：
| | | |
|---|
cid | | | |
name | | | |
type | | | |
notnull | | | |
dflt_value | | | |
pk | | | |
8.3 统计总行数：数据量有多大？
count_df = pd.read_sql("SELECT COUNT(*) as row_count FROM weather_2012", con)
count_df
代码解释：
- •
COUNT(*)：SQL的聚合函数，统计所有行数 - •
as row_count：给结果列起一个易懂的别名
执行结果：
| row_count |
|-----------|
| 8784 |
九、重要知识点总结：这些你要记住
- •
df.to_sql()将DataFrame写入数据库
- • 查看所有表：查询
sqlite_master系统表 - • 查看表结构：使用
PRAGMA table_info()
十、实际应用建议：工作中这么用
- 1. 数据迁移标准化流程：
CSV文件 → pandas DataFrame → SQLite数据库表
这是最常见的数据导入流程
- • 使用
if_exists='append'参数增量备份
结语
通过今天的学习，你已经掌握了pandas与SQLite数据库交互的核心技能。从简单的数据读取到复杂的数据查询，从数据迁移到数据库探索，这些都是数据分析工作中的常见任务。
记住几个关键点：
- • 连接对才能操作对：始终清楚con连接的是哪个数据库
- • 探索后再操作：先了解数据库结构，再进行数据操作
实践是最好的老师。尝试用今天学到的知识处理你自己的数据，遇到问题不要怕，每个错误都是学习的机会。
📦 资源获取提示
关注「码农自习室」，后台回复关键词 pandas学习，即可获取本文完整代码及配套练习数据集，一起动手掌握高效数据操作的核心技巧！
❤️ 支持我们
如果觉得本文对你有帮助，欢迎点赞 + 关注，您的支持是我们持续创作优质内容的最大动力！
📚 参考资料声明
主要学习素材来源：
项目名称：Pandas Cookbook
作者：Julia Evans (jvns)
GitHub仓库：https://github.com/jvns/pandas-cookbook
致谢：
感谢 Julia Evans 的优质开源内容。本文是在学习该项目过程中产生的笔记和总结，所有原创代码版权归原仓库所有。本文仅供学习交流，如有侵权请联系删除。
