gctools/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 True:
            row = cursor.fetchone()
            if row is None:
                break
            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)