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)
