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会自动创建它。连接数据库的基本语法如下：

```python
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` 语句，可以在数据库中创建新表。以下是一个创建用户信息表的例子：

```python
# 创建用户信息表
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`表插入新记录的示例：

```python
# 插入数据
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`语句从表中检索数据。以下是一个查询所有用户信息的示例：

```python
# 查询所有用户
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注入攻击。为了避免这种风险，应使用参数化查询。

```python
# 参数化查询
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`语句。以下是使用这些语句的示例：

```python
# 更新数据
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数据库来支持你的自动化办公需求，进一步提升工作效率和数据处理能力。

该分类下的相关小册推荐：

Python与办公-玩转Excel

Python数据分析与挖掘实战(上)

Python合辑4-130个字符串操作示例

Python与办公-玩转PPT

Python编程轻松进阶(四)

Python合辑13-面向对象编程案例(上)

Python合辑12-面向对象

Python合辑8-变量和运算符

Python爬虫入门与实战开发(下)

机器学习算法原理与实战

Python合辑3-字符串用法深度总结

Python合辑14-面向对象编程案例(下)