Spaces:
Sleeping
Sleeping
| import sql_utils as su | |
| from tqdm.notebook import tqdm_notebook | |
| import datetime | |
| def create_general_info_tables(connection): | |
| general_info_string = "CREATE TABLE IF NOT EXISTS GeneralInfo " + \ | |
| "(ID INT, sampleName VARCHAR(150), " + \ | |
| "createdYear INT, createdMonth INT);" | |
| keyword_info_string = "CREATE TABLE IF NOT EXISTS " + \ | |
| "SampleKeyWords (ID INT, sampleName VARCHAR(150), " + \ | |
| "SampleKeyWord VARCHAR(150));" | |
| table_creation_querys = [ | |
| general_info_string, | |
| keyword_info_string | |
| ] | |
| for query in table_creation_querys: | |
| results = su.simple_querry( | |
| connection, | |
| query | |
| ) | |
| if len(results) == 0: | |
| print("Table Exists") | |
| else: | |
| print(results) | |
| su.show_tables(connection) | |
| def general_info_to_sql( | |
| contact_dt, | |
| connection | |
| ): | |
| total_ids = len(contact_dt) | |
| with tqdm_notebook( | |
| total=total_ids, | |
| desc='Processing', | |
| unit='ID' | |
| ) as progress_bar: | |
| # Create a cursor object | |
| cursor = connection.cursor() | |
| for ID in contact_dt: | |
| sample_info = contact_dt[ID] | |
| datetime_obj = datetime.datetime.fromtimestamp( | |
| sample_info['createdDate'] / 1000 | |
| ) | |
| year = datetime_obj.year | |
| month = datetime_obj.month | |
| # Write to NanoEntCom | |
| GeneralInfo_insert = ( | |
| ID, | |
| sample_info['sampleName'], | |
| year, | |
| month | |
| ) | |
| # Execute a SELECT statement to check | |
| # if the entry already exists | |
| search_query = "SELECT COUNT(*) " + \ | |
| "FROM GeneralInfo WHERE " + \ | |
| "ID = ? AND sampleName = ? " + \ | |
| "AND createdYear = ? AND createdMonth = ?;" | |
| cursor.execute(search_query, GeneralInfo_insert) | |
| count = cursor.fetchone()[0] | |
| # Check the count to determine if the entry exists | |
| if count == 0: | |
| # Entry does not exist, proceed with insertion | |
| insert_query = "INSERT INTO GeneralInfo (" + \ | |
| "ID, " + \ | |
| "sampleName, " + \ | |
| "createdYear, " + \ | |
| "createdMonth) " + \ | |
| "VALUES (?, ?, ?, ?)" | |
| cursor.execute( | |
| insert_query, | |
| GeneralInfo_insert | |
| ) | |
| connection.commit() | |
| if sample_info['keywords'] and '<br />' in sample_info['keywords']: | |
| keyword_list = sample_info['keywords'].split("<br />") | |
| else: | |
| if sample_info['keywords']: | |
| keyword_list = [sample_info['keywords']] | |
| else: | |
| keyword_list = ["None"] | |
| for keyword in keyword_list: | |
| # Write to NanoEntCom | |
| keyword_insert = ( | |
| ID, | |
| sample_info['sampleName'], | |
| keyword | |
| ) | |
| # Execute a SELECT statement to check | |
| # if the entry already exists | |
| search_query = "SELECT COUNT(*) " + \ | |
| "FROM SampleKeyWords WHERE " + \ | |
| "ID = ? AND sampleName = ? " + \ | |
| "AND SampleKeyWord = ?;" | |
| cursor.execute(search_query, keyword_insert) | |
| count = cursor.fetchone()[0] | |
| # Check the count to determine if the entry exists | |
| if count == 0: | |
| # Entry does not exist, proceed with insertion | |
| insert_query = "INSERT INTO SampleKeyWords (" + \ | |
| "ID, " + \ | |
| "sampleName, " + \ | |
| "SampleKeyWord) " + \ | |
| "VALUES (?, ?, ?)" | |
| cursor.execute( | |
| insert_query, | |
| keyword_insert | |
| ) | |
| progress_bar.update(1) | |
| cursor.close() | |