数据库基本操作

SQLite 数据库基本操作

小象实战讲义 · Python数据分析实战

在数据分析工作中，我们经常需要从数据库中获取数据。SQLite 是一个轻量级的关系型数据库，它无需独立的服务器进程，整个数据库就是一个文件，非常适合入门学习和本地开发。本节我们将学习如何使用 Python 的 sqlite3 标准库进行数据库的连接、表的创建、数据的增删改查等基本操作。

💡 核心导读

• SQLite 特点：轻量级、无服务器、文件式的关系型数据库。
• 操作流程：连接数据库 → 获取游标 → 执行 SQL → 提交更改 → 关闭连接。
• 核心方法：execute() 执行单条 SQL，executemany() 批量插入，fetchone()/fetchall() 获取查询结果。
• 实战演练：从创建数据库、建表、插入数据到查询数据的完整流程。

SQLite 简介

SQLite 是一个关系型数据库管理系统，但它与我们熟知的 MySQL、PostgreSQL 等数据库有一个显著区别：SQLite 不是一个客户端/服务器（C/S）架构的数据库引擎。这意味着它不需要安装独立的数据库服务器软件，也没有“服务器进程”与“客户端进程”的区分。

SQLite 的核心特点可以概括为：

• 轻量级、嵌入式：整个数据库就是一个独立的文件（例如 test.db），可以直接集成到应用程序中。无需额外的管理软件，这使得它在浏览器、移动设备和嵌入式系统中被广泛使用。
• 功能完备：虽然轻量，但它实现了 SQL 标准的大多数功能，支持常见的 SQL 语句（如 SELECT, INSERT, UPDATE, DELETE 等）以及事务处理。
• 零配置：使用 SQLite 时，你只需要一个数据库文件路径，无需配置连接参数、用户权限等。

正是由于这些特点，我们选择 SQLite 作为数据分析入门学习的数据库工具。

使用 Python 操作 SQLite 的完整流程

操作数据库的流程类似于操作文件：先“打开”，然后进行读写，最后“关闭”。具体步骤如下：

1. 连接数据库：相当于打开数据库文件，获取访问权限。
2. 获取游标：通过游标（Cursor）来执行具体的 SQL 命令。
3. 执行 SQL 操作：使用游标执行增删改查（CRUD）等 SQL 语句。
4. 提交更改：对于修改数据的操作，必须提交（commit）才能使更改永久生效。
5. 关闭连接：操作完成后，关闭连接以释放系统资源。

下面我们通过代码来详细演示每一步。

第一步：连接数据库与获取游标

Python 标准库中的 sqlite3 模块提供了操作 SQLite 的所有功能。使用 sqlite3.connect() 函数连接数据库。如果指定的数据库文件存在，则打开它；如果不存在，则会创建一个新的数据库文件。

import sqlite3

# 连接数据库。如果 'test.db' 文件不存在，则会自动创建。
conn = sqlite3.connect('test.db')
print("数据库连接成功。")

# 获取游标（Cursor）。游标是执行 SQL 语句的工作区。
cur = conn.cursor()
print("游标获取成功。")

数据库连接成功。
游标获取成功。

第二步：执行 SQL 语句与查询

获取游标后，就可以使用 cursor.execute() 方法来执行任何 SQL 语句。我们先执行一个简单的查询来验证环境。

import sqlite3

# 每个代码块独立运行，需要重新连接和获取游标
conn = sqlite3.connect('test.db')
cur = conn.cursor()

# 查询 SQLite 的版本
cur.execute("SELECT SQLITE_VERSION();")
version = cur.fetchone()  # fetchone() 获取单条记录，返回一个元组
print("SQLite 数据库版本:", version[0])

# 查询当前数据库中有哪些表
# sqlite_master 是 SQLite 的一个特殊系统表，存储所有表的信息
cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
tables = cur.fetchall()  # fetchall() 获取所有记录，返回一个列表
print("当前数据库中的表:", tables)

# 关闭游标和连接（好的习惯是操作完成后立即关闭）
cur.close()
conn.close()

SQLite 数据库版本: 3.50.4
当前数据库中的表: [('book',)]

输出显示数据库版本，以及当前数据库中已有的表。若你此前运行过本节示例，列表中会看到 book 表——test.db 是磁盘上的持久化文件，重复运行时旧表会保留（这正是下一步先 DROP TABLE IF EXISTS 再建表的原因）；如果是全新首次运行，表列表则为空。

第三步：创建表与插入数据

接下来，我们创建一个名为 book 的表来存储图书信息，并向其中插入数据。

import sqlite3

conn = sqlite3.connect('test.db')
cur = conn.cursor()

# 1. 创建表之前，先删除已存在的同名表（避免冲突）
cur.execute("DROP TABLE IF EXISTS book;")

# 2. 创建 book 表，定义三个字段：id(整数), name(文本), price(浮点数)
create_table_sql = """
CREATE TABLE book (
    id INT,
    name TEXT,
    price DOUBLE
);
"""
cur.execute(create_table_sql)
print("表 'book' 创建成功。")

# 3. 向表中插入单条数据
insert_sql = "INSERT INTO book VALUES(1, 'Python编程从入门到实践', 89.00);"
cur.execute(insert_sql)
print("单条数据插入成功。")

# 4. 批量插入多条数据
# 使用 executemany() 和参数化查询（用 ? 作为占位符），更安全高效。
more_books = [
    (2, '数据科学导论', 65.50),
    (3, '机器学习实战', 118.00),
    (4, 'SQL必知必会', 45.00),
    (5, '深入浅出统计学', 76.80)
]
cur.executemany("INSERT INTO book VALUES(?, ?, ?)", more_books)
print("批量数据插入成功。")

