diff options
| -rw-r--r-- | scripts/insert_data.py | 28 |
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 |