def get_song(index):
try:
connection = connect_to_db()
sql = "SELECT * FROM songs WHERE idSong=%s"
if (connection.is_connected()):
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, (index, ))
row = cursor.fetchone()
if (not row):
response = jsonify("Song not on DB.")
response.status_code = 404
else:
row_headers = [
column_name[0] for column_name in cursor.description
]
response = []
response.append(dict(zip(row_headers, row)))
response = jsonify(response)
response.status_code = 200
return response
except Exception as e:
response = jsonify("Error: " + str(e))
response.status_code = 400
return response
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
def id_on_db(id, table):
try:
find = False
if (table == "artists"):
sql = "SELECT * FROM artists WHERE idArtist=%s"
elif (table == "albuns"):
sql = "SELECT * FROM albuns WHERE idAlbum=%s"
elif (table == "songs"):
sql = "SELECT * FROM songs WHERE idSong=%s"
connection = connect_to_db()
if connection.is_connected():
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, (id, ))
row = cursor.fetchone()
if (not row):
find = False
else:
find = True
return (find, row)
except Exception as e:
print("Error: ", e)
finally:
if connection.is_connected():
cursor.close()
connection.close()
def get_all_songs():
try:
sql = "SELECT * FROM songs"
connection = connect_to_db()
if (connection.is_connected()):
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql)
rows = cursor.fetchall()
row_headers = [
column_name[0] for column_name in cursor.description
]
response = []
for result in rows:
response.append(dict(zip(row_headers, result)))
response = jsonify(response)
response.status_code = 200
return response
except Exception as e:
response = jsonify("Error: " + str(e))
response.status_code = 400
return response
finally:
if connection.is_connected():
cursor.close()
connection.close()
def group_by_ner():
con, cur = db_connect.connect_to_db("localhost")
cur.execute("SELECT article_id, "
" compared_article_id "
"FROM articles.ner_score "
"WHERE score >= {threshold}"
.format(threshold=config.NER_THRESHOLD
)
)
results = cur.fetchall()
connections = set()
for res in results:
connections.add((res[0], res[1]))
ner_graph = graph.Graph(list(connections))
groups = ner_graph.return_connections()
for group in groups:
title_group = []
for article_id in group:
cur.execute("SELECT title "
"FROM articles.meta_info "
"WHERE id='{}'".format(article_id))
try:
result = cur.fetchone()[0]
title_group.append(result)
except IndexError:
print("Id is not in DB.")
print(title_group)
def delete_songs_album(index):
try:
find, _ = helpers.id_on_db(index, "albuns")
if (not find):
response = jsonify("Album not on DB.")
response.status_code = 404
else:
connection = connect_to_db()
sql = "DELETE FROM songs WHERE idAlbumSong=%s"
if (connection.is_connected()):
cursor = connection.cursor()
cursor.execute(sql, (index, ))
connection.commit()
response = "Deletion successful."
response = jsonify(response)
response.status_code = 200
return response
except Exception as e:
response = jsonify("Error: " + str(e))
response.status_code = 400
return response
finally:
if (find and connection.is_connected()):
cursor.close()
connection.close()
def get_all_songs_artist(index):
try:
find, _ = helpers.id_on_db(index, "artists")
if (not find):
response = jsonify("Artist not on DB.")
response.status_code = 404
else:
connection = connect_to_db()
sql = "SELECT songs.* FROM songs INNER JOIN albuns ON albuns.idArtistAlbum=%s"
if (connection.is_connected()):
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, (index, ))
rows = cursor.fetchall()
row_headers = [
column_name[0] for column_name in cursor.description
]
response = []
for row in rows:
response.append(dict(zip(row_headers, row)))
response = jsonify(response)
response.status_code = 200
return response
except Exception as e:
reponse = jsonify("Error: " + str(e))
response.status_code = 400
return response
finally:
if (find and connection.is_connected()):
cursor.close()
connection.close()
def get_songs_album(index):
try:
find, _ = helpers.id_on_db(index, "albuns")
if (not find):
response = jsonify("Album not on DB.")
response.status_code = 404
else:
connection = connect_to_db()
sql = "SELECT * FROM songs WHERE idAlbumSong = %s"
data = (index, )
if (connection.is_connected()):
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, data)
rows = cursor.fetchall()
row_headers = [
column_name[0] for column_name in cursor.description
]
response = []
for result in rows:
response.append(dict(zip(row_headers, result)))
response = jsonify(response)
response.status_code = 200
return response
except Exception as e:
response = jsonify("Error: " + str(e))
response.status_code = 400
return response
finally:
if (find and connection.is_connected()):
cursor.close()
connection.close()
def create_db_table():
try:
connection = db_connect.connect_to_db()
cursor = connection.cursor()
cursor.execute(TABLE_SQL)
cursor.close()
connection.commit()
except (Exception, Error) as error:
print(f'Failed to create table: {error}')
finally:
db_connect.close_db(connection)
def test_connect_to_db(self,mock):
# Quando conecta
mock.return_value.is_connected.return_value = True
result = connect_to_db()
self.assertIsNotNone(result)
self.assertIsInstance(result,unittest.mock.MagicMock)
# Quando não conecta
mock.return_value.is_connected.return_value = False
result = connect_to_db()
self.assertIsNone(result)
self.assertNotIsInstance(result,unittest.mock.MagicMock)
# # Quando ocorre uma exceção
mock.side_effect = Exception()
result = connect_to_db()
self.assertIsNotNone(result)
self.assertRaises(Exception)
self.assertIsInstance(result,str)
self.assertRegex(result,"Error:*")
def update_last_sent(id):
connection = db_connect.connect_to_db()
cursor = connection.cursor()
try:
cursor.execute(
'''UPDATE twinspirations SET last_sent = %s WHERE id = %s''',
("now", id))
connection.commit()
except (Exception, Error) as error:
print("Error while updating last_sent", error)
finally:
# closing database connection.
db_connect.close_db(connection)
def update_acknowledged_followers_in_db(followers):
# Updates acknowledged_followers with all new followers
connection = db_connect.connect_to_db()
try:
for follower in followers:
add_acknowledged_follower(connection, follower)
except (Exception, Error) as error:
print("Failed to update acknowledged followers in db", error)
finally:
# closing database connection.
db_connect.close_db(connection)
def populate_twinspirations(twinspirations):
connection = db_connect.connect_to_db()
cursor = connection.cursor()
try:
for twinspiration in twinspirations:
insert_twinspiration(cursor, twinspiration)
connection.commit()
except (Exception, Error) as error:
print(f'Failed to populate twinspirations: {error}')
finally:
if (connection):
if (cursor):
cursor.close()
connection.close()
def get_twinspiration():
connection = db_connect.connect_to_db()
cursor = connection.cursor()
twinspiration = ""
try:
cursor.execute(
'''SELECT id,twinspiration FROM twinspirations ORDER BY last_sent LIMIT 1;'''
)
twinspirations = cursor.fetchall()
except (Exception, Error) as error:
print("Error while fetching data from DB", error)
finally:
# closing database connection.
db_connect.close_db(connection)
return twinspirations[0]
def update_follower_count(count):
# Updates the follower_counts table with the current number of followers and the current timestamp
connection = db_connect.connect_to_db()
cursor = connection.cursor()
try:
insert_query = '''INSERT INTO follower_counts (COUNT, CREATED_AT) VALUES (%s,%s);'''
record_to_insert = (count, "now")
cursor.execute(insert_query, record_to_insert)
record_count = cursor.rowcount
if record_count:
connection.commit()
print("Updated follower count to ", count)
except (Exception, Error) as error:
print("Error updating count: ", error)
finally:
db_connect.close_db(connection)
def create_twinspirations_table():
try:
connection = db_connect.connect_to_db()
cursor = connection.cursor()
cursor.execute('''CREATE TABLE twinspirations (
id SERIAL PRIMARY KEY,
twinspiration VARCHAR(280),
last_sent TIMESTAMP); ''')
cursor.close()
connection.commit()
except (Exception, Error) as error:
print(f'Failed to create twinspiration table: {error}')
finally:
if (connection):
if (cursor):
cursor.close()
connection.close()
def get_acked_followers():
# Returns a list of follower ids for already acknowledged followers
connection = db_connect.connect_to_db()
cursor = connection.cursor()
follower_records = []
get_acked_followers_query = '''SELECT "follower_id" FROM acknowledged_followers order by date_sent desc;'''
try:
cursor.execute(get_acked_followers_query)
follower_records = cursor.fetchall()
except (Exception, Error) as error:
print("Error while fetching data from PostgreSQL", error)
finally:
# closing database connection.
db_connect.close_db(connection)
return collect_follower_ids(follower_records)
def delete_all_songs():
try:
sql = "DELETE FROM songs"
connection = connect_to_db()
if (connection.is_connected()):
cursor = connection.cursor()
cursor.execute(sql)
connection.commit()
response = jsonify("Deletion successful")
response.status_code = 200
return response
except Exception as e:
response = jsonify("Error: " + str(e))
response.status_code = 400
return response
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
def on_db(item, table):
try:
find = False
connection = connect_to_db()
if (table == "artists"):
sql = "SELECT * FROM artists"
if (connection.is_connected()):
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
if (row[1].lower() == item['name'].lower()):
find = True
break
elif (table == "albuns"):
sql = "SELECT idAlbumItunes FROM albuns"
if (connection.is_connected()):
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
if (row[0] == item):
find = True
break
elif (table == "songs"):
sql = "SELECT idSongItunes FROM songs"
if (connection.is_connected()):
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
if (row[0] == item):
find = True
break
return find
except Exception as e:
# print("Error: ", e)
return "Error: " + str(e)
finally:
if (connection.is_connected()):
cursor.close()
connection.close()
def delete_artist(index):
try:
find, _ = helpers.id_on_db(index, "artists")
if (not find):
response = jsonify("Artist not on DB.")
response.status_code = 404
else:
sql = "DELETE FROM artists WHERE idArtist = %s"
connection = connect_to_db()
if (connection.is_connected()):
cursor = connection.cursor()
cursor.execute(sql, (index, ))
connection.commit()
response = jsonify("Deletion successful.")
response.status_code = 200
return response
except Exception as e:
response = jsonify("Error: " + str(e))
response.status_code = 400
return response
finally:
if (find and connection.is_connected()):
cursor.close()
connection.close()
def delete_song_album(index_album, index_musica):
try:
find, _ = helpers.id_on_db(index_album, "albuns")
if (not find):
response = jsonify("Album not on DB.")
response.status_code = 404
else:
findSong, row = helpers.id_on_db(index_musica, "songs")
if (not findSong):
response = jsonify("Song not on DB.")
response.status_code = 404
else:
if (row[7] == index_album):
connection = connect_to_db()
if (connection.is_connected()):
sql = "DELETE FROM songs WHERE idSong=%s"
cursor = connection.cursor()
cursor.execute(sql, (index_musica, ))
connection.commit()
response = "Deletion successful."
response = jsonify(response)
response.status_code = 200
else:
response = "Song not from album."
response = jsonify(response)
response.status_code = 200
return response
except Exception as e:
response = jsonify("Error: " + str(e))
response.status_code = 400
return response
finally:
if (find and findSong and row[7] == index_album
and connection.is_connected()):
cursor.close()
connection.close()
#!/usr/bin/env python
from __future__ import print_function
import calculate_distance
import db_connect
import json
import pymysql
import re
from datetime import *
from dateutil.parser import parse
conn = db_connect.connect_to_db()
cur = conn.cursor()
def get_all_employee_addresses():
sql = "SELECT employee_id, CONCAT(first_name, ' ', last_name) as full_name, " \
"CONCAT(address_num, ' ', street, '. ', " \
"city, ', ', state, ' ' , zip) as full_address, work_level FROM employees ORDER BY last_name"
cur.execute(sql)
return cur.fetchall()
def get_employee_address(emp):
sql = "SELECT CONCAT(address_num, ' ', street, '. ', city, ', ', state, ' ', zip) FROM employees " \
"WHERE employee_id=" + str(emp)
cur.execute(sql)
return cur.fetchone()
def add_song(index):
try:
newSong = request.get_json()
noParamName = False
inputNotString = False
if (not newSong):
response = jsonify("No Body in request.")
response.status_code = 400
elif ("name" not in newSong.keys()):
noParamName = True
response = jsonify("No name parameter in request body.")
response.status_code = 400
elif (type(newSong["name"]) != str):
inputNotString = True
response = jsonify("Name parameter must be str type.")
response.status_code = 400
else:
findAlbum, row = helpers.id_on_db(index, "albuns")
if (not findAlbum):
response = jsonify("Album not on DB.")
response.status_code = 404
else:
connection = connect_to_db()
if (connection.is_connected()):
cursor = connection.cursor()
albumId = row[5]
if (not albumId):
response = jsonify("Album not on iTunes, no Songs.")
response.status_code = 200
else:
songs = helpers.get_type_from_id(albumId, "song")
if (type(songs) == str):
response = jsonify(songs)
response.status_code = 400
else:
dataToSave = []
for song in songs:
if (song['trackName'].lower() ==
newSong['name'].lower()):
dataToSave.append({
'nameSong':
song['trackName'],
'explicit':
song['trackExplicitness'],
'genre':
song['primaryGenreName'],
'idSongItunes':
song['trackId'],
'nameArtistSong':
song['artistName'],
'nameAlbumSong':
song['collectionName'],
'artistIdItunes':
song['artistId']
})
break
if (not dataToSave):
response = jsonify("Song not on iTunes.")
response.status_code = 200
else:
findSong = helpers.on_db(
dataToSave[0]['idSongItunes'], "songs")
if (findSong):
response = jsonify("Song already on DB.")
response.status_code = 200
else:
sql = "INSERT INTO songs (nameSong, explicit, genre, idSongItunes, nameArtistSong, nameAlbumSong, idAlbumSong) VALUES (%s,%s,%s,%s,%s,%s,%s)"
data = (dataToSave[0]['nameSong'],
dataToSave[0]['explicit'],
dataToSave[0]['genre'],
dataToSave[0]['idSongItunes'],
dataToSave[0]['nameArtistSong'],
dataToSave[0]['nameAlbumSong'],
index)
cursor.execute(sql, data)
connection.commit()
response = jsonify("Added successful.")
response.status_code = 200
return response
except Exception as e:
response = jsonify("Error: " + str(e))
response.status_code = 400
return response
finally:
if (newSong and not noParamName and not inputNotString and findAlbum
and connection.is_connected()):
cursor.close()
connection.close()
def update_artist(index):
try:
artist = request.get_json()
noParamName = False
if (not artist):
response = jsonify("No Body in request.")
response.status_code = 400
elif ("name" not in artist.keys()):
noParamName = True
response = jsonify("No name parameter in request body.")
response.status_code = 400
else:
find, _ = helpers.id_on_db(index, "artists")
if (not find):
response = jsonify("Artist not on DB.")
response.status_code = 404
else:
result = helpers.is_on_itunes(artist['name'])
if (type(result) == str):
response = jsonify(result)
response.status_code = 400
else:
connection = connect_to_db()
if (connection.is_connected()):
# Deletar todos os albuns e musicas do artista antes
cursor = connection.cursor()
sql = "DELETE FROM albuns WHERE idArtistAlbum=%s"
cursor.execute(sql, (index, ))
if (not result[0]):
# Sem ID iTunes
id = None
name = artist['name']
else:
# Com ID iTunes
id = result[1]
name = result[2]
# Colocar albuns no BD
helpers.add_all_items_to_db(
id, index, "album", cursor)
# Pegar IDs albuns do artista
sql = "SELECT idAlbum,idAlbumItunes FROM albuns WHERE idArtistAlbum=%s"
cursor.execute(sql, (index, ))
rows = cursor.fetchall()
# app.logger(rows)
# Colocar musicas nos albuns
for row in rows:
helpers.add_all_items_to_db(
row[1], row[0], "song", cursor)
findNameArtist = helpers.on_db(artist, "artists")
if (not findNameArtist):
sql = "UPDATE artists SET nameArtist = %s, idArtistItunes=%s WHERE idArtist= %s"
data = (name, id, index)
cursor.execute(sql, data)
connection.commit()
response = jsonify("Update successful.")
response.status_code = 200
else:
response = jsonify("Artist already on DB.")
response.status_code = 200
return response
except Exception as e:
print("Error: ", e)
finally:
if (artist and not noParamName and find and type(result) != str
and connection.is_connected()):
cursor.close()
connection.close()
def add_album(index):
try:
newAlbum = request.get_json()
noParamName = False
inputNotString = False
if (not newAlbum):
response = jsonify("No Body in request.")
response.status_code = 400
elif ("name" not in newAlbum.keys()):
noParamName = True
response = jsonify("No name parameter in request body.")
response.status_code = 400
elif (type(newAlbum["name"]) != str):
inputNotString = True
response = jsonify("Name parameter must be str type.")
response.status_code = 400
else:
findArtist, row = helpers.id_on_db(index, "artists")
if (not findArtist):
response = jsonify("Artist not on DB.")
response.status_code = 404
else:
connection = connect_to_db()
if (connection.is_connected()):
cursor = connection.cursor()
artistId = row[2]
if (not artistId):
response = jsonify("Artist not on iTunes, no Albuns.")
response.status_code = 200
else:
albuns = helpers.get_type_from_id(artistId, "album")
if (type(albuns) == str):
response = albuns
response = jsonify(response)
response.status_code = 400
else:
dataToSave = []
for album in albuns:
if (album['collectionName'].lower() ==
newAlbum['name'].lower()):
dataToSave.append({
'nameAlbum':
album['collectionName'],
'trackCount':
album['trackCount'],
'explicit':
album['collectionExplicitness'],
'genre':
album['primaryGenreName'],
'idAlbumItunes':
album['collectionId'],
'nameArtistAlbum':
album['artistName'],
'artistIdItunes':
album['artistId']
})
break
if (not dataToSave):
response = jsonify("Album not on iTunes.")
response.status_code = 200
else:
findAlbum = helpers.on_db(
dataToSave[0]['idAlbumItunes'], "albuns")
if (findAlbum):
response = jsonify("Album already on DB.")
response.status_code = 200
else:
sql = "INSERT INTO albuns (nameAlbum, trackCount,explicit,genre,idAlbumItunes, nameArtistAlbum, idArtistAlbum) VALUES (%s,%s,%s,%s,%s,%s,%s)"
data = (dataToSave[0]['nameAlbum'],
dataToSave[0]['trackCount'],
dataToSave[0]['explicit'],
dataToSave[0]['genre'],
dataToSave[0]['idAlbumItunes'],
dataToSave[0]['nameArtistAlbum'],
index)
cursor.execute(sql, data)
connection.commit()
response = jsonify("Added successful.")
response.status_code = 200
return response
except Exception as e:
response = jsonify("Error: " + str(e))
response.status_code = 400
return response
finally:
if (newAlbum and not noParamName and not inputNotString and findArtist
and connection.is_connected()):
cursor.close()
connection.close()
def add_artist():
try:
artist = request.get_json()
noParamName = False
notStr = False
if (not artist):
response = jsonify("No Body in request.")
response.status_code = 400
elif ("name" not in artist.keys()):
noParamName = True
response = jsonify("No name parameter in request body.")
response.status_code = 400
elif (type(artist['name']) != str):
notStr = True
response = jsonify("Name parameter must be str type.")
response.status_code = 400
else:
connection = connect_to_db()
cursor = connection.cursor()
find = helpers.on_db(artist, "artists")
if (find):
response = jsonify("Artist already on DB.")
response.status_code = 200
else:
result = helpers.is_on_itunes(artist['name'])
if (type(result) == str):
response = jsonify(result)
response.status_code = 400
else:
if (not result[0]):
id = None
name = artist['name']
else:
id = result[1]
name = result[2]
if (connection.is_connected()):
sql = "INSERT INTO artists (nameArtist,idArtistItunes) VALUES (%s,%s)"
data = (name, id)
cursor.execute(sql, data)
if (id):
# Pegar ID do artista
sql = "SELECT idArtist FROM artists WHERE nameArtist=%s"
data = (name, )
cursor = connection.cursor(
pymysql.cursors.DictCursor)
cursor.execute(sql, data)
row = cursor.fetchone()
# Colocar albuns no BD
helpers.add_all_items_to_db(
id, row[0], "album", cursor)
# Pegar IDs albuns do artista
sql = "SELECT idAlbum,idAlbumItunes FROM albuns WHERE idArtistAlbum=%s"
data = (row[0], )
cursor.execute(sql, data)
rows = cursor.fetchall()
# Colocar musicas nos albuns
for row in rows:
helpers.add_all_items_to_db(
row[1], row[0], "song", cursor)
# app.logger.info(aux)
response = jsonify("Added successful.")
response.status_code = 200
# Commit só depois que adicionar artista, checar e adicionar albuns e checar e adicionar musicas
connection.commit()
return response
except Exception as e:
response = jsonify("Error: " + str(e))
response.status_code = 400
return response
finally:
if (artist and not notStr and not noParamName
and connection.is_connected()):
cursor.close()
connection.close()
