Spaces:
Sleeping
Sleeping
File size: 5,638 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 148 149 150 |
import data_utils as du
import sql_utils as su
from tqdm.notebook import tqdm_notebook
def create_ChemAsso_tables(connection):
table_String_ChemAssoName = "CREATE TABLE IF NOT EXISTS ChemAsso (" + \
"ID INT, " + \
"AssociationType VARCHAR(150), " + \
"BondType VARCHAR(150), " + \
"Description TEXT, " + \
"dataId INT, " + \
"ComposingElementNameA VARCHAR(150), " + \
"ComposingElementNameB VARCHAR(150), " + \
"CompositiontypeB VARCHAR(150), " + \
"CompositiontypeA VARCHAR(150), " + \
"DomainElementNameB VARCHAR(150), " + \
"DomainElementNameA VARCHAR(150), " + \
"DomainAssociationId INT, " + \
"ComposingElemetIdB INT, " + \
"ComposingElemetIdA INT, " + \
"ComposingElementTypeA VARCHAR(150), " + \
"EntityDisplayNameB VARCHAR(150), " + \
"ComposingElementTypeB VARCHAR(150), " + \
"EntityDisplayNameA VARCHAR(150), " + \
"AttachmentId INT);"
table_creation_querys = [
table_String_ChemAssoName
]
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 chemicalassociation_to_sql(
composition_dt,
connection
):
chemicalassociation_dt = {}
du.parse_dictionary(
composition_dt,
"chemicalassociation",
chemicalassociation_dt
)
total_ids = len(chemicalassociation_dt)
with tqdm_notebook(
total=total_ids,
desc='Processing',
unit='ID'
) as progress_bar:
# Create a cursor object
cursor = connection.cursor()
for ID in chemicalassociation_dt:
sample_info = chemicalassociation_dt[ID]
for AssociationType in sample_info:
AssociationType_info = sample_info[AssociationType]
for Entry in AssociationType_info:
# Create a cursor object
elements = Entry['AssocitedElements']
# Write to NanoEntCom
ChemAsso_insert = (
ID,
AssociationType,
Entry['BondType'],
Entry['Description'],
Entry['dataId'],
elements['ComposingElementNameA'],
elements['ComposingElementNameB'],
elements['CompositiontypeB'],
elements['CompositiontypeA'],
elements['DomainElementNameB'],
elements['DomainElementNameA'],
elements['DomainAssociationId'],
elements['ComposingElemetIdB'],
elements['ComposingElemetIdA'],
elements['ComposingElementTypeA'],
elements['EntityDisplayNameB'],
elements['ComposingElementTypeB'],
elements['EntityDisplayNameA'],
Entry['AttachmentId']
)
# Execute a SELECT statement to check
# if the entry already exists
search_query = "SELECT COUNT(*) " + \
"FROM ChemAsso WHERE " + \
"ID = ? AND AssociationType = ? " + \
"AND BondType = ? " + \
"AND Description = ? " + \
"AND dataId = ?;"
cursor.execute(search_query, ChemAsso_insert[:5])
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 ChemAsso (" + \
"ID, " + \
"AssociationType, " + \
"BondType, " + \
"Description, " + \
"dataId, " + \
"ComposingElementNameA, " + \
"ComposingElementNameB, " + \
"CompositiontypeB, " + \
"CompositiontypeA, " + \
"DomainElementNameB, " + \
"DomainElementNameA, " + \
"DomainAssociationId, " + \
"ComposingElemetIdB, " + \
"ComposingElemetIdA, " + \
"ComposingElementTypeA, " + \
"EntityDisplayNameB, " + \
"ComposingElementTypeB, " + \
"EntityDisplayNameA, " + \
"AttachmentId) " + \
"VALUES (?, ?, ?, ?, " + \
"?, ?, ?, ?, ?, ?, ?, ?, " + \
"?, ?, ?, ?, ?, ? , ?)"
cursor.execute(
insert_query,
ChemAsso_insert
)
connection.commit()
# else:
# # Entry already exists, skip
# print("Entry already exists, skipping...")
progress_bar.update(1)
cursor.close()
|