使用Python连接数据库并提取数据的完整指南
引言
在现代数据分析和后端开发中，Python因其简洁语法和强大的生态系统成为首选语言之一。其中，从数据库中读取数据是常见且关键的操作。本文将介绍如何通过Python连接主流关系型数据库（如MySQL、PostgreSQL和SQLite），执行查询并安全地获取结果。
准备工作：安装依赖库
不同数据库需要对应的驱动程序来与Python交互。以下是常用数据库的连接库及其安装方式：
- MySQL：使用
pymysql或mysql-connector-python
安装命令：pip install pymysql - PostgreSQL：使用
psycopg2
安装命令：pip install psycopg2-binary - SQLite：Python内置
sqlite3模块，无需额外安装
通用操作流程
无论使用哪种数据库，基本的数据提取流程都包括以下几个步骤：
- 导入库
- 建立数据库连接
- 创建游标对象
- 执行SQL语句
- 提取查询结果
- 关闭资源
具体实现示例
1. 连接 MySQL 数据库
import pymysql
# 建立连接
conn = None
try:
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='your_password',
database='test_db',
charset='utf8mb4'
)
# 创建游标并执行查询
with conn.cursor() as cur:
cur.execute("SELECT id, name, age FROM users WHERE age > %s", (18,))
results = cur.fetchall()
for record in results:
print(f"ID: {record[0]}, Name: {record[1]}, Age: {record[2]}")
except Exception as e:
print(f"数据库错误: {e}")
finally:
if conn:
conn.close()
2. 连接 PostgreSQL 数据库
import psycopg2
try:
conn = psycopg2.connect(
host="localhost",
database="myapp",
user="admin",
password="secret",
port=5432
)
with conn.cursor() as cur:
cur.execute("SELECT * FROM products ORDER BY price DESC LIMIT 10")
rows = cur.fetchall()
for row in rows:
print(row)
except psycopg2.Error as e:
print(f"PostgreSQL 错误: {e}")
finally:
if conn:
conn.close()
3. 使用 SQLite（无服务器模式）
import sqlite3
# 连接本地数据库文件
with sqlite3.connect('local_data.db') as conn:
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS logs (id INTEGER PRIMARY KEY, message TEXT, timestamp DATETIME)")
cursor.execute("INSERT INTO logs (message, timestamp) VALUES (?, datetime('now'))")
# 查询数据
cursor.execute("SELECT * FROM logs")
for log in cursor.fetchall():
print(log)
结果处理方法对比
| 方法 | 说明 | 适用场景 |
|---|---|---|
fetchone() |
返回单行数据，格式为元组 | 逐行处理大数据集，节省内存 |
fetchall() |
返回所有结果，格式为元组列表 | 结果集较小，需一次性加载 |
fetchmany(n) |
返回最多 n 行数据 | 分批读取，平衡性能与内存 |
最佳实践建议
- 使用参数化查询：避免SQL注入攻击，例如
WHERE id = %s而非字符串拼接 - 善用上下文管理器：利用
with自动释放游标和连接 - 异常捕获：对连接失败、权限不足等情况进行合理处理
- 连接池优化：高并发应用可引入
DBUtils或 ORM 框架中的连接池机制
各数据库特性简析
MySQL：广泛用于Web应用，支持主从复制和读写分离，适合中高流量系统。
PostgreSQL：支持JSON、GIS、窗口函数等高级功能，适合复杂分析场景。
SQLite：零配置、单文件存储，适用于移动端或小型工具类项目。
掌握这些基础技能后，可以进一步结合 pandas 的 read_sql_query() 实现数据分析流水线，或将数据库操作封装成服务接口供其他模块调用。
