Python数据库操作

数据库概述

为什么要使用数据库？

假设你要管理一个学校的学生信息：

方式一：文本文件存储

 张三,99
 李四,85
 王五,59
 赵六,87

问题：格式不统一，查询困难，数据量大时内存装不下

方式二：JSON文件存储

 [
   {"name":"张三","score":99},
   {"name":"李四","score":85}
 ]

问题：虽然结构化，但仍需全部读入内存才能查询

方式三：数据库

 SELECT name, score FROM students WHERE score > 90;

优势：

结构化存储：数据按表组织，关系清晰
快速查询：无需加载全部数据，直接定位目标
数据安全：支持事务、备份、权限控制
海量数据：轻松处理百万、千万级数据

总结：数据库是专门用来安全、高效、结构化存储和管理数据的软件系统。

数据库的分类

关系型数据库 (SQL)

数据以表格形式存储
表与表之间有关联关系
使用 SQL 语言操作
代表：MySQL、PostgreSQL、SQLite、Oracle、SQL Server

非关系型数据库 (NoSQL)

数据以键值对/文档/图等形式
灵活的数据模型
高性能、高扩展
代表：MongoDB (文档型)、Redis (键值型)、Neo4j (图数据库)

为什么选择 SQLite？

SQLite 是一个嵌入式关系型数据库，特点如下：

特性	说明
免费开源	零成本，可商用
零配置	无需安装服务器，开箱即用
轻量级	整个数据库就是一个文件
Python内置	直接导入 `sqlite3` 模块即可使用
跨平台	Windows、Mac、Linux、Android、iOS 都支持

对比：

MySQL/PostgreSQL 像大型超市，需要专门场地（服务器），适合大规模应用
SQLite 像家里的储物柜，小巧方便，适合本地存储和小型应用

SQLite 管理工具

虽然可以用命令行操作 SQLite，但图形化工具更直观。推荐使用 DB Browser for SQLite：

安装后界面操作非常直观，可以用它打开代码创建的数据库文件，可视化查看数据。

学习路径建议

掌握 SQL 基础
- SQLite（入门首选）
- MySQL（生产环境最常用）
- PostgreSQL（功能强大）
学习数据库设计
- 表结构设计
- 索引优化
- 事务管理
了解 NoSQL（可选）
- Redis（缓存）
- MongoDB（文档型）
- Elasticsearch（搜索）

建议：先扎实掌握 SQL 基础，再学习 NoSQL 会事半功倍！

Python 操作 SQLite 数据库

核心概念：Connection 和 Cursor

在 Python 中操作数据库，只需要记住两个核心对象：

Connection（连接对象）

作用：建立与数据库的连接
方法：
- cursor() - 创建游标
- commit() - 提交事务
- close() - 关闭连接

Cursor（游标对象）

作用：执行 SQL 语句，获取结果
方法：
- execute() - 执行单条 SQL
- executemany() - 执行多条 SQL
- fetchall() - 获取所有结果
- close() - 关闭游标

重要提醒：

Connection 和 Cursor 使用完后必须关闭！
增删改操作后必须调用 commit() 提交，否则数据不会保存！
建议使用 try...except...finally 确保资源正确释放

完整示例：创建数据库并操作数据

下面是一个完整的示例，演示如何：

创建数据库文件
创建数据表
插入数据
查询数据
关闭连接

