def deleteuser(table,data):
sql = 'delete from %s where id="%s";'%(table,data['id'])
try:
res = cur.execute(sql)
utils.WriteLog('sql').info("deleteuser:%s" % sql)
result = {'code':0,'msg':'delete ok'}
except:
utils.WriteLog('db').error('Execute %s error:%s' %(sql,traceback.format_exc()))
result = {'code':1,'msg':'delete fail'}
return result
def delete(table, data):
sql = "delete from %s where id = %s" % (table, data['id'])
try:
res = cur.execute(sql)
utils.WriteLog("db模块", info_filename).info("执行语句：%s" % sql)
result = {'code': 0, 'msg': 'delete ok'}
except:
result = {'code': 1, 'errmsg': 'delete fail'}
utils.WriteLog("db模块", error_filename).error(
"执行语句：%s,错误信息：%s" % (sql, traceback.format_exc()))
return result
def insert(table,field,data):
sql = "insert into %s (%s) values (%s)"%(table,','.join(field),','.join(['"%s"' % data[x] for x in field]))
print sql
try:
res = cur.execute(sql)
utils.WriteLog('sql').info("insert:%s" % sql)
result = {'code':0,'msg':'insert ok'}
except:
result = {'code':1,'msg':'insert fail'}
utils.WriteLog('db').error('Execute %s error:%s' %(sql,traceback.format_exc()))
return result
def modify(table,data):
conditions = ["%s='%s'" % (k,data[k]) for k in data]
sql = "update %s set %s where id=%s;" %(table,','.join(conditions),data['id'])
print sql
try:
res = cur.execute(sql)
utils.WriteLog('sql').info("modify:%s" % sql)
result = {'code':0,'msg':'update ok'}
except:
utils.WriteLog('db').error('Execute %s error:%s' %(sql,traceback.format_exc()))
result = {'code':1,'msg':'update fail'}
return result
def insert(table, field, data):
sql = "insert into %s (%s) values(%s)" % (table, ','.join(field), ','.join(
['"%s"' % data[x] for x in field]))
try:
res = cur.execute(sql)
utils.WriteLog("db模块", info_filename).info("执行语句：%s" % sql)
result = {'code': 0, 'msg': 'insert ok'}
except:
result = {'code': 1, 'errmsg': 'insert fail'}
utils.WriteLog("db模块", error_filename).error(
"执行语句：%s,错误信息：%s" % (sql, traceback.format_exc()))
return result
def updata(table, data):
conditions = ["%s = '%s'" % (k, data[k]) for k in data]
sql = "update %s set %s where id = %s" % (table, ",".join(conditions),
data['id'])
try:
res = cur.execute(sql)
result = {'code': 0, 'msg': 'updata ok'}
utils.WriteLog("db模块", info_filename).info("执行语句：%s" % sql)
except:
result = {'code': 1, 'errmsg': 'updata fail'}
utils.WriteLog("db模块", error_filename).error(
"执行语句：%s,错误信息：%s" % (sql, traceback.format_exc()))
return result
def alllist(table,field):
sql = 'select * from %s;'%(table)
utils.WriteLog('sql').info("alllist:%s" % sql)
cur.execute(sql)
try:
res = cur.fetchall()
utils.WriteLog('sql').info("alllist:%s" % sql)
data = [{k:row[i] for i,k in enumerate(field)} for row in res]
result = {'code':0,'msg':data}
except:
utils.WriteLog('db').error('Execute %s error:%s' %(sql,traceback.format_exc()))
result = {'code':1,'msg':'data is null'}
return result
def getlist(table, field):
sql = "select * from %s" % table
try:
cur.execute(sql)
res = cur.fetchall()
user = [{k: row[i] for i, k in enumerate(field)} for row in res]
result = {'code': 0, 'msg': user}
utils.WriteLog("db模块", info_filename).info("执行语句：%s" % sql)
except:
result = {'code': 1, 'errmsg': 'data is null'}
utils.WriteLog("db模块", error_filename).error(
"执行语句：%s,错误信息：%s" % (sql, traceback.format_exc()))
result = result['msg']
return result
def getone(table,field,data):
if data.has_key('username'):
sql = 'select * from %s where username="******";'%(table,data['username'])
else:
sql = 'select * from %s where id ="%s"'%(table,data['id'])
cur.execute(sql)
try:
res = cur.fetchone()
print res
utils.WriteLog('sql').info("getone:%s" % sql)
data = {k:res[i] for i,k in enumerate(field)}
result = {'code':0,'msg':data}
except:
utils.WriteLog('db').error('Execute %s error:%s' %(sql,traceback.format_exc()))
result = {'code':1,'msg':'data is null'}
return result
def delete(table, filed, data):
sql = "delete from %s where id = %s" % (table, data['id'])
utils.WriteLog(sql).info("delete %s:" % sql)
res = cur.execute(sql)
if res:
res = {'code': 0, 'msg': 'delete secussfor'}
else:
res = {'code': 1, 'errmsg': 'delete failed'}
return res
def insert(table,filed,users):
sql = "insert into %s (%s) values (%s);" %(table,','.join(filed), ','.join([ '"%s"' %users[n] for n in filed]))
utils.WriteLog(sql).info("insert %s:" %sql)
res = cur.execute(sql)
if res:
result ={'code':0,'msg':'insert is ok'}
else:
result ={'code':1,'errmsg':'insert is error'}
return result
def getone(table, field, data):
if data.has_key('username'):
sql = 'select * from %s where username = "******"' % (table,
data["username"])
elif data.has_key('name'):
sql = 'select * from %s where name = "%s"' % (table, data["name"])
else:
sql = 'select * from %s where id = "%s"' % (table, data["id"])
try:
cur.execute(sql)
res = cur.fetchone()
user = {k: res[i] for i, k in enumerate(field)}
result = {'code': 0, 'msg': user}
utils.WriteLog("db模块", info_filename).info("执行语句：%s" % sql)
except:
result = {'code': 1, 'errmsg': 'data is null'}
utils.WriteLog("db模块", error_filename).error(
"执行语句：%s,错误信息：%s" % (sql, traceback.format_exc()))
return result
def select(table, filed):
sql = "select * from %s" % table
utils.WriteLog(sql).info("select %s:" % sql)
cur.execute(sql)
res = cur.fetchall()
if res:
user = [{k: row[i] for i, k in enumerate(filed)} for row in res]
result = {'code': 0, 'msg': user}
else:
result = {'code': 1, 'errmsg': 'data is null'}
return result
def update(table,filed,data):
result=['%s = "%s"' %(k,data[k]) for k in data]
sql ="update %s set %s where id = %s"%(table,','.join(result),data['id'])
utils.WriteLog(sql).info("update %s:" %sql)
res = cur.execute(sql)
print res
if res:
res = {'code':0,'msg':'update sucessfor'}
else:
res = {'code':1,'errmsg':'update failed'}
return res
def getone(table,filed,data):
if data.has_key("username"):
sql = 'select * from %s where username= "******"' %(table,data['username'])
else:
sql = 'select * from %s where id = "%s"' %(table,data['id'])
utils.WriteLog(sql).info("getone %s:" %sql)
cur.execute(sql)
res = cur.fetchone()
if res:
users = {k:res[i] for i,k in enumerate(filed)}
user = {'code':0,'msg':users}
else:
user= {'code':1,'errmsg':'no users'}
return user
def login():
if request.method == 'POST':
data = {k: v[0] for k, v in dict(request.form).items()}
result = db.getone('user', field, data)
if result["code"] == 0:
if result['msg']["password"] == data["password"]:
session["username"] = data["username"]
session["role"] = result['msg']["role"]
data = result
utils.WriteLog("app模块", info_filename).info(
"%s用户登录系统" % session["username"])
return json.dumps(data)
else:
data['errmsg'] = "密码错误"
return json.dumps(data)
else:
data['errmsg'] = "用户名错误"
return json.dumps(data)
return render_template("login.html")
