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 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()