def insert(table, file, data):
sql = "insert into %s(%s) values (%s)" % (table, ",".join(file), ",".join(
["'%s'" % data[x] for x in file]))
try:
res = cur.execute(sql)
loginfo.WriteLog("sql").info("insert:%s" % sql)
result = {"code": 0, "msg": "insert ok"}
except:
result = {'code': 1, 'msg': 'insert fail'}
loginfo.WriteLog("db", "/tmp/error.log").error(
"Except:%s error:%s" % (sql, traceback.format_exc()))
return result
def delete(table, data):
tag = False
sql = "delete from %s where id=%s" % (table, data["id"])
try:
res = cur.execute(sql)
conn.commit()
loginfo.WriteLog("sql").info("delete:%s" % sql)
result = {'code': 0, 'msg': 'delete ok'}
except:
result = {'code': 1, 'errmsg': 'delet fail'}
loginfo.WriteLog('db', "/tmp/error.log").error(
"Except:%s error:%s" % (sql, traceback.format_exc()))
return result
def update(table, file, data):
condition = ["%s='%s'" % (k, data[k]) for k in data] #获取客户信息 data为字典格式
sql = "update %s set %s where id=%s" % (
table, ",".join(condition), data['id']) #",".join(condition)为元组格式
try:
res = cur.execute(sql)
loginfo.WriteLog("sql").info("update:%s" % sql)
result = {"code": 0, "msg": "updata ok"}
except:
result = {"code": 1, "msg": "updata fail"}
loginfo.WriteLog('db', "/tmp/error.log").error(
"Except:%s error:%s" % (sql, traceback.format_exc()))
return result
def maps(table):
sql = "select name,value from %s " % (table)
try:
cur.execute(sql)
res = cur.fetchall()
field = ["name", "value"]
data = [dict((k, row[i]) for i, k in enumerate(field)) for row in res]
loginfo.WriteLog("sql").info("delete:%s" % sql)
result = {"code": 0, "mapdata": data}
except:
result = {"code": 1, "msg": "SQL Error"}
loginfo.WriteLog('db', "/tmp/error.log").error(
"Except:%s error:%s" % (sql, traceback.format_exc()))
return result
def list(table, file):
sql = "select * from %s" % (table)
try:
cur.execute(sql)
res = cur.fetchall()
loginfo.WriteLog("sql").info("list:%s" % sql)
user = [{v: row[k]
for k, v in enumerate(file)}
for row in res] #调取数据库的数据 列表套字典[{k:v},{k1:v1]
result = {"code": 0, "msg": user}
except:
result = {"code": 1, "msg": "data is null"}
loginfo.WriteLog('db', "/tmp/error.log").error(
"Except:%s error:%s" % (sql, traceback.format_exc()))
return result
def log(table, file):
sql = "select status As name,count(1) As value from %s group by status asc order by COUNT(1) DESC" % (
table)
try:
cur.execute(sql)
res = cur.fetchall()
file = ["name", "value"]
data = [dict((v, row[k]) for k, v in enumerate(file)) for row in res]
loginfo.WriteLog("sql").info("delete:%s" % sql)
result = {"code": 0, "data": data}
except:
result = {"code": 1, "msg": "data is null"}
loginfo.WriteLog('db', "/tmp/error.log").error(
"Except:%s error:%s" % (sql, traceback.format_exc()))
return result
def check(table, file, data):
sql = "select *from %s where id='%s'" % (table, data["id"])
try:
loginfo.WriteLog("sql").info("check:%s" % sql)
cur.execute(sql)
res = cur.fetchone()
user = {v: res[k] for k, v in enumerate(file)}
print user["passwd"]
passwd = request.form.get('passwd')
if user["passwd"] == passwd:
result = {"code": 0, "msg": "yes"}
except:
result = {"code": 1, "errmsg": "updata fail"}
loginfo.WriteLog('db', "/tmp/error.log").error(
"Except:%s error:%s" % (sql, traceback.format_exc()))
return result
def _delete(table_name, data):
tag = False
try:
sql = 'DELETE FROM %s where id="%s" ;' % (table_name, data['id'])
if cur.execute(sql):
connect_db.commit()
loginfo.WriteLog("SQL").info("Delete:%s" % sql)
tag = True
except Exception, e:
print 'Error %s' % (sql)
def getone(table, file, 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"])
try:
cur.execute(sql)
res = cur.fetchone()
loginfo.WriteLog("sql").info("getone:%s" % sql)
user = {
v: res[k]
for k, v in enumerate(file)
} #调取数据库的数据 {'username': u'lhs', 'status': None, 'passwd': u'123', 'age': 1, 'id': 53, 'phone': u'12312', 'role': 0, 'sex': u'\u7537', 'email': u'*****@*****.**'}
result = {"code": 0, "msg": user}
except:
result = {"code": 1, "errmsg": "slelet fail"}
loginfo.WriteLog('db', "/tmp/error.log").error(
"Except:%s error:%s" % (sql, traceback.format_exc()))
return result
def insert_sql(table_name, field, data):
sql = "INSERT INTO %s (%s) VALUES (%s);" % (table_name, ','.join(
field), ','.join(['"%s"' % data[x] for x in field]))
print sql
loginfo.WriteLog("SQL").info("Insert:%s" % sql)
res = cur.execute(sql)
connect_db.commit()
if res:
result = {'code': 0, 'msg': 'insert ok'}
else:
result = {'code': 1, 'msg': 'insert fail'}
return result
def list(table_name, field):
sql = "select * from %s ;" % table_name
cur.execute(sql)
res = cur.fetchall()
loginfo.WriteLog("SQL").info("Select:%s" % sql)
if res:
user = [dict((k, row[i]) for i, k in enumerate(field)) for row in res]
result = {'code': 0, 'msg': user}
else:
result = {'code': 1, 'errmsg': 'data is null'}
return result
def _update(table, field, 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
res = cur.execute(sql)
loginfo.WriteLog("SQL").info("Update:%s" % sql)
if res:
connect_db.commit()
result = {'code': 0, 'msg': 'update ok'}
else:
result = {'code': 1, 'errmsg': 'Update fail'}
return result
def maps(table):
sql = "select name,value from %s " % (table)
try:
if cur.execute(sql):
res = cur.fetchall()
loginfo.WriteLog("SQL").info("Map:%s" % sql)
field = ["name", "value"]
data = [
dict((k, row[i]) for i, k in enumerate(field)) for row in res
]
result = {'code': 0, 'mapdata': data}
else:
result = {'code': 1, 'msg': "data is null"}
except Exception, e:
result = {'code': 1, 'msg': "SQL Error "}
def logs(table, field):
sql = "select status AS name,count(1) AS value from %s group by status asc order by count(1) desc; " % (
table)
try:
if cur.execute(sql):
res = cur.fetchall()
loginfo.WriteLog("SQL").info("Status:%s" % sql)
fields = ["name", "value"]
data = [
dict((k, row[i]) for i, k in enumerate(fields)) for row in res
]
result = {'code': 0, 'data': data, 'legend': field}
else:
result = {'code': 1, 'msg': "data is null"}
except Exception, e:
result = {'code': 1, 'msg': "SQL Error "}
def getone(table, data, field):
if data.has_key('username'):
sql = 'select * from %s where username="******";' % (table,
data['username'])
else:
sql = 'select %s from %s where id="%s";' % (','.join(field), table,
data['id'])
print sql
cur.execute(sql)
res = cur.fetchone()
loginfo.WriteLog("SQL").info("Getone:%s" % sql)
if res:
user = {k: res[i] for i, k in enumerate(field)}
result = {'code': 0, 'msg': user}
else:
result = {'code': 1, 'msg': "data is null"}
return result
def check(table, field, where):
if isinstance(where, dict) and where:
conditions = []
for k, v in where.items():
conditions.append("%s='%s'" % (k, v))
sql = "select %s from %s where %s ;" % (','.join(field), table,
' AND '.join(conditions))
print sql
try:
if cur.execute(sql):
res = cur.fetchone()
print res
loginfo.WriteLog("SQL").info("Check:%s" % sql)
user = {k: res[i] for i, k in enumerate(field)}
print user
result = {'code': 0, 'msg': user}
else:
result = {'code': 1, 'msg': "data is null"}
except Exception, e:
result = {'code': 1, 'msg': "SQL Error "}