SQLite3数据库的操作与调用【linux版】

SQLite3 完整使用教程

第一部分：SQLite3 命令行操作

1. 安装 SQLite3

Ubuntu/Debian 系统

sudo apt-get update
sudo apt-get install sqlite3
sudo apt-get install libsqlite3-dev  # C语言开发库

CentOS/RHEL 系统

sudo yum install sqlite
# 或者
sudo dnf install sqlite

2. 启动与退出

创建并打开数据库文件

sqlite3 test.db

使用内存数据库（不创建文件）

sqlite3

退出 SQLite3

.quit

或者

.exit

3. SQLite3 系统命令 (以 . 开头)

进入数据库界面后，即可输入SQLite3系统命令，如果出现...>的模式，CTRL + d 结束，或者ctrl + z

显示帮助信息

.help

查看所有数据库

.databases

查看所有表

.tables

查看指定表的结构

.schema student

查看所有对象的结构

.schema

设置友好的显示格式

.mode column
.headers on

导出整个数据库到 SQL 文件

.output backup.sql
.dump
.output stdout

从 SQL 文件导入数据

.read backup.sql

4. SQL 语句操作 (以 ; 结尾)

4.1 创建表 (CREATE)

创建一个学生表

CREATE TABLE student (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    score REAL,
    address TEXT
);

4.2 插入数据 (INSERT)

插入一条完整数据

INSERT INTO student VALUES (1, '张三', 20, 85.5, '北京');

插入部分数据，指定列名

INSERT INTO student (name, age, score) VALUES ('李四', 21, 92.0);

一次插入多条数据

INSERT INTO student (name, age, score) VALUES 
    ('王五', 19, 78.5),
    ('赵六', 22, 88.0);

4.3 查询数据 (SELECT)

查询所有数据

SELECT * FROM student;

查询特定字段

SELECT name, score FROM student;

按条件查询

SELECT * FROM student WHERE score > 80;

4.4 更新数据 (UPDATE)

更新单个字段

UPDATE student SET score = 95.0 WHERE name = '张三';

4.5 删除数据 (DELETE / DROP)

删除特定记录

DELETE FROM student WHERE name = '王五';

删除整张表（结构和数据）

DROP TABLE student;

第二部分：C语言中使用 SQLite3

在使用 C 语言 API 之前，我们先来详细了解几个核心函数。

C API 核心函数详解

1. sqlite3_open()

• 功能: 打开一个数据库连接。如果数据库文件不存在，则会创建它。
• 原型: int sqlite3_open(const char *filename, sqlite3 **ppDb);
• 参数:
  • filename: 数据库文件的路径。如果传入 :memory:，则会创建一个内存数据库。
  • ppDb: 一个指向 sqlite3 指针的指针。如果函数成功，它会把创建的数据库连接句柄（一个 sqlite3* 类型的指针）存入这个地址。
• 返回值: 成功时返回 SQLITE_OK，失败时返回其他错误码。

2. sqlite3_close()

• 功能: 关闭一个数据库连接，释放相关资源。
• 原型: int sqlite3_close(sqlite3 *db);
• 参数:
  • db: sqlite3_open() 返回的数据库连接句柄。
• 返回值: 成功时返回 SQLITE_OK。如果还有未完成的查询，可能会返回 SQLITE_BUSY。

3. sqlite3_exec()

• 功能: 执行一条或多条 SQL 语句。这是最常用的函数之一，适用于不返回数据的操作（如 INSERT, UPDATE, DELETE, CREATE）或需要简单处理查询结果的场景。
• 原型: int sqlite3_exec(sqlite3 *db, const char *sql, int (*callback)(void*,int,char**,char**), void *pArg, char **errmsg);
• 参数:
  • db: 数据库连接句柄。
  • sql: 要执行的 SQL 语句字符串。
  • callback: 回调函数指针。对于 SELECT 查询，每找到一行数据，SQLite 就会调用这个函数一次。如果不需要回调，可以设为 NULL。
  • pArg: 传递给回调函数的第一个参数，可用于在不同调用间传递数据。
  • errmsg: 用于存放错误信息的指针。如果发生错误，SQLite 会在这里分配内存并存入错误描述，使用后需要调用 sqlite3_free() 释放。
