def get_screen_by_title_condition(title, type_screen, condition_uid):
# Prepare SQL query to get conditions by uid.
sql = (
"SELECT COUNT(*) FROM screens WHERE title = '%s' AND condition_uid = %s AND type = '%s'"
% (title, condition_uid, type_screen))
results = du.execute(sql)
return results
def insert_center_lat_lng(lat, lng):
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = (
"INSERT INTO group_geolocation(lat, lon, is_university) VALUES (%s, %s, 0)"
)
print('SQL INSERT: ', sql % (lat, lng))
try:
# Execute the SQL command
cursor.execute(sql, (lat, lng))
# Commit your changes in the database
db.commit()
except Exception as ex:
print('Error: ', ex)
# Rollback in case there is any error
db.rollback()
# disconnect from server
cursor.close()
db.close()
def get_artists_genres(lst_song_uid):
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = ("SELECT DISTINCT genre, artist FROM songs WHERE uid IN (" +
lst_song_uid[0])
for index in range(1, len(lst_song_uid)):
print('song_uid: ', lst_song_uid[index])
sql = sql + (',%s' % lst_song_uid[index])
sql += ")"
print('sql: ', sql)
cursor.execute(sql)
results = []
for genre, artist in cursor:
print('genre: ', genre)
print('artist: ', artist)
results.append(genre)
results.append(artist)
# disconnect from server
cursor.close()
db.close()
return results
def get_user_data():
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = (
"SELECT se.channelUid, re.user_uid, se.songUid,"
"IF(relation ='like' OR relation = 'follow', 2, 1) AS rating,"
"se.songArtist,se.songGenre, se.channelTags "
"FROM relation_user_object AS re "
"INNER JOIN "
"(SELECT S.uid AS songUid,"
"S.name AS songName, S.artist AS songArtist,"
"S.genre AS songGenre, C.uid AS channelUid,"
"JSON_UNQUOTE(JSON_EXTRACT(C.tags, '$[*].name')) AS channelTags "
"FROM songs AS S "
"LEFT JOIN channels AS C "
"ON JSON_SEARCH(JSON_EXTRACT(songs, '$[*].uid'), 'one', S.uid) IS NOT NULL) AS se "
"ON re.object_uid = se.songUid "
"ORDER BY se.channelUid")
cursor.execute(sql)
print(cursor.rowcount)
results = []
for user in cursor:
results.append(user)
# disconnect from server
cursor.close()
db.close()
return results
def get_all_geolocation():
# Prepare SQL query to get user location.
sql = (
"SELECT DISTINCT JSON_EXTRACT(A.data, '$.location.lat') AS lat, JSON_EXTRACT(A.data, '$.location.lon') AS lon "
"FROM activities as A WHERE action = 'geolocation'")
results = du.execute(sql)
return results
def get_exist_gelocation(lat, lng):
# Prepare SQL query to get a geolocation by lat and lng geolocation.
print('lat: ', lat)
print('lng: ', lng)
sql = ("SELECT id FROM geolocation AS g WHERE g.lat = %s AND g.lng = %s")
results = du.execute(sql % (lat, lng))
return results
def get_gelocation_no_tag():
# Prepare SQL query to get all geolocations.
sql = (
"SELECT g.id, g.lat, g.lon FROM group_geolocation AS g WHERE g.tags IS NULL"
)
results = du.execute(sql)
return results
def insert_data(title, type_screen, condition_uid, data):
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = (
"INSERT INTO screens(title, type, condition_uid, channel_datas, created_at, updated_at) VALUES (%s, %s, %s, %s, %s, %s)"
)
print(
'SQL INSERT: ', sql % (title, type_screen, condition_uid, data,
datetime.now(), datetime.now()))
try:
# Execute the SQL command
cursor.execute(sql, (title, type_screen, condition_uid, data,
datetime.now(), datetime.now()))
# Commit your changes in the database
db.commit()
except Exception as ex:
print('Error: ', ex)
# Rollback in case there is any error
db.rollback()
# disconnect from server
cursor.close()
db.close()
def insert_data(name, lat, lng, type, address):
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = (
"INSERT INTO geolocation(name, lat, lng, type, address) VALUES (%s, %s, %s, %s, %s)"
)
print('SQL INSERT: ', sql % (name, lat, lng, type, address))
try:
# Execute the SQL command
cursor.execute(sql, (name, lat, lng, type, address))
# Commit your changes in the database
db.commit()
except Exception as ex:
print('Error: ', ex)
# Rollback in case there is any error
db.rollback()
# disconnect from server
cursor.close()
db.close()
def get_condition_by_user_uid(user_uid):
# Prepare SQL query to get conditions by uid.
sql = (
"SELECT IFNULL(JSON_EXTRACT(C.conditions, '$.favorites.tags[*].name'),'[]') AS tags, "
"IFNULL(JSON_EXTRACT(C.conditions, '$.favorites.genres[*].name'),'[]') AS genres, "
"IFNULL(JSON_EXTRACT(C.conditions, '$.favorites.artists[*].name'),'[]') AS artists, "
"IFNULL(JSON_EXTRACT(C.conditions, '$.favorites.genres[*].childrens[*].name'),'[]') AS children_genres "
"FROM recommend_conditions as C WHERE C.user_uid = %s")
results = du.execute(sql % user_uid)
return results
def get_user_location(user_uid):
# Prepare SQL query to get user location.
sql = (
"SELECT JSON_EXTRACT(A.data, '$.time.datetime') AS datetime, JSON_EXTRACT(A.data, '$.time.timestamp') AS timestamp, "
"JSON_EXTRACT(A.data, '$.location.lat') AS lat, JSON_EXTRACT(A.data, '$.location.lon') AS lon, "
" JSON_EXTRACT(A.data, '$.location.speed') AS speed "
"FROM activities as A WHERE user = %s AND action = 'geolocation' "
"ORDER BY updated_at DESC limit 1")
results = du.execute(sql % user_uid)
return results
def get_all_conditions():
# Prepare SQL query to get all conditions.
sql = (
"SELECT C.uid AS condition_uid, IFNULL(JSON_EXTRACT(C.conditions, '$.channels'),'[]') AS channels,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.songs'),'[]') AS songs,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.like.song[*].uid'),'[]') AS likesongs,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.like.channel[*].uid'),'[]') AS likechannels,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.dislike.song[*].uid'),'[]') AS dislikesongs,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.dislike.channel[*].uid'),'[]') AS dislikechannels,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.favorite.tags[*].name'),'[]') AS tags, "
"IFNULL(JSON_EXTRACT(C.conditions, '$.favorite.genres[*].name'),'[]') AS genres, "
"IFNULL(JSON_EXTRACT(C.conditions, '$.favorite.memberOfArtists[*].artistName'),'[]') AS artists "
"FROM recommend_conditions as C ")
results = du.execute(sql)
return results
def get_all_user_conditions():
# Prepare SQL query to get all user conditions.
sql = (
"SELECT U.uid, C.uid AS condition_uid, IFNULL(JSON_EXTRACT(C.conditions, '$.channels'),'[]') AS channels,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.songs'),'[]') AS songs,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.like.song[*].uid'),'[]') AS likesongs,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.like.channel[*].uid'),'[]') AS likechannels,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.dislike.song[*].uid'),'[]') AS dislikesongs,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.dislike.channel[*].uid'),'[]') AS dislikechannels,"
"IFNULL(JSON_EXTRACT(C.conditions, '$.favorite.tags[*].name'),'[]') AS tags, "
"IFNULL(JSON_EXTRACT(C.conditions, '$.favorite.genres[*].name'),'[]') AS genres, "
"IFNULL(JSON_EXTRACT(C.conditions, '$.favorite.memberOfArtists[*].artistName'),'[]') AS artists "
"FROM users as U INNER JOIN recommend_conditions as C "
"ON JSON_SEARCH(JSON_EXTRACT(U.info, '$.recommendCondition.uid'), 'one', C.uid) IS NOT NULL "
)
results = du.execute(sql)
return results
def get_all_tags():
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to get all tags.
sql = ("SELECT uid, name FROM tags")
cursor.execute(sql)
print(cursor.rowcount)
results = []
for tag_uid, tag_name in cursor:
results.append([tag_uid, tag_name])
# disconnect from server
cursor.close()
db.close()
return results
def get_genres():
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = ("SELECT DISTINCT genre FROM songs WHERE genre <> ''")
cursor.execute(sql)
results = []
for genre in cursor:
results.extend(genre)
# disconnect from server
cursor.close()
db.close()
return results
def get_all_data():
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = (
"SELECT S.uid AS songUid, S.genre AS songGenre, S.artist, C.uid AS channelUid, JSON_EXTRACT(C.tags, '$[*].name') AS c_tags"
" FROM songs AS S INNER JOIN channels AS C ON JSON_SEARCH(JSON_EXTRACT(songs, '$[*].uid'), 'one', S.uid) IS NOT NULL"
)
cursor.execute(sql)
print(cursor.rowcount)
results = []
for channel in cursor:
results.append(channel)
# disconnect from server
cursor.close()
db.close()
return results
def insert_data(source_uid, similar_uid, similarity):
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = (
"INSERT INTO similarity(source_uid, similar_uid, similarity, created_at) VALUES (%s, %s, %s, %s)"
)
try:
# Execute the SQL command
cursor.execute(sql,
(source_uid, similar_uid, similarity, datetime.now()))
# Commit your changes in the database
db.commit()
except Exception as ex:
print('Error: ', ex)
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
def update_geolocation_by_group(group_id, lat, lng):
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = ("UPDATE geolocation SET group_id = %s Where lat = %s AND lng = %s")
print('SQL Update: ', sql % (group_id, lat, lng))
try:
# Execute the SQL command
cursor.execute(sql, (group_id, lat, lng))
# Commit your changes in the database
db.commit()
except Exception as ex:
print('Error: ', ex)
# Rollback in case there is any error
db.rollback()
# disconnect from server
cursor.close()
db.close()
def update_by_title_condition(title, type_screen, condition_uid, data):
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database.
sql = (
"UPDATE screens SET channel_datas = %s WHERE title = %s AND condition_uid = %s AND type = %s"
)
print('SQL: ', sql % (data, title, condition_uid, type_screen))
try:
# Execute the SQL command
cursor.execute(sql, (data, title, condition_uid, type_screen))
# Commit your changes in the database
db.commit()
except Exception as ex:
print('Error: ', ex)
# Rollback in case there is any error
db.rollback()
# disconnect from server
cursor.close()
db.close()
def update_by_id(id, tags, is_university, location_key):
# Open database connection
db = du.init_connection()
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to update a record into the database.
sql = (
"UPDATE group_geolocation SET tags = %s, is_university = %s, location_key = %s WHERE id = %s"
)
print('SQL: ', sql % (tags, is_university, location_key, id))
try:
# Execute the SQL command
cursor.execute(sql, (tags, is_university, location_key, id))
# Commit your changes in the database
db.commit()
except Exception as ex:
print('Error: ', ex)
# Rollback in case there is any error
db.rollback()
# disconnect from server
cursor.close()
db.close()
def get_all_gelocation():
# Prepare SQL query to get all geolocations.
sql = ("SELECT g.lat, g.lng FROM geolocation AS g")
results = du.execute(sql)
return results
def get_user_by_condition_uid(condition_uid):
# Prepare SQL query to get users by using condition_uid.
sql = ("SELECT U.uid FROM users as U WHERE U.condition_uid = %s")
results = du.execute(sql % condition_uid)
return results
def get_user_by_uid(user_uid):
sql = "SELECT birthday, gender FROM users WHERE uid = %s"
results = du.execute(sql % user_uid)
return results
def count_all_user():
# Prepare SQL query to get all users.
sql = ("SELECT COUNT(*) FROM users")
results = du.execute(sql)
return results
def get_group_by_geolocation(lat, lng):
# Prepare SQL query to get conditions by uid.
sql = ("SELECT id FROM group_geolocation WHERE lat = %s AND lon = %s" %
(lat, lng))
results = du.execute(sql)
return results
