Open xapian database once, but SQL database once for each query.

We reopen the SQL database once for each query because it times out if
kept waiting.

* wqflask/scripts/index.py (main): Open xapian database once, but SQL
database once for each query.

1 files changed, 71 insertions, 71 deletions

diff --git a/wqflask/scripts/index.py b/wqflask/scripts/index.py
index f59a8788..c871db43 100644
--- a/wqflask/scripts/index.py
+++ b/wqflask/scripts/index.py
@@ -30,47 +30,47 @@ def write_document(db, idterm, doctype, doc):
 
 # pylint: disable=missing-function-docstring
 def main():
-    with database_connection() as conn, conn.cursor(MonadicDictCursor) as cursor:
-        # FIXME: Some Max LRS values in the DB are wrongly listed as
-        # 0.000, but shouldn't be displayed. Make them NULLs in the
-        # database.
-        cursor.execute("""
-        SELECT ProbeSet.Name AS name,
-               ProbeSet.Symbol AS symbol,
-               ProbeSet.description AS description,
-               ProbeSet.Chr AS chr,
-               ProbeSet.Mb AS mb,
-               ProbeSet.alias AS alias,
-               ProbeSet.GenbankId AS genbankid,
-               ProbeSet.UniGeneId AS unigeneid,
-               ProbeSet.Probe_Target_Description AS probe_target_description,
-               ProbeSetFreeze.Name AS dataset,
-               ProbeSetFreeze.FullName AS dataset_fullname,
-               ProbeSetFreeze.Id AS dataset_id,
-               Species.Name AS species,
-               InbredSet.Name AS `group`,
-               Tissue.Name AS tissue,
-               ProbeSetXRef.Mean AS mean,
-               ProbeSetXRef.LRS AS lrs,
-               ProbeSetXRef.additive AS additive,
-               Geno.Chr as geno_chr,
-               Geno.Mb as geno_mb
-        FROM Species
-             INNER JOIN InbredSet ON InbredSet.SpeciesId = Species.Id
-             INNER JOIN ProbeFreeze ON ProbeFreeze.InbredSetId = InbredSet.Id
-             INNER JOIN Tissue ON ProbeFreeze.TissueId = Tissue.Id
-             INNER JOIN ProbeSetFreeze ON ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id
-             INNER JOIN ProbeSetXRef ON ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id
-             INNER JOIN ProbeSet ON ProbeSet.Id = ProbeSetXRef.ProbeSetId
-             LEFT JOIN Geno ON ProbeSetXRef.Locus = Geno.Name AND Geno.SpeciesId = Species.Id
-        WHERE ProbeSetFreeze.confidentiality < 1 AND ProbeSetFreeze.public > 0
-        """)
-        termgenerator = xapian.TermGenerator()
-        termgenerator.set_stemmer(xapian.Stem("en"))
-        indexer = partial(index_text, termgenerator)
-
-        # pylint: disable=invalid-name
-        with xapian_writable_database() as db:
+    termgenerator = xapian.TermGenerator()
+    termgenerator.set_stemmer(xapian.Stem("en"))
+    indexer = partial(index_text, termgenerator)
+
+    # FIXME: Some Max LRS values in the DB are wrongly listed as
+    # 0.000, but shouldn't be displayed. Make them NULLs in the
+    # database.
+    # pylint: disable=invalid-name
+    with xapian_writable_database() as db:
+        with database_connection() as conn, conn.cursor(MonadicDictCursor) as cursor:
+            cursor.execute("""
+            SELECT ProbeSet.Name AS name,
+                   ProbeSet.Symbol AS symbol,
+                   ProbeSet.description AS description,
+                   ProbeSet.Chr AS chr,
+                   ProbeSet.Mb AS mb,
+                   ProbeSet.alias AS alias,
+                   ProbeSet.GenbankId AS genbankid,
+                   ProbeSet.UniGeneId AS unigeneid,
+                   ProbeSet.Probe_Target_Description AS probe_target_description,
+                   ProbeSetFreeze.Name AS dataset,
+                   ProbeSetFreeze.FullName AS dataset_fullname,
+                   ProbeSetFreeze.Id AS dataset_id,
+                   Species.Name AS species,
+                   InbredSet.Name AS `group`,
+                   Tissue.Name AS tissue,
+                   ProbeSetXRef.Mean AS mean,
+                   ProbeSetXRef.LRS AS lrs,
+                   ProbeSetXRef.additive AS additive,
+                   Geno.Chr as geno_chr,
+                   Geno.Mb as geno_mb
+            FROM Species
+                 INNER JOIN InbredSet ON InbredSet.SpeciesId = Species.Id
+                 INNER JOIN ProbeFreeze ON ProbeFreeze.InbredSetId = InbredSet.Id
+                 INNER JOIN Tissue ON ProbeFreeze.TissueId = Tissue.Id
+                 INNER JOIN ProbeSetFreeze ON ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id
+                 INNER JOIN ProbeSetXRef ON ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id
+                 INNER JOIN ProbeSet ON ProbeSet.Id = ProbeSetXRef.ProbeSetId
+                 LEFT JOIN Geno ON ProbeSetXRef.Locus = Geno.Name AND Geno.SpeciesId = Species.Id
+            WHERE ProbeSetFreeze.confidentiality < 1 AND ProbeSetFreeze.public > 0
+            """)
             for trait in cursor.fetchall():
                 doc = xapian.Document()
                 termgenerator.set_document(doc)
@@ -87,36 +87,36 @@ def main():
                 doc.set_data(json.dumps(trait.data))
                 write_document(db, trait["name"].bind(lambda name: f"Q{name}"), "gene", doc)
 
-        cursor.execute("""
-        SELECT Species.Name AS species,
-               InbredSet.Name AS `group`,
-               PublishFreeze.Name AS dataset,
-               PublishFreeze.FullName AS dataset_fullname,
-               PublishXRef.Id AS name,
-               COALESCE(Phenotype.Post_publication_abbreviation, Phenotype.Pre_publication_abbreviation) AS abbreviation,
-               COALESCE(Phenotype.Post_publication_description, Phenotype.Pre_publication_description) AS description,
-               Phenotype.Lab_code,
-               Publication.Abstract,
-               Publication.Title,
-               Publication.Authors AS authors,
-               Publication.Year AS year,
-               Publication.PubMed_ID AS pubmed_id,
-               PublishXRef.LRS as lrs,
-               PublishXRef.additive,
-               InbredSet.InbredSetCode AS inbredsetcode,
-               PublishXRef.mean,
-               PublishFreeze.Id AS dataset_id,
-               Geno.Chr as geno_chr,
-               Geno.Mb as geno_mb
-        FROM Species
-             INNER JOIN InbredSet ON InbredSet.SpeciesId = Species.Id
-             INNER JOIN PublishFreeze ON PublishFreeze.InbredSetId = InbredSet.Id
-             INNER JOIN PublishXRef ON PublishXRef.InbredSetId = InbredSet.Id
-             INNER JOIN Phenotype ON PublishXRef.PhenotypeId = Phenotype.Id
-             INNER JOIN Publication ON PublishXRef.PublicationId = Publication.Id
-             LEFT JOIN Geno ON PublishXRef.Locus = Geno.Name AND Geno.SpeciesId = Species.Id
-        """)
-        with xapian_writable_database() as db:
+        with database_connection() as conn, conn.cursor(MonadicDictCursor) as cursor:
+            cursor.execute("""
+            SELECT Species.Name AS species,
+                   InbredSet.Name AS `group`,
+                   PublishFreeze.Name AS dataset,
+                   PublishFreeze.FullName AS dataset_fullname,
+                   PublishXRef.Id AS name,
+                   COALESCE(Phenotype.Post_publication_abbreviation, Phenotype.Pre_publication_abbreviation) AS abbreviation,
+                   COALESCE(Phenotype.Post_publication_description, Phenotype.Pre_publication_description) AS description,
+                   Phenotype.Lab_code,
+                   Publication.Abstract,
+                   Publication.Title,
+                   Publication.Authors AS authors,
+                   Publication.Year AS year,
+                   Publication.PubMed_ID AS pubmed_id,
+                   PublishXRef.LRS as lrs,
+                   PublishXRef.additive,
+                   InbredSet.InbredSetCode AS inbredsetcode,
+                   PublishXRef.mean,
+                   PublishFreeze.Id AS dataset_id,
+                   Geno.Chr as geno_chr,
+                   Geno.Mb as geno_mb
+            FROM Species
+                 INNER JOIN InbredSet ON InbredSet.SpeciesId = Species.Id
+                 INNER JOIN PublishFreeze ON PublishFreeze.InbredSetId = InbredSet.Id
+                 INNER JOIN PublishXRef ON PublishXRef.InbredSetId = InbredSet.Id
+                 INNER JOIN Phenotype ON PublishXRef.PhenotypeId = Phenotype.Id
+                 INNER JOIN Publication ON PublishXRef.PublicationId = Publication.Id
+                 LEFT JOIN Geno ON PublishXRef.Locus = Geno.Name AND Geno.SpeciesId = Species.Id
+            """)
             for i, trait in enumerate(cursor.fetchall()):
                 doc = xapian.Document()
                 termgenerator.set_document(doc)