Python-Projekte/tools/database/ift_import.py

from pathlib import Path
import subprocess

cfg = {
    'schema': 'temp',
    'scripts_dir': 'C:/GlobalCube/Tasks/scripts',
    'query_dir': 'C:/GlobalCube/System/NAVISION/SQL/exec',
}
target_tables = {
    'A': 'IFT_Actuals',
    'B': 'IFT_Budget',
    'C': 'IFT_Commitment',
    'K': 'IFT_Controlling_Documents',
    'P': 'IFT_Plan'
}


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


def ift_import(csv_file, target_table):
    bulkcopy = str(Path(cfg['scripts_dir']) / 'bulkcopy_import.bat')
    sqlquery = str(Path(cfg['scripts_dir']) / 'sqlquery.bat')
    sqlexec = str(Path(cfg['scripts_dir']) / 'sqlexec.bat')
    query_file = str(Path(cfg['query_dir']) / (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}")


def import_dir(base_dir=None):
    if base_dir is None:
        base_dir = '/home/robert/projekte/python/BMW_Wien/IFT/prod_csv'

    for csv_file in Path(base_dir).glob('*.csv'):
        if csv_file.name.count('_') == 2:
            _, filetype, _ = csv_file.name.split('_')
            ift_import(str(csv_file), target_tables[filetype])
            # csv_file.unlink()


if __name__ == '__main__':
    import_dir()