# -*- coding: UTF-8 -*-
 import sqlite3
 import os
 import json
 
 dbPath = "data.sqlite"
 # 只有data.sqlite文件不存在时才创建该文件
 if not os.path.exists(dbPath):
     # 创建 SQLite数据库
     conn = sqlite3.connect(dbPath)
     # 获取 sqlite3.Cursor 对象
     cursor = conn.cursor()
     # 创建 Persons 表
     cursor.execute("""CREATE TABLE persons (
         id INT PRIMARY KEY NOT NULL,
         name TEXT NOT NULL,
         age INT NOT NULL,
         address CHAR(50),
         salary REAL
     ); """)
     # 关闭游标
     cursor.close()
     # 修改数据库后必须调用 commit 方法提交才能生效
     conn.commit()
     # 关闭数据库连接
     conn.close()
     print("创建数据库成功")
 
 conn = sqlite3.connect(dbPath)
 c = conn.cursor()
 # 删除 persons 表中的所有数据
 c.execute("delete from persons;")
 # 下面的 4 条语句向 persons表中插入4条记录
 c.execute("INSERT INTO persons(id,name,age,address,salary) VALUES(1,'张三',32,'北京',50000.00)")
 c.execute("INSERT INTO persons(id,name,age,address,salary) VALUES(2,'李四',25,'上海',15000.00)")
 c.execute("INSERT INTO persons(id,name,age,address,salary) VALUES(3,'王五',23,'深圳',20000.00)")
 c.execute("INSERT INTO persons(id,name,age,address,salary) VALUES(4,'渣男教父',25,'长沙',60000.00)")
 #  必须提交才能生效
 conn.commit()  # 提交事务
 print("插入数据成功")
 
 # 查询 persons 表中的所有记录，并按age升序排列
 c.execute("SELECT name,age,address,salary from persons order by age")
 # 利用fetchall()可以拿到结果集。结果集是一个list，每个元素都是一个tuple，对应一行记录
 persons = c.fetchall()
 print(type(persons))
 for person in persons:
     print(type(person), person)
 
 # 将查询结果转换为字符串形式，如果要将数据通过网络传输，就需要首先转换为字符串形式才能传输
 resultStr = json.dumps(persons)
 print(type(resultStr))
 print(resultStr)
 c.close()
 conn.close()

代码解析：

sqlite3.connect(dbPath) - 连接数据库（不存在则自动创建）
conn.cursor() - 创建游标对象
cursor.execute(sql) - 执行 SQL 语句
conn.commit() - 必须提交，否则数据不会保存！
cursor.fetchall() - 获取所有查询结果
cursor.close() / conn.close() - 必须关闭，释放资源

SQL 速查表

SQLite 支持标准 SQL 语句，下面是常用的 增删改查（CRUD）操作：

操作	SQL 语句	说明
增 (Create)	`INSERT INTO 表名 VALUES (...)`	插入新数据
删 (Delete)	`DELETE FROM 表名 WHERE 条件`	删除数据
改 (Update)	`UPDATE 表名 SET 列=值 WHERE 条件`	修改数据
查 (Retrieve)	`SELECT 列名 FROM 表名 WHERE 条件`	查询数据

查询数据 SELECT

1. 查询所有记录

 SELECT * FROM persons ORDER BY age DESC;

* - 查询所有列
ORDER BY age - 按 age 列排序（默认升序 ASC）
DESC - 降序排列（从大到小）

代码示例：

 # 导入sqlite3模块
 from sqlite3 import Error
 import sqlite3
 
 # try-except:防止因连接失败导致程序崩溃
 try:
     # 数据库文件路径
     db_file = 'data.sqlite'
     # 连接数据库
     conn = sqlite3.connect(db_file)
     # 创建游标
     cour = conn.cursor()
     # 查询语句sql：select 列名(*-所有列) from 表名 [where 条件] [order by 列名]
     sql = 'select * from persons order by age desc'
     # 执行sql语句
     cour.execute(sql)
     # 打印查询结果
     print(cour.fetchall())
     # 关闭游标
     cour.close()
     # 关闭连接
     conn.close()
 except Error as e:
     print('连接失败')

2. 条件查询

使用 WHERE 子句添加查询条件：

 SELECT * FROM persons WHERE name = '张三';

安全提示：使用 ? 占位符防止 SQL 注入！

 # 错误写法（有 SQL 注入风险）
 sql = f"SELECT * FROM persons WHERE name = '{user_input}'"
 
 # 正确写法（使用占位符）
 sql = "SELECT * FROM persons WHERE name = ?"
 cursor.execute(sql, ('张三',))  # 参数必须是元组

什么是 SQL 注入？

如果用户输入 ' OR '1'='1，错误写法会变成：

 SELECT * FROM persons WHERE name = '' OR '1'='1'

这将返回所有记录！使用 ? 占位符可以避免这种攻击。

