Here is a little script to import a CSV file into a database. But it is not for a server. This is a standalone program. If you want to program it for a server, then you should use another method as with getpass, because getpass is waiting for an input. It is not useful, that you should enter every time a passport if you want to automate the process.

If you downloaded a CSV file from the internet, and you want to import into a database, then you can it with this script.

What you do you need for this script?

• Python
• MariaDB
• Modul mariadb
• modul csv
• modul pathlib
• modul getpass
• modul warning

Except the mariadb module, all other modules have long been included in Python.

The mariadb module can be installed in the terminal using the command pip install mariadb.

The Script

import mariadb
import csv
from pathlib import Path
import subprocess
import getpass
import warnings 

print("Import of CSV file into MariaDB")

warnings.filterwarnings("ignore", category=UserWarning, module="mariadb")

passwort = getpass.getpass("Bitte geben Sie Ihr MariaDB-Passwort ein: ")
datenbankname = input("Bitte geben Sie den Datenbanknamen ein (z.B. gas_storage_db): ")
tabellenname = input("Bitte geben Sie den Tabellennamen ein (z.B. gas_storage): ")
csvdateipfad = Path.home() / "Linux_PC/Programmieren/Python/Python_eigene_Scripte/fuellstand_gas_deutschland/csv_export.csv"

def servercheck():
    status = subprocess.run(['systemctl', 'is-active', 'mariadb.service'], 
                            capture_output=True, text=True).stdout.strip()
    print(f"Mariadb Service Status: {status}")

def insert_csv_to_db(csv_file_path, db_config):
    try:
        conn = mariadb.connect(
            user='sven',
            password=passwort,
            host='localhost',
            port=3306
        )
        cursor = conn.cursor()

        cursor.execute(f"CREATE DATABASE IF NOT EXISTS {datenbankname}")
        cursor.execute(f"USE {datenbankname}")

        # Tabelle erstellen
        cursor.execute(f"""
        CREATE TABLE IF NOT EXISTS {tabellenname} (
            id INT AUTO_INCREMENT PRIMARY KEY,
            status VARCHAR(50),
            name VARCHAR(100),
            full_percentage FLOAT,
            gas_in_storage_twh FLOAT,
            withdrawal_gwh_d FLOAT
        )
        """)

        with open(csv_file_path, mode='r', encoding='utf-8') as csvfile:
            # WICHTIG: Hier 'csvfile' übergeben, nicht den Pfad
            csvreader = csv.DictReader(csvfile, delimiter=';')
            
            for row in csvreader:
                # Wir mappen deine CSV-Spaltennamen auf die DB-Struktur
                # Achte darauf, dass die Namen in row['...'] EXAKT wie in der CSV sind
                insert_query = f"INSERT INTO {tabellenname} (status, name, full_percentage, gas_in_storage_twh, withdrawal_gwh_d) VALUES (?, ?, ?, ?, ?)"
                
                cursor.execute(insert_query, (
                    row.get('Typen', 'N/A'),  # Falls 'Status' eigentlich 'Typen' heißt
                    row.get('Status Name', 'Unbekannt'),
                    float(row['Full (%)'].replace(',', '.')),
                    float(row['Gas in storage (TWh)'].replace(',', '.')),
                    float(row['Withdrawal (GWh/d)'].replace(',', '.'))
                ))

        conn.commit()
        print("Daten erfolgreich importiert!")
        
    except mariadb.Error as e:
        print(f"Fehler bei MariaDB: {e}")
    finally:
        if 'conn' in locals():
            conn.close()

# Aufruf der Funktion
insert_csv_to_db(csvdateipfad, {})