def collection(): sql = "select * from tbl_sp where id_sp = 33" cursor.execute(sql) record1 = cursor.fetchall() sql = "select * from tbl_sp where id_sp = 34" cursor.execute(sql) record2 = cursor.fetchall() return render_template("collection.html", r1=record1, r2=record2)
def index(): sql = "select * from tbl_sp limit 6" cursor.execute(sql) record = cursor.fetchall() sql = "select * from tbl_sp where id_sp = 33" cursor.execute(sql) record1 = cursor.fetchall() sql = "select * from tbl_sp where id_sp = 34" cursor.execute(sql) record2 = cursor.fetchall() return render_template("index.html", r=record, r1=record1, r2=record2)
def getFaixaCompositores(): cursor.execute("SELECT * from FaixasCompositorAux") faixacompositores = [] for row in cursor.fetchall(): faixacompositores.append({'id_compositor':row[0], 'id_faixa': row[1]}) return jsonify(faixacompositores)
def get_recommendation(): if request.method == "POST": RATINGS_PATH = '/srv/movielens/ratings_matrix.npz' RATINGS_MATRIX = ratings_matrix.ratings_matrix(RATINGS_PATH) user_id = request.json["user_id"] sql_query = "SELECT DISTINCT movie_id FROM user_favorite WHERE user_id = %s" cursor.execute(sql_query, tuple([user_id])) movie_ids = cursor.fetchall() movie_list = [] if len(movie_ids) == 0: result = {"recommended_movies": movie_list} return jsonify(result) liked = {} for movie_id in movie_ids: movie_id = movie_id[0] liked[movie_id] = 50 print(liked) recommendations = get_matches(RATINGS_MATRIX, liked, NUM_RECOMMENDATIONS) print(recommendations) sql_query = "SELECT * FROM movie WHERE id = %s" for index in range(NUM_RECOMMENDATIONS): movie_id = recommendations[index][1] movie_id = RATINGS_MATRIX.imdb_id(movie_id) cursor.execute(sql_query, tuple([int(movie_id)])) movie_row = cursor.fetchone() movie = get_movie_details(movie_row) movie_list.append(movie) result = {"recommended_movies": movie_list} print(movie_list) return jsonify(result)
def getTracks(): cursor.execute( """SELECT Faixas.id,Faixas.id_faixa_album,Faixas.nome,Faixas.descricao,Faixas.tipo_gravacao,Faixas.album,Faixas.tipo_composicao,Faixas.duracao,Compositor.nome,Interprete.nome,Composicao.nome From Faixas, FaixasCompositorAux,Compositor, FaixasInterpreteAux, Interprete,Composicao WHERE Faixas.id = FaixasCompositorAux.id_faixa AND FaixasCompositorAux.id_compositor = Compositor.id AND FaixasInterpreteAux.id_faixa = Faixas.id AND FaixasInterpreteAux.id_interprete = Interprete.id AND Faixas.tipo_composicao = Composicao.id GROUP BY Faixas.id,Faixas.id_faixa_album,Faixas.nome,Faixas.descricao,Faixas.tipo_gravacao,Faixas.album,Faixas.tipo_composicao,Faixas.duracao,Compositor.nome,Interprete.nome,Composicao.nome ORDER BY Faixas.id""") faixas = [] for row in cursor.fetchall(): faixas.append({ 'id': row[0], 'id_faixa_album': row[1], 'nome': row[2], 'descricao': row[3], 'tipo_gravacao': row[4], 'album': row[5], 'tipo_composicao': row[6], 'duracao': (str(row[7])), 'nome_compositor': row[8], 'nome_interprete': row[9], 'nome_composicao': row[10] }) return jsonify(faixas)
def index(): if "username" in session: sql = "select room_name,room_price,room_decr from tbl_room" cursor.execute(sql) record = cursor.fetchall() return render_template("index.html",r=record,us=session["username"]) else: sql = "select room_name,room_price,room_decr from tbl_room" cursor.execute(sql) record = cursor.fetchall() return render_template("index.html",r=record)
def getInterpretes(): cursor.execute("SELECT * from Interprete") interpretes = [] for row in cursor.fetchall(): interpretes.append({'id':row[0], 'nome': row[1], 'tipo':row[2]}) return jsonify(interpretes)
def getPeriodos(): cursor.execute("SELECT * from PeriodoMusical") periodos = [] for row in cursor.fetchall(): periodos.append({ 'id': row[0], 'descricao': row[1], 'tempo_atividade': row[2] }) return jsonify(periodos)
def product(): if "username" in session: id_sp = request.args.get("id_sp", type=int) sql = "select * from tbl_sp" cursor.execute(sql) record = cursor.fetchall() print(record) return render_template("ad_product.html", r=record, id_sp=id_sp) else: return redirect("/login")
def get(self): get_parcels_query = "SELECT * FROM parcels" cursor.execute(get_parcels_query) all_parcels = cursor.fetchall() return { 'status': 200, 'data': json.dumps(all_parcels, default=str, separators=(',', ': ')) }, 200
def getGravadoras(): cursor.execute("SELECT * from Gravadora") gravadoras = [] for row in cursor.fetchall(): gravadoras.append({ 'id': row[0], 'nome': row[1], 'endereco': row[2], 'telefone': row[3], 'site': row[4] }) return jsonify(gravadoras)
def get(self, _id): get_query = """SELECT * FROM parcels WHERE placedby = %s""" cursor.execute(get_query, (_id, )) user_parcels = cursor.fetchall() return { 'status': 200, 'data': json.dumps(user_parcels, default=str, separators=(',', ': ')) }, 200
def admin(): if "username" in session: sql = "select * from tbl_user" # Executing a SQL query cursor.execute(sql) # Fetch result record = cursor.fetchall() return render_template("admin/master.html",r=record,us=session["username"]) else: return redirect("/")
def login_dn(): us = request.form.get("us") pa = request.form.get("pa") sql = "select * from tbl_user where user_name = '" + us + "' and pass_word='" + pa + "'" cursor.execute(sql) # Fetch result record = cursor.fetchall() if (len(record) == 1): session["username"] = us return redirect("/product") else: return render_template("login.html")
def getCompositores(): cursor.execute("SELECT * from Compositor") compositores = [] for row in cursor.fetchall(): compositores.append({ 'id': row[0], 'nome': row[1], 'cidade_natal': row[2], 'pais_natal': row[3], 'data_nasc': row[4], 'data_morte': row[5], 'periodo_musical': row[6] }) return jsonify(compositores)
def maps(): cursor.execute("select title, address, time, img, long, lat from db_maps") version = cursor.fetchall() geo_json = [] for row in version: geo_json.append({ "loc": [row[5], row[4]], "title": row[0], "address": row[1], "time": row[2], "img": row[3] }) # cursor.close() return render_template("maps.html", data=json.dumps(geo_json))
def getAlbums(): cursor.execute("SELECT * from Album") albums = [] for row in cursor.fetchall(): print(row) albums.append({ 'cod': row[0], 'nome': row[1], 'data_gravacao': row[2], 'data_compra': row[3], 'preco_compra': (float(row[4])), 'tipo_compra': row[5], 'cod_gravadora': row[6], }) return jsonify(albums)
def get_favorite(): if request.method == "POST": user_id = request.json["user_id"] sql_query = "SELECT movie_id FROM user_favorite WHERE user_id = %s" cursor.execute(sql_query, tuple([user_id])) rows = cursor.fetchall() movie_list = [] for movie_id in rows: movie_id = movie_id[0] sql_query = "SELECT * FROM movie WHERE id = %s" cursor.execute(sql_query, tuple([movie_id])) movie_row = cursor.fetchone() movie = get_movie_details(movie_row) movie_list.append(movie) result = {"favorite_movies": movie_list} return jsonify(result)
def shop(): keyword = request.args.get("search", type=str) max_s = request.args.get("max", type=int) min_s = request.args.get("min", type=int) sql = f"SELECT * FROM tbl_sp;" if keyword != None: sql = f"select * from tbl_sp WHERE ten_sp LIKE N'%{keyword}%';" elif max_s != None and min_s != None: sql = f"select * from tbl_sp WHERE gia_sp BETWEEN '{min_s}' AND '{max_s}';" cursor.execute(sql) record = cursor.fetchall() if record: err = "" else: err = f"Không tìm thấy sản phẩm nào chứa '{keyword}'" connection.commit() return render_template("shop.html", r=record, err=err)
def getPlaylists(): cursor.execute(""" SELECT p.id,p.nome,p.descricao,p.data_criacao, COUNT(fp.id_faixa),convert(char(8),dateadd(second,SUM ( DATEPART(hh,(convert(datetime,f.duracao,1))) * 3600 + DATEPART(mi, (convert(datetime,f.duracao,1))) * 60 + DATEPART(ss,(convert(datetime,f.duracao,1)))),0),108) FROM FaixasPlaylistAux as fp RIGHT OUTER JOIN Playlist as p ON fp.id_playlist = p.id LEFT OUTER JOIN Faixas as f ON fp.id_faixa = f.id GROUP BY p.id,p.nome,p.descricao,p.data_criacao,fp.id_playlist ORDER BY p.id ASC """) playlists = [] for row in cursor.fetchall(): playlists.append({ 'id': row[0], 'nome': row[1], 'descricao': row[2], 'data_criacao': row[3], 'num_musicas': row[4], 'tempo_total': row[5] }) return jsonify(playlists)
from db import connection, cursor, pointsCache cursor.execute(''' SELECT * FROM preset_preset LIMIT 50 ''') for row in cursor.fetchall(): for metadata, value in zip(cursor.description, row): print '%-16s%s' % (metadata[0], value) print
def room(): room_id = request.args.get("room_id", type = int) sql = "select * from tbl_room order by room_id ASC" cursor.execute(sql) record = cursor.fetchall() return render_template("admin/room/room.html", room = record, room_id = room_id)
def getFaixasInterpretes(): cursor.execute("SELECT * from FaixasInterpreteAux") compositores = [] for row in cursor.fetchall(): compositores.append({'id_faixa': row[0], 'id_interprete': row[1]}) return jsonify(compositores)
from db import connection, cursor, pointsCache cursor.execute('DELETE FROM point_preset WHERE preset_type = "area"') cursor.execute('SELECT id FROM preset WHERE type = "area"') presets_ids = [row[0] for row in cursor.fetchall()] for preset_id in presets_ids: cursor.execute( ''' INSERT INTO point_preset ( point_id , preset_type , preset_id , score , nscore ) SELECT p_p.point1_id AS point_id , "area" AS preset_type , %s AS preset_id , SUM(p_p.score * pr_p.value) AS score , SUM(p_p.score * pr_p.value) AS nscore FROM preset_point AS pr_p INNER JOIN point_point AS p_p ON p_p.point2_id = pr_p.point_id WHERE pr_p.preset_type = "area" AND pr_p.preset_id = %s GROUP BY
def shoes(): sql = "select * from tbl_sp limit 6" cursor.execute(sql) record = cursor.fetchall() return render_template("shoes.html", r=record)
def getComposicoes(): cursor.execute("SELECT * from Composicao") composicoes = [] for row in cursor.fetchall(): composicoes.append({'id': row[0], 'nome': row[1], 'descricao': row[2]}) return jsonify(composicoes)
from db import connection, cursor, pointsCache from collections import defaultdict from math import sqrt # get all points around those print 'Query points...' cursor.execute( 'SELECT p.id, p.x, p.y, p.z FROM point AS p INNER JOIN preset_point AS pp ON pp.point_id = p.id WHERE pp.preset_type = "area" GROUP BY p.id ORDER BY p.id DESC' ) print 'Fetch points...' points = cursor.fetchall() print 'Insert points...' i = 0 n = len(points) # for id1, x, y, z in points: # cursor.execute(''' # INSERT IGNORE INTO # point # ( x # , y # , z # ) # SELECT # %s + dx AS x # , %s + dy AS y # , %s + dz AS z # FROM # dpoint # ''', (x, y, z)) # i += 1 # if i % 100:
preset_type = "area" ''') print '\nGetting all the presets...' cursor.execute( ''' SELECT type, id, rscore FROM preset WHERE type = "area" ''') rows = cursor.fetchall() print '\nUpdating...' t0 = time() for preset_type, preset_id, rscore in rows: t1 = time() cursor.execute( ''' INSERT INTO point_preset SELECT p_p.point2_id AS point_id, %s AS preset_type, %s AS preset_id, SUM(p_p.score) AS score,
def user(): user_id = request.args.get("user_id", type = int) sql = "select * from tbl_user order by user_id ASC" cursor.execute(sql) record = cursor.fetchall() return render_template("admin/user/user.html", user = record, user_id = user_id)