# ！！！重要：所有修改数据的操作，必须提交才能生效。
conn.commit()
print("数据更改已提交。")

# 查询验证数据是否插入
cur.execute("SELECT COUNT(*) FROM book;")
count = cur.fetchone()[0]
print(f"当前 book 表中共有 {count} 条记录。")

cur.close()
conn.close()

表 'book' 创建成功。
单条数据插入成功。
批量数据插入成功。
数据更改已提交。
当前 book 表中共有 5 条记录。

第四步：数据的查询、更新与删除

现在表中已有数据，我们来练习查询、更新和删除操作。

import sqlite3

conn = sqlite3.connect('test.db')
cur = conn.cursor()

print("=== 初始数据查询 ===")
cur.execute("SELECT * FROM book;")
all_books = cur.fetchall()
for book in all_books:
    print(f"ID: {book[0]}, 书名: {book[1]}, 价格: {book[2]}")

print("\n=== 条件查询：价格低于80元的书 ===")
cur.execute("SELECT name, price FROM book WHERE price < 80.00;")
cheap_books = cur.fetchall()
for book in cheap_books:
    print(f"书名: {book[0]}, 价格: {book[1]}")

print("\n=== 更新数据：将《机器学习实战》价格调整为99元 ===")
cur.execute("UPDATE book SET price = 99.00 WHERE name = '机器学习实战';")
conn.commit()
cur.execute("SELECT price FROM book WHERE name = '机器学习实战';")
updated_price = cur.fetchone()[0]
print(f"更新后的价格: {updated_price}")

print("\n=== 删除数据：删除价格高于100元的书 ===")
cur.execute("DELETE FROM book WHERE price > 100.00;")
conn.commit()
cur.execute("SELECT COUNT(*) FROM book;")
new_count = cur.fetchone()[0]
print(f"删除后剩余记录数: {new_count}")

print("\n=== 最终数据 ===")
cur.execute("SELECT * FROM book;")
final_books = cur.fetchall()
for book in final_books:
    print(f"ID: {book[0]}, 书名: {book[1]}, 价格: {book[2]}")

cur.close()
conn.close()

=== 初始数据查询 ===
ID: 1, 书名: Python编程从入门到实践, 价格: 89.0
ID: 2, 书名: 数据科学导论, 价格: 65.5
ID: 3, 书名: 机器学习实战, 价格: 118.0
ID: 4, 书名: SQL必知必会, 价格: 45.0
ID: 5, 书名: 深入浅出统计学, 价格: 76.8

=== 条件查询：价格低于80元的书 ===
书名: 数据科学导论, 价格: 65.5
书名: SQL必知必会, 价格: 45.0
书名: 深入浅出统计学, 价格: 76.8

=== 更新数据：将《机器学习实战》价格调整为99元 ===
更新后的价格: 99.0

=== 删除数据：删除价格高于100元的书 ===
删除后剩余记录数: 5

=== 最终数据 ===
ID: 1, 书名: Python编程从入门到实践, 价格: 89.0
ID: 2, 书名: 数据科学导论, 价格: 65.5
ID: 3, 书名: 机器学习实战, 价格: 99.0
ID: 4, 书名: SQL必知必会, 价格: 45.0
ID: 5, 书名: 深入浅出统计学, 价格: 76.8

📝 动手练一练

1. 概念辨析：SQLite 和 MySQL 这类数据库在架构上最主要的区别是什么？这种区别给 SQLite 带来了哪些优点和局限性？
2. 代码实战：基于上面最终的 book 表，请编写一个 Python 程序，实现以下功能： a) 向表中插入一本新书，书名为“算法图解”，价格为 49.00 元。 b) 查询出所有价格低于 80 元的图书，并只打印出它们的书名和价格。 c) 计算所有图书的平均价格。

import sqlite3

conn = sqlite3.connect('test.db')
cur = conn.cursor()

# a) 插入新图书
cur.execute("INSERT INTO book VALUES(6, '算法图解', 49.00);")
conn.commit()
print("新图书插入成功。")

# b) 查询价格低于80元的图书
cur.execute("SELECT name, price FROM book WHERE price < 80.00;")
affordable_books = cur.fetchall()
print("\n价格低于80元的图书：")
for book in affordable_books:
    print(f"  书名: {book[0]}, 价格: {book[1]}")

# c) 计算平均价格
cur.execute("SELECT AVG(price) FROM book;")
avg_price = cur.fetchone()[0]
print(f"\n所有图书的平均价格为: {avg_price:.2f} 元")

cur.close()
conn.close()

新图书插入成功。

价格低于80元的图书：
  书名: 数据科学导论, 价格: 65.5
  书名: SQL必知必会, 价格: 45.0
  书名: 深入浅出统计学, 价格: 76.8
  书名: 算法图解, 价格: 49.0

所有图书的平均价格为: 70.72 元

本章小结

本节我们掌握了使用 Python 操作 SQLite 数据库的核心技能。SQLite 以其轻量、易用的特性，成为本地数据存储和入门学习的绝佳选择。

📋 行动清单

• 动手创建：在你的项目目录下，用 Python 代码创建一个新的 SQLite 数据库文件。
• 练习 CRUD：在自建的数据库中，创建一个表，并完成数据的插入、查询、更新和删除全套操作。
• 思考场景：结合你的工作或学习，想一想哪些场景适合使用 SQLite 来管理本地数据（例如：缓存数据、配置存储、小型项目原型等）。

—— 小象教研组