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 - 排除两个查询结果中的重复行