# 导入sqlite3模块
 from sqlite3 import Error
 import sqlite3
 
 # try-except:防止因连接失败导致程序崩溃
 try:
     # 数据库文件路径
     db_file = 'data.sqlite'
     # 连接数据库
     conn = sqlite3.connect(db_file)
     # 创建游标
     cour = conn.cursor()
     # 编写sql语句
     # 查询语句sql：select 列名(*-所有列) from 表名 [where 条件]
     # ?-占位符，在cour.execute()参数中，传入数据元组
     sql = 'select * from persons where name=?'
     # 构建数据元组
     name = ('渣男教父',)
     # 执行sql语句
     cour.execute(sql, name)
     # 打印查询结果
     print(cour.fetchall())
     # 关闭游标
     cour.close()
     # 关闭连接
     conn.close()
 except Error as e:
     print('连接失败')

增加数据 INSERT

1. 插入单条记录

INSERT INTO persons(id, name, age, address, salary) 
VALUES (5, '托尼老弟', 35, '长沙', 6000.00);

代码示例：

# 导入sqlite3模块
 from sqlite3 import Error
 import sqlite3
 
 # try-except:防止因连接失败导致程序崩溃
 try:
     # 数据库文件路径
     db_file = 'data.sqlite'
     # 连接数据库
     conn = sqlite3.connect(db_file)
     # 创建游标
     cour = conn.cursor()
     # 编写sql语句
     # 添加语句sql：INSERT INTO 表名(列名)
     # VALUES (<列1的值>,[<列2的值>,<列3的值>]);
     # ?-占位符，在cour.execute()参数中，传入数据元组
     # 主键id也可设置为自增，自增的列可以不需要传入
     sql = 'INSERT INTO persons(id,name,age,address,salary) VALUES(?,?,?,?,?)'
     # 构建数据元组
     p_data = (5, '托尼老弟', 35, '长沙', 6000.00)
     # 执行sql语句
     cour.execute(sql, p_data)
     # 提交数据-同步到数据库文件-增删改查，除了查询以外有需要进行提交
     conn.commit()
     # 打印受影响的行数
     print(cour.rowcount)
     # 关闭游标
     cour.close()
     # 关闭连接
     conn.close()
 except Error as e:
     print('连接失败')

2. 插入多条记录

使用 executemany() 方法批量插入：

# 导入sqlite3模块
 from sqlite3 import Error
 import sqlite3
 
 # try-except:防止因连接失败导致程序崩溃
 try:
     # 数据库文件路径
     db_file = 'data.sqlite'
     # 连接数据库
     conn = sqlite3.connect(db_file)
     # 创建游标
     cour = conn.cursor()
     # 编写sql语句
     # 添加语句sql：INSERT INTO 表名(列名)
     # VALUES (<列1的值>,[<列2的值>,<列3的值>]);
     # ?-占位符，在cour.execute()参数中，传入数据元组
     sql = 'INSERT INTO persons(id,name,age,address,salary) VALUES(?,?,?,?,?)'
     # 构建数据元组列表
     p_data = [
         (6, '张三', 32, '北京', 50000.00),
         (7, '李四', 25, '上海', 15000.00),
         (8, '王五', 23, '深圳', 20000.00)
     ]
     # 执行sql语句
     cour.executemany(sql, p_data)
     # 提交数据-同步到数据库文件-增删改查，除了查询以外有需要进行提交
     conn.commit()
     # 打印受影响的行数
     print(cour.rowcount)
     # 关闭游标
     cour.close()
     # 关闭连接
     conn.close()
 except Error as e:
     print('连接失败')

修改数据 UPDATE

修改记录

 UPDATE persons SET salary = 106000 WHERE name = '渣男教父';

注意：不要忘记 WHERE 子句！

-- 危险！会修改所有记录的 salary
UPDATE persons SET salary = 106000;

-- 正确！只修改 name='渣男教父' 的记录
UPDATE persons SET salary = 106000 WHERE name = '渣男教父';

# 导入sqlite3模块
from sqlite3 import Error
import sqlite3

