Python-Projekte/tools/database/cet.py

import plac
import pyodbc
from os import path


@plac.pos('query', 'SQL Query', type=str)
@plac.pos('mode', '', choices=['in', 'out', 'queryout'])
@plac.pos('csv_file', '', type=str)
@plac.opt('Server', 'Hostname or DSN', type=str)
@plac.opt('database', '', type=str)
@plac.opt('User', '', type=str)
@plac.opt('Password', '', type=str)
@plac.flg('charset', '')
@plac.opt('Codepage', '', type=str)
@plac.opt('errorlog', '', type=str)
def run(query, mode, csv_file, Server='localhost\\GLOBALCUBE', database='master',
        User='sa', Password='Mffu3011#', charset=False, Codepage='65001', errorlog='error.log'):
    dsn = f"dsn={Server};uid={User};pwd={Password}"
    if Codepage.isnumeric():
        Codepage = 'cp' + Codepage
    if mode == 'queryout':
        queryout(dsn, query, csv_file, Codepage, errorlog)
        return
    print('This is madness')


def convert_data(element):
    txt = str(element)
    txt = txt.replace('None', '')
    txt = txt.replace('False', '0').replace('True', '1')
    txt = txt.replace('\t', '').replace('\r', '').replace('\n', '')
    txt = txt.replace('\x81', '').replace('\x90', '')
    return txt


def queryout(dsn, query, csv_file, codepage, errorlog):
    if path.exists(query):
        with open(query, 'r', encoding=codepage) as frh:
            query = frh.read()

    try:
        conn = pyodbc.connect(dsn)
        cursor = conn.cursor()
        cursor.execute(query)
    except pyodbc.InterfaceError as e:
        print(e.args[1])

    with open(csv_file, 'w', encoding=codepage) as fwh:
        while row := cursor.fetchone():
            try:
                fwh.write(('\t'.join(map(convert_data, row)) + '\n'))
            except pyodbc.DataError as e:
                print(e.args[1])


if __name__ == '__main__':
    plac.call(run)