Spaces:
Sleeping
Sleeping
| import data_utils as du | |
| import sql_utils as su | |
| from tqdm.notebook import tqdm_notebook | |
| def create_nanomaterial_entity_tables(connection): | |
| NanoEntDes_str = "CREATE TABLE IF NOT EXISTS `NanoEntDes` " + \ | |
| "(`ID` INT, `NanoEntity` VARCHAR(255), `Description` TEXT);" | |
| NanoEntCom_str = "CREATE TABLE IF NOT EXISTS NanoEntCom " + \ | |
| "(ID INT, NanoEntity VARCHAR(255), Composition VARCHAR(100), " + \ | |
| "CompositionType VARCHAR(100), MolecularWeight VARCHAR(150), " + \ | |
| "PubChemID VARCHAR(255));" | |
| table_creation_querys = [ | |
| NanoEntDes_str, | |
| NanoEntCom_str | |
| ] | |
| 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 nanomaterialentity_to_sql( | |
| composition_dt, | |
| connection | |
| ): | |
| nanomaterialentity_dt = {} | |
| du.parse_dictionary( | |
| composition_dt, | |
| "nanomaterialentity", | |
| nanomaterialentity_dt | |
| ) | |
| total_ids = len(nanomaterialentity_dt) | |
| with tqdm_notebook( | |
| total=total_ids, | |
| desc='Processing', | |
| unit='ID' | |
| ) as progress_bar: | |
| for ID in nanomaterialentity_dt: | |
| sample_info = nanomaterialentity_dt[ID] | |
| for NanoEntity in sample_info: | |
| NanoEntity_info = sample_info[NanoEntity] | |
| for Entry in NanoEntity_info: | |
| # Create a cursor object | |
| cursor = connection.cursor() | |
| # Write to NanoEntDes | |
| NanoEntDes_insert = (ID, NanoEntity, Entry['Description']) | |
| # Execute a SELECT statement to check | |
| # if the entry already exists | |
| search_query = "SELECT COUNT(*) FROM NanoEntDes" + \ | |
| " WHERE ID = ? AND NanoEntity = ?" + \ | |
| " AND Description = ?" | |
| cursor.execute(search_query, NanoEntDes_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 NanoEntDes " + \ | |
| "(ID, NanoEntity, Description) " + \ | |
| "VALUES (?, ?, ?)" | |
| cursor.execute(insert_query, NanoEntDes_insert) | |
| connection.commit() | |
| # else: | |
| # # Entry already exists, skip | |
| # print("Entry already exists, skipping...") | |
| # Commit the changes | |
| connection.commit() | |
| for composition in Entry['ComposingElements']: | |
| if 'DisplayName' in composition and not composition[ | |
| 'DisplayName' | |
| ]: | |
| composition_type = "NULL" | |
| composition_name = "NULL" | |
| composition_MolecularWeight = "NULL" | |
| else: | |
| # Extract composition_type | |
| displayname = composition['DisplayName'] | |
| index_open = displayname.find("(") | |
| composition_type = displayname[ | |
| :index_open | |
| ].strip() if index_open != -1 else "NULL" | |
| # Extract composition_name and composition_quantity | |
| index_name = displayname.find( | |
| "name: " | |
| ) + len("name: ") | |
| index_amount = displayname.find(", amount: ") | |
| if index_name != -1: | |
| if index_amount != -1: | |
| composition_name = displayname[ | |
| index_name:index_amount | |
| ].strip() | |
| composition_MolecularWeight = displayname[ | |
| index_amount + len(", amount: "):-1 | |
| ].strip() | |
| else: | |
| composition_name = displayname[ | |
| index_name:-1 | |
| ].strip() | |
| composition_MolecularWeight = "NULL" | |
| else: | |
| composition_name = "NULL" | |
| composition_MolecularWeight = "NULL" | |
| if 'PubChemId' in composition and not composition[ | |
| 'PubChemId' | |
| ]: | |
| PubChemID = "Null" | |
| else: | |
| PubChemID = composition['PubChemId'] | |
| # Write to NanoEntCom | |
| NanoEntCom_insert = ( | |
| ID, | |
| NanoEntity, | |
| composition_name, | |
| composition_type, | |
| composition_MolecularWeight, | |
| PubChemID | |
| ) | |
| # Execute a SELECT statement to check | |
| # if the entry already exists | |
| search_query = "SELECT COUNT(*) " + \ | |
| "FROM NanoEntCom WHERE " + \ | |
| "ID = ? AND NanoEntity = ? " + \ | |
| "AND Composition = ? " + \ | |
| "AND CompositionType = ? " + \ | |
| "AND MolecularWeight = ? " + \ | |
| "AND PubChemID = ?" | |
| cursor.execute(search_query, NanoEntCom_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 NanoEntCom " + \ | |
| "(ID, NanoEntity, " + \ | |
| "Composition, " + \ | |
| "CompositionType, " + \ | |
| "MolecularWeight, PubChemID) " + \ | |
| "VALUES (?, ?, ?, ?, ?, ?)" | |
| cursor.execute(insert_query, NanoEntCom_insert) | |
| connection.commit() | |
| # else: | |
| # # Entry already exists, skip | |
| # print("Entry already exists, skipping...") | |
| progress_bar.update(1) | |
| cursor.close() | |