コード例 #1
0
ファイル: dbsql2xlsx.py プロジェクト: gmaterni/pyutils
def do_main(ini, file_xls, sql):
    rgx = re.compile(r'^\-?\d+\.?\d*$')
    uadb = UaDb.from_file(ini)
    rt = uadb.fetchall(sql, [])
    cols = rt.cols
    rows = rt.rowset
    wb = Workbook()
    ws = wb.active
    for c, col in enumerate(cols):
        ws.cell(row=1, column=c + 1).value = col
    for r, row in enumerate(rows):
        for c, col in enumerate(cols):
            value = row[c]
            if value is None:
                ws.cell(row=r + 2, column=c + 1).value = value
                continue
            s = str(value)
            if rgx.match(s) is None:
                ws.cell(row=r + 2, column=c + 1).value = value
            else:
                try:
                    s = Decimal(value)
                    ws.cell(row=r + 2, column=c + 1).value = s
                except Exception as e:
                    print(e)
                    print(value)
                    ws.cell(row=r + 2, column=c + 1).value = value
    wb.save(file_xls)
    os.chmod(file_xls, 0o666)
コード例 #2
0
ファイル: dbsql2xlsx.py プロジェクト: gmaterni/pyutils
def do_main_num(ini, file_xls, sql, cols_numeric):
    uadb = UaDb.from_file(ini)
    rt = uadb.fetchall(sql, [])
    cols = rt.cols
    rows = rt.rowset
    wb = Workbook()
    ws = wb.active
    nums = [cols.index(c) for c in cols_numeric.split(',')]
    for c, col in enumerate(cols):
        ws.cell(row=1, column=c + 1).value = col
    for r, row in enumerate(rows):
        for c, col in enumerate(cols):
            value = row[c]
            if c in nums:
                if value is None:
                    s = 0
                elif str(value).lower() == 'null':
                    s = 0
                elif str(value) == '':
                    s = 0
                else:
                    try:
                        s = Decimal(value)
                    except InvalidOperation:
                        print("numeric ERROR! row:%s  col:%s value:%s " % (r, col, value))
                        s = 0
                # ws.cell(row=r + 2, column=c + 1).number_format = '#,###.00'
            else:
                # s = value
                s = re.sub(r'[^\x00-\x7F]', ' ', value)
            ws.cell(row=r + 2, column=c + 1).value = s
    wb.save(file_xls)
    os.chmod(file_xls, 0o666)
コード例 #3
0
def do_main(ini, file_csv, sep, sql):
    uadb = UaDb.from_file(ini)
    rt = uadb.fetchall(sql, [])
    csv = rt.csv(header=1, sep=sep)
    with open(file_csv, "w+")as f:
        f.write(csv)
    os.chmod(file_csv, 0o666)
コード例 #4
0
ファイル: dbsql2xls.py プロジェクト: gmaterni/pyutils
def do_main(ini, xls_path, sql):
    stylenum = xlwt.XFStyle()
    stylenum.num_format_str = '#0.00'
    # rgx = re.compile(r'^\-?\d+\.\d*$')
    rgx = re.compile(r'^\-?\d+\.?\d*$')
    uadb = UaDb.from_file(ini)
    rt = uadb.fetchall(sql, [])
    cols = rt.cols
    rows = rt.rowset
    wb = xlwt.Workbook()
    ws = wb.add_sheet("foglio1")
    for c, col in enumerate(cols):
        ws.write(0, c, col)
    for r, row in enumerate(rows):
        for c, col in enumerate(cols):
            value = row[c]
            if value is None:
                ws.write(r + 1, c, value)
                continue
            s = str(value)
            if rgx.match(s) is None:
                ws.write(r + 1, c, value)
            else:
                s = Decimal(value)
                ws.write(r + 1, c, s, stylenum)
    wb.save(xls_path)
    os.chmod(xls_path, 0o666)
コード例 #5
0
ファイル: dbschemajson.py プロジェクト: gmaterni/pyutils
def do_main(ini, table, file_json):
    uadb = UaDb.from_file(ini)
    sql = " select * from %s  where 1=2 " % (table)
    rt = uadb.fetchall(sql, [])
    js = schema_json(uadb, rt)
    with open(file_json, "w+")as f:
        f.write(js)
    os.chmod(file_json, 0o666)
コード例 #6
0
ファイル: json2db.py プロジェクト: gmaterni/pyutils
def do_main(ini, file_json, table):
    uadb = UaDb.from_file(ini)
    with open(file_json, "r+") as f:
        txt = f.read()
    js = json.loads(txt)
    rows = js["rows"]
    for i in range(len(rows)):
        print(i)
        row = rows[i]
        uadb.insert_row(table, row)
コード例 #7
0
def do_main(ini, file_json, sql):
    uadb = UaDb.from_file(ini)
    rt = uadb.fetchall(sql, [])
    rs = rt.rows
    # print(rs)
    # js = {"rows": rs}
    s = json.dumps(rs, indent=4)
    with open(file_json, "w+") as f:
        f.write(s)
    os.chmod(file_json, 0o666)
