167 lines
5.1 KiB
Python
167 lines
5.1 KiB
Python
import os
|
||
import sqlite3
|
||
from datetime import datetime, timedelta
|
||
from passlib.context import CryptContext
|
||
|
||
# 加密工具
|
||
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")
|
||
|
||
def init_database():
|
||
"""初始化数据库，确保所有必要的表和数据存在"""
|
||
# 连接数据库
|
||
db_path = 'aiplatform.db'
|
||
|
||
# 如果数据库文件已存在，先删除
|
||
if os.path.exists(db_path):
|
||
print(f"删除现有数据库文件: {db_path}")
|
||
os.remove(db_path)
|
||
|
||
print(f"创建新数据库: {db_path}")
|
||
conn = sqlite3.connect(db_path)
|
||
cursor = conn.cursor()
|
||
|
||
# 创建表（如果不存在）
|
||
create_tables(cursor)
|
||
|
||
# 初始化基础数据
|
||
init_data(cursor)
|
||
|
||
# 提交更改
|
||
conn.commit()
|
||
conn.close()
|
||
|
||
print("数据库初始化完成")
|
||
|
||
def create_tables(cursor):
|
||
"""创建所有必要的表"""
|
||
print("创建数据库表...")
|
||
|
||
# 用户表
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
username TEXT UNIQUE,
|
||
email TEXT UNIQUE,
|
||
hashed_password TEXT,
|
||
is_active INTEGER DEFAULT 1,
|
||
is_admin INTEGER DEFAULT 0,
|
||
balance REAL DEFAULT 0
|
||
)
|
||
''')
|
||
|
||
# 应用表
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS apps (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
name TEXT,
|
||
desc TEXT,
|
||
price REAL,
|
||
status TEXT DEFAULT '上架',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
)
|
||
''')
|
||
|
||
# 订单表
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS orders (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id INTEGER,
|
||
app_id INTEGER,
|
||
type TEXT DEFAULT '应用调用',
|
||
amount REAL,
|
||
description TEXT,
|
||
status TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users (id),
|
||
FOREIGN KEY (app_id) REFERENCES apps (id)
|
||
)
|
||
''')
|
||
|
||
# 历史记录表
|
||
cursor.execute('''
|
||
CREATE TABLE IF NOT EXISTS history (
|
||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
user_id INTEGER,
|
||
type TEXT,
|
||
amount REAL,
|
||
desc TEXT,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users (id)
|
||
)
|
||
''')
|
||
|
||
print("数据库表创建完成")
|
||
|
||
def init_data(cursor):
|
||
"""初始化基础数据"""
|
||
print("初始化基础数据...")
|
||
|
||
# 1. 创建admin用户
|
||
print("创建admin用户...")
|
||
hashed_password = pwd_context.hash("admin123")
|
||
cursor.execute('''
|
||
INSERT INTO users (username, hashed_password, is_admin, is_active, balance)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
''', ("admin", hashed_password, 1, 1, 100))
|
||
|
||
# 2. 创建Twitter应用
|
||
print("创建Twitter应用...")
|
||
apps = [
|
||
("Twitter推文摘要", "输入Twitter用户名，获取最近推文摘要", 12.0, "上架", datetime.utcnow()),
|
||
("Twitter自动发推", "输入Twitter用户名，获取摘要并发送到Twitter", 15.0, "上架", datetime.utcnow())
|
||
]
|
||
|
||
for app in apps:
|
||
cursor.execute('''
|
||
INSERT INTO apps (name, desc, price, status, created_at)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
''', app)
|
||
|
||
# 获取新创建的应用ID
|
||
cursor.execute("SELECT id FROM apps WHERE name = 'Twitter推文摘要'")
|
||
summary_app_id = cursor.fetchone()[0]
|
||
|
||
cursor.execute("SELECT id FROM apps WHERE name = 'Twitter自动发推'")
|
||
post_app_id = cursor.fetchone()[0]
|
||
|
||
# 3. 创建示例订单
|
||
print("创建示例订单...")
|
||
# 获取admin用户ID
|
||
cursor.execute("SELECT id FROM users WHERE username = 'admin'")
|
||
admin_id = cursor.fetchone()[0]
|
||
|
||
days_ago_5 = datetime.utcnow() - timedelta(days=5)
|
||
days_ago_2 = datetime.utcnow() - timedelta(days=2)
|
||
|
||
orders = [
|
||
(admin_id, summary_app_id, "应用调用", 12.0, "使用Twitter推文摘要服务", "已完成", days_ago_5),
|
||
(admin_id, post_app_id, "应用调用", 15.0, "使用Twitter自动发推服务", "已完成", days_ago_2)
|
||
]
|
||
|
||
for order in orders:
|
||
cursor.execute('''
|
||
INSERT INTO orders (user_id, app_id, type, amount, description, status, created_at)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?)
|
||
''', order)
|
||
|
||
# 4. 创建历史记录
|
||
print("创建历史记录...")
|
||
days_ago_10 = datetime.utcnow() - timedelta(days=10)
|
||
|
||
histories = [
|
||
(admin_id, "recharge", 100.0, "账户充值", days_ago_10),
|
||
(admin_id, "consume", -12.0, "使用Twitter推文摘要服务", days_ago_5),
|
||
(admin_id, "consume", -15.0, "使用Twitter自动发推服务", days_ago_2)
|
||
]
|
||
|
||
for history in histories:
|
||
cursor.execute('''
|
||
INSERT INTO history (user_id, type, amount, desc, created_at)
|
||
VALUES (?, ?, ?, ?, ?)
|
||
''', history)
|
||
|
||
# 如果想要手动运行初始化
|
||
if __name__ == "__main__":
|
||
print("开始初始化数据库...")
|
||
init_database()
|
||
print("数据库初始化完成!") |