119 lines
4.9 KiB
Python
Executable File
119 lines
4.9 KiB
Python
Executable File
#!/usr/bin/env python3
|
|
"""
|
|
Migration Script: SQLite -> MariaDB
|
|
Überträgt alle Daten (Gerichte und Rezepte) von der lokalen SQLite-Datenbank in die MariaDB.
|
|
Verwendung:
|
|
python3 migrate_to_mariadb.py
|
|
"""
|
|
import os
|
|
import sqlite3
|
|
import sys
|
|
from dotenv import load_dotenv
|
|
|
|
load_dotenv()
|
|
|
|
try:
|
|
import pymysql
|
|
import pymysql.cursors
|
|
except ImportError:
|
|
print("Fehler: PyMySQL ist nicht installiert. Bitte 'pip install pymysql' ausführen.")
|
|
sys.exit(1)
|
|
|
|
SQLITE_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "data", "wtc.db")
|
|
|
|
MARIADB_HOST = os.getenv("MARIADB_HOST", "127.0.0.1")
|
|
MARIADB_PORT = int(os.getenv("MARIADB_PORT", "3306"))
|
|
MARIADB_USER = os.getenv("MARIADB_USER", "v2_usr_wtc")
|
|
MARIADB_PASS = os.getenv("MARIADB_PASS", "Wtc_Kochbuch_Pass_2026!")
|
|
MARIADB_NAME = os.getenv("MARIADB_NAME", "v2_db_wtc")
|
|
|
|
def migrate():
|
|
print(f"=== Starte Migration: SQLite ({SQLITE_PATH}) -> MariaDB ({MARIADB_HOST}:{MARIADB_PORT}/{MARIADB_NAME}) ===")
|
|
|
|
if not os.path.exists(SQLITE_PATH):
|
|
print(f"Hinweis: Keine SQLite-Datenbank unter '{SQLITE_PATH}' gefunden.")
|
|
return
|
|
|
|
# 1. SQLite öffnen
|
|
sqlite_conn = sqlite3.connect(SQLITE_PATH)
|
|
sqlite_conn.row_factory = sqlite3.Row
|
|
sqlite_cursor = sqlite_conn.cursor()
|
|
|
|
# 2. MariaDB öffnen
|
|
try:
|
|
maria_conn = pymysql.connect(
|
|
host=MARIADB_HOST,
|
|
port=MARIADB_PORT,
|
|
user=MARIADB_USER,
|
|
password=MARIADB_PASS,
|
|
database=MARIADB_NAME,
|
|
charset="utf8mb4",
|
|
cursorclass=pymysql.cursors.DictCursor,
|
|
autocommit=False
|
|
)
|
|
except Exception as e:
|
|
print(f"Fehler bei Verbindung zur MariaDB: {e}")
|
|
print("Tipp: Wurde init_mariadb.sql bereits auf dem MariaDB-Server ausgeführt?")
|
|
sys.exit(1)
|
|
|
|
maria_cursor = maria_conn.cursor()
|
|
|
|
try:
|
|
# A. Gerichte migrieren
|
|
sqlite_cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='dishes'")
|
|
if sqlite_cursor.fetchone():
|
|
sqlite_cursor.execute("SELECT id, name, category, effort, created_at FROM dishes")
|
|
dishes = sqlite_cursor.fetchall()
|
|
print(f"Gefundene Gerichte in SQLite: {len(dishes)}")
|
|
|
|
for d in dishes:
|
|
maria_cursor.execute("""
|
|
INSERT INTO dishes (id, name, category, effort, created_at)
|
|
VALUES (%s, %s, %s, %s, %s)
|
|
ON DUPLICATE KEY UPDATE category=VALUES(category), effort=VALUES(effort)
|
|
""", (d["id"], d["name"], d["category"], d["effort"], d["created_at"]))
|
|
print("Gerichte erfolgreich nach MariaDB übertragen.")
|
|
|
|
# B. Rezepte migrieren
|
|
sqlite_cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='recipes'")
|
|
if sqlite_cursor.fetchone():
|
|
sqlite_cursor.execute("""
|
|
SELECT id, title, category, chapter_number, effort, prep_time, cook_time, portions, ingredients, instructions, notes, source, image_url, created_at, updated_at
|
|
FROM recipes
|
|
""")
|
|
recipes = sqlite_cursor.fetchall()
|
|
print(f"Gefundene Rezepte in SQLite: {len(recipes)}")
|
|
|
|
for r in recipes:
|
|
source_val = r["source"] if "source" in r.keys() and r["source"] else ""
|
|
maria_cursor.execute("""
|
|
INSERT INTO recipes (id, title, category, chapter_number, effort, prep_time, cook_time, portions, ingredients, instructions, notes, source, image_url, created_at, updated_at)
|
|
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
|
|
ON DUPLICATE KEY UPDATE
|
|
title=VALUES(title), category=VALUES(category), chapter_number=VALUES(chapter_number),
|
|
effort=VALUES(effort), prep_time=VALUES(prep_time), cook_time=VALUES(cook_time),
|
|
portions=VALUES(portions), ingredients=VALUES(ingredients), instructions=VALUES(instructions),
|
|
notes=VALUES(notes), source=VALUES(source), image_url=VALUES(image_url)
|
|
""", (
|
|
r["id"], r["title"], r["category"], r["chapter_number"], r["effort"],
|
|
r["prep_time"], r["cook_time"], r["portions"], r["ingredients"],
|
|
r["instructions"], r["notes"], source_val, r["image_url"], r["created_at"], r["updated_at"]
|
|
))
|
|
print("Rezepte erfolgreich nach MariaDB übertragen.")
|
|
|
|
maria_conn.commit()
|
|
print("\n✅ Migration erfolgreich abgeschlossen!")
|
|
print("Um die Anwendung auf MariaDB umzustellen, setze in der .env-Datei:")
|
|
print("DB_ENGINE=mariadb")
|
|
|
|
except Exception as e:
|
|
maria_conn.rollback()
|
|
print(f"Fehler während der Migration: {e}")
|
|
raise
|
|
finally:
|
|
sqlite_conn.close()
|
|
maria_conn.close()
|
|
|
|
if __name__ == "__main__":
|
|
migrate()
|