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;