コード例 #8
0
def do_main(ini, file_xls, table):
    uadb = UaDb.from_file(ini)
    xr = XlsReader()
    xr.open(file_xls)
    rows = xr.list_dict()
    print("num.rows:%s" % (len(rows)))
    for i in range(len(rows)):
        if i % 1000 == 0:
            print(i)
        row = rows[i]
        r = {}
        # for k, v in row.iteritems(): python2
        for k, v in row.items():
            s = str(v, 'utf-8')
            x = re.sub(r'[^\x00-\x7F]', ' ', s)
            r[k] = x
        uadb.insert_row(table, r)
コード例 #9
0
def do_main(ini, file_xls, table):
    # rgx = re.compile(r'^\-?\d+\.\d*$')
    rgx = re.compile(r'^\-?\d+\.?\d*$')
    uadb = UaDb.from_file(ini)
    sql = " select * from %s " % (table)
    rt = uadb.fetchall(sql, [])
    cols = rt.cols
    rows = rt.rowset

    wb = Workbook()
    ws = wb.active

    for c, col in enumerate(cols):
        ws.cell(row=1, column=c + 1).value = col

    for r, row in enumerate(rows):
        for c, col in enumerate(cols):
            value = row[c]
            if value is None:
                ws.cell(row=r + 2, column=c + 1).value = value
                continue
            s = str(value)
            if rgx.match(s) is None:
                try:
                    v = re.sub(r'[^\x00-\x7F]', ' ', value)
                    ws.cell(row=r + 2, column=c + 1).value = v
                except Exception as err:
                    print(err)
            else:
                try:
                    s = Decimal(value)
                except decimal.InvalidOperation:
                    print("numeric ERROR! row:%s  col:%s value:%s " %
                          (r, col, value))
                    s = value
                ws.cell(row=r + 2, column=c + 1).value = s

    wb.save(file_xls)
    os.chmod(file_xls, 0o666)
コード例 #10
0
ファイル: dbtable2xls.py プロジェクト: gmaterni/pyutils
def do_main(ini, file_xls, table):
    stylenum = xlwt.XFStyle()
    stylenum.num_format_str = '#0.00'
    # rgx = re.compile(r'^\-?\d+\.\d*$')
    rgx = re.compile(r'^\-?\d+\.?\d*$')
    uadb = UaDb.from_file(ini)
    sql = " select * from %s " % (table)
    rt = uadb.fetchall(sql, [])
    cols = rt.cols
    rows = rt.rowset
    wb = xlwt.Workbook()
    ws = wb.add_sheet("foglio1")

    for c, col in enumerate(cols):
        ws.write(0, c, col)

    for r, row in enumerate(rows):
        for c, col in enumerate(cols):
            value = row[c]
            if value is None:
                ws.write(r + 1, c, value)
                continue
            s = str(value)
            if rgx.match(s) is None:
                value = re.sub(r'[^\x00-\x7F]', ' ', value)
                ws.write(r + 1, c, value)
            else:
                try:
                    s = Decimal(value)
                    # print("c:%s col:%s val:%s" % (c,col,s))
                except decimal.InvalidOperation:
                    print("numeric ERROR! row:%s  col:%s value:%s " %
                          (r, col, value))
                ws.write(r + 1, c, value, stylenum)

    wb.save(file_xls)
    os.chmod(file_xls, 0o666)
コード例 #11
0
def do_main(ini, csv_path, delimiter, table):
    uadb = UaDb.from_file(ini)
    f = open(csv_path, 'r')
    for line in f:
        fs = str(line).split(delimiter)
        cols = []
        for col in fs:
            # set_trace()
            v = re.sub(r'[^\x00-\x7F]', '', col)
            s = v.lower().strip().replace(' ', '_').replace('.', '').replace('"', '').replace("'", "").replace("\\n", "")
            s = s.strip()
            if s == "":
                continue
            cols.append(s)
        break

    le = len(cols)
    rjs = {}
    i = 0
    for line in f:
        if i % 100 == 0:
            print(i)
        i += 1
        row = str(line).split(delimiter)
        try:
            for c in range(le):
                val = re.sub(r'[^\x00-\x7F]', ' ', row[c])
                col = cols[c].strip()
                col = "col%s" % c if col == '' else col
                rjs[col] = val.replace("\"", "").replace('\n', '').replace("'", "")
        except Exception as e:
            print("ERROR %s %s" % (i, e))
            print(line)
            continue
        uadb.insert_row(table, rjs)
    print(i)
コード例 #12
0
ファイル: uadbf.py プロジェクト: gmaterni/pyutils
def h():
    print("uadbf <file.ini> <file sql> [<file output>] ")


if __name__ == '__main__':
    args = sys.argv[1:]
    if len(args) < 2:
        h()
        sys.exit(0)
    ini = args[0]

    fsql = args[1]
    f = open(fsql, "r+")
    sql = f.read()
    f.close()
    print(sql)
    sep = "|"

    fout = None if len(args) < 3 else args[2]

    db = UaDb.from_file(ini)
    rt = db.fetchall(sql)
    if rt is None:
        print("sql errore")
        sys.exit(1)
    if fout is None:
        print(rt.csv(header=1, sep=sep))
    else:
        rt.write_csv(fout, sep=sep)
コード例 #13
0
def init(ini):
    global __db
    global separator
    separator = "|"
    __db = UaDb.from_file(ini)