def delete_Books(ISBN='', condition=''):
if ISBN == '' and condition == '':
query = "DELETE FROM Books;"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
elif ISBN != '':
query = "DELETE FROM Books WHERE ISBN = '"
query += ISBN
query += "';"
else:
query = "DELETE FROM Books WHERE "
query += condition
query += ";"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def delete_Members(MemberID=0, condition=''):
if MemberID == 0 and condition == '':
query = "DELETE FROM Members;"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
elif MemberID != 0:
query = "DELETE FROM Members WHERE MemberID = "
query += str(MemberID)
query += ";"
else:
query = "DELETE FROM Members WHERE "
query += condition
query += ";"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def read_excel_blob_from_remoter_db(job_id, tag, data_type): args = (job_id, tag, data_type) query = 'SELECT XLSXFile FROM SMSParameters AS P INNER JOIN Jobs AS J ON P.JobID = J.JobID ' \ 'WHERE P.XLSXFile is NOT NULL AND P.JobID = %s AND P.Tag = %s AND P.DataType = %s AND J.Status = 2 ORDER BY J.CreateTime DESC LIMIT 1' db_config = read_db_config(section='mysqlRemoteR') print(db_config) return read_one_data_from_db(db_config, query, args)
def insert_matched_title(source_name, stage, es_source, magic_title,
confident):
title = es_source['_source']
hash_value = source_name + "_" + title['srcid']
hashobj = hashlib.sha256(hash_value.encode('utf-8'))
val_hex = hashobj.hexdigest()
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
#table = dbconfig['titleMatchingTable']
cursor = conn.cursor()
SourceID = source_name.lower() + '_id'
if (source_name == 'MaxusDCM'):
title['s_release_date'] = ""
if (check_source_title_exists(val_hex) == 0):
add_title = ("INSERT INTO `fma_data`.`"+TABLE+"` (`SourceName`,`SourceLookup`,`SourceID`,`SourceTitleName`,`SourceReleaseDate`,`MagicSourceTitleID`,`Stage`,`InsertedDateTime`,`TypeOfMatch`,`CreatedBy`,`Confidence`,`SourceHash`,`ESScore`,`ESDocID`,`imdbid`,`imdbtitle`)"\
"VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)")
data_title = (title['s_source_name'], SourceID, title['srcid'],
title['s_title_name'], title['s_release_date'],
magic_title['movie_id'], stage,
datetime.now().date(), 'AutoTitleMatching', 'ATM',
confident, val_hex, es_source['_score'],
es_source['_id'], magic_title['imdbid'],
magic_title['imdbtitle'])
cursor.execute(add_title, data_title)
conn.commit()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def insert_book(ISBN, Title, Pages, Publication_Year, Publishers_Name,
Library_Name, Author):
query = "INSERT INTO Books VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"
AuthorName, AuthorSur = div_first_last(Author)
args = (ISBN, Title, Pages, Publication_Year, Publishers_Name,
Library_Name, AuthorName, AuthorSur)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def insert_Member(Name,
Surname,
Address,
num_books_borrowed,
Birthdate,
Can_Borrow,
Library='NTUA'):
query = "INSERT INTO Members(Birthdate, Address, Name, Surname, num_books_borrowed, Can_Borrow, LibraryName) VALUES(%s , %s , %s , %s , %s , %s , %s )"
args = (Birthdate, Address, Name, Surname, num_books_borrowed, 1, Library)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def fetch_titles_for_fuzzy_match(sourcelookup):
titles_for_fuzzy = list()
try:
dbconfig = read_db_config()
#table = dbconfig['titleMatchingTable']
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
query = "select sourceid,sourcetitlename,sourcereleasedate,year(sourcereleasedate) as releaseyear,imdbid,imdbtitle from " + TABLE + " where SourceLookup='magic_id' and MagicSourceTitleID not in (select MagicSourceTitleID from " + TABLE + " where SourceLookup='" + sourcelookup + "') order by MagicSourceTitleID"
#query="select sourceid,sourcetitlename,sourcereleasedate,year(sourcereleasedate) as releaseyear,imdbid,imdbtitle from TitleMatchingTest where SourceLookup='magic_id' and MagicSourceTitleID in (30836 ,41759) order by MagicSourceTitleID"
print(query)
cursor.execute(query)
for b in cursor:
c = dict()
c['movie_id'] = b[0]
c['movie_name'] = b[1]
c['release_date'] = b[2]
c['release_year'] = b[3]
c['imdbid'] = b[4]
c['imdbtitle'] = b[5]
titles_for_fuzzy.append(c)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return titles_for_fuzzy
def generateSampleID(patient_id, pathological_status, sample_class): args = (patient_id, pathological_status, sample_class) query = 'SELECT MAX(Sample_ID) FROM Sample WHERE Patient_ID = %i AND Pathological_Status = %i AND Sample_Class = %i' db_config = read_db_config(section = 'mysqlSMS') last_sample_id = read_one_data_from_db(db_config, query, args) new_sample_id = None pathological_status_str = str(pathological_status) sample_class_str = str(sample_class) if(len(pathological_status_str)==1): pathological_status_str = "0" + pathological_status_str if(len(sample_class_str)==1): sample_class_str = "0" + sample_class_str if(last_sample_id == None): new_sample_id = patient_id + pathological_status_str + sample_class_str + "00" else: last_2_digit_int = int(last_sample_id[11:2]) if(last_2_digit_int == 99): last_2_digit_int = 0 else: last_2_digit_int += 1 last_2_digit_str = str(last_2_digit_int) if(len(last_2_digit_str) == 1): last_2_digit_str = "0" + last_2_digit_str new_sample_id = patient_id + pathological_status_str + sample_class_str + last_2_digit_str return new_sample_id
def insert_enrollstudy_into_sms_db(enroll_study_data): args = (enroll_study_data) query = 'INSERT INTO EnrollStudy(Study_ID, Patient_ID, Within_Study_Patient_ID, ' \ 'Sample_UUID, Within_Study_Sample_ID, CreateTime) ' \ 'VALUES(%(Study_ID)s,%(Patient_ID)s,%(Within_Study_Patient_ID)s, ' \ '%(Sample_UUID)s,%(Within_Study_Sample_ID)s,now())' db_config = read_db_config(section='mysqlSMS') return insert_one_data_into_db(db_config, query, args)
def insert_success_result_into_remoteR_db(job_id, sample_uuid, is_new_patient, patient_id, is_new_enroll_study, enroll_study_id): if(is_new_patient == False): patient_id = "NULL" if(is_new_enroll_study == False): enroll_study_id = "NULL" args = (job_id, sample_uuid, patient_id, enroll_study_id) query = 'INSERT INTO SMSSuccessResults(JobID, NewSampleUUID, NewPatientID, NewEnrollStudyID) ' \ 'VALUES(%s,%s,%s,%s)' db_config = read_db_config(section='mysqlRemoteR') insert_one_data_into_db(db_config, query, args)
def generatePatientID(is_unlinked_patient_id):
pat_id_len = 7
max_pat_id = 999999
query = ('SELECT MAX(Patient_ID) FROM Patient WHERE Patient_ID REGEXP "^[A-Z]{%s}$"' %(pat_id_len)) \
if (is_unlinked_patient_id) \
else ('SELECT MAX(Patient_ID) FROM Patient WHERE Patient_ID REGEXP "^[A-Z]{1}[0-9]{%s}$"' %(pat_id_len-1))
db_config = read_db_config(section = 'mysqlSMS')
last_patient_id = read_one_data_from_db(db_config, query, None)
new_patient_id = None
if(last_patient_id == None):
new_patient_id = ('A'*pat_id_len) if (is_unlinked_patient_id) else ("A"+"0"*(pat_id_len-1))
else:
postfix_pat_id = ""
carry = True
if(is_unlinked_patient_id):
for i in reversed(range(pat_id_len)):
if(carry == False):
break
char = last_patient_id[i]
if(char == "Z"):
char = "A"
else:
carry = False
char = chr((ord(char)+1))
postfix_pat_id = char + postfix_pat_id
new_patient_id= last_patient_id[0:pat_id_len - len(postfix_pat_id)] + postfix_pat_id
else:
res_chars = last_patient_id[1:pat_id_len]
res_chars_len = len(res_chars)
for i in reversed(range(res_chars_len)):
if(carry == False):
break
char = res_chars[i]
if(char == "9"):
char = "0"
else:
carry = False
char = str(int(char) + 1)
postfix_pat_id = char + postfix_pat_id
new_patient_id= res_chars[0:res_chars_len - len(postfix_pat_id)] + postfix_pat_id
first_char = last_patient_id[0]
if(carry == True):
if(first_char == "Z"):
first_char = "A"
else:
first_char = chr(ord(first_char) + 1)
new_patient_id = first_char + new_patient_id
return new_patient_id
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.')
def query_other_release_dates(movie_id):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
query = "select date(release_date) from magic_release_dates_in_178 where magic_source_title_id=" + movie_id
#print(query)
cursor.execute(query)
other_release_dates = list()
for date in cursor:
other_release_dates.append(date[0])
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return other_release_dates
def insert_stuff(country_data):
""" connect to mysql-database """
query = "INSERT INTO country(continent, name, geo_id, pop_data, gdp_per_capita, median_age, bed_per_1k, first_rec)" \
"VALUES(%s, %s, %s, %s, %s, %s, %s, STR_TO_DATE(%s, '%d-%m-%Y'))"
#single row
#for .commit() args = (, geo_id)
db_config = read_db_config()
conn = None
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
if conn.is_connected():
print('Connection established.')
cursor.executemany(query, country_data)
"""
if u insert single row
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
"""
conn.commit()
else:
print('Connection failed.')
except Error as e:
print(e)
finally:
if conn is not None and conn.is_connected():
cursor.close()
conn.close()
print('Connection closed.')
def insert_stuff(country_data):
""" connect to mysql-database """
query = "INSERT INTO data_log(geo_id, date_Rep, cases, deaths)" \
"VALUES(%s, STR_TO_DATE(%s, '%d-%m-%Y'), %s, %s)"
#single row
#for .commit() args = (, geo_id)
db_config = read_db_config()
conn = None
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
if conn.is_connected():
print('Connection established.')
cursor.executemany(query, country_data)
"""
if u insert single row
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
"""
conn.commit()
else:
print('Connection failed.')
except Error as e:
print(e)
finally:
if conn is not None and conn.is_connected():
cursor.close()
conn.close()
print('Connection closed.')
def query_with_fetchmany():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM books")
for row in iter_row(cursor, 1):
print(row)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def insert_sample_into_sms_db(one_sample_data):
args = (one_sample_data)
# if Parent_UUID is NULL
query = 'INSERT INTO Sample(UUID, Sample_ID, Local_Sample_ID, Patient_ID, Sample_Contributor_Consortium_ID, ' \
'Sample_Contributor_Institute_ID, Procedure_Type, Date_Procedure, Parent_UUID, Date_Derive_From_Parent, ' \
'Pathological_Status, Sample_Class, Sample_Type, Storage_Room, Cabinet_Type, Cabinet_Temperature, ' \
'Cabinet_Number, Shelf_Number, Rack_Number, Box_Number, Position_Number, Quantity_Value, Quantity_Unit, ' \
'Concentration_Value, Concentration_Unit, Specimen_Type, Nucleotide_Size_Group_200, Anatomical_Site, ' \
'Anatomical_Laterality, Notes, CreateTime)' \
'VALUES(%(UUID)s,%(Sample_ID)s,%(Local_Sample_ID)s,%(Patient_ID)s,%(Sample_Contributor_Consortium_ID)s, ' \
'%(Sample_Contributor_Institute_ID)s,%(Procedure_Type)s,%(Date_Procedure)s,NULL,%(Date_Derive_From_Parent)s, ' \
'%(Pathological_Status)s,%(Sample_Class)s,%(Sample_Type)s,%(Storage_Room)s,%(Cabinet_Type)s,%(Cabinet_Temperature)s, ' \
'%(Cabinet_Number)s,%(Shelf_Number)s,%(Rack_Number)s,%(Box_Number)s,%(Position_Number)s,%(Quantity_Value)s,%(Quantity_Unit)s, ' \
'%(Concentration_Value)s,%(Concentration_Unit)s,%(Specimen_Type)s,%(Nucleotide_Size_Group_200)s,%(Anatomical_Site)s,' \
'%(Anatomical_Laterality)s,%(Notes)s,now())'
db_config = read_db_config(section='mysqlSMS')
insert_one_data_into_db(db_config, query, args)
def update_imdb(imdb):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
#table = dbconfig['titleMatchingTable']
cursor = conn.cursor()
update_imdb = "UPDATE " + TABLE + " SET imdbid = %s, imdbtitle= %s WHERE MagicSourceTitleID = %s"
imdb_data = (imdb['imdbid'], imdb['imdbtitle'],
imdb['MagicSourceTitleID'])
cursor.execute(update_imdb, imdb_data)
conn.commit()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def query_with_fetchall():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT city_id, city FROM city where country_id IN(50,60)")
rows = cursor.fetchall()
print('Total Row(s):', cursor.rowcount)
for row in rows:
print(row)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def connecting():
db_config = read_db_config()
success = False
try:
print('Connecting to database....')
mysqlConn = MySQLConnection(**db_config)
if mysqlConn.is_connected():
print('connection estabslished.')
success = True
else:
print('connection failed')
return mysqlConn
except Error as err:
print(err)
return
def query_with_fetchone():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT city_id, city FROM city")
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def read_wallets():
query = "SELECT * from vertcoin_wallet"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
cursor = conn.cursor()
cursor.execute(query)
for row in cursor:
print(row[0], row[1])
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def query_with_fetchone():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM god")
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone()
except Error as e:
print(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)
finally:
conn.close()
print('Connection closed.')
def mysql_connect(query, args):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def query_with_fetchall(db):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(f"SELECT * FROM {db}")
rows = cursor.fetchall()
# print('Total Row(s):', cursor.rowcount)
names = []
for row in rows:
names.append(row[0])
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return names
def insert_items(names):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
query = "INSERT INTO item(item_name) VALUE(%s)"
cursor.executemany(query, names)
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 check_source_title_exists(source_hash):
count = 0
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
#table = dbconfig['titleMatchingTable']
cursor = conn.cursor()
query = "select * from " + TABLE + " where SourceHash ='{}'".format(
source_hash)
cursor.execute(query)
for c in cursor:
if (c[19] == source_hash):
count = 1
else:
count = 0
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return count
def insert_Category(Name, Parent_category):
query = "INSERT INTO Category VALUES (%s,%s)"
args = (Name, Parent_category)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def query_items_for_build(god_id, category):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(f"""
SELECT god_name, item_name FROM {CATEGORIES[category]}
JOIN god ON {CATEGORIES[category]}.god_id=god.god_id
JOIN item ON {CATEGORIES[category]}.item_id=item.item_id
WHERE god.god_id={god_id}
""")
rows = cursor.fetchall()
items = [row[1] for row in rows]
# print('Total Row(s):', cursor.rowcount)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return items
def insert_Permanent(Staff_StaffID, Hiring_Date):
query = "INSERT INTO Permanent VALUES (%s,%s)"
args = (Staff_StaffID, Hiring_Date)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def insert_Borrows(Members_MemberID, Copy_Number, Copy_Books_ISBN, Start_Date, Return_Date, Due_Date):
query = "INSERT INTO Borrows VALUES (%s,%s,%s,%s,NULL,%s)"
args = (Members_MemberID, Copy_Number, Copy_Books_ISBN, Start_Date, Due_Date)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def query_title_matching_records():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(
"select sourceid,sourcetitlename,sourcereleasedate,year(sourcereleasedate) as releaseyear from "
+ TABLE + " where SourceLookup like '%magic_id%'")
magic_titles = list()
for b in cursor:
c = dict()
c['movie_id'] = b[0]
c['movie_name'] = b[1]
c['release_date'] = b[2]
c['release_year'] = b[3]
magic_titles.append(c)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return magic_titles
def insert_Reminds(Staff_StaffID, Members_MemberID, Date_of_Reminder):
query = "INSERT INTO Reminds VALUES (%s,%s,%s)"
args = (Staff_StaffID, Members_MemberID, Date_of_Reminder)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
