From 4d95424e265d1838ec647206f269ac862bd147ea Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Tue, 14 Dec 2021 03:02:22 +0300
Subject: Set explicit formatting for sequences in queries

Issue:
https://github.com/genenetwork/gn-gemtext-threads/blob/main/topics/gn1-migration-to-gn2/partial-correlations.gmi

* the mysqlclient is sometimes finicky with the format specifiers, especially
  in the `WHERE ... IN ...` clauses where a sequence of values is
  provided. This commit explicitly sets the format specifiers for such clauses
  so as to avoid sporadic failures due to differences in the form of data from
  one query to the next.
---
 gn3/db/correlations.py | 12 +++++++-----
 1 file changed, 7 insertions(+), 5 deletions(-)

(limited to 'gn3')

diff --git a/gn3/db/correlations.py b/gn3/db/correlations.py
index 1743405..268cfdd 100644
--- a/gn3/db/correlations.py
+++ b/gn3/db/correlations.py
@@ -157,11 +157,12 @@ def fetch_symbol_value_pair_dict(
         symbol: data_id_dict.get(symbol) for symbol in symbol_list
         if data_id_dict.get(symbol) is not None
     }
-    query = "SELECT Id, value FROM TissueProbeSetData WHERE Id IN %(data_ids)s"
+    query = "SELECT Id, value FROM TissueProbeSetData WHERE Id IN ({})".format(
+        ",".join(f"%(id{i})s" for i in range(data_ids.values())))
     with conn.cursor() as cursor:
         cursor.execute(
             query,
-            data_ids=tuple(data_ids.values()))
+            **{f"id{i}": did for i, did in enumerate(data_ids.values())})
         value_results = cursor.fetchall()
         return {
             key: tuple(row[1] for row in value_results if row[0] == key)
@@ -406,14 +407,15 @@ def fetch_sample_ids(
     """
     query = (
         "SELECT Strain.Id FROM Strain, Species "
-        "WHERE Strain.Name IN %(samples_names)s "
+        "WHERE Strain.Name IN ({}) "
         "AND Strain.SpeciesId=Species.Id "
-        "AND Species.name=%(species_name)s")
+        "AND Species.name=%(species_name)s").format(
+            ",".join(f"%(s{i})s" for i in range(len(sample_names))))
     with conn.cursor() as cursor:
         cursor.execute(
             query,
             {
-                "samples_names": tuple(sample_names),
+                **{f"s{i}": sname for i, sname in enumerate(sample_names)},
                 "species_name": species_name
             })
         return tuple(row[0] for row in cursor.fetchall())
-- 
cgit v1.2.3