SQLite

背景：工作项目使用SQLite记录log，因此接触到相关读写。

1. SQLite 简介

  • SQLite 是一个轻量级、嵌入式的关系型数据库管理系统。
  • 它将整个数据库（定义、表、索引和数据）存储在一个单独的文件中。
  • SQLite 是跨平台的，广泛用于移动应用、客户端-服务器应用等。

2. 在 Python 中使用 SQLite

Python 提供了内置的 sqlite3 模块来直接与 SQLite 数据库进行交互。

2.1 安装

默认情况下，sqlite3 模块包含在Python的标准库中，所以不需要额外安装。

2.2 连接数据库

要连接到一个 SQLite 数据库，使用 sqlite3.connect() 方法：

1
2
3
4
5
6
7
import sqlite3

# 连接到（或创建）数据库
connection = sqlite3.connect('example.db')

# 获取游标对象
cursor = connection.cursor()

2.3 创建表

使用 SQL 语句创建表：

1
2
3
4
5
6
7
8
9
# 创建一个表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER,
        grade TEXT
    )
''')

可以看出语法比较简单，students为表名，下面第一列列名，第二列类型。主KEY除了例中给出的指定方式外，还可以直接以PRIMARY KEY (XXX, XXX)的方式指定复合主键。

此外，还有外键指定：FOREIGN KEY (XXX) REFERENCES 关联表名(关联列名)

关于主键外键：

主键 (Primary Key)

  • 定义: 主键是一列或多列，其值能够唯一标识表中的每一行数据。每个表只能有一个主键（但主键本身可以是复合多列的）。
  • 特性:
    • 唯一性: 在同一张表内，不允许有两个相同的主键值。
    • 非空性: 主键列不允许为空值 (NULL)。
  • 作用: 主键主要用于保证数据的完整性，帮助快速查找特定的数据记录。

外键 (Foreign Key)

  • 定义: 外键是指一张表中的某一列或某些列，这些列的值对应于另一张表的主键值。换句话说，外键用来建立两张表之间的关联。
  • 特性:
    • 参照完整性: 外键的值必须匹配另一张表中某个主键的值，或者可以为空 (NULL)。
  • 作用: 外键有助于维护不同表之间的一致性和参照完整性，使得数据库的设计更加合理化。

示例

假设我们有两个表：users 和 orders。

  • users 表中有字段 user_id, name, email，其中 user_id 是主键。
  • orders 表中有字段 order_id, product, quantity, user_id，其中 order_id 是主键，而 user_id 是外键，指向 users 表中的 user_id。

这样设计的好处在于，可以通过 user_id 将订单信息与用户信息联系起来，从而实现更复杂的数据查询和管理功能。

2.4 插入数据

向表中插入数据：

1
2
3
4
5
6
7
8
# 插入数据
cursor.execute('''
    INSERT INTO students (name, age, grade)
    VALUES (?, ?, ?)
''', ('Alice', 20, 'A'))

# 提交事务
connection.commit()

2.5 查询数据

使用 SELECT 语句来查询数据：

1
2
3
4
5
6
# 查询数据
cursor.execute('SELECT * FROM students')
students = cursor.fetchall()

for student in students:
    print(student)

如果需要查询指定列，将*替换成列名即可（可以多列）。

2.6 更新数据

使用 UPDATE 语句来更新数据：

1
2
3
4
5
6
7
# 更新数据
cursor.execute('''
    UPDATE students
    SET grade = ?
    WHERE name = ?
''', ('A+', 'Alice'))
connection.commit()

2.7 删除数据

使用 DELETE 语句来删除数据：

1
2
3
4
5
6
# 删除数据
cursor.execute('''
    DELETE FROM students
    WHERE name = ?
''', ('Alice',))
connection.commit()

2.8 关闭连接

操作完成后，关闭连接以释放资源：

1
2
3
# 关闭游标和连接
cursor.close()
connection.close()

2.9 完整示例项目

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
import sqlite3

def create_connection(db_file):
    return sqlite3.connect(db_file)

def create_table(conn):
    sql_create_table = """
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        age INTEGER,
        grade TEXT
    );"""
    conn.execute(sql_create_table)

def insert_student(conn, student):
    sql_insert = 'INSERT INTO students (name, age, grade) VALUES (?, ?, ?)'
    conn.execute(sql_insert, student)
    conn.commit()

def query_students(conn):
    cursor = conn.execute('SELECT * FROM students')
    return cursor.fetchall()

def main():
    database = "example.db"
    conn = create_connection(database)
    
    with conn:
        create_table(conn)
        insert_student(conn, ('Bob', 22, 'B'))
        insert_student(conn, ('Eve', 21, 'A'))
        
        students = query_students(conn)
        for student in students:
            print(student)

if __name__ == '__main__':
    main()

3. 可视化读表软件

DB Browser for SQLite 是一个开源的可视化工具，可用于管理 SQLite 数据库。它提供了一个直观的用户界面，可以方便地执行各种数据库操作，包括创建和修改表、插入和导出数据、执行 SQL 查询等。

这个是目前最常用的，截至本笔记编辑期间，还在活跃更新。