gctools/database/sql_query.py

import pandas as pd
from sqlalchemy import create_engine
from database import conn_string

cfg = {
    "clients": [
        "deop01",
        "deop02",
        "deop03",
        "deop04",
        "deop05",
        "deop06",
        "deop07",
        "deop08",
        "deop09",
        "deop10",
        "deop11",
        "deop12",
        "deop13",
        "deop14",
        "deop15",
        "deop16",
        "deop17",
        "deop18",
        "deop19",
        "deop20",
        "deop21",
        "deop22",
        "deop23",
        "deop24",
        "deop25",
        "deop26",
        "deop27",
        "deop28",
        "deop29",
        "deho02",
        "deni02",
    ],
    "date_filter": "'2019-01-01'",
    "source_dsn": {
        "user": "sa",
        "pass": "Mffu3011#",
        "server": "GC-SERVER1\\GLOBALCUBE",
        "database": "desk01",
        "driver": "mssql",
    },
    "source_schema": "dbo",
    "source_query": "Belege_Planung_Ist_FC.sql",
    "source_header": [
        "Datenbank",
        "Betrieb_Nr",
        "Marke",
        "Konto_Nr",
        "Bookkeep_Period",
        "Betrag",
        "Menge",
    ],
    "target_csv": "Belege_Planung_Ist_FC.csv",
}


def db_import(select_query, source_db, current_table, target_db, target_schema):
    pd.read_sql(select_query, source_db).to_sql(
        current_table["target"],
        target_db,
        schema=target_schema,
        index=False,
        if_exists="append",
    )


source_db = create_engine(conn_string(cfg["source_dsn"]))
with open(cfg["source_query"], "r") as f:
    select_query = f.read()


with open(cfg["target_csv"], "w") as f:
    f.write(";".join(cfg["source_header"]) + "\n")

for current_client in cfg["clients"]:
    current_query = select_query.replace("desk01", current_client)
    pd.read_sql(current_query, source_db).to_csv(
        cfg["target_csv"],
        sep=";",
        decimal=",",
        encoding="latin-1",
        index=False,
        header=False,
        mode="a",
    )
    print(current_client)