def deleteData(request, table_name, id):
sql = "delete from {0} where id = {1}".format(table_name, id)
try:
localQueryByDict(request.app, db, sql)
result = '修改成功'
except Exception as e:
result = '修改失败'
return result
def create_new_table(request, table_name, cols):
sql = "CREATE TABLE `{0}` (".format(table_name)
sql += "`id` int(11) NOT NULL AUTO_INCREMENT,"
for item in cols.split(','):
sql += "`{0}` varchar(200) COLLATE utf8_bin DEFAULT NULL,".format(item)
sql += 'PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=634 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;'
try:
localQueryByDict(request.app, db, sql)
result = '{0}添加成功'.format(table_name)
except Exception as e:
result = e
return result
def set_project_map_info(request, table_name):
sql_count = "select count(1) as qty from t_map where `table_name`='{0}' ".format(
table_name)
if (localQueryByDict(request.app, db, sql_count)[0]['qty'] == 0):
sql = "insert into t_map(`table_name`,`status`) VALUES('{0}',1)".format(
table_name)
try:
localQueryByDict(request.app, db, sql)
result = 't_map项目数据添加成功'
except Exception as e:
result = e
else:
result = 'table exists'
return result
def import_excel(request, file, table_name):
data = xlrd.open_workbook(file_contents=file, encoding_override="utf-8")
table = data.sheets()[0]
nrows = table.nrows
list = []
for i in range(nrows):
sql = "insert into {0}({1}) VALUES ('{2}');".format(
table_name, ','.join(table.row_values(0)),
"','".join(table.row_values(i)))
try:
localQueryByDict(request.app, 'creation', sql)
result = {'line:{0}'.format(i): 'success'}
except Exception as e:
result = {'line:{0}'.format(i): e}
list.append(result)
return list
def getTableCols(request, table_name):
sql = "SHOW FULL COLUMNS FROM {0}.{1}".format(db, table_name)
data = localQueryByDict(request.app, db, sql)
cols = []
for line in data:
cols.append(line['Field'])
return cols
def check_api_type_ifexists(request, type, table_name):
result = []
if (type == 'all'):
sql = "select `get`,`post`,`put`,`delete` from t_map where table_name='{0}'".format(
table_name)
res = localQueryByDict(request.app, db, sql)[0]
for item in res.keys():
if (res[item] == None or res[item] == ''):
result.append(item)
else:
sql = "select `{1}` from t_map where table_name='{0}'".format(
table_name, type)
if (localQueryByDict(request.app, db, sql)[0][type] == None
or localQueryByDict(request.app, db, sql)[0][type] == ''):
result.append(type)
return result
def create_new_route(request, comefrom, table_name, project):
sql_count = "select route from t_map where `table_name` = '{0}'".format(
table_name)
sql_update = "update t_map set route=1 where `table_name` = '{0}'".format(
table_name)
if localQueryByDict(request.app, db, sql_count)[0]['route'] != 1:
try:
str = readFile(get_module_path('url', comefrom)).replace(
'$project', project).replace('$table_name', table_name)
table_urls_path = sys.path[1] + '/knocks/route/table_urls.py'
addFile(table_urls_path, str)
localQueryByDict(request.app, db, sql_update)
result = '添加路由成功'
except Exception as e:
result = e
else:
result = '路由已存在'
return result
def setData(request, table_name, formdata):
listA = getTableCols(request, table_name)
listB = formdata.keys()
cols = diff(listA, listB)
vals = []
for item in cols:
vals.append(formdata[item][0])
sql = "insert into {0}({1}) VALUES('{2}')".format(table_name,
','.join(cols),
"','".join(vals))
try:
localQueryByDict(request.app, db, sql)
result = '添加成功'
except Exception as e:
result = '添加失败'
return result
def getAPIinfo(request, table_name):
sql = "select `get`,`post`,`put`,`delete` from t_map where table_name='{0}'".format(
table_name)
data = localQueryByDict(request.app, db, sql)[0]
dict = {}
for col in (data.keys()):
if (data[col] != None and data[col] != ''):
dict[col] = data[col]
return dict
def updateData(request, table_name, formdata):
listA = getTableCols(request, table_name)
listB = formdata.keys()
cols = diff(listA, listB)
str = ''
print(cols[-1])
for item in cols:
if (item != 'id' and item != cols[-1]):
str += "{0}='{1}',".format(item, formdata[item][0])
elif (item == cols[-1]):
str += "{0}='{1}'".format(item, formdata[item][0])
sql = "update {0} set {1} where id={2}".format(table_name, str,
formdata['id'][0])
try:
localQueryByDict(request.app, db, sql)
result = '修改成功'
except Exception as e:
result = '修改失败'
return result
def checkproject(request, project):
sql = "select count(1) as qty from t_map where table_name = '{0}'".format(
't_' + project)
count = localQueryByDict(request.app, 'creation', sql)[0]['qty']
return count
def getTableVals(request, table_name, where=None):
sql = "select * from {0}".format(table_name)
data = localQueryByDict(request.app, db, sql)
return data
def set_api_type_info(request, table_name, type, url):
sql = "update t_map set `{0}`='{1}' where TABLE_NAME='{2}'".format(
type, url, table_name)
return localQueryByDict(request.app, db, sql)
def getTableVals(request, sql):
data = localQueryByDict(request.app, db, sql)
return data
