掌握Python与SQLite的完全指南

发表时间: 2024-04-13 17:03

SQLite是一个轻量级的数据库，非常适合嵌入式系统、移动应用和小型项目。Python的sqlite3模块为SQLite数据库提供了强大的支持，使得在Python中进行数据库操作变得简单快捷。通过掌握上述的SQLite语句和Python代码实例，你可以有效地在Python项目中使用SQLite数据库进行数据存储和管理。

1. 创建和连接数据库

在Python中使用SQLite的第一步是创建和连接到数据库。可以使用sqlite3模块来完成这个任务。

import sqlite3# 连接到数据库，如果数据库不存在则创建conn = sqlite3.connect('mydatabase.db')

2. 创建表格

创建表格是数据库操作的基础。在SQLite中，可以使用CREATE TABLE语句来创建一个新的表格。

cursor = conn.cursor()# 创建一个名为"students"的表格cursor.execute('''CREATE TABLE IF NOT EXISTS students (                  id INTEGER PRIMARY KEY,                  name TEXT NOT NULL,                  age INTEGER              )''')conn.commit()

3. 插入数据

插入数据到表格中可以使用INSERT INTO语句。

# 插入一名学生的信息cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ('Alice', 25))

4. 查询数据

查询数据是数据库操作中最常用的功能之一。SELECT语句用于从表格中检索数据。

# 查询所有学生的信息cursor.execute("SELECT * FROM students")rows = cursor.fetchall()for row in rows:    print(row)

5. 更新数据

UPDATE语句用于更新表格中的现有数据。

# 更新名为Alice的学生的年龄cursor.execute("UPDATE students SET age = ? WHERE name = ?", (26, 'Alice'))

6. 删除数据

DELETE语句用于从表格中删除数据。

# 删除名为Bob的学生记录cursor.execute("DELETE FROM students WHERE name = 'Bob'")

7. 事务处理

SQLite支持事务，可以确保数据库操作的完整性。

# 开始事务conn.execute("BEGIN")# 执行一些操作# ...# 提交事务conn.commit()

8. 创建索引

为了提高查询效率，可以在表格上创建索引。

# 创建索引cursor.execute("CREATE INDEX idx_name ON students (name)")

9. 数据库备份和恢复

SQLite提供了简单的备份和恢复机制。

# 备份数据库conn.execute("BACKUP DATABASE ? TO ?", ('mydatabase.db', 'mydatabase_backup.db'))# 恢复数据库conn.execute("RESTORE DATABASE ? FROM ?", ('mydatabase.db', 'mydatabase_backup.db'))

10. 使用函数和触发器

SQLite支持使用函数和触发器来自动化某些操作。

# 创建一个触发器cursor.execute("CREATE TRIGGER update_age BEFORE INSERT ON students FOR EACH ROW BEGIN UPDATE students SET age = NEW.age + 1 WHERE name = NEW.name; END")# 使用函数cursor.execute("SELECT length(name) FROM students")

11. 数据库优化

使用ANALYZE和VACUUM语句可以优化数据库性能。

# 分析表格cursor.execute("ANALYZE students")# 清理并优化数据库conn.execute("VACUUM")

12. 常用的SQLite语句

以下是一些常用的SQLite语句：

SELECT - 选择数据
INSERT INTO - 插入数据
UPDATE - 更新数据
DELETE - 删除数据
CREATE TABLE - 创建新表格
ALTER TABLE - 修改表格结构
DROP TABLE - 删除表格
CREATE INDEX - 创建索引
DROP INDEX - 删除索引
SELECT DISTINCT - 选择唯一记录
ORDER BY - 排序查询结果
GROUP BY - 按列分组
HAVING - 过滤分组后的数据
LIMIT - 限制查询结果数量
JOIN - 连接表格
LEFT JOIN - 左连接
RIGHT JOIN - 右连接
FULL OUTER JOIN - 全外连接
UNION - 合并两个查询结果
EXCEPT - 排除两个查询结果中的重复行