# try-except:防止因连接失败导致程序崩溃
try:
    # 数据库文件路径
    db_file = 'data.sqlite'
    # 连接数据库
    conn = sqlite3.connect(db_file)
    # 创建游标
    cour = conn.cursor()
    # 编写sql语句
    # 修改语句sql：UPDATE  <表名>
    # SET  <列名1>=<值1>[,<列名2>=<值2>]
    # [WHERE <条件>];
    # ?-占位符，在cour.execute()参数中，传入数据元组
    sql = 'update persons set salary=? where name=?'
    # 构建数据元组
    p_data = (106000, '渣男教父')
    # 执行sql语句
    cour.execute(sql, p_data)
    # 提交数据-同步到数据库文件-增删改查，除了查询以外有需要进行提交
    conn.commit()
    # 打印受影响的行数
    print(cour.rowcount)
    # 关闭游标
    cour.close()
    # 关闭连接
    conn.close()
except Error as e:
    print('连接失败')

删除数据 DELETE

1. 删除单条记录

DELETE FROM persons WHERE id = 1;

注意：DELETE 也需要 WHERE 子句！

-- 危险！会删除所有记录
DELETE FROM persons;

-- 正确！只删除 id=1 的记录
DELETE FROM persons WHERE id = 1;

# 导入sqlite3模块
 from sqlite3 import Error
 import sqlite3
 
 # try-except:防止因连接失败导致程序崩溃
 try:
     # 数据库文件路径
     db_file = 'data.sqlite'
     # 连接数据库
     conn = sqlite3.connect(db_file)
     # 创建游标
     cour = conn.cursor()
     # 编写sql语句
     # 删除语句sql：DELETE FROM 表名
     # WHERE <列名1>=<值1>
     # ?-占位符，在cour.execute()参数中，传入数据元组
     sql = 'delete from persons where id=?'
     # 构建数据元组
     p_data = (1,)
     # 执行sql语句
     cour.execute(sql, p_data)
     # 提交数据-同步到数据库文件-增删改查，除了查询以外有需要进行提交
     conn.commit()
     # 打印受影响的行数
     print(cour.rowcount)
     # 关闭游标
     cour.close()
     # 关闭连接
     conn.close()
 except Error as e:
     print('连接失败')

2. 删除多条记录

同样使用 executemany() 批量删除：

 # 导入sqlite3模块
 from sqlite3 import Error
 import sqlite3
 
 # try-except:防止因连接失败导致程序崩溃
 try:
     # 数据库文件路径
     db_file = 'data.sqlite'
     # 连接数据库
     conn = sqlite3.connect(db_file)
     # 创建游标
     cour = conn.cursor()
     # 编写sql语句
     # 删除语句sql：DELETE FROM 表名
     # WHERE <列名1>=<值1>
     # ?-占位符，在cour.execute()参数中，传入数据元组
     sql = 'delete from persons where id=?'
     # 构建数据元组列表
     p_data = [(2,), (3,)]
     # 执行sql语句
     cour.executemany(sql, p_data)
     # 提交数据-同步到数据库文件-增删改查，除了查询以外有需要进行提交
     conn.commit()
     # 打印受影响的行数
     print(cour.rowcount)
     # 关闭游标
     cour.close()
     # 关闭连接
     conn.close()
 except Error as e:
     print('连接失败')

文档总结

一、核心知识点回顾

1. 数据库基础

数据库：结构化存储和管理数据的软件系统
SQLite：轻量级嵌入式数据库，Python 内置支持
SQL：结构化查询语言，用于操作数据库

2. Python 操作数据库步骤

import sqlite3

# 1. 连接数据库
conn = sqlite3.connect('数据库文件.sqlite')

# 2. 创建游标
cursor = conn.cursor()

# 3. 执行 SQL
cursor.execute('SQL语句')

# 4. 提交事务（增删改需要）
conn.commit()

# 5. 关闭资源
cursor.close()
conn.close()

3. SQL 增删改查

操作	SQL 语句	Python 方法
增	`INSERT INTO ... VALUES (...)`	`execute()` / `executemany()`
删	`DELETE FROM ... WHERE ...`	`execute()` / `executemany()`
改	`UPDATE ... SET ... WHERE ...`	`execute()`
查	`SELECT ... FROM ... WHERE ...`	`execute()` + `fetchall()`

4. 安全最佳实践

使用 ? 占位符：防止 SQL 注入攻击
及时 commit：增删改操作后必须提交
关闭资源：使用 try-finally 或上下文管理器确保关闭
异常处理：捕获数据库操作异常

