def deleteDatabase(database):
try:
dbconfig = Read_dbconfig()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("use " + database + ";")
cursor.execute("DROP DATABASE IF EXISTS " + database + ";")
conn.commit()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def DB_Exists(DB):
#db_config = Read_dbconfig()
dbconfig = Read_dbconfig()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
#cursor.execute("use " + DB)
# DBs = cursor.execute("SHOW DATABASES LIKE '"+DB+"';")
# conn.commit()
#cursor.execute("use " + DB)
cursor.execute("SHOW DATABASES LIKE '" + DB + "';")
DBs = cursor.fetchall()
if (DBs):
return True
return False
def getSongOrder(database, Mode: Index):
try:
dbconfig = Read_dbconfig()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("use " + database)
cursor.execute("SELECT * FROM SongList ORDER BY songRank DESC;")
row = [item[Mode] for item in cursor.fetchall()]
return row
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def upVote(database, songName, upvote):
try:
dbconfig = Read_dbconfig()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("use " + database + ";")
cursor.execute("update SongList set songRank = songRank + " + upvote +
" where songName = " + "'" + songName + "'" + ";")
conn.commit()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def createSongDB(party):
query = "Create table if not exists SongList (URL varchar(255) unique, songName varchar(255) unique, songRank int, userDisplayName varchar(255), record int auto_increment primary key);"
try:
db_config = Read_dbconfig()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("use " + party)
cursor.execute(query)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def createPrevList(party):
query = "Create table if not exists PrevSongList (prevSong varchar(255), record int auto_increment primary key);"
try:
db_config = Read_dbconfig()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("use " + party)
cursor.execute(query)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def getSongOrderUser(database):
try:
dbconfig = Read_dbconfig()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("use " + database)
cursor.execute(
"select userDisplayName from SongList where record in (select record from song order by songRank DESC);"
)
row = [item[0] for item in cursor.fetchall()]
return row
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def insertPrevSong(database, songName):
try:
db_config = Read_dbconfig()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("use " + database)
cursor.execute(
"insert into PrevSongList (prevSong)\n" + "select '" + songName +
"'\n" +
"where not exists (select prevSong from PrevSongList where prevSong = '"
+ songName + "');")
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def insertSong(database, URL, songName, songRank, userDisplayName):
try:
db_config = Read_dbconfig()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("use " + database)
cursor.execute(
"insert into SongList (URL, songName, songRank, userDisplayName) \n"
+ "select '" + URL + "', '" + songName + "', '" + songRank +
"', '" + userDisplayName + "' \n" +
"Where NOT exists (select URL from SongList where URL = '" + URL +
"');")
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def createDatabase(partyID):
query = "CREATE DATABASE IF NOT EXISTS " + partyID
print("DataBase ID: " + partyID)
try:
db_config = Read_dbconfig()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
cursor.execute("use " + partyID)
createPrevList(partyID)
createSongDB(partyID)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def connect():
"""Connect to the database"""
db_config = Read_dbconfig()
conn = None
try:
print("Connecting to MySQL database...")
conn = MySQLConnection(**db_config)
if conn.is_connected():
print("Connection established.")
else:
print("Connection Failed")
except Error as error:
print(error)
finally:
if conn is not None and conn.is_connected():
conn.close()
print("Connection Closed.")
def DeleteSong(database, songName):
try:
dbconfig = Read_dbconfig()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("use " + database)
cursor.execute("select record from SongList where songName = '" +
songName + "';")
record = str(cursor.fetchall())
length = len(record)
toDelete = record[2:length - 3]
cursor.execute("DELETE FROM SongList where record = " + toDelete + ";")
conn.commit()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def pop(database):
try:
dbconfig = Read_dbconfig()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("use " + database)
cursor.execute(
"select URL, songName, songRank, userDisplayName from SongList where songRank = (select max(SongList.songRank) from SongList);"
)
out = str(cursor.fetchall())
cursor.execute(
"select record from SongList where songRank = (select max(SongList.songRank) from SongList);"
)
record = str(cursor.fetchall())
toDelete = record[2:record.index(',')]
cursor.execute("DELETE FROM SongList where record = " + toDelete + ";")
conn.commit()
return out
except Error as e:
print(e)
finally:
cursor.close()
conn.close()