Python-Projekte/tools/database/gebos_import.py

from pathlib import Path
import subprocess

cfg = {
    'schema': 'temp',
    'scripts_dir': 'E:\\GlobalCube\\Tasks\\scripts',
    'query_dir': 'E:\\GlobalCube\\System\\NAVISION\\SQL\\exec',
}
target_tables = {
    'account2': 'GEBOS_Account',
    'activity2': 'GEBOS_Activity',
    'activitytype2': 'GEBOS_Activity_Type',
    'costcenter2': 'GEBOS_Cost_Center',
    'department2': 'GEBOS_Department',
    'employee2': 'GEBOS_Employee',
    'employeeaccount2': 'GEBOS_Employee_Account',
    'employmenttype2': 'GEBOS_Employment_Type',
    'timemodel2': 'GEBOS_Time_Model',
    'timetracking2': 'GEBOS_Time_Tracking'
}


def task(args):
    return subprocess.Popen(args, stdout=subprocess.DEVNULL).wait()


def gebos_import(csv_file, target_table):
    bulkcopy = str(Path(cfg['scripts_dir']) / 'bcp_import.bat')
    sqlquery = str(Path(cfg['scripts_dir']) / 'sqlquery.bat')
    sqlexec = str(Path(cfg['scripts_dir']) / 'sqlexec.bat')
    query_file = target_table + '.sql'

    task(f"{sqlquery} \"TRUNCATE TABLE [{cfg['schema']}].[{target_table}]\" ")
    task(f"{bulkcopy} [{cfg['schema']}].[{target_table}] \"{csv_file}\" ")
    task(f"{sqlexec} {query_file} \"{csv_file}\" ")


def import_dir(base_dir=None):
    if base_dir is None:
        base_dir = 'E:\\GEBOS\\staging'

    for csv_file in Path(base_dir).glob('*.csv'):
        print(csv_file.name)
        for key, table in target_tables.items():
            if not csv_file.name.startswith(key):
                continue
            gebos_import(str(csv_file), table)
            csv_file.unlink()


if __name__ == '__main__':
    import_dir()