105 lines
3.4 KiB
Python
105 lines
3.4 KiB
Python
import json
|
|
import sqlite3
|
|
import os
|
|
from datetime import datetime
|
|
|
|
|
|
home_dir = os.path.expanduser("~")
|
|
global_host_data_dir = f'{home_dir}/hostdir/scripts_data'
|
|
global_share_data_dir = f'{home_dir}/sharedata'
|
|
|
|
db_path = f"{global_share_data_dir}/shared.db"
|
|
|
|
def create_table():
|
|
"""创建 SQLite 数据表"""
|
|
conn = sqlite3.connect(db_path)
|
|
cursor = conn.cursor()
|
|
cursor.execute('''
|
|
CREATE TABLE IF NOT EXISTS javhd_models (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
rank INTEGER,
|
|
ja_name TEXT,
|
|
zh_name TEXT,
|
|
en_name TEXT,
|
|
url TEXT UNIQUE,
|
|
pic TEXT,
|
|
height TEXT,
|
|
weight TEXT,
|
|
breast_size TEXT,
|
|
breast_factor TEXT,
|
|
hair_color TEXT,
|
|
eye_color TEXT,
|
|
birth_date TEXT,
|
|
ethnicity TEXT,
|
|
birth_place TEXT,
|
|
created_at TEXT DEFAULT (datetime('now', 'localtime')),
|
|
updated_at TEXT DEFAULT (datetime('now', 'localtime'))
|
|
)
|
|
''')
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
def insert_data(data):
|
|
"""插入 JSON 数据到数据库，处理冲突情况"""
|
|
conn = sqlite3.connect(db_path)
|
|
cursor = conn.cursor()
|
|
|
|
sql = '''
|
|
INSERT INTO javhd_models (
|
|
rank, ja_name, zh_name, en_name, url, pic, height, weight,
|
|
breast_size, breast_factor, hair_color, eye_color, birth_date,
|
|
ethnicity, birth_place, updated_at
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'))
|
|
ON CONFLICT(url) DO UPDATE SET
|
|
rank=excluded.rank,
|
|
ja_name=excluded.ja_name,
|
|
zh_name=excluded.zh_name,
|
|
en_name=excluded.en_name,
|
|
pic=excluded.pic,
|
|
height=excluded.height,
|
|
weight=excluded.weight,
|
|
breast_size=excluded.breast_size,
|
|
breast_factor=excluded.breast_factor,
|
|
hair_color=excluded.hair_color,
|
|
eye_color=excluded.eye_color,
|
|
birth_date=excluded.birth_date,
|
|
ethnicity=excluded.ethnicity,
|
|
birth_place=excluded.birth_place,
|
|
updated_at=datetime('now', 'localtime');
|
|
'''
|
|
|
|
for item in data:
|
|
try:
|
|
cursor.execute(sql, (
|
|
item.get("rank"), item.get("ja_name"), item.get("zh_name"), item.get("en_name"),
|
|
item.get("url"), item.get("pic"), item.get("Height"), item.get("Weight"),
|
|
item.get("Breast size"), item.get("Breast factor"), item.get("Hair color"),
|
|
item.get("Eye color"), item.get("Birth date"), item.get("Ethnicity"),
|
|
item.get("Birth place")
|
|
))
|
|
except sqlite3.Error as e:
|
|
print(f"[ERROR] 插入数据时发生错误: {e}")
|
|
|
|
conn.commit()
|
|
conn.close()
|
|
|
|
def load_json(file_path):
|
|
"""读取 JSON 文件并返回数据"""
|
|
if not os.path.exists(file_path):
|
|
print("[ERROR] JSON 文件不存在！")
|
|
return []
|
|
|
|
with open(file_path, "r", encoding="utf-8") as f:
|
|
try:
|
|
data = json.load(f)
|
|
return data
|
|
except json.JSONDecodeError as e:
|
|
print(f"[ERROR] 解析 JSON 文件失败: {e}")
|
|
return []
|
|
|
|
if __name__ == "__main__":
|
|
#create_table()
|
|
json_data = load_json("./result/models_detail.json")
|
|
if json_data:
|
|
insert_data(json_data)
|
|
print("[INFO] 数据导入完成！") |