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 "}