Spaces:
Sleeping
Sleeping
File size: 5,727 Bytes
12ac14b |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 |
import data_utils as du
import sql_utils as su
from tqdm.notebook import tqdm_notebook
def create_functionalizing_entity_tables(connection):
FuncEntDes_str = "CREATE TABLE IF NOT EXISTS FuncEntDes " + \
"(ID INT, FunctionEntity VARCHAR(255), FunctionEntityType " + \
"VARCHAR(150), Description TEXT, ActivationMethod TEXT," + \
" pubChemID VARCHAR(50), MolarMass VARCHAR(100)," + \
" MolarMassUnit VARCHAR(100));"
FuncEntFunction_str = "CREATE TABLE IF NOT EXISTS FuncEntFunction " + \
"(ID INT, FunctionEntity VARCHAR(255), Function VARCHAR(255), " + \
"FunctionDescription TEXT);"
table_creation_querys = [
FuncEntDes_str,
FuncEntFunction_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 functionalizingentity_to_sql(
composition_dt,
connection
):
functionalizingentity_dt = {}
du.parse_dictionary(
composition_dt,
"functionalizingentity",
functionalizingentity_dt
)
total_ids = len(functionalizingentity_dt)
with tqdm_notebook(
total=total_ids,
desc='Processing',
unit='ID'
) as progress_bar:
for ID in functionalizingentity_dt:
sample_info = functionalizingentity_dt[ID]
for FuncEntity in sample_info:
FuncEntity_info = sample_info[FuncEntity]
for Entry in FuncEntity_info:
# Create a cursor object
cursor = connection.cursor()
# Write to NanoEntDes
FuncEntDes_insert = (
ID,
Entry['Name'],
FuncEntity,
Entry['description'],
Entry['ActivationMethod'],
Entry['pubChemID'],
Entry['value'],
Entry['valueUnit']
)
# Execute a SELECT statement to check
# if the entry already exists
search_query = "SELECT COUNT(*) FROM FuncEntDes" + \
" WHERE ID = ? AND FunctionEntity = ?" + \
" AND FunctionEntityType = ?" + \
" AND Description = ?" + \
" AND ActivationMethod = ?" + \
" AND pubChemID = ?" + \
" AND MolarMass = ?" + \
" AND MolarMassUnit = ?;"
cursor.execute(search_query, FuncEntDes_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 FuncEntDes " + \
"(ID, FunctionEntity, " + \
"FunctionEntityType, " + \
"Description, " + \
"ActivationMethod, pubChemID, " + \
"MolarMass, MolarMassUnit) " + \
"VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
cursor.execute(insert_query, FuncEntDes_insert)
connection.commit()
# else:
# # Entry already exists, skip
# print("Entry already exists, skipping...")
# Commit the changes
connection.commit()
for function in Entry['Functions']:
# Write to NanoEntCom
FuncEntFunction_insert = (
ID,
Entry['Name'],
function['Type'],
function['FunctionDescription']
)
# Execute a SELECT statement to check
# if the entry already exists
search_query = "SELECT COUNT(*) " + \
"FROM FuncEntFunction WHERE " + \
"ID = ? AND FunctionEntity = ? " + \
"AND Function = ? " + \
"AND FunctionDescription = ?;"
cursor.execute(search_query, FuncEntFunction_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 FuncEntFunction " + \
"(ID, FunctionEntity, " + \
"Function, " + \
"FunctionDescription) " + \
"VALUES (?, ?, ?, ?)"
cursor.execute(insert_query,
FuncEntFunction_insert)
connection.commit()
# else:
# # Entry already exists, skip
# print("Entry already exists, skipping...")
progress_bar.update(1)
cursor.close()
|