aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--scripts/rqtl2/install_genotypes.py41
1 files changed, 40 insertions, 1 deletions
diff --git a/scripts/rqtl2/install_genotypes.py b/scripts/rqtl2/install_genotypes.py
index d28b3b7..63e6113 100644
--- a/scripts/rqtl2/install_genotypes.py
+++ b/scripts/rqtl2/install_genotypes.py
@@ -67,7 +67,44 @@ def cross_reference_individuals(dbconn: mdb.Connection,
"ON DUPLICATE KEY UPDATE InbredSetId=InbredSetId",
tuple(ids))
return cursor.rowcount
- return cursor.rowcount
+
+def insert_genotype_data(dbconn: mdb.Connection,
+ speciesid: int,
+ genotypes: tuple[dict, ...],
+ individuals: tuple[str, ...]) -> tuple[int, tuple[int, ...]]:
+ """Insert the genotype data values into the database."""
+ with dbconn.cursor(cursorclass=DictCursor) as cursor:
+ paramstr = ", ".join(["%s"] * len(individuals))
+ cursor.execute(
+ f"SELECT Id, Name FROM Strain WHERE Name IN ({paramstr})",
+ individuals)
+ indids = {row["Name"]: row["Id"] for row in cursor.fetchall()}
+ markers = tuple(key for key in genotypes[0].keys() if key != "id")
+ paramstr = ", ".join(["%s"] * len(markers))
+ cursor.execute(
+ f"SELECT Id, Name FROM Geno WHERE SpeciesId=%s AND Name IN ({paramstr})",
+ (speciesid,)+markers)
+ markerids = {row["Name"]: row["Id"] for row in cursor.fetchall()}
+ cursor.execute("SELECT MAX(Id) AS lastid FROM GenoData")
+ lastid = cursor.fetchone()["lastid"]
+ data = tuple(
+ {**row, "gid": gid}
+ for gid, row in enumerate(
+ ({
+ "value": innerrow[marker],
+ "indid": indids[innerrow["id"]],
+ "markerid": markerids[marker]
+ } for innerrow in genotypes for marker in
+ (key for key in innerrow.keys() if key != "id")),
+ start=lastid+1))
+ cursor.executemany(
+ "INSERT INTO GenoData(Id, StrainId, value) "
+ "VALUES(%(gid)s, %(indid)s, %(value)s)",
+ data)
+ return cursor.rowcount, tuple({
+ "dataid": row["gid"],
+ "markerid": row["markerid"]
+ } for row in data)
def install_genotypes(dbconn: mdb.Connection,
speciesid: int,
@@ -104,6 +141,8 @@ def install_genotypes(dbconn: mdb.Connection,
insert_individuals(dbconn, speciesid, individuals)
cross_reference_individuals(
dbconn, speciesid, populationid, individuals)
+ _num_rows, data_ids = insert_genotype_data(
+ dbconn, speciesid, batch, individuals)
count = count + len(batch)
if "gmap" in cdata: