def load():
basedir = '/operation'
file = request.args.get('file', None)
if file is None:
return 'Error - Please enter a file (file).\n'
file = os.path.join(basedir, file)
if not os.path.exists(file):
return 'Error - File not found {}.\n'.format(
request.args.get('file', None))
wb = openpyxl.load_workbook(file)
logging.warning('sheetnames', wb.sheetnames)
sheet = request.args.get('sheet', 'new')
if sheet not in wb.sheetnames:
return 'Error - No such sheet {}\n'.format(sheet)
sheet = wb[sheet]
ri = 1
ci = 1
sdict = {}
while sheet.cell(row=ri, column=ci).value is not None:
sdict[ci] = sheet.cell(row=ri,
column=ci).value.replace(' ',
'').replace('-', '')
ci += 1
logging.warning('Number of rows - ', sheet.max_row)
logging.warning('Columns - {}'.format(sdict))
myengine = getEngine('operation')
if myengine is None:
return 'Error connecting to db\n'
for ri in range(2, sheet.max_row + 1):
params = ''
values = ''
for ci in sdict.keys():
if sheet.cell(row=ri, column=ci).value is None or sheet.cell(
row=ri, column=ci).value == '-':
continue
key = sdict[ci]
value = sheet.cell(row=ri, column=ci).value
params += key + ','
if type(value) is str:
values += "'{0}',".format(value)
else:
values += "{0},".format(value)
sql = "INSERT INTO satop ({0}) VALUES({1})".format(
params[:-1], values[:-1])
logging.warning('sql', sql)
result = myengine.execute(sql)
myengine.dispose()
sdict['Number of rows'] = sheet.max_row
return 'Done - {}\n'.format(sdict)
def create():
basedir = '/operation'
file = 'template.xlsx'
file = os.path.join(basedir, file)
if not os.path.exists(file):
return 'Error - File not found {}.\n'.format(
request.args.get('file', None))
wb = openpyxl.load_workbook(file)
sheet = wb['new']
ri = 1
ci = 1
sdict = {}
while sheet.cell(row=ri, column=ci).value is not None:
sdict[ci] = {}
name = sheet.cell(row=ri,
column=ci).value.replace(' ', '').replace('-', '')
sdict[ci]['name'] = name
type = sheet.cell(row=ri + 1, column=ci).value
sdict[ci]['type'] = type
if sheet.cell(row=ri + 2, column=ci).value is not None:
size = sheet.cell(row=ri + 2, column=ci).value
sdict[ci]['size'] = size
if sheet.cell(row=ri + 3, column=ci).value is None or sheet.cell(
row=ri + 3, column=ci).value == '':
sdict[ci]['null'] = 'NULL'
else:
sdict[ci]['null'] = 'NOT NULL'
logging.warning('Columns - {} {} {}'.format(
ci,
sheet.cell(row=ri + 3, column=ci).value, sdict[ci]))
ci += 1
myengine = getEngine('operation')
if myengine is None:
return 'Error connecting to db\n'
sql = "DROP TABLE IF EXISTS satop"
logging.warning(sql)
result = myengine.execute(sql)
sql = "CREATE TABLE IF NOT EXISTS satop (id bigint(20) NOT NULL AUTO_INCREMENT,"
for ci in sdict.keys():
if 'size' in sdict[ci]:
sql += "{} {}({}) {},".format(sdict[ci]['name'], sdict[ci]['type'],
sdict[ci]['size'], sdict[ci]['null'])
else:
sql += "{} {} {},".format(sdict[ci]['name'], sdict[ci]['type'],
sdict[ci]['null'])
sql = sql[:-1] + ",CONSTRAINT satop_pk PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1"
logging.warning(sql)
result = myengine.execute(sql)
myengine.dispose()
return 'Done - {}\n'.format(sql)
def testinsert():
recs = request.args.get('recs', 500000)
myengine = getEngine('operation')
if myengine is None:
return 'Error connecting to db\n'
sql = "TRUNCATE testinsertc"
result = myengine.execute(sql)
sql = "TRUNCATE testinserti"
result = myengine.execute(sql)
for ri in range(recs):
sql = "INSERT INTO testinsertc (id,c1,c2) VALUES('ABC{0}XYZ','{0}','{0}')".format(ri)
result = myengine.execute(sql)
sql = "INSERT INTO testinserti (c1,c2) VALUES('{0}','{0}')".format(ri)
result = myengine.execute(sql)
myengine.dispose()
return 'Done - {}\n'.format(ri)