def one_update(table=None, where=None, where_value=None, setcol=None, set_value=None, and_where=None, and_where_value=None): try: if and_where: sql = 'UPDATE `%s` SET `%s`="%s" WHERE `%s`=%s AND `%s`=%s;' % ( table, setcol, set_value, where, where_value, and_where, and_where_value) else: sql = 'UPDATE `%s` SET `%s`="%s" WHERE `%s`=%s;' % ( table, setcol, set_value, where, where_value) print sql cur = db.query(sql) cur.close() time.sleep(0.1) print "success update one" return "success" except: print "failed update one" return "failed"
def select_json(table='', column='*', where='1', value='1', limit='10', order='id', sortir='DESC'): try: value_isi = value.split() if value_isi[0] == "like": sql = "SELECT %s FROM %s WHERE %s='%s' AND %s like '%s%%' order by %s %s LIMIT %s" % ( column, table, where, value_isi[1], value_isi[2], value_isi[3], order, sortir, limit) elif value_isi[0] == "and": sql = "SELECT %s FROM %s WHERE %s='%s' AND %s='%s' order by %s %s LIMIT %s" % ( column, table, where, value_isi[1], value_isi[2], value_isi[3], order, sortir, limit) else: sql = "SELECT %s FROM %s WHERE %s='%s' order by %s %s LIMIT %s" % ( column, table, where, value, order, sortir, limit) #print sql cur = db.query(sql) columns = cur.description result = [{ columns[index][0]: column for index, column in enumerate(value) } for value in cur.fetchall()] cur.close() if result == []: # data = {"data":result,"meta":{"code":404,"status":"Data Not Found"}} return response(data=result, code=404, message="Data Not Found") else: try: data = json.dumps(result, default=decimal_default) data = json.loads(data) return response(data=data, code=200, message="Success") except: data = json.dumps(result, default=datetime_handler) data = json.loads(data) return response(data=data, code=200, message="Success") #db.commit() # cur.close() print "cursor closed select_json" return jsonify(data) #db.close() except Exception as e: #cur.close() #print "cursor closed select_json failed" return response(code=501, message=e)
def insert(table,data_post,data="Success"): try: columns = ', '.join(data_post.keys()) values = '", "'.join(str(v) for v in data_post.values()) placeholders = ', '.join('?' * len(data_post)) sql = 'INSERT INTO %s (%s) VALUES ("%s")' % (table,columns, str(values)) cur = db.query(sql) try: res=cur.lastrowid except: res = "success" print "insert success" cur.close() return res except: print "insert error" return "err"
def select_one(table=None,column=None,where=None,where_value=None): try: if where: sql= """SELECT %s FROM %s WHERE %s='%s' """ % (column,table,where,where_value) else: sql= """SELECT %s FROM %s""" % (column,table) cur = db.query(sql) data = cur.fetchone() cur.close() time.sleep(0.1) print "cursor closed select_one" print sql return data[0] #cur.close() #db.close() except: #cur.close() #print "cursor closed select_one failed" return response(data=None,code=500,message="Error")
def update(table,data_post,data={"status":"Success"}): try: columns = ', '.join(data_post.keys()) values = '", "'.join(data_post.values()) placeholders = ', '.join('?' * len(data_post)) sql = 'REPLACE INTO %s (%s) VALUES ("%s")' % (table,columns, str(values)) cur = db.query(sql) # db.commit() # cur.close() cur.close() time.sleep(0.1) print sql res=data print "cursor closed update" return response(data=res,code=200,message="Update Success") except : #cur.close() print "cursor closed update failed" return jsonify(data_post)
def create_token(data,status): username, password = decode(data) password = md5.new(password + secret).hexdigest() ses = md5.new(username+password).hexdigest() try: res = jwt.decode(session.get(ses),secret,algorithms=['HS256']) data = {"token":session.get(ses)} return response(data=data,code=200,message="Token Not Expired") except jwt.ExpiredSignatureError: del session[ses] if(status=='user'): sql = "SELECT id,username FROM users WHERE username='******' AND password='******'" % (username,password) cur = db.query(sql) res = cur.fetchone() cur.close() result = { "id":res[0], "username":res[1], "status":"user", "exp": token_expired } elif(status=='admin'): sql = "SELECT id,username FROM admins WHERE username='******' AND password='******'" % (username,password) cur = db.query(sql) res = cur.fetchone() cur.close() result = { "id":res[0], "username":res[1], "status":"admin", "exp": token_expired } print 'processing create token' token = jwt.encode(result, secret, algorithm='HS256') session[ses] = token print "token refreshed" data = {"token":token} print "cursor closed token login refresh" time.sleep(0.1) return response(data=data,code=200,message="Token Refreshed") except: try: if(status=='user'): sql = "SELECT id,username FROM users WHERE username='******' AND password='******'" % (username,password) cur = db.query(sql) res = cur.fetchone() cur.close() result = { "id":res[0], "username":res[1], "status":"user", "exp": token_expired } elif(status=='admin'): sql = "SELECT id,username FROM admins WHERE username='******' AND password='******'" % (username,password) cur = db.query(sql) res = cur.fetchone() cur.close() result = { "id":res[0], "username":res[1], "status":"admin", "exp": token_expired } print 'processing create token' token = jwt.encode(result, secret, algorithm='HS256') session[ses] = token print "token created" #db.commit() data = {"token":token} # cur.close() print "cursor closed token login login" time.sleep(0.1) return response(data=data,code=200,message="Login Success") #cur.close() #db.close() except: #cur.close() #print "cursor closed token login failed" return response(code=403,message="Login Failed")