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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
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)
def init(ini):
global __db
global separator
separator = "|"
__db = UaDb.from_file(ini)