aboutsummaryrefslogtreecommitdiff
path: root/scripts/maintenance/datastructure.py
diff options
context:
space:
mode:
authorzsloan2021-10-18 17:50:26 +0000
committerzsloan2021-10-18 17:50:26 +0000
commite36eaf0003a598bc5aa688803dd1b36c24a4c051 (patch)
treea59b7dadf02241575eb0774f97c6048e2425c053 /scripts/maintenance/datastructure.py
parentbd421438f1f0b4de913fa40cd49cfcda27e6b16f (diff)
parent04f3d13aceeaec2e52b94037d59f08ed6dc6a8bb (diff)
downloadgenenetwork2-e36eaf0003a598bc5aa688803dd1b36c24a4c051.tar.gz
Merge branch 'testing' of github.com:genenetwork/genenetwork2 into feature/remove_trait_creation_from_search
Diffstat (limited to 'scripts/maintenance/datastructure.py')
-rwxr-xr-xscripts/maintenance/datastructure.py177
1 files changed, 177 insertions, 0 deletions
diff --git a/scripts/maintenance/datastructure.py b/scripts/maintenance/datastructure.py
new file mode 100755
index 00000000..9f3e8b1e
--- /dev/null
+++ b/scripts/maintenance/datastructure.py
@@ -0,0 +1,177 @@
+import utilities
+
+def get_probesetfreezes(inbredsetid):
+ cursor, con = utilities.get_cursor()
+ sql = """
+ SELECT ProbeSetFreeze.`Id`, ProbeSetFreeze.`Name`, ProbeSetFreeze.`FullName`
+ FROM ProbeSetFreeze, ProbeFreeze
+ WHERE ProbeSetFreeze.`ProbeFreezeId`=ProbeFreeze.`Id`
+ AND ProbeFreeze.`InbredSetId`=%s
+ """
+ cursor.execute(sql, (inbredsetid))
+ return cursor.fetchall()
+
+def get_probesetfreeze(probesetfreezeid):
+ cursor, con = utilities.get_cursor()
+ sql = """
+ SELECT ProbeSetFreeze.`Id`, ProbeSetFreeze.`Name`, ProbeSetFreeze.`FullName`
+ FROM ProbeSetFreeze
+ WHERE ProbeSetFreeze.`Id`=%s
+ """
+ cursor.execute(sql, (probesetfreezeid))
+ return cursor.fetchone()
+
+def get_strains(inbredsetid):
+ cursor, con = utilities.get_cursor()
+ sql = """
+ SELECT Strain.`Id`, Strain.`Name`
+ FROM StrainXRef, Strain
+ WHERE StrainXRef.`InbredSetId`=%s
+ AND StrainXRef.`StrainId`=Strain.`Id`
+ ORDER BY StrainXRef.`OrderId`
+ """
+ cursor.execute(sql, (inbredsetid))
+ return cursor.fetchall()
+
+def get_inbredset(probesetfreezeid):
+ cursor, con = utilities.get_cursor()
+ sql = """
+ SELECT InbredSet.`Id`, InbredSet.`Name`, InbredSet.`FullName`
+ FROM InbredSet, ProbeFreeze, ProbeSetFreeze
+ WHERE InbredSet.`Id`=ProbeFreeze.`InbredSetId`
+ AND ProbeFreeze.`Id`=ProbeSetFreeze.`ProbeFreezeId`
+ AND ProbeSetFreeze.`Id`=%s
+ """
+ cursor.execute(sql, (probesetfreezeid))
+ return cursor.fetchone()
+
+def get_species(inbredsetid):
+ cursor, con = utilities.get_cursor()
+ sql = """
+ SELECT Species.`Id`, Species.`Name`, Species.`MenuName`, Species.`FullName`
+ FROM InbredSet, Species
+ WHERE InbredSet.`Id`=%s
+ AND InbredSet.`SpeciesId`=Species.`Id`
+ """
+ cursor.execute(sql, (inbredsetid))
+ return cursor.fetchone()
+
+def get_genofreeze_byinbredsetid(inbredsetid):
+ cursor, con = utilities.get_cursor()
+ sql = """
+ SELECT GenoFreeze.`Id`, GenoFreeze.`Name`, GenoFreeze.`FullName`, GenoFreeze.`InbredSetId`
+ FROM GenoFreeze
+ WHERE GenoFreeze.`InbredSetId`=%s
+ """
+ cursor.execute(sql, (inbredsetid))
+ return cursor.fetchone()
+
+def get_nextdataid_genotype():
+ cursor, con = utilities.get_cursor()
+ sql = """
+ SELECT GenoData.`Id`
+ FROM GenoData
+ ORDER BY GenoData.`Id` DESC
+ LIMIT 1
+ """
+ cursor.execute(sql)
+ re = cursor.fetchone()
+ dataid = re[0]
+ dataid += 1
+ return dataid
+
+def get_nextdataid_phenotype():
+ cursor, con = utilities.get_cursor()
+ sql = """
+ SELECT PublishData.`Id`
+ FROM PublishData
+ ORDER BY PublishData.`Id` DESC
+ LIMIT 1
+ """
+ cursor.execute(sql)
+ re = cursor.fetchone()
+ dataid = re[0]
+ dataid += 1
+ return dataid
+
+def get_nextorderid_strainxref(inbredsetid):
+ cursor, con = utilities.get_cursor()
+ sql = """
+ SELECT StrainXRef.`OrderId`
+ FROM StrainXRef
+ WHERE StrainXRef.`InbredSetId`=%s
+ ORDER BY StrainXRef.`OrderId` DESC
+ LIMIT 1
+ """
+ cursor.execute(sql, (inbredsetid))
+ re = cursor.fetchone()
+ if re:
+ orderid = re[0] + 1
+ else:
+ orderid = 1
+ return orderid
+
+def insert_strain(inbredsetid, strainname):
+ speciesid = get_species(inbredsetid)[0]
+ cursor, con = utilities.get_cursor()
+ sql = """
+ INSERT INTO Strain
+ SET
+ Strain.`Name`=%s,
+ Strain.`Name2`=%s,
+ Strain.`SpeciesId`=%s
+ """
+ cursor.execute(sql, (strainname, strainname, speciesid))
+
+def insert_strainxref(inbredsetid, strainid):
+ orderid = get_nextorderid_strainxref(inbredsetid)
+ cursor, con = utilities.get_cursor()
+ sql = """
+ INSERT INTO StrainXRef
+ SET
+ StrainXRef.`InbredSetId`=%s,
+ StrainXRef.`StrainId`=%s,
+ StrainXRef.`OrderId`=%s,
+ StrainXRef.`Used_for_mapping`=%s,
+ StrainXRef.`PedigreeStatus`=%s
+ """
+ cursor.execute(sql, (inbredsetid, strainid, orderid, "N", None))
+
+def get_strain(inbredsetid, strainname):
+ speciesid = get_species(inbredsetid)[0]
+ cursor, con = utilities.get_cursor()
+ sql = """
+ SELECT Strain.`Id`, Strain.`Name`
+ FROM Strain
+ WHERE Strain.`SpeciesId`=%s
+ AND Strain.`Name` LIKE %s
+ """
+ cursor.execute(sql, (speciesid, strainname))
+ return cursor.fetchone()
+
+def get_strainxref(inbredsetid, strainid):
+ cursor, con = utilities.get_cursor()
+ sql = """
+ SELECT StrainXRef.`StrainId`
+ FROM StrainXRef
+ WHERE StrainXRef.`InbredSetId`=%s
+ AND StrainXRef.`StrainId`=%s
+ """
+ cursor.execute(sql, (inbredsetid, strainid))
+ return cursor.fetchone()
+
+def get_strain_sure(inbredsetid, strainname, updatestrainxref=None):
+ strain = get_strain(inbredsetid, strainname)
+ if not strain:
+ insert_strain(inbredsetid, strainname)
+ strain = get_strain(inbredsetid, strainname)
+ strainxref = get_strainxref(inbredsetid, strain[0])
+ if not strainxref and updatestrainxref:
+ insert_strainxref(inbredsetid, strain[0])
+ return strain
+
+def get_strains_bynames(inbredsetid, strainnames, updatestrainxref=None):
+ strains = []
+ for strainname in strainnames:
+ strains.append(get_strain_sure(inbredsetid, strainname, updatestrainxref))
+ return strains