Learn everything you need to know about databases in Python - SQLite!

SQLite is a lightweight database that is ideal for embedded systems, mobile applications, and small projects. The sqlite3 module of Python provides powerful support for SQLite databases, making database operations in Python easy and fast. By mastering the above SQLite statements and Python code examples, you can effectively use SQLite databases for data storage and management in your Python projects.

1. Create and connect to the database

The first step in using SQLite in Python is to create and connect to a database. This can be done using the sqlite3 module.

import sqlite3

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

2. Create a table

Creating tables is the basis for database operations. In SQLite, you can use the CREATE TABLE statement to create a new 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 data

You can use the INSERT INTO statement to insert data into a table.

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

4. Query the data

Querying data is one of the most commonly used functions in database operations. The SELECT statement is used to retrieve data from a table.

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

5. Update your data

The UPDATE statement is used to update the existing data in the table.

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

6. Deletion of Data

DELETE statements are used to delete data from a table.

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

7. Transaction Processing

SQLite supports transactions to ensure the integrity of database operations.

# 开始事务
conn.execute("BEGIN")

# 执行一些操作
# ...

# 提交事务
conn.commit()           

8. Create an index

To improve query efficiency, you can create indexes on tables.

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

9. Database Backup and Recovery

SQLite provides a simple backup and recovery mechanism.

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

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

10. Use Functions and Triggers

SQLite supports the use of functions and triggers to automate certain actions.

# 创建一个触发器
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. Database Optimization

You can use the ANALYZE and VACUUM statements to optimize database performance.

# 分析表格
cursor.execute("ANALYZE students")

# 清理并优化数据库
conn.execute("VACUUM")           

12. Common SQLite statements

Here are some commonly used SQLite statements:

1. SELECT - Select the data
2. INSERT INTO - 插入数据
3. UPDATE - 更新数据
4. DELETE - 删除数据
5. CREATE TABLE - 创建新表格
6. ALTER TABLE - 修改表格结构
7. DROP TABLE - 删除表格
8. CREATE INDEX - 创建索引
9. DROP INDEX - 删除索引
10. SELECT DISTINCT - 选择唯一记录
11. ORDER BY - 排序查询结果
12. GROUP BY - 按列分组
13. HAVING - Filtered grouped data
14. LIMIT—Limits the number of query results
15. JOIN—Connect the form
16. LEFT JOIN - 左连接
17. RIGHT JOIN - 右连接
18. FULL OUTER JOIN - 全外连接
19. UNION—Merges the results of two queries
20. EXCEPT—Excludes duplicate rows from both query results