def hist_suc_venta_fechas(suc,fecha_ini,fecha_fin): d_fecha_ini = datetime.strptime(fecha_ini, '%Y-%m-%d').date() d_fecha_fin = datetime.strptime(fecha_fin, '%Y-%m-%d').date() from app import mysql cur_sucursales = mysql.get_db().cursor() cur_sucursales.execute("""call _get_sucursal_by_name (%s)""", (suc,)) data_sucursales = cur_sucursales.fetchall()[0] cur_venta = mysql.get_db().cursor() cur_venta.execute(""" call _get_venta_articulos_por_fechas(%s, %s , %s,%s)""", (data_sucursales[0], d_fecha_ini, d_fecha_fin,10)) data = cur_venta.fetchall() data_json =[] for i in data: data_json.append([i[0], i[1],i[2], str(i[3])]) print(data_json) return jsonify ( {'ventas': data_json , 'sucursal':data_sucursales[1], 'fecha_ini': fecha_ini, 'fecha_fin': fecha_fin })
def agregar_par(): if request.method == 'POST': nombre = request.form['parentesco'] id = request.form['id'] print(request.form) from app import mysql print('Entre al post') if id == '0': print('agregando') db = mysql.get_db() cur = db.cursor() cur.execute(""" call add_parentesco(%s) """, (nombre, )) db.commit() flash('El parentesco "' + nombre + '" se ha registrado con exito', 'exito') #print("agrego") else: #edit_parentesco print('Editandoo') db = mysql.get_db() cur = db.cursor() cur.execute("""call edit_parentesco (%s,%s)""", ( id, nombre, )) db.commit() flash('La informacion se actualizo correctamente', 'exito') return redirect(url_for('admin.c_parentescos'))
def agrenar_nivel(): if request.method == 'POST': nombre = request.form['nivel'] id = request.form['id'] from app import mysql if id == '0': db = mysql.get_db() cur = db.cursor() cur.execute(""" call add_nivel_estudio(%s) """, (nombre, )) db.commit() flash('El nivel ' + nombre + " se ha registrado con exito", 'exito') #print("agrego") else: #edit_nivel_estudio db = mysql.get_db() cur = db.cursor() cur.execute("""call edit_nivel_estudio (%s,%s)""", ( id, nombre, )) db.commit() flash('La informacion se actualizo correctamente', 'exito') return redirect(url_for('admin.c_nivel_estudio'))
def delTest(id): cur = mysql.get_db().cursor() cur.execute('DELETE FROM EX_CONTIENE_PRE WHERE EXM_CODIGO = %s', (id, )) cur.execute('DELETE FROM EXAMEN WHERE EXM_CODIGO = %s', (id, )) mysql.get_db().commit() flash(u'El examen se eliminó exitosamente', 'success') return redirect(url_for("exams"))
def historico_suc_name_rango_fechas_periodo(nombre,fecha_ini,fecha_fin,periodo): d_fecha_ini = datetime.strptime(fecha_ini, '%Y-%m-%d').date() d_fecha_fin = datetime.strptime(fecha_fin, '%Y-%m-%d').date() fecha_actual = d_fecha_ini fechas_ini = [] for result in perdelta(d_fecha_ini, d_fecha_fin, timedelta(days=int(periodo))): fechas_ini.append(result) fechas = [] for i in range(len(fechas_ini) - 1): fechas.append([fechas_ini[i] + timedelta(days=1), fechas_ini[i + 1]]) for i in fechas: print(i) from app import mysql cur_sucursales = mysql.get_db().cursor() cur_sucursales.execute ("""call _get_sucursal_by_name (%s)""",(nombre,)) data_sucursales = cur_sucursales.fetchall()[0] list_historico=[] for i in fechas: cur_venta = mysql.get_db().cursor() cur_venta.execute("""call _get_venta_suc_by_fecha (%s,%s,%s) """ , (data_sucursales[0], i[0],i[1],)) list_historico.append(cur_venta.fetchall()[0]) print(list_historico) val_max=list_historico[0][2] val_min=list_historico[0][2] for i in list_historico: if i[2]> val_max: val_max = i[2] if i[2]< val_min: val_min = i[2] print([val_max, val_min]) list_json = [] for i in list_historico: estado = 0 if i[2] == val_max: estado =1 if i[2] == val_min: estado =-1 list_json.append([i[0],i[1], str(i[2]), estado]) return jsonify ( {'historico': list_json, 'ini':fecha_ini, 'fin':fecha_fin, 'ID': data_sucursales[0], 'nombre': nombre} )
def modifyTest(testID): name = request.form['name'] year = request.form['year'] cur = mysql.get_db().cursor() cur.execute('UPDATE EXAMEN SET NOMBRE=%s, ANHO=%s WHERE EXM_CODIGO = %s', (name, year, testID)) mysql.get_db().commit() flash(u'El examen se modificó exitosamente', 'success') return redirect(url_for('exams'))
def delQuestion(id, examID): cur = mysql.get_db().cursor() cur.execute('DELETE FROM EX_CONTIENE_PRE WHERE CODIGO_PREGUNTA = %s', (id, )) cur.execute('DELETE FROM PRES_COMPUESTA_RESP WHERE CODIGO_PREGUNTA = %s', (id, )) cur.execute('DELETE FROM PREGUNTA WHERE CODIGO_PREGUNTA = %s', (id, )) mysql.get_db().commit() flash(u'La pregunta se eliminó exitosamente', 'success') return redirect(url_for("modifyquestion", testID=examID))
def examsAdd(): name = request.form['name'] year = float(request.form['year']) cur = mysql.get_db().cursor() cur.execute('INSERT INTO EXAMEN (ANHO, NOMBRE) VALUES (%s,%s)', (year, name)) mysql.get_db().commit() flash(u'El examen se registró exitosamente', 'success') cur.execute('SELECT * FROM EXAMEN') data = cur.fetchall() return render_template('admin/exams.html', exams=data)
def mud(examID): if request.method == "POST": question = request.form['pregunta'] idQ = request.form['id1'] print(idQ) answer = request.form['respuesta'] idC = request.form['respC'] idI1 = request.form['respI1'] idI2 = request.form['respI2'] idI3 = request.form['respI3'] link = request.form['enlace'] answer1 = request.form['respuesta1'] answer2 = request.form['respuesta2'] answer3 = request.form['respuesta3'] fileSize = request.form['imageSize'] cur = mysql.get_db().cursor() if request.files: print(fileSize) if fileSize != "x_1": if not allowed_image_filesize(fileSize): flash("La imagen excede el límite de tamaño", "danger") return redirect(request.url) image = request.files["imgInp"] #if image.filename == "": #flash("No filename", "danger") #return redirect(request.url) if allowed_image(image.filename): image_string = base64.b64encode(image.read()) cur.execute( 'UPDATE PREGUNTA SET PREGUNTA_IMAGEN = %s WHERE CODIGO_PREGUNTA=%s', (image_string, idQ)) else: flash("Imagen no válida", "danger") return redirect(request.url) cur.execute( 'UPDATE PREGUNTA SET PREGUNTA_ENLACE=%s, TEXTO_PREGUNTA=%s WHERE CODIGO_PREGUNTA=%s', (link, question, idQ)) cur.execute( 'UPDATE RESPUESTA SET TEXTO_RESPUESTA=%s WHERE RESP_CODIGO=%s', (answer, idC)) cur.execute( 'UPDATE RESPUESTA SET TEXTO_RESPUESTA=%s WHERE RESP_CODIGO=%s', (answer1, idI1)) cur.execute( 'UPDATE RESPUESTA SET TEXTO_RESPUESTA=%s WHERE RESP_CODIGO=%s', (answer2, idI2)) cur.execute( 'UPDATE RESPUESTA SET TEXTO_RESPUESTA=%s WHERE RESP_CODIGO=%s', (answer3, idI3)) mysql.get_db().commit() flash('Se ha modificado correctamente', 'success') return redirect(url_for("modifyquestion", testID=examID))
def ventas_x_mes(): fecha_ = datetime.now() list = [] datetime_str = '2000/01/01 00:00:00' fecha_ = datetime.strptime(datetime_str, '%Y/%m/%d %H:%M:%S') list.append([str(fecha_.year) + '/01/01' , str(fecha_.year) + '/01/31' ]) #ene list.append([str(fecha_.year) + '/02/01', str(fecha_.year) + '/02/28'])#feb list.append([str(fecha_.year) + '/03/01', str(fecha_.year) + '/03/31'])#mar """ list.append([str(fecha_.year) + '/04/01', str(fecha_.year) + '/04/30'])#abrl list.append([str(fecha_.year) + '/05/01', str(fecha_.year) + '/05/31'])#mayo list.append([str(fecha_.year) + '/06/01', str(fecha_.year) + '/06/30'])#juni list.append([str(fecha_.year) + '/07/01', str(fecha_.year) + '/07/31'])#jul list.append([str(fecha_.year) + '/08/01', str(fecha_.year) + '/08/31'])#ago list.append([str(fecha_.year) + '/09/01', str(fecha_.year) + '/09/30'])#sep list.append([str(fecha_.year) + '/10/01', str(fecha_.year) + '/10/31'])#oct list.append([str(fecha_.year) + '/11/01', str(fecha_.year) + '/11/30'])#nov list.append([str(fecha_.year) + '/12/01', str(fecha_.year) + '/12/31'])#dic """ #call get_all_empresas() from app import mysql cur_sucursales = mysql.get_db().cursor() cur_sucursales.execute ("""call get_all_empresas()""") sucursales = cur_sucursales.fetchall() list_historial=[] for sucursal in sucursales: his_suc=[] for i in list: db = mysql.get_db() cur_venta = db.cursor() cur_venta.execute(""" call get_ventas_por_fechas_y_sucursal(%s,%s,%s) ; """, (i[0], i[1], sucursal[0],)) data = cur_venta.fetchall() c_data = [] for j in data: c_data.append([sucursal[0],sucursal[1], j[0], j[1],j[2], str(j[3]),j[4]]) his_suc.append(c_data) print(i) print(c_data ) list_historial.append(his_suc) print('exit') return jsonify ( {'historico': list_historial,'ini':list[0][0], 'fin':list[2][1] } )
def saveuser(): name = request.form['name'] lastname = request.form['lastname'] user = request.form['user'] passw = request.form['pass'] admin = 'ADMIN' cur = mysql.get_db().cursor() cur.execute( 'INSERT INTO USUARIO (USU_NOMBRE, USU_APELLIDO, USU_CORREO, USU_CONTRASENHA, USU_TIPO) VALUES (%s, %s, %s, %s, %s)', (name, lastname, user, passw, admin)) mysql.get_db().commit() flash(u'El usuario se registró exitosamente', 'success') return redirect(url_for("modify_user"))
def muser(id): name = request.form['name'] lastname = request.form['lastname'] user = request.form['user'] passw = request.form['pass'] admin = 'ADMIN' cur = mysql.get_db().cursor() cur.execute( 'UPDATE USUARIO SET USU_NOMBRE=%s, USU_APELLIDO=%s, USU_CORREO=%s, USU_CONTRASENHA=%s, USU_TIPO=%s WHERE USU_CODIGO_USUARIO= %s', (name, lastname, user, passw, admin, id)) mysql.get_db().commit() flash(u'El usuario se modificó exitosamente', 'success') return redirect(url_for("modify_user"))
def historico_sucursles_por_fechas_periodo(fecha_ini,fecha_fin,periodo): d_fecha_ini = datetime.strptime(fecha_ini, '%Y-%m-%d').date() d_fecha_fin = datetime.strptime(fecha_fin, '%Y-%m-%d').date() fecha_actual = d_fecha_ini fechas_ini = [] """" while fecha_actual < d_fecha_fin: fecha_temp = fecha_actual+ timedelta(days=int(periodo)) fechas.append([fecha_actual , fecha_temp]) fecha_actual = fecha_temp+ timedelta(days=1) """ for result in perdelta(d_fecha_ini,d_fecha_fin, timedelta(days=int(periodo))): fechas_ini.append(result) fechas=[] for i in range(len(fechas_ini)-1): fechas.append([fechas_ini[i]+timedelta(days=1), fechas_ini[i+1]]) for i in fechas: print (i) from app import mysql cur_sucursales = mysql.get_db().cursor() cur_sucursales.execute("call get_all_empresas(); ") data = cur_sucursales.fetchall() list_sucursales = [] for i in data: list_sucursales.append([i , []]) for i in fechas: cur_historial = mysql.get_db().cursor() cur_historial.execute("""call get_ventas_por_fechas(%s ,%s );""" , (i[0],i[1],)) data_hist = cur_historial.fetchall() #print(data_hist)# data_his almaceda los puntos obtenidos en las graficas for it_data in data_hist: for it_suc in list_sucursales: if it_data[0] == it_suc[0][0]: it_suc[1].append([ str(it_data[2]), i[1]]) return jsonify({'ventas': list_sucursales, 'fecha_ini':fecha_ini, 'fecha_fin':fecha_fin})
def create_user(): try: content_json = request.json # print content_json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) #if content_json.setdefault('isAnonymous'): # content_json['name'] = '' # content_json['username'] = '' # content_json['about'] = '' else: if 'username' not in content_json or 'about' not in content_json or 'name' not in content_json \ or 'email' not in content_json or 'isAnonymous' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor() try: cursor.execute( """INSERT INTO `users` (`email`, `username`, `name`, `about`, `isAnonymous`) VALUES (%s, %s, %s, %s, %s);""", ( content_json['email'], content_json['username'], content_json['name'], content_json['about'], content_json['isAnonymous'] ) ) except MySQLdb.Error: return jsonify({'code': 5, 'response': "Incorrect request: user is already exist"}) user_id = cursor.lastrowid db.commit() content_json.update({'id': user_id}) return jsonify({'code': 0, 'response': content_json})
def create_forum(): try: content_json = request.json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'name' not in content_json or 'short_name' not in content_json or 'user' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor() try: cursor.execute( """INSERT INTO `forums` (`name`, `short_name`, `user`) VALUES (%s, %s, %s);""", ( content_json['name'], content_json['short_name'], content_json['user'] ) ) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request: name and short_name must be unique"}) forum_id = cursor.lastrowid db.commit() content_json.update({'id': forum_id}) return jsonify({'code': 0, 'response': content_json})
def delete_follow(): try: content_json = request.json # print content_json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'follower' not in content_json or 'followee' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute( """DELETE FROM `followers` WHERE `follower` = %s and `followee` = %s;""", ( content_json['follower'], content_json['followee'] ) ) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) db.commit() user = functions.user_details(cursor, content_json['follower']) return jsonify({'code': 0, 'response': user})
def ventas_fechas(fecha_ini,fecha_fin): print('inicia') from app import mysql cur = mysql.get_db().cursor() cur.execute(""" call get_ventas_por_fechas(%s ,%s ) ; """, (fecha_ini, fecha_fin,)) data = cur.fetchall() list = [] for i in data: list.append(i[2]) val_max = max(list) val_min = min(list) c_data = [] for i in data: is_max = 0 is_min = 0 if i[2] == val_max: is_max = 1 if i[2] == val_min: is_min = 1 c_data.append([i[0], i[1], str(i[2]), is_max, is_min]) print(fecha_ini) print(fecha_fin) return jsonify ( {'ventas':c_data, 'Fecha_ini': fecha_ini, 'Fecha_fin': fecha_fin} )
def create_thread(): try: content_json = request.json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'forum' not in content_json or 'title' not in content_json or 'isClosed' not in content_json \ or 'user' not in content_json or 'date' not in content_json or 'message' not in content_json \ or 'slug' not in content_json or 'isDeleted' not in content_json: return jsonify({ 'code': 3, 'response': "Incorrect request: some data missing" }) db = mysql.get_db() cursor = db.cursor() try: cursor.execute( """INSERT INTO `threads` (`forum`, `title`, `isClosed`, `user`, `date`,`message`, `slug`, `isDeleted`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s);""", (content_json['forum'], content_json['title'], content_json['isClosed'], content_json['user'], content_json['date'], content_json['message'], content_json['slug'], content_json['isDeleted'])) thread_id = cursor.lastrowid except MySQLdb.Error: return jsonify({ 'code': 3, 'response': "Incorrect request: user is already exist" }) db.commit() content_json.update({'id': thread_id}) return jsonify({'code': 0, 'response': content_json})
def community_(category): connection = mysql.get_db() cursor = connection.cursor() page = request.args.get('page', type=int, default=1) category = ",".join([(word.capitalize() if word != 'and' else word) for word in category.split('-')]) res_len = cursor.execute( "SELECT cid, name, uid FROM Community WHERE (cid) IN (SELECT cid FROM CommunityCategories WHERE categoryName='{}')" .format(category)) start_row = MAX_PER_PAGE * (page - 1) end_row = start_row + MAX_PER_PAGE if ( start_row + MAX_PER_PAGE < res_len) else res_len communities = [ dict(cid=row[0], name=row[1].replace('/', '\''), uid=row[2]) for row in cursor.fetchall()[start_row:end_row] ] cursor.close() pagination = Pagination(page=page, total=res_len, per_page=MAX_PER_PAGE, css_framework='bootstrap3') return render_template('communities.html', communities=communities, pagination=pagination)
def update_user(): try: content_json = request.json # print content_json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'user' not in content_json or 'about' not in content_json or 'name' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute( """UPDATE `users` SET `about` = %s, `name` = %s WHERE `email` = %s;""", ( content_json['about'], content_json['name'], content_json['user'] ) ) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request: user is already exist"}) db.commit() user = functions.user_details(cursor, content_json['user']) return jsonify({'code': 0, 'response': user})
def index(): connection = mysql.get_db() cursor = connection.cursor() cursor.execute("SELECT title FROM event") all_events = [tup[0] for tup in cursor.fetchall()] return render_template('index.html', all_events=all_events)
def get(self, cp): cur = mysql.get_db().cursor() #query insert record cur.execute( "INSERT INTO records (url, cp, host) VALUES ('/postal',%s,%s)", (cp, request.remote_addr)) # query consult código postal query = cur.execute( "SELECT cp,colonia,municipio,estado,ciudad FROM `location_postal` WHERE cp=%s ", (cp)) cur.connection.commit() if query > 0: #result=cur.fetchone() #col_names = [i[0] for i in cur.description] col_names = cur.fetchone() col_colonias = [i[1] for i in cur] res = { "status": "success", "data": { "cp": col_names[0], "colonia": col_colonias, "municipio": col_names[2], "estado": col_names[3], "ciudad": col_names[4] }, "error": "" } return res else: return { "status": "error", "data": "", "error": "No se encontraron coincidencias" }
def sign_up(): connection = mysql.get_db() cursor = connection.cursor() form = SignupForm(request.form) if request.method == "POST": if form.validate() == False: return jsonify(errors=True, firstname_error=form.firstname.errors, lastname_error=form.lastname.errors, username_error=form.username.errors, password_error=form.password.errors, confirm_password_error=form.confirm_password.errors, email_error=form.email.errors) # return render_template('signup.html', form=form) else: password_hash = generate_password_hash(form.password.data) attr = (form.firstname.data, form.lastname.data, form.email.data, form.username.data, password_hash) cursor.callproc('CreateUser', (attr[0], attr[1], attr[2], attr[3], attr[4])) connection.commit() session['username'] = form.username.data return jsonify(errors=False) # return("thank you for signing up!") elif request.method == 'GET': return render_template('signup.html', form=form)
def update_post(): try: content_json = request.json # print content_json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'post' not in content_json or 'message' not in content_json: return jsonify({ 'code': 3, 'response': "Incorrect request: some data missing" }) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute( """UPDATE `posts` SET `message` = %s WHERE `id` = %s;""", ( content_json['message'], int(content_json['post']), )) db.commit() except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) post = functions.post_details(cursor, int(content_json['post'])) return jsonify({'code': 0, 'response': post})
def ventas_anio_actual(): fecha_ = datetime.now() fecha_ini = str(fecha_.year) +'/01/01' fecha_fin = str(fecha_.year) +'/12/31' from app import mysql cur = mysql.get_db().cursor() cur.execute(""" call get_ventas_por_fechas(%s ,%s ) ; """, (fecha_ini, fecha_fin,) ) data = cur.fetchall() list = [] for i in data: list.append(i[2]) val_max = max(list) val_min = min(list) c_data = [] for i in data: is_max = 0 is_min = 0 if i[2] == val_max: is_max = 1 if i[2] == val_min: is_min = 1 c_data.append([i[0], i[1], str(i[2]), is_max, is_min]) return jsonify ( {'historico': c_data,'anio': fecha_.year} )
def restore_post(): try: content_json = request.json # print content_json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'post' not in content_json: return jsonify({ 'code': 3, 'response': "Incorrect request: some data missing" }) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute( """UPDATE `threads` SET `posts` = `posts` + 1 WHERE `id` = ( SELECT `thread` FROM `posts` WHERE `id` = %s );""", (int(content_json['post']), )) cursor.execute( """UPDATE `posts` SET `isDeleted` = FALSE WHERE `id` = %s;""", (content_json['post'], )) db.commit() except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) return jsonify({'code': 0, 'response': {'post': content_json['post']}})
def thread_detail(): thread_id = request.args.get('thread', None) related = request.args.getlist('related') thread_id = int(thread_id) if thread_id is ('' or None) and thread_id <= 0: return jsonify({ 'code': 2, 'response': "Incorrect request: some data missing" }) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) thread = functions.thread_details(cursor, thread_id) if thread is None: return jsonify({'code': 1, 'response': "Post not found"}) if 'thread' in related: return jsonify({'code': 3, 'response': "Incorrect request"}) if 'user' in related: user = functions.user_details(cursor, thread['user']) thread.update({'user': user}) if 'forum' in related: forum = functions.forum_details(cursor, thread['forum']) thread.update({'forum': forum}) return jsonify({'code': 0, 'response': thread})
def restore_thread(): try: content_json = request.json # print content_json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'thread' not in content_json or int(content_json['thread']) <= 0: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: count = cursor.execute( """UPDATE `posts` SET `isDeleted` = FALSE WHERE `thread` = %s;""", (content_json['thread'],) ) cursor.execute( """UPDATE `threads` SET `isDeleted` = FALSE, `posts` = %s WHERE `id` = %s;""", (count, content_json['thread']) ) db.commit() except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) return jsonify({'code': 0, 'response': {'thread': content_json['thread']}})
def settings(): form = interest_form(request.form) categories = form.categories.data connection = mysql.get_db() cursor = connection.cursor() cursor.execute("SELECT uid FROM User WHERE username = '******'".format( session['username'])) uid = cursor.fetchall()[0][0] if request.method == "POST": cursor.execute( "DELETE FROM HasInterests WHERE HasInterests.uid = '{}'".format( uid)) connection.commit() for category in categories: cursor.execute( "INSERT INTO HasInterests(uid, categoryName) VALUES('{}', '{}')" .format(uid, category)) connection.commit() cursor.execute( "SELECT categoryName FROM HasInterests WHERE uid ={}".format(uid)) pre_selected = [tup[0] for tup in cursor.fetchall()] form.categories.data = pre_selected return render_template('settings.html', form=form)
def create_thread(): try: content_json = request.json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'forum' not in content_json or 'title' not in content_json or 'isClosed' not in content_json \ or 'user' not in content_json or 'date' not in content_json or 'message' not in content_json \ or 'slug' not in content_json or 'isDeleted' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor() try: cursor.execute( """INSERT INTO `threads` (`forum`, `title`, `isClosed`, `user`, `date`,`message`, `slug`, `isDeleted`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s);""", ( content_json['forum'], content_json['title'], content_json['isClosed'], content_json['user'], content_json['date'], content_json['message'], content_json['slug'], content_json['isDeleted'] ) ) thread_id = cursor.lastrowid except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request: user is already exist"}) db.commit() content_json.update({'id': thread_id}) return jsonify({'code': 0, 'response': content_json})
def update_thread(): try: content_json = request.json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'message' not in content_json or 'slug' not in content_json or 'thread' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute( """UPDATE `threads` SET `message` = %s, `slug` = %s WHERE `id` = %s;""", ( content_json['message'], content_json['slug'], int(content_json['thread']) ) ) db.commit() except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) thread = functions.thread_details(cursor, int(content_json['thread'])) return jsonify({'code': 0, 'response': {'thread': thread}})
def update_post(): try: content_json = request.json # print content_json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'post' not in content_json or 'message' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute( """UPDATE `posts` SET `message` = %s WHERE `id` = %s;""", ( content_json['message'], int(content_json['post']), ) ) db.commit() except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) post = functions.post_details(cursor, int(content_json['post'])) return jsonify({'code': 0, 'response': post})
def thread_detail(): thread_id = request.args.get('thread', None) related = request.args.getlist('related') thread_id = int(thread_id) if thread_id is ('' or None) and thread_id <= 0: return jsonify({'code': 2, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) thread = functions.thread_details(cursor, thread_id) if thread is None: return jsonify({'code': 1, 'response': "Post not found"}) if 'thread' in related: return jsonify({'code': 3, 'response': "Incorrect request"}) if 'user' in related: user = functions.user_details(cursor, thread['user']) thread.update({'user': user}) if 'forum' in related: forum = functions.forum_details(cursor, thread['forum']) thread.update({'forum': forum}) return jsonify({'code': 0, 'response': thread})
def index(): from app import mysql cur = mysql.get_db().cursor() cur.execute("""Select * from empresas """) data = cur.fetchall() print(data) return render_template('index.html')
def vote_post(): try: content_json = request.json # print content_json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'post' not in content_json or 'vote' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) if content_json['vote'] != 1 and content_json['vote'] != -1: return jsonify({'code': 3, 'response': "Incorrect request: vote is wrong"}) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: if content_json['vote'] == 1: cursor.execute( """UPDATE `posts` SET `likes` = `likes` + 1, `points` = `points` + 1 WHERE `id` = %s;""", (int(content_json['post']),)) else: cursor.execute( """UPDATE `posts` SET `dislikes` = `dislikes` + 1, `points` = `points` - 1 WHERE `id` = %s;""", (int(content_json['post']),)) db.commit() except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) post = functions.post_details(cursor, int(content_json['post'])) return jsonify({'code': 0, 'response': post})
def restore_thread(): try: content_json = request.json # print content_json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'thread' not in content_json or int(content_json['thread']) <= 0: return jsonify({ 'code': 3, 'response': "Incorrect request: some data missing" }) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: count = cursor.execute( """UPDATE `posts` SET `isDeleted` = FALSE WHERE `thread` = %s;""", (content_json['thread'], )) cursor.execute( """UPDATE `threads` SET `isDeleted` = FALSE, `posts` = %s WHERE `id` = %s;""", (count, content_json['thread'])) db.commit() except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) return jsonify({'code': 0, 'response': {'thread': content_json['thread']}})
def remove_post(): try: content_json = request.json print content_json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'post' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute( """UPDATE `threads` SET `posts` = `posts` - 1 WHERE `id` = ( SELECT `thread` FROM `posts` WHERE `id` = %s );""", (int(content_json['post']),) ) cursor.execute("""UPDATE `posts` SET `isDeleted` = TRUE WHERE `id` = %s;""", (content_json['post'],)) db.commit() except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) return jsonify({'code': 0, 'response': {'post': content_json['post']}})
def create_forum(): try: content_json = request.json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'name' not in content_json or 'short_name' not in content_json or 'user' not in content_json: return jsonify({ 'code': 3, 'response': "Incorrect request: some data missing" }) db = mysql.get_db() cursor = db.cursor() try: cursor.execute( """INSERT INTO `forums` (`name`, `short_name`, `user`) VALUES (%s, %s, %s);""", (content_json['name'], content_json['short_name'], content_json['user'])) except MySQLdb.Error: return jsonify({ 'code': 3, 'response': "Incorrect request: name and short_name must be unique" }) forum_id = cursor.lastrowid db.commit() content_json.update({'id': forum_id}) return jsonify({'code': 0, 'response': content_json})
def forum_detail(): short_name = request.args.get('forum', None) related = request.args.get('related', []) if short_name is ('' or None): return jsonify({ 'code': 2, 'response': "Incorrect request: some data missing" }) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute("""SELECT * FROM `forums` WHERE `short_name` = %s;""", (short_name, )) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) forum = cursor.fetchone() if related == 'user': user = functions.user_details(cursor, forum['user']) forum.update({'user': user}) if forum is None: return jsonify({'code': 1, 'response': "Post not found"}) return jsonify({'code': 0, 'response': forum})
def c_nivel_estudio(): from app import mysql cur = mysql.get_db().cursor() cur.execute("""Select * from nivel_estudios """) data = cur.fetchall() print(data) return render_template('catalogos/niveles_estudio.html', niveles=data)
def update_thread(): try: content_json = request.json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'message' not in content_json or 'slug' not in content_json or 'thread' not in content_json: return jsonify({ 'code': 3, 'response': "Incorrect request: some data missing" }) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute( """UPDATE `threads` SET `message` = %s, `slug` = %s WHERE `id` = %s;""", (content_json['message'], content_json['slug'], int(content_json['thread']))) db.commit() except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) thread = functions.thread_details(cursor, int(content_json['thread'])) return jsonify({'code': 0, 'response': {'thread': thread}})
def details_post(): post_id = request.args.get('post', None) related = request.args.getlist('related') if post_id is None: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) post_id = int(post_id) if post_id < 1: return jsonify({'code': 1, 'response': "Incorrect request: post don\'t found"}) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) post = functions.post_details(cursor, post_id) if 'user' in related: user = functions.user_details(cursor, post['user']) post.update({'user': user}) if 'forum' in related: forum = functions.forum_details(cursor, post['forum']) post.update({'forum': forum}) if 'thread' in related: thread = functions.thread_details(cursor, post['thread']) post.update({'thread': thread}) return jsonify({'code': 0, 'response': post})
def create_post(): try: content_json = request.json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'isApproved' not in content_json or 'user' not in content_json or 'date' not in content_json \ or 'message' not in content_json or 'isSpam' not in content_json or 'isHighlighted' not in content_json \ or 'thread' not in content_json or 'forum' not in content_json or 'isDeleted' not in content_json\ or 'isEdited' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor() if content_json['parent'] is None: isRoot = True path = '' else: isRoot = False cursor.execute("""SELECT `path` FROM `posts` WHERE `id` = %s""", (content_json['parent'],)) path = cursor.fetchone()[0] try: cursor.execute( """INSERT INTO `posts` (`isApproved`, `user`, `date`, `message`, `isSpam`,`isHighlighted`, `thread`, `forum`,`isDeleted`, `isEdited`,`parent`,`isRoot`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);""", ( content_json['isApproved'], content_json['user'], content_json['date'], content_json['message'], content_json['isSpam'], content_json['isHighlighted'], content_json['thread'], content_json['forum'], content_json['isDeleted'], content_json['isEdited'], content_json['parent'], isRoot ) ) post_id = cursor.lastrowid base36 = int2str(int(post_id), radix=36) path += str(len(base36)) + base36 cursor.execute("""UPDATE `posts` SET path = %s WHERE `id` = %s""", (path, post_id)) cursor.execute("""UPDATE `threads` SET `posts` = `posts` + 1 WHERE `id` = %s;""", (content_json['thread'],)) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request: post is already exist"}) db.commit() content_json.update({'id': post_id}) return jsonify({'code': 0, 'response': content_json})
def status(): tables = ['users', 'threads', 'forums', 'posts'] response = {} db = mysql.get_db() cursor = db.cursor() for table in tables: cursor.execute('SELECT COUNT(1) FROM %s' % table) db.commit() response[table] = cursor.fetchone()[0] #db.close() return jsonify({'code': 0, 'response': response})
def details_user(): user_email = request.args.get('user', None) if user_email is ('' or None): return jsonify({'code': 2, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) user = functions.user_details(cursor, user_email) if user is None: return jsonify({'code': 1, 'response': 'User not found '}) return jsonify({'code': 0, 'response': user})
def list_posts_from_forum(): forum = request.args.get('forum', None) since = request.args.get('since', None) limit = request.args.get('limit', None) order = request.args.get('order', 'DESC') related = request.args.getlist('related') if forum is None: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) else: query = """SELECT `id`, `message`, `forum`, `user`, `thread`, `likes`, `dislikes`, `points`, `isDeleted`, `isSpam`, `isEdited`, `isApproved`, `isHighlighted`, `date`, `parent` FROM `posts` WHERE `forum` = %s """ query_params = (forum,) if since is not None: query += " AND `date` >= %s " query_params += (since,) query += " ORDER BY `date` " + order + " " if limit is not None: query += " LIMIT %s;" query_params += (int(limit),) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute(query, query_params) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) posts = [i for i in cursor.fetchall()] resp = [] for post in posts: if 'user' in related: user = functions.user_details(cursor, post['user']) post.update({'user': user}) if 'forum' in related: forum = functions.forum_details(cursor, post['forum']) post.update({'forum': forum}) if 'thread' in related: thread = functions.thread_details(cursor, post['thread']) post.update({'thread': thread}) post.update({'date': str(post['date'])}) resp.append(post) return jsonify({'code': 0, 'response': resp})
def list_followers(): user_email = request.args.get('user', None) order = request.args.get('order', 'DESC') limit = request.args.get('limit', None) since_id = request.args.get('since_id', '1') if user_email is None: return jsonify({'code': 1, 'response': "User not found "}) if limit is None: limit = " " else: limit = ' LIMIT ' + limit db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute( """SELECT `about`, `email`, `id`, `isAnonymous`, `name`, `username` FROM `followers` AS `f` JOIN `users` ON `users`.`email` = `f`.`follower` WHERE `f`.`followee` = %s AND `users`.`id` >= %s ORDER BY `name` """ + order + limit + " ;", ( user_email, int(since_id) ) ) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) users = [i for i in cursor.fetchall()] for user in users: following = functions.list_following(cursor, user['email']) followers = functions.list_followers(cursor, user['email']) cursor.execute( """SELECT `thread` FROM `subscriptions` WHERE `user` = %s;""", ( user['email'], ) ) threads = [i['thread'] for i in cursor.fetchall()] user.update({'following': following, 'followers': followers, 'subscriptions': threads}) return jsonify({'code': 0, 'response': users})
def list_threads_from_forum(): forum = request.args.get('forum', None) since = request.args.get('since', None) limit = request.args.get('limit', None) order = request.args.get('order', 'DESC') related = request.args.getlist('related') if forum is None: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) else: query = """ SELECT * FROM `threads` WHERE `forum` = %s """ query_params = (forum,) if since is not None: query += " AND `date` >= %s " query_params += (since,) query += " ORDER BY `date` " + order + " " if limit is not None: query += "LIMIT %s;" query_params += (int(limit),) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute(query,query_params) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) resp = [] threads = [i for i in cursor.fetchall()] for thread in threads: if 'user' in related: user = functions.user_details(cursor, thread['user']) thread.update({'user': user}) if 'forum' in related: forum = functions.forum_details(cursor, thread['forum']) thread.update({'forum': forum}) thread.update({'date': str(thread['date'])}) resp.append(thread) return jsonify({'code': 0, 'response': resp})
def clear(): db = mysql.get_db() cursor = db.cursor() cursor.execute('SET FOREIGN_KEY_CHECKS=0') cursor.execute('TRUNCATE TABLE users') cursor.execute('TRUNCATE TABLE threads') cursor.execute('TRUNCATE TABLE posts') cursor.execute('TRUNCATE TABLE forums') cursor.execute('TRUNCATE TABLE followers') cursor.execute('TRUNCATE TABLE subscriptions') cursor.execute('SET FOREIGN_KEY_CHECKS=1') db.commit() return jsonify({'code': 0, 'response': 'OK'})
def list_users_from_forum(): forum = request.args.get('forum', None) since_id = request.args.get('since_id', None) limit = request.args.get('limit', None) order = request.args.get('order', 'DESC') if forum is None: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) if since_id is None: since_str = " " else: since_str = " AND users.id >= " + since_id if limit is None: limit = " " else: limit = ' LIMIT ' + limit db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute( """SELECT * FROM `users` WHERE `email` IN (SELECT DISTINCT `user` FROM `posts` WHERE `forum` = %s)""" + since_str + " ORDER BY `name` " + order + limit + " ;", ( forum, ) ) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) resp = [] users = [i for i in cursor.fetchall()] for user in users: user = functions.user_details(cursor, user['email']) resp.append(user) return jsonify({'code': 0, 'response': resp})
def list_threads_from_thread(): forum = request.args.get('forum', None) user = request.args.get('user', None) since = request.args.get('since', None) limit = request.args.get('limit', None) order = request.args.get('order', 'DESC') if user is None and forum is None: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) if user is not None: query = """SELECT * FROM `threads` WHERE `user` = %s """ query_params = (user,) else: query = """SELECT * FROM `threads` WHERE `forum` = %s """ query_params = (forum,) if since is not None: query += "AND `date` >= %s " query_params += (since,) query += "ORDER BY `date` " + order + " " if limit is not None: query += "LIMIT %s;" query_params += (int(limit),) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute(query,query_params) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) threads = [i for i in cursor.fetchall()] for thread in threads: thread.update({'date': str(thread['date'])}) return jsonify({'code': 0, 'response': threads})
def list_posts_from_user(): user = request.args.get('user', None) since = request.args.get('since', None) limit = request.args.get('limit', None) order = request.args.get('order', 'DESC') if user is None: return jsonify({'code': 1, 'response': "User not found "}) query = """SELECT `id`, `message`, `forum`, `user`, `thread`, `likes`, `dislikes`, `points`, `isDeleted`, `isSpam`, `isEdited`, `isApproved`, `isHighlighted`, `date`, `parent` FROM `posts` WHERE `user` = %s """ query_params = (user,) if since is not None: query += "AND `date` >= %s " query_params += (since,) query += "ORDER BY `date` " + order + " " if limit is not None: query += "LIMIT %s;" query_params += (int(limit),) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute(query, query_params) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) posts = [i for i in cursor.fetchall()] for post in posts: post.update({'date': str(post['date'])}) return jsonify({'code': 0, 'response': posts})
def close_thread(): try: content_json = request.json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'thread' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor() try: cursor.execute( """UPDATE `threads` SET `isClosed` = TRUE WHERE `id` = %s;""", (int(content_json['thread']),) ) except MySQLdb.Error: db.rollback() db.commit() return jsonify({'code': 0, 'response': content_json})
def subscribe(): try: content_json = request.json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'user' not in content_json or 'thread' not in content_json: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor() try: cursor.execute( """INSERT INTO `subscriptions` (`user`, `thread`) VALUES (%s, %s);""", ( content_json['user'], content_json['thread'], ) ) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) db.commit() return jsonify({'code': 0, 'response': content_json})
def list_posts(): forum = request.args.get('forum', None) thread = request.args.get('thread', None) since = request.args.get('since') limit = request.args.get('limit') order = request.args.get('order', 'desc') if thread is None and forum is None: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) if forum is not None: query = """SELECT * FROM `posts` WHERE `forum` = %s """ query_params = (forum,) else: query = """SELECT * FROM `posts` WHERE `thread` = %s """ query_params = (thread,) if since is not None: query += "AND `date` >= %s " query_params += (since,) query += "ORDER BY `date` " + order + " " if limit is not None: query += "LIMIT %s;" query_params += (int(limit),) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) cursor.execute(query, query_params) posts = [i for i in cursor.fetchall()] for post in posts: post.update({'date': str(post['date'])}) return jsonify({'code': 0, 'response': posts})
def unsubscribe(): try: content_json = request.json # print content_json except BadRequest: return jsonify({'code': 2, 'response': "Invalid request(syntax)"}) if 'user' not in content_json or 'thread' not in content_json : return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor() try: cursor.execute( """DELETE FROM`subscriptions` WHERE `user` = %s AND `thread` = %s ;""", ( content_json['user'], int(content_json['thread']), ) ) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) db.commit() return jsonify({'code': 0, 'response': content_json})
def forum_detail(): short_name = request.args.get('forum', None) related = request.args.get('related', []) if short_name is ('' or None): return jsonify({'code': 2, 'response': "Incorrect request: some data missing"}) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute("""SELECT * FROM `forums` WHERE `short_name` = %s;""", (short_name,)) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) forum = cursor.fetchone() if related == 'user': user = functions.user_details(cursor, forum['user']) forum.update({'user': user}) if forum is None: return jsonify({'code': 1, 'response': "Post not found"}) return jsonify({'code': 0, 'response': forum})
def list_posts_from_threads(): thread = request.args.get('thread', None) since = request.args.get('since', " ") limit = request.args.get('limit', None) order = request.args.get('order', 'desc') sort = request.args.get('sort', 'flat') if thread is None: return jsonify({'code': 3, 'response': "Incorrect request: some data missing"}) if sort is "flat": query = """SELECT * FROM `posts` WHERE `thread` = %s """ query_params = (thread,) if since is not None: query += "AND `date` >= %s " query_params += (since,) query += "ORDER BY `date` " + order + " " if limit is not None: query += "LIMIT %s;" query_params += (int(limit),) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute(query, query_params) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) posts = [i for i in cursor.fetchall()] for post in posts: post.update({'date': str(post['date'])}) return jsonify({'code': 0, 'response': posts}) else: subquery = """SELECT `path` FROM `posts` WHERE `isRoot` = TRUE AND `thread` = %s """ query_params = (thread,) if since is not None: subquery += " AND `date` >= %s " query_params += (since,) if limit is not None: subquery += " ORDER BY `path` " + order + " LIMIT %s " query_params += (int(limit),) query = """SELECT * FROM ( """ + subquery + """ ) as root INNER JOIN `posts` as child ON child.path LIKE CONCAT(root.path,'%%') """ if since is not None: query += " WHERE child.date >= %s " query_params += (since,) query += " ORDER BY root.path " + order + ", child.path ASC " if sort == 'tree' and limit is not None: query += " LIMIT %s;" query_params += (int(limit),) db = mysql.get_db() cursor = db.cursor(MySQLdb.cursors.DictCursor) try: cursor.execute(query, query_params) except MySQLdb.Error: return jsonify({'code': 3, 'response': "Incorrect request"}) posts = [i for i in cursor.fetchall()] for post in posts: post.update({'date': str(post['date'])}) return jsonify({'code': 0, 'response': posts})