二、常见应用场景

数据持久化：将程序数据保存到数据库
数据查询：快速检索大量数据
数据管理：增删改查操作
数据分析：结合 pandas 进行数据分析
Web 开发：网站后端数据存储

三、学习建议

多练习 SQL：SQL 是数据库的核心，要多写多练
理解事务：掌握 commit 和 rollback
注意安全：始终使用占位符防止 SQL 注入
善用工具：使用 DB Browser 可视化查看数据
阅读文档：遇到问题时查阅官方文档

四、下篇预告

下一篇我们将学习：

openpyxl 操作 Excel
读取和写入 Excel 文件
批量处理 Excel 数据
数据格式转换

掌握数据库操作 + Excel 操作，你就能实现数据的导入导出了！

动手练一练

编程题

1. 学生成绩管理系统

创建一个学生成绩管理系统：

创建 students.sqlite 数据库
创建 students 表（id, name, chn, math, eng, total）
插入若干学生数据
计算每个学生的总分并更新
查询并显示所有学生成绩（按总分降序）

参考答案

import sqlite3
 import os
 
 # 数据库文件路径
 db_path = 'students.sqlite'
 
 # 如果数据库已存在，先删除（方便重复运行）
 if os.path.exists(db_path):
     os.remove(db_path)
 
 # 1. 连接数据库
 conn = sqlite3.connect(db_path)
 cursor = conn.cursor()
 
 print("开始创建学生成绩管理系统...\n")
 
 # 2. 创建 students 表
 cursor.execute('''
     CREATE TABLE students (
         id INTEGER PRIMARY KEY,
         name TEXT NOT NULL,
         chn INTEGER,
         math INTEGER,
         eng INTEGER,
         total INTEGER DEFAULT 0
     )
 ''')
 print("表创建成功！")
 
 # 3. 插入数据
 students_data = [
     (1, '张三', 96, 96, 98, 0),
     (2, '李四', 94, 95, 90, 0),
     (3, '王五', 90, 92, 95, 0),
     (4, '赵六', 97, 100, 96, 0)
 ]
 
 cursor.executemany(
     'INSERT INTO students (id, name, chn, math, eng, total) VALUES (?, ?, ?, ?, ?, ?)',
     students_data
 )
 print(f"插入了 {cursor.rowcount} 条学生记录！")
 
 # 4. 更新总分
 cursor.execute('''
     UPDATE students 
     SET total = chn + math + eng
 ''')
 print(f"更新了 {cursor.rowcount} 条记录的总分！")
 
 # 提交事务
 conn.commit()
 
 # 5. 查询并显示结果（按总分降序）
 print("\n" + "=" * 60)
 print("学生成绩表（按总分降序）")
 print("=" * 60)
 print(f"{'学号':<6}{'姓名':<12}{'语文':<8}{'数学':<8}{'英语':<8}{'总分':<8}")
 print("-" * 60)
 
 cursor.execute('SELECT * FROM students ORDER BY total DESC')
 for row in cursor.fetchall():
     id, name, chn, math, eng, total = row
     print(f"{id:<6}{name:<12}{chn:<8}{math:<8}{eng:<8}{total:<8}")
 
 print("=" * 60)
 
 # 统计信息
 cursor.execute('SELECT AVG(total), MAX(total), MIN(total) FROM students')
 avg, max_score, min_score = cursor.fetchone()
 print(f"\n统计信息：")
 print(f"   平均分：{avg:.2f}")
 print(f"   最高分：{max_score}")
 print(f"   最低分：{min_score}")
 
 # 6. 关闭连接
 cursor.close()
 conn.close()
 
 print("\n操作完成，数据库已保存！")

2. 简易图书管理系统

要求：

创建 library.sqlite 数据库
创建 books 表，包含：
- id（INTEGER，主键，自增）
- title（TEXT，书名）
- author（TEXT，作者）
- price（REAL，价格）
- stock（INTEGER，库存）
实现以下功能函数：
- add_book(title, author, price, stock) - 添加图书
- query_all_books() - 查询所有图书
- query_by_author(author) - 按作者查询
- update_stock(id, new_stock) - 更新库存
- delete_book(id) - 删除图书