aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--scripts/insert_data.py28
1 files changed, 19 insertions, 9 deletions
diff --git a/scripts/insert_data.py b/scripts/insert_data.py
index 53bf4bd..6491b06 100644
--- a/scripts/insert_data.py
+++ b/scripts/insert_data.py
@@ -109,11 +109,25 @@ def annotationinfo(
return {**annot_dicts[0], **annot_dicts[1]}
return {}
+def __format_query__(query, params):
+ "Format the query for output"
+ def __param_str__(param):
+ return "', '".join(str(elt) for elt in param)
+ idx = query.find("VALUES")
+ fields = tuple(
+ elt.strip().replace("%(", "").replace(")s", "")
+ for elt in query[idx + 7:-1].split(","))
+ values = (
+ tuple(param[field] for field in fields)
+ for param in params)
+ values_str = ", ".join(
+ f"('{__param_str__(value_tup)}')" for value_tup in values)
+ return f"{query[:idx]} VALUES{values_str};"
+
def insert_means(
filepath: str, speciesid: int, datasetid: int, dbconn: mdb.Connection,
rconn: Redis) -> int: # pylint: disable=[unused-argument]
"Insert the means/averages data into the database"
- print("INSERTING MEANS/AVERAGES DATA.")
headings = read_file_headings(filepath)
strains = strains_info(dbconn, headings[1:], speciesid)
check_strains(headings[1:], strains)
@@ -122,7 +136,7 @@ def insert_means(
"VALUES(%(ProbeSetDataId)s, %(StrainId)s, %(DataValue)s)")
xref_query = (
"INSERT INTO ProbeSetXRef(ProbeSetFreezeId, ProbeSetId, DataId) "
- "VALUES (%(ProbeSetFreezeId)s, %(ProbeSetId)s, %(ProbeSetDataId)s)")
+ "VALUES(%(ProbeSetFreezeId)s, %(ProbeSetId)s, %(ProbeSetDataId)s)")
the_means = (
{"ProbeSetFreezeId": datasetid, "ProbeSetDataId": data_id, **mean}
for data_id, mean in
@@ -134,9 +148,8 @@ def insert_means(
means = tuple(take(the_means, 1000))
if not bool(means):
break
- print(
- f"\nEXECUTING QUERIES:\n\t* {means_query}\n\t* {xref_query}\n"
- f"with parameters\n\t{means}")
+ print(__format_query__(means_query, means))
+ print(__format_query__(xref_query, means))
cursor.executemany(means_query, means)
cursor.executemany(xref_query, means)
return 0
@@ -145,7 +158,6 @@ def insert_se(# pylint: disable = [too-many-arguments]
filepath: str, speciesid: int, platformid: int, datasetid: int,
dbconn: mdb.Connection, rconn: Redis) -> int: # pylint: disable=[unused-argument]
"Insert the standard-error data into the database"
- print("INSERTING STANDARD ERROR DATA...")
headings = read_file_headings(filepath)
strains = strains_info(dbconn, headings[1:], speciesid)
check_strains(headings[1:], strains)
@@ -168,9 +180,7 @@ def insert_se(# pylint: disable = [too-many-arguments]
serrors = tuple(take(se_values, 1000))
if not bool(serrors):
break
- print(
- f"\nEXECUTING QUERY:\n\t* {se_query}\n\tWITH PARAMETERS:\n\t"
- f"{serrors}")
+ print(__format_query__(se_query, serrors))
cursor.executemany(se_query, serrors)
return 0