SQLite数据库实战: 创建用户表和智能家居状态表

本文将使用Python和SQLite数据库，创建一个简单的智能家居系统数据模型。该模型包含两个表：用户表和智能家居状态表，并演示如何插入数据以及建立表之间的关联。

代码示例

以下是使用SQLite建立用户表和智能家居状态表的Python代码示例：pythonimport sqlite3

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

创建用户表cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL, password TEXT NOT NULL)''')

创建智能家居状态表cursor.execute('''CREATE TABLE IF NOT EXISTS smart_home_status (id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL, home_name TEXT NOT NULL, home_status TEXT NOT NULL, value REAL NOT NULL, mode TEXT NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id))''')

插入用户数据cursor.execute('INSERT INTO users (username, password) VALUES (?, ?)', ('user1', 'password1'))cursor.execute('INSERT INTO users (username, password) VALUES (?, ?)', ('user2', 'password2'))

获取用户IDuser1_id = cursor.lastrowid

插入智能家居状态数据cursor.execute('INSERT INTO smart_home_status (user_id, home_name, home_status, value, mode) VALUES (?, ?, ?, ?, ?)', (user1_id, 'Home1', 'On', 50, 'Auto'))

提交事务并关闭连接conn.commit()conn.close()

代码说明

1. 创建数据库连接: 使用 sqlite3.connect('smart_home.db') 创建一个名为 'smart_home.db' 的SQLite数据库连接。2. 创建数据表: - users 表包含用户的 id、username 和 password 信息。 - smart_home_status 表存储智能家居的状态信息，包括所属用户的 user_id、home_name、home_status、value 和 mode。3. 建立表关联: smart_home_status 表中的 user_id 列作为外键，关联到 users 表的 id 列，确保每个状态信息都与一个用户关联。4. 插入数据: 使用参数化查询 (?) 插入数据，有效防止SQL注入攻击。5. 获取用户ID: 使用 cursor.lastrowid 获取最后插入的用户的ID，用于关联智能家居状态数据。

总结

这段代码演示了如何使用SQLite和Python创建简单的数据库模型，并实现了用户表和智能家居状态表之间的关联。 通过学习本示例，你可以了解如何使用SQLite进行数据库设计和数据操作，为构建更复杂的应用打下基础。