21｜SQLite文本数据库：如何进行数据管理（上）？-Python自动化办公实战

当前位置:　首页>> 技术小册>> Python自动化办公实战

21｜SQLite文本数据库：如何进行数据管理（上）

在数字化办公日益普及的今天，数据管理成为了提升工作效率与决策能力的关键环节。Python，凭借其强大的数据处理能力和丰富的库支持，成为了自动化办公的得力助手。其中，SQLite作为一款轻量级的嵌入式数据库，以其无需配置、直接读写文件、易于集成到应用程序中等特点，在自动化办公场景中尤为受欢迎。本章将深入探讨如何使用Python与SQLite进行基本的数据管理操作，为构建高效的数据处理流程打下坚实基础。

一、SQLite简介

SQLite是一个开源的、轻量级的、高性能的、独立的、事务性的SQL数据库引擎。它不需要一个独立的服务器进程或操作系统级别的安装。SQLite数据库本身就是一个存储在磁盘上的文件，这使得它非常适合用于轻量级应用、桌面应用以及作为应用程序内部的数据存储解决方案。Python通过sqlite3模块提供了对SQLite数据库的全面支持，使得在Python程序中操作SQLite数据库变得非常简单。

二、SQLite数据库的基本操作

2.1 连接到SQLite数据库

在Python中，使用sqlite3模块操作SQLite数据库的第一步是连接到数据库。如果数据库文件不存在，SQLite会自动创建它。连接数据库的基本语法如下：

import sqlite3
# 连接到SQLite数据库
# 如果文件不存在，会自动在当前目录创建:
conn = sqlite3.connect('example.db')
# 创建一个Cursor对象，通过它执行SQL命令
cursor = conn.cursor()
# 执行完操作后，不要忘记关闭Cursor和Connection
# cursor.close()
# conn.close()
# 注意：为了保持示例的连续性，这里暂不关闭

2.2 创建表

在SQLite中，表是数据存储的基本结构。通过执行SQL CREATE TABLE 语句，可以在数据库中创建新表。以下是一个创建用户信息表的例子：

# 创建用户信息表
create_table_sql = '''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT UNIQUE NOT NULL
);
'''
cursor.execute(create_table_sql)
# 提交事务
conn.commit()

这段代码首先检查users表是否已存在，如果不存在则创建。表中包含四个字段：id（主键，自增）、name（姓名，非空）、age（年龄，可为空）、email（邮箱，唯一且非空）。

2.3 插入数据

向表中插入数据使用INSERT INTO语句。以下是一个向users表插入新记录的示例：

# 插入数据
insert_data_sql = '''
INSERT INTO users (name, age, email)
VALUES ('Alice', 30, 'alice@example.com');
'''
cursor.execute(insert_data_sql)
# 提交事务
conn.commit()

每次执行插入、更新或删除操作后，都需要调用conn.commit()来提交事务，以确保更改被保存到数据库中。

2.4 查询数据

查询数据是数据库操作中最常见的需求之一。在SQLite中，使用SELECT语句从表中检索数据。以下是一个查询所有用户信息的示例：

# 查询所有用户
query_all_sql = 'SELECT * FROM users;'
cursor.execute(query_all_sql)
# 获取查询结果
rows = cursor.fetchall()
for row in rows:
    print(row)

fetchall()方法返回查询结果的所有行，每一行是一个元组（tuple），包含了查询结果的一列数据。

三、数据管理的高级技巧

3.1 使用参数化查询防止SQL注入

在执行SQL语句时，特别是当SQL语句中包含用户输入的数据时，直接使用字符串拼接构造SQL语句可能会导致SQL注入攻击。为了避免这种风险，应使用参数化查询。

# 参数化查询
user_name = 'Bob'
user_email = 'bob@example.com'
insert_data_parameterized = '''
INSERT INTO users (name, email)
VALUES (?, ?);
'''
cursor.execute(insert_data_parameterized, (user_name, user_email))
conn.commit()

在这个例子中，?是参数占位符，execute()方法的第二个参数是一个包含所有参数的元组。这种方式可以有效防止SQL注入。

3.2 更新与删除数据

更新和删除数据分别使用UPDATE和DELETE语句。以下是使用这些语句的示例：

# 更新数据
update_data_sql = '''
UPDATE users
SET age = ?
WHERE name = ?;
'''
new_age = 31
target_name = 'Alice'
cursor.execute(update_data_sql, (new_age, target_name))
conn.commit()
# 删除数据
delete_data_sql = '''
DELETE FROM users
WHERE name = ?;
'''
target_name_for_delete = 'Bob'
cursor.execute(delete_data_sql, (target_name_for_delete,))
conn.commit()

注意，在执行删除操作时要格外小心，因为一旦数据被删除，就很难恢复（除非有备份）。

四、总结与展望

在本章中，我们学习了如何使用Python的sqlite3模块进行SQLite数据库的基本操作，包括连接数据库、创建表、插入数据、查询数据以及更新和删除数据。同时，我们也探讨了如何使用参数化查询来防止SQL注入攻击，这是保证数据库安全性的重要措施。

然而，数据管理远不止于此。在下一章节中，我们将继续深入探讨SQLite数据库的进阶应用，包括事务处理、触发器、索引的使用、以及如何通过Python脚本自动化地备份和恢复SQLite数据库等高级话题。通过这些内容的学习，你将能够更加熟练地运用SQLite数据库来支持你的自动化办公需求，进一步提升工作效率和数据处理能力。