SQLite的入门级项目学习记录（三）

一些常用的 SQLite 命令和操作：

1. 数据库操作

        创建数据库：

sqlite3 mydatabase.db

        这条命令会创建一个名为 mydatabase.db 的数据库文件，并打开它；如果该文件不存在，SQLite 将创建一个新的数据库文件；

2. 表操作

        创建表：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);

这条命令创建一个名为 users 的表，包含三个字段：id（整数主键）、name（非空文本）和 age（整数）。

        查看表结构：

PRAGMA table_info(users);

        删除表：

DROP TABLE users;

3. 数据操作

插入数据：

INSERT INTO users (name, age) VALUES ('Alice', 30);
INSERT INTO users (name, age) VALUES ('Bob', 25);

        查询数据：

SELECT * FROM users;
# 查询表 users 中的所有数据。

        更新数据：

UPDATE users SET age = 31 WHERE name = 'Alice';

        删除数据：

DELETE FROM users WHERE name = 'Bob';

4. 索引操作

        创建索引：

CREATE INDEX idx_users_name ON users(name);

        删除索引：

DROP INDEX idx_users_name;

5. 事务操作

        开始事务：

BEGIN TRANSACTION;

        提交事务：commit()

# 提交事务
conn.commit()

        回滚事务：rollback()

import sqlite3

# 创建数据库连接
conn = sqlite3.connect('example.db')

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

try:
    # 创建游标
    cursor = conn.cursor()

    # 插入数据
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('张三', 'zhangsan@example.com'))
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('李四', 'lisi@example.com'))

    # 故意引发一个错误
    raise ValueError("模拟错误发生")

    # 提交事务
    conn.commit()

except Exception as e:
    print(f"发生错误：{e}")
    # 回滚事务
    conn.rollback()

finally:
    # 关闭游标和连接
    cursor.close()
    conn.close()

6. 表的其他操作

        添加列：

ALTER TABLE users ADD COLUMN email TEXT;

        删除列 在 SQLite 中，删除列的方法稍微有些复杂，因为 SQLite 不直接支持删除列的语法。通过以下步骤来实现删除列的效果：
创建一个新表：创建一个新表，该表的结构是你想要的，即不包含你想删除的列。
复制数据：将旧表中的数据复制到新表中。
删除旧表：删除原来的旧表。
重命名新表：将新表重命名为旧表的名称。
下面是一个具体的例子：
假设我们有一个名为 users 的表，它包含三个列：id、name 和 email。现在我们想要删除 email 列。

-- 第一步：创建一个新表，不包含 email 列
CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    name TEXT
);

-- 第二步：将旧表中的数据复制到新表中
INSERT INTO users_new (id, name)
SELECT id, name FROM users;

-- 第三步：删除旧表
DROP TABLE users;

-- 第四步：将新表重命名为旧表的名称
ALTER TABLE users_new RENAME TO users;

 具体代码如下：

import sqlite3

# 创建数据库连接
conn = sqlite3.connect('example.db')

# 创建游标
cursor = conn.cursor()

# 执行删除列的操作
cursor.execute('''
CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT
)
''')

cursor.execute('''
INSERT INTO users_new (id, email)
SELECT id, email FROM users
''')

cursor.execute('DROP TABLE users')

cursor.execute('ALTER TABLE users_new RENAME TO users')

# 提交事务
conn.commit()

# 关闭游标和连接
cursor.close()
conn.close()

7. 数据库的其他操作

        备份数据库：

.backup main backup.db

“.backup main backup.db” 的意思是执行一个备份操作，将名为 “main” 的对象备份到文件 “backup.db” 中。通常在数据库或某些特定的软件环境中，这条指令用于创建数据的备份副本，以便在数据丢失或损坏时进行恢复。

import sqlite3

def backup_database(source_db, backup_db):
    try:
        # 连接到源数据库
        source_conn = sqlite3.connect(source_db)
        print(f"成功连接到源数据库 '{source_db}'")
        
        # 连接到备份数据库（如果备份数据库不存在，它会被创建）
        backup_conn = sqlite3.connect(backup_db)
        print(f"成功连接到备份数据库 '{backup_db}'")
        
        # 获取源数据库的备份对象
        with source_conn:
            with backup_conn:
                print("开始备份数据库...")
                # 使用 backup 方法执行备份
                source_conn.backup(backup_conn)
                print("数据库备份成功!")
    
    except sqlite3.Error as e:
        print(f"SQLite 错误: {e}")
    
    except Exception as e:
        print(f"发生错误: {e}")
    
    finally:
        # 确保在操作完成后关闭数据库连接
        try:
            source_conn.close()
            print(f"关闭源数据库连接 '{source_db}'")
        except NameError:
            # 如果 source_conn 未定义，则不需要关闭
            pass

        try:
            backup_conn.close()
            print(f"关闭备份数据库连接 '{backup_db}'")
        except NameError:
            # 如果 backup_conn 未定义，则不需要关闭
            pass

# 调用函数进行备份
source_database = 'source.db'
backup_database_file = 'backup.db'
backup_database(source_database, backup_database_file)

        查看数据库文件的大小：

PRAGMA page_size;
PRAGMA page_count;