aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi2023-10-18 12:07:34 +0300
committerFrederick Muriuki Muriithi2023-10-18 12:07:34 +0300
commit2fcf052d9369a78e7f6cf99e73d504dcacdbbfe3 (patch)
tree702b7e2ccce95604071d10fbfabc46696cc45102
parent5bd8b593ff1136bd76b8dc132a39d9ed3458b187 (diff)
downloadgn-uploader-2fcf052d9369a78e7f6cf99e73d504dcacdbbfe3.tar.gz
Link to `ProbeSet.Id` in XRef Table
The "ProbeSetId" value in the file is actually stored in the database in the `ProbeSet.Name` field, whereas the `ProbeSetXRef` table actually uses the `ProbeSet.Id` value to define the relationships. This commit fixes that issue.
-rw-r--r--scripts/insert_data.py17
1 files changed, 16 insertions, 1 deletions
diff --git a/scripts/insert_data.py b/scripts/insert_data.py
index f8b73f6..614ba4e 100644
--- a/scripts/insert_data.py
+++ b/scripts/insert_data.py
@@ -125,6 +125,21 @@ def __format_query__(query, params):
"INSERT INTO ", "INSERT INTO\n\t")
return f"{insert_str}\nVALUES\n\t{values_str};"
+def __xref_params__(dbconn: mdb.Connection, means: list[dict]) -> dict:
+ """Process params for cross-reference table."""
+ xref_names = tuple({mean["ProbeSetId"] for mean in means})
+ with dbconn.cursor(cursorclass=DictCursor) as cursor:
+ params_str = ", ".join(["%s"] * len(xref_names))
+ cursor.execute(
+ f"SELECT Name, Id FROM ProbeSet WHERE Name IN ({params_str})",
+ xref_names)
+ ids = {row["Name"]: row["Id"] for row in cursor.fetchall()}
+ return tuple({
+ **mean,
+ "ProbeSetName": mean["ProbeSetId"],
+ "ProbeSetId": ids[str(mean["ProbeSetId"])]
+ } for mean in means)
+
def insert_means(
filepath: str, speciesid: int, platform_id: int, datasetid: int,
dbconn: mdb.Connection, rconn: Redis) -> int: # pylint: disable=[unused-argument]
@@ -159,7 +174,7 @@ def insert_means(
print(__format_query__(xref_query, means))
cursor.executemany(probeset_query, means)
cursor.executemany(means_query, means)
- cursor.executemany(xref_query, means)
+ cursor.executemany(xref_query, __xref_params__(dbconn, means))
return 0
def insert_se(# pylint: disable = [too-many-arguments]