当前位置：首页 > news >正文

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

news 来源：原创 2024/9/22 10:35:56

一些常用的 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 sqlite3def 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 未定义，则不需要关闭passtry: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;