Spaces:
Running
Running
File size: 2,932 Bytes
714c84b |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 |
import sqlite3
from config import DB_PATH,HF_TOKEN, REPO_ID, DATA_FILE_HUB, DATA_FILE_LOCAL, DEFAULT_USER_ID
import pandas as pd
def init_db(db_path):
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS timing (
id INTEGER PRIMARY KEY,
label TEXT NOT NULL
)
""")
cursor.execute("""
CREATE TABLE IF NOT EXISTS records (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT,
timestamp TEXT,
carbs REAL,
current_bg REAL,
icr REAL,
isf REAL,
carb_insulin REAL,
correction_insulin REAL,
total_insulin REAL,
target_bg REAL,
timing_id INTEGER,
FOREIGN KEY (timing_id) REFERENCES timing(id)
)
""")
timing_data = [
(1, "朝食"), (2, "昼食"), (3, "夜食"),
(4, "おやつ"), (5, "追加"), (6, "寝前")
]
cursor.executemany("INSERT OR IGNORE INTO timing (id, label) VALUES (?, ?)", timing_data)
conn.commit()
conn.close()
###############################CSVからDBにデータ移行########################################
# 1. DB準備
hf_db_sync.download_and_prepare_db()
# 2. CSV初期化
def initialize_data_file():
try:
hf_hub_download(
repo_id=REPO_ID,
filename=DATA_FILE_HUB,
local_dir="./",
repo_type="dataset",
token=HF_TOKEN
)
print("✅ 既存のCSVファイルを取得しました")
except Exception as e:
print(f"⚠️ CSVが取得できなかったため、新規作成します: {e}")
df = pd.DataFrame(columns=[
'user_id', 'timestamp', 'carbs', 'current_bg', 'icr', 'isf',
'carb_insulin', 'correction_insulin', 'total_insulin', 'target_bg', 'timing'
])
df.to_csv(DATA_FILE_LOCAL, index=False)
initialize_data_file()
# 3. CSV読み込みと変換
csv_path = DATA_FILE_LOCAL
df = pd.read_csv(csv_path)
timing_map = {
"朝食": 1, "昼食": 2, "夜食": 3,
"おやつ": 4, "追加": 5, "寝前": 6,
None: None, "": None
}
if "timing" in df.columns:
df["timing_id"] = df["timing"].map(timing_map)
df.drop(columns=["timing"], inplace=True)
else:
df["timing_id"] = None
# 4. DBに書き込み
conn = sqlite3.connect(LOCAL_DB_PATH)
df.to_sql("records", conn, if_exists="append", index=False)
conn.close()
print(f"✅ {len(df)} 行を SQLite に移行しました")
# 5. HF Dataset へアップロード
hf_db_sync.upload_db_to_dataset()
##############DB内容確認#########################
hf_db_sync.download_and_prepare_db()
conn = sqlite3.connect(LOCAL_DB_PATH) # ← あなたのDBパスに変更
cursor = conn.cursor()
cursor.execute("SELECT count(*) FROM records")
tables = cursor.fetchall()
conn.close()
print("📋 テーブル一覧:")
for t in tables:
print("-", t[0])
|