• 返回值: 成功时返回 SQLITE_OK。

4. 回调函数 (Callback)

• 功能: sqlite3_exec 的回调函数，用于处理 SELECT 语句的每一行结果。
• 原型: int callback(void *pArg, int argc, char **argv, char **azColName);
• 参数:
  • pArg: sqlite3_exec 的第四个参数，原样传入。
  • argc: 结果集中的列数（字段数量）。
  • argv: 一个字符串数组，存放当前行每个字段的值。argv[0] 是第一个字段，argv[1] 是第二个，依此类推。
  • azColName: 一个字符串数组，存放每个字段的名称。
• 返回值: 如果返回 0，则继续处理下一行；如果返回非零值，则中断 sqlite3_exec 的执行。

5. sqlite3_errmsg()

• 功能: 获取与数据库连接相关的最新错误信息的描述。
• 原型: const char *sqlite3_errmsg(sqlite3 *db);
• 参数:
  • db: 数据库连接句柄。
• 返回值: 一个描述错误的字符串。

6. sqlite3_free()

• 功能: 释放由 SQLite 库分配的内存，通常用于释放 sqlite3_exec 返回的错误信息。
• 原型: void sqlite3_free(void *ptr);
• 参数:
  • ptr: 指向要释放的内存的指针。

7. sqlite3_get_table()

• 功能: 执行一个 SELECT 查询，并将整个结果集一次性读入内存。
• 原型: int sqlite3_get_table(sqlite3 *db, const char *sql, char ***pazResult, int *pnRow, int *pnColumn, char **pzErrmsg);
• 参数:
  • pazResult: 存放结果集的指针。结果集是一个一维字符串数组，需要通过 results[行 * 列 + 列] 的方式访问。
  • pnRow: 存放结果集行数的指针。
  • pnColumn: 存放结果集列数的指针。
• 返回值: SQLITE_OK 表示成功。使用后必须调用 sqlite3_free_table() 释放结果集内存。

1. 基本程序框架

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>

int main() {
    sqlite3 *db;
    char *err_msg = 0;
    int rc;

    // 1. 打开数据库
    rc = sqlite3_open("test.db", &db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        return 1;
    }

    printf("Database opened successfully.\n");

    // ... 在此执行 SQL 操作 ...

    // 2. 关闭数据库
    sqlite3_close(db);
    return 0;
}

2. 编译方法

gcc -o sqlite_test sqlite_test.c -lsqlite3

3. 完整示例程序 (使用 sqlite3_exec)

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>

// 查询回调函数
static int callback(void *data, int argc, char **argv, char **azColName) {
    for (int i = 0; i < argc; i++) {
        printf("%s = %s\t", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

int main() {
    sqlite3 *db;
    char *err_msg = 0;
    int rc;

    rc = sqlite3_open("school.db", &db);
    if (rc != SQLITE_OK) { /* ... 错误处理 ... */ }

    // 创建表
    char *sql_create = "CREATE TABLE IF NOT EXISTS student(id INTEGER PRIMARY KEY, name TEXT, score REAL);";
    rc = sqlite3_exec(db, sql_create, 0, 0, &err_msg);

    // 插入数据
    char *sql_insert = "INSERT INTO student (name, score) VALUES ('张三', 85.5), ('李四', 92.0);";
    rc = sqlite3_exec(db, sql_insert, 0, 0, &err_msg);

    // 查询数据
    printf("查询所有学生信息：\n");
    char *sql_select = "SELECT * FROM student;";
    rc = sqlite3_exec(db, sql_select, callback, 0, &err_msg);

    sqlite3_close(db);
    return 0;
}

第三部分：Python 中使用 SQLite3

Python 内置了 sqlite3 模块，其接口遵循 Python DB-API 2.0 规范，使用起来非常便捷。

Python sqlite3 模块核心对象与方法

1. sqlite3.connect()

• 功能: 连接到 SQLite 数据库。如果文件不存在，则创建该文件。
• 用法: conn = sqlite3.connect('database_file.db', ...)
• 参数:
  • database: 数据库文件名。使用 :memory: 创建内存数据库。
• 返回值: 一个 Connection 对象，代表与数据库的连接。

2. Connection 对象

这是代表数据库连接的对象，主要方法有：

• cursor():
  • 功能: 创建一个 Cursor 对象。所有 SQL 命令都通过游标执行。
  • 用法: cursor = conn.cursor()
• commit():
  • 功能: 提交当前事务。在对数据库进行修改（INSERT, UPDATE, DELETE）后，必须调用此方法，更改才会生效。
  • 用法: conn.commit()
• rollback():
  • 功能: 回滚自上次 commit 以来的所有更改。
  • 用法: conn.rollback()
• close():
  • 功能: 关闭数据库连接。
  • 用法: conn.close()

3. Cursor 对象

这是执行 SQL 语句的核心对象，主要方法有：

• execute(sql, [parameters]):
  • 功能: 执行单条 SQL 语句。
  • 用法: cursor.execute("SELECT * FROM users WHERE name = ?", ('Alice',))
  • 参数:
    • sql: SQL 语句字符串。
    • parameters: 一个可选的元组或列表，用于替换 SQL 语句中的 ? 占位符。强烈推荐使用这种方式来传递参数，可以有效防止 SQL 注入。
• executemany(sql, seq_of_parameters):
  • 功能: 对参数序列中的每一项重复执行同一条 SQL 语句。常用于批量插入。
  • 用法: cursor.executemany("INSERT INTO users VALUES (?, ?)", [('Alice', 20), ('Bob', 22)])
• fetchone():
  • 功能: 从查询结果中获取下一行数据，返回一个元组。如果无更多数据，则返回 None。
  • 用法: row = cursor.fetchone()
• fetchall():
  • 功能: 从查询结果中获取所有剩余的行，返回一个列表，列表中的每个元素是一个元组。
  • 用法: all_rows = cursor.fetchall()
• fetchmany(size):
  • 功能: 从查询结果中获取指定数量的行。
  • 用法: three_rows = cursor.fetchmany(3)

1. 基本操作流程

import sqlite3

# 1. 连接数据库（如果不存在，则会自动创建）
conn = sqlite3.connect('school.db')
# 2. 创建一个 Cursor 对象
cursor = conn.cursor()

# 3. 执行 SQL 语句来创建表
cursor.execute('''
    CREATE TABLE IF NOT EXISTS student (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        score REAL
    )
''')

# 4. 插入数据 (使用 ? 占位符)
cursor.execute("INSERT INTO student (name, age, score) VALUES (?, ?, ?)", 
               ('张三', 20, 85.5))

# 5. 批量插入数据
students = [('王五', 19, 78.5), ('赵六', 22, 88.0)]
cursor.executemany("INSERT INTO student (name, age, score) VALUES (?, ?, ?)", students)

# 6. 提交事务，让更改生效
conn.commit()

# 7. 查询数据并获取结果
print("--- 查询所有学生 ---")
cursor.execute("SELECT * FROM student")
rows = cursor.fetchall()  # 获取所有结果
for row in rows:
    print(row)

# 8. 更新和删除数据
cursor.execute("UPDATE student SET score = ? WHERE name = ?", (95.0, '张三'))
cursor.execute("DELETE FROM student WHERE name = ?", ('王五',))

# 9. 再次提交并关闭连接
conn.commit()
conn.close()

第四部分：最佳实践和注意事项

1. 错误处理

if (rc != SQLITE_OK) {
    fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
    // 处理错误
}

2. 防止 SQL 注入

// ❌ 错误方式（容易被注入）
char sql[256];
sprintf(sql, "SELECT * FROM student WHERE name = '%s'", user_input);

// ✅ 正确方式（使用参数绑定）
const char *sql = "SELECT * FROM student WHERE name = ?";
sqlite3_stmt *stmt;
sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
sqlite3_bind_text(stmt, 1, user_input, -1, SQLITE_STATIC);

3. 事务处理

// 开始事务
sqlite3_exec(db, "BEGIN TRANSACTION;", NULL, NULL, NULL);

// 执行多个操作...

// 提交事务
sqlite3_exec(db, "COMMIT;", NULL, NULL, NULL);

// 或回滚事务
sqlite3_exec(db, "ROLLBACK;", NULL, NULL, NULL);

4. 资源清理

// 始终记得关闭数据库连接
sqlite3_close(db);

// 释放错误信息
sqlite3_free(err_msg);