def insert_music(data_array):
"""
input - множество групп/исполнителей
Перед началом вставки чистим таблицу
"""
query = "INSERT INTO artists(id,nconst,fullname,proffesion,titles) VALUES(%s,%s,%s,%s,%s)"
del_query = "DELETE FROM music"
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(del_query)
conn.commit()
print('delete music')
for row in data_array:
# print(row)
cursor.execute(query, row)
conn.commit()
# cursor.executemany(query, data_array)
# conn.commit()
print('music inserted')
except Error as e:
print('Error:', e)
finally:
cursor.close()
conn.close()
def connect():
db_config = read_db_config()
try:
print("Connecting to DB....")
conn = MySQLConnection(**read_db_config())
if conn.is_connected():
print("Conneection established.")
else:
print("Cinnection failed")
except Error as error:
print(error)
finally:
conn.close()
print"Connection closed" \
def insert_data(text): # note kwargs...
''' Insert data into database
:param filename: name of the configuration file
:param section: section of database configuration
:return: a dictionary of database parameters
# preapare query format
# query is like:
query = "INSERT INTO books(title,isbn) " \
"VALUES(%s,%s)"
# and it's args
args = (title, isbn)
# connect ...
conn = MySQLConnection(**db_config)
# create cursor
cursor = conn.cursor()
# execute
cursor.execute(query, args)
# commit
conn.commit()
# close cursor and connection
cursor.close()
conn.close()
'''
query = ("INSERT INTO todozero" \
"(todozero_text, todozero_num) " \
"VALUES (%s, 0)")
args = (text,)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args) # possible cursor.executemany(query, text)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def connect(self):
try:
print("Connecting to DB....")
self.conn = mysql.connector.connect(**read_db_config())
if self.conn.is_connected():
self.cursor = self.conn.cursor()
print("Conneection established.")
else:
print("Cinnection failed")
except mysql.connector.Error as error:
print(error)
def connect():
""" Connect to MySQL database """
db_config = read_db_config("config.ini", "mysql")
table_config = read_db_config("config.ini", "tabledata")
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
spotifyCall(conn, table_config)
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def connect():
""" Connect to MySQL database """
db_config = read_db_config("config.ini", "mysql")
table_config = read_db_config("config.ini", "tabledata")
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
spotifyCall(conn, table_config)
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def truncate_table_metrics(): # Очищает таблицу table_metrics с данными
query = "TRUNCATE TABLE db_metrics.table_metrics;"
try:
conn = MySQLConnection(**read_db_config())
cursor = conn.cursor()
cursor.execute(query)
conn.commit()
print('\033[1;31m' + "Table [table_metrics] has been cleared successfully" + '\033[0m')
except Error as e:
print('\033[1;31m' + 'Error: ', e, '\033[0m')
finally:
cursor.close()
conn.close()
def insertFingerprint(template, fp_hash):
query = "INSERT INTO tbl_fp(fp_template, fp_hash) VALUES (%s, %s)"
args = (template, fp_hash)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as e:
raise ValueError('Error', e)
finally:
cursor.close()
conn.close()
def connect():
""" Connect to MySQL database """
db_config = read_db_config()
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)
def send_metrics_to_db(indexes, metrics, timestamp):
query = """INSERT INTO table_metrics(TP,TP_alternative,FP,FP_alternative,FN,Prec,Precision_alternative,Recall,Recall_alternative,F1score,F1score_alternative,Updated)
VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
args = (indexes[0], indexes[1], indexes[2], indexes[3], indexes[4], metrics[0],
metrics[1], metrics[2], metrics[3], metrics[4], metrics[5], timestamp)
try:
conn = MySQLConnection(**read_db_config())
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as error:
print(error)
finally:
print('\033[92m'+'{} 1 row of data has been sent to [db_metrics].[table_metrics] '.format(timestamp)+'\033[0m')
cursor.close()
conn.close()
def insert_recent(data_array):
"""
input - массив данных кликнутой ссылки
Перед началом вставки чистим таблицу
"""
query = "INSERT INTO recent_search(film,artist,song,movieurl,imgurl,datetime) VALUES(%s,%s,%s,%s,%s,%s)"
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(query, data_array)
conn.commit()
# print('resent_insert')
except Error as e:
print('Error:', e)
finally:
cursor.close()
conn.close()
def send_f_measure_dependency(dependency_graph):
clear = """TRUNCATE TABLE db_metrics.table_dependency;"""
query = """INSERT INTO table_dependency(beta,fmeasure)
VALUES(%s,%s)"""
try:
conn = MySQLConnection(**read_db_config())
cursor = conn.cursor()
cursor.execute(clear)
conn.commit()
conn.cursor()
cursor.executemany(query, dependency_graph)
conn.commit()
print('\033[92m' + 'Table [db_metrics].[table_dependency] has been updated' + '\033[0m')
except Error as e:
print('\033[1;31m' + 'Error: ', e, '\033[0m')
finally:
cursor.close()
conn.close()
def sql_request (query):
"""
input: query - запрос
output: result - возвращеает информацию по запросу с ключем по названию поля в таблице
"""
result=[]
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor(dictionary=True)
cursor.execute(query)
for row in cursor:
result.append(row)
return result
except Error as e:
print('Error:', e)
finally:
cursor.close()
conn.close()
def insert_music(data_array):
"""
input - множество групп/исполнителей
Перед началом вставки чистим таблицу
"""
query = "INSERT INTO artists(id,nconst,fullname,proffesion,titles) VALUES(%s,%s,%s,%s,%s)"
del_query = "DELETE FROM music"
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(del_query)
conn.commit()
print('delete music')
for row in data_array:
# print(row)
cursor.execute(query, row)
conn.commit()
# cursor.executemany(query, data_array)
# conn.commit()
print('music inserted')
except Error as e:
print('Error:', e)
finally:
cursor.close()
conn.close()
# def main():
# array = [{1, 'nm0000001','dasdasd','sadas/ddasda/dasd','tt001,tt321321321'},
# {2, 'nm0000002','dasdasd','sadas/ddasda/dasd','tt0012,tt321321321'},
# {4, 'nm0000003', 'dasdasd', 'sadas/ddasda/dasd', 'tt00121,tt321321321'},
# {3, 'nm0000003', 'dasdasd', 'sadas/ddasda/dasd', 'tt00121,tt321321321'}]
# insert_music(array)
#
# if __name__ == '__main__':
# main()
def connect():
""" Connect to MySQL database """
db_config = read_db_config()
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
print('DBG conn:', type(conn), conn)
# print('DBG dir(conn):', dir(conn))
print('DBG server info:', conn.get_server_info())
print('DBG server version:', conn.get_server_version())
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def connect():
""" Connect to MySQL database """
db_config = read_db_config()
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:
conn.close()
print('Connection closed.')
# if __name__ == '__main__':
# connect()
def __init__(self):
self.dbconfig = read_db_config()
self.conn = None
self.cursor = None
:param: a dictionary of database configuration
:return: None
"""
# connect to database
db = pymysql.connect(**db_config)
# execute sql query
try:
with db.cursor() as cursor:
sql = "SELECT VERSION()"
cursor.execute(sql)
result = cursor.fetchone()
db.commit()
except Exception as e:
print(f"fetch error {e}")
# close database connection
finally:
db.close()
print(f"Database version {result}")
if __name__ == "__main__":
# read database configuration
db_server = read_db_config()
# connect and execute sql query
execute_sql_query(db_server)
