import sqlite3
# 创建数据库连接
conn = sqlite3.connect('engineering_platform.db')
cursor = conn.cursor()
# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS projects (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT,
student_id INTEGER,
status TEXT DEFAULT 'pending'
)
''')
# 插入示例数据
cursor.execute('''
INSERT INTO projects (name, description, student_id)
VALUES ('智能交通系统', '基于机器学习的交通流量优化', 101)
''')
# 查询数据
cursor.execute('SELECT * FROM projects')
rows = cursor.fetchall()
for row in rows:
print(row)
# 提交并关闭连接
conn.commit()
conn.close()
]]>
# 扩展数据库结构
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
password TEXT NOT NULL,
role TEXT DEFAULT 'student'
)
''')
# 添加外键约束
cursor.execute('ALTER TABLE projects ADD COLUMN user_id INTEGER REFERENCES users(id)')
conn.commit()
]]>