使用 Fernet 和 SQLite 在 Python 中创建加密的本地数据库

import sqlite3
from cryptography.fernet import Fernet

# 创建加密密钥并保存到文件
key = Fernet.generate_key()
with open('key.key', 'wb') as key_file:
    key_file.write(key)

# 加载密钥
with open('key.key', 'rb') as key_file:
    key = key_file.read()
    f = Fernet(key)

# 创建或打开数据库
conn = sqlite3.connect('my_database.db')

# 创建表格
conn.execute('''CREATE TABLE IF NOT EXISTS my_table
                (ID INTEGER PRIMARY KEY AUTOINCREMENT,
                 name TEXT NOT NULL,
                 encrypted_data TEXT NOT NULL);''')

# 添加数据到表格
name = 'pidancode.com'
data = 'This is sensitive data'
encrypted_data = f.encrypt(data.encode('utf-8')).decode('utf-8')
conn.execute("INSERT INTO my_table (name, encrypted_data) VALUES (?, ?)", (name, encrypted_data))

name = '皮蛋编程'
data = 'This is also sensitive data'
encrypted_data = f.encrypt(data.encode('utf-8')).decode('utf-8')
conn.execute("INSERT INTO my_table (name, encrypted_data) VALUES (?, ?)", (name, encrypted_data))

# 提交更改并关闭数据库连接
conn.commit()
conn.close()

# 加载密钥
with open('key.key', 'rb') as key_file:
    key = key_file.read()
    f = Fernet(key)

# 打开数据库
conn = sqlite3.connect('my_database.db')

# 从表格中读取数据
cursor = conn.execute("SELECT ID, name, encrypted_data FROM my_table")
for row in cursor:
    id = row[0]
    name = row[1]
    encrypted_data = row[2]
    decrypted_data = f.decrypt(encrypted_data.encode('utf-8')).decode('utf-8')
    print(f"{id}: {name} - {decrypted_data}")

# 关闭数据库连接
conn.close()