def delete(publishxrefid, inbredsetid):
cursor = utilities.get_cursor()
sql = """
DELETE Phenotype
FROM PublishXRef,Phenotype
WHERE PublishXRef.`Id`=%s
AND PublishXRef.`InbredSetId`=%s
AND PublishXRef.`PhenotypeId`=Phenotype.`Id`
"""
cursor.execute(sql, (publishxrefid, inbredsetid))
sql = """
DELETE PublishData
FROM PublishXRef,PublishData
WHERE PublishXRef.`Id`=%s
AND PublishXRef.`InbredSetId`=%s
AND PublishXRef.`DataId`=PublishData.`Id`
"""
cursor.execute(sql, (publishxrefid, inbredsetid))
sql = """
DELETE PublishXRef
FROM PublishXRef
WHERE PublishXRef.`Id`=%s
AND PublishXRef.`InbredSetId`=%s
"""
cursor.execute(sql, (publishxrefid, inbredsetid))
def check_or_insert_geno(config_dic, marker_dic):
cursor, con = utilities.get_cursor()
sql = """
SELECT Geno.`Id`
FROM Geno
WHERE Geno.`SpeciesId`=%s
AND Geno.`Name` like %s
"""
cursor.execute(sql, (config_dic["speciesid"], marker_dic['locus']))
result = cursor.fetchone()
if result:
genoid = result[0]
print("get geno record: %d" % genoid)
else:
sql = """
INSERT INTO Geno
SET
Geno.`SpeciesId`=%s,
Geno.`Name`=%s,
Geno.`Marker_Name`=%s,
Geno.`Chr`=%s,
Geno.`Mb`=%s
"""
cursor.execute(sql, (config_dic['speciesid'], marker_dic['locus'], marker_dic['locus'], marker_dic['chromosome'], marker_dic['mb']))
rowcount = cursor.rowcount
genoid = con.insert_id()
print("INSERT INTO Geno: %d record: %d" % (rowcount, genoid))
return genoid
def check_or_insert_geno(params, marker):
cursor, con = utilities.get_cursor()
sql = """
SELECT Geno.`Id`
FROM Geno
WHERE Geno.`SpeciesId`=%s
AND Geno.`Name` like %s
"""
cursor.execute(sql, (speciesid, locus)) #This is correct
result = cursor.fetchone()
if result:
geno_id = result[0]
print("get geno record: ", geno_id)
else:
sql = """
INSERT INTO Geno
SET
Geno.`SpeciesId`=%s,
Geno.`Name`=%s,
Geno.`Marker_Name`=%s,
Geno.`Chr`=%s,
Geno.`Mb`=%s
"""
cursor.execute(sql, (species_id, locus, locus, chr, mb))
row_count = cursor.rowcount
geno_id = con.insert_id()
print("INSERT INTO Geno: %d record: %d" % (row_count, geno_id))
return geno_id
def delete_publishdata_publishxrefid(publishxrefid, inbredsetid):
cursor, con = utilities.get_cursor()
sql = """
DELETE PublishData
FROM PublishXRef,PublishData
WHERE PublishXRef.`Id`=%s
AND PublishXRef.`InbredSetId`=%s
AND PublishXRef.`DataId`=PublishData.`Id`
"""
cursor.execute(sql, (publishxrefid, inbredsetid))
sql = """
DELETE PublishSE
FROM PublishXRef,PublishSE
WHERE PublishXRef.`Id`=%s
AND PublishXRef.`InbredSetId`=%s
AND PublishXRef.`DataId`=PublishSE.`DataId`
"""
cursor.execute(sql, (publishxrefid, inbredsetid))
sql = """
DELETE NStrain
FROM PublishXRef,NStrain
WHERE PublishXRef.`Id`=%s
AND PublishXRef.`InbredSetId`=%s
AND PublishXRef.`DataId`=NStrain.`DataId`
"""
cursor.execute(sql, (publishxrefid, inbredsetid))
con.close()
def insert_strain(inbredsetid, strainname, updatestrainxref=None):
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))
strainid = con.insert_id()
if updatestrainxref:
sql = """
SELECT StrainXRef.`OrderId`
FROM StrainXRef
where StrainXRef.`InbredSetId`=%s
ORDER BY StrainXRef.`OrderId` DESC
LIMIT 1
"""
cursor.execute(sql, (inbredsetid))
re = cursor.fetchone()
orderid = re[0] + 1
#
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 check_or_insert_geno(config_dic, marker_dic):
cursor, con = utilities.get_cursor()
sql = """
SELECT Geno.`Id`
FROM Geno
WHERE Geno.`SpeciesId`=%s
AND Geno.`Name` like %s
"""
cursor.execute(sql, (config_dic["speciesid"], marker_dic['locus']))
result = cursor.fetchone()
if result:
genoid = result[0]
print("get geno record: %d" % genoid)
else:
sql = """
INSERT INTO Geno
SET
Geno.`SpeciesId`=%s,
Geno.`Name`=%s,
Geno.`Marker_Name`=%s,
Geno.`Chr`=%s,
Geno.`Mb`=%s
"""
cursor.execute(sql, (config_dic['speciesid'], marker_dic['locus'], marker_dic['locus'], marker_dic['chromosome'], marker_dic['mb']))
rowcount = cursor.rowcount
genoid = con.insert_id()
print("INSERT INTO Geno: %d record: %d" % (rowcount, genoid))
return genoid
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_phenotype(phenotypeid):
cursor, con = utilities.get_cursor()
sql = """
SELECT Phenotype.`Original_description`, Phenotype.`Pre_publication_description`, Phenotype.`Post_publication_description`
FROM Phenotype
WHERE Phenotype.`Id`=%s
"""
cursor.execute(sql, (phenotypeid))
return cursor.fetchone()
def get_publication(publicationid):
cursor, con = utilities.get_cursor()
sql = """
SELECT Publication.`Authors`, Publication.`Abstract`
FROM Publication
WHERE Publication.`Id`=%s
"""
cursor.execute(sql, (publicationid))
return cursor.fetchone()
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_publishxrefs(inbredsetid):
cursor, con = utilities.get_cursor()
sql = """
SELECT PublishXRef.`Id`, PublishXRef.`PhenotypeId`, PublishXRef.`PublicationId`, PublishXRef.`DataId`
FROM PublishXRef
WHERE PublishXRef.`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_probesetxref(probesetfreezeid):
cursor = utilities.get_cursor()
sql = """
SELECT ProbeSetXRef.`ProbeSetId`, ProbeSetXRef.`DataId`
FROM ProbeSetXRef
WHERE ProbeSetXRef.`ProbeSetFreezeId`=%s
"""
cursor.execute(sql, (probesetfreezeid))
return cursor.fetchall()
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_probeset(probesetid):
cursor = utilities.get_cursor()
sql = """
SELECT ProbeSet.`Id`, ProbeSet.`Name`, ProbeSet.`Symbol`, ProbeSet.`description`, ProbeSet.`Probe_Target_Description`, ProbeSet.`Chr`, ProbeSet.`Mb`
FROM ProbeSet
WHERE ProbeSet.`Id`=%s
"""
cursor.execute(sql, (probesetid))
return cursor.fetchone()
def getexceptions():
cursor = get_cursor("maintain_sb")
sql = """SELECT domain.name, nameserver.name
FROM domain, nameserver
WHERE nameserver.name NOT LIKE "%ns_.oregonstate.edu%"
AND nameserver.domain = domain.id"""
cursor.execute(sql)
return cursor.fetchall()
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_probesetxref_probesetfreezeid(locus, probesetfreezeid):
cursor = utilities.get_cursor()
sql = """
SELECT ProbeSetXRef.`ProbeSetId`
FROM ProbeSetXRef
WHERE ProbeSetXRef.`ProbeSetFreezeId`=%s
AND ProbeSetXRef.`Locus` LIKE %s
"""
cursor.execute(sql, (probesetfreezeid, locus))
return cursor.fetchall()
def get_probesetdata(probesetdataid):
cursor = utilities.get_cursor()
sql = """
SELECT Strain.`Id`, Strain.`Name`, ProbeSetData.`value`
FROM ProbeSetData, Strain
WHERE ProbeSetData.`Id`=%s
AND ProbeSetData.`StrainId`=Strain.`Id`;
"""
cursor.execute(sql, (probesetdataid))
return cursor.fetchall()
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_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_publishdatan(publishdataid):
cursor, con = utilities.get_cursor()
sql = """
SELECT Strain.`Id`, Strain.`Name`, NStrain.`count`
FROM NStrain, Strain
WHERE NStrain.`DataId`=%s
AND NStrain.`StrainId`=Strain.`Id`
"""
cursor.execute(sql, (publishdataid))
return cursor.fetchall()
def get_probesetxref(probesetfreezeid):
cursor, con = utilities.get_cursor()
sql = """
SELECT ProbeSetXRef.`ProbeSetId`, ProbeSetXRef.`DataId`, ProbeSetXRef.`Locus`, ProbeSetXRef.`LRS`, ProbeSetXRef.`pValue`,
ProbeSetXRef.`mean`, ProbeSetXRef.`se`
FROM ProbeSetXRef
WHERE ProbeSetXRef.`ProbeSetFreezeId`=%s
"""
cursor.execute(sql, (probesetfreezeid))
return cursor.fetchall()
def get_publishdata(publishdataid):
cursor, con = utilities.get_cursor()
sql = """
SELECT Strain.`Id`, Strain.`Name`, PublishData.`value`
FROM PublishData, Strain
WHERE PublishData.`Id`=%s
AND PublishData.`StrainId`=Strain.`Id`
"""
cursor.execute(sql, (publishdataid))
return cursor.fetchall()
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_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_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 handle(input):
#
cursor, con = utilities.get_cursor()
#
input = open(input, 'rb')
line = input.readline()
rowheaders = line.split()
del rowheaders[0]
rowids = map(lambda x : x.split('--')[1], rowheaders)
rowindex = 0
for line in input.readlines():
#
print("%s" % rowindex)
rowindex += 1
#
cells = line.split()
colheader = cells[0]
colid = colheader.split('--')[1]
#
for i in range(len(rowids)):
rowid = rowids[i]
v = cells[i+1]
# print("[%s\t%s] %s" % (rowid, colid, v))
#
sql = """
SELECT *
FROM LCorrRamin3
WHERE LCorrRamin3.`GeneId1`=%s
AND LCorrRamin3.`GeneId2`=%s
"""
cursor.execute(sql, (rowid, colid))
result = cursor.fetchone()
if result:
sql = """
UPDATE LCorrRamin3
SET LCorrRamin3.`value`=%s
WHERE LCorrRamin3.`GeneId1`=%s
AND LCorrRamin3.`GeneId2`=%s
"""
cursor.execute(sql, (v, rowid, colid))
rowcount = cursor.rowcount
# print("UPDATE: %s" % rowcount)
else:
sql = """
INSERT INTO LCorrRamin3
SET LCorrRamin3.`GeneId1`=%s,
LCorrRamin3.`GeneId2`=%s,
LCorrRamin3.`value`=%s
"""
cursor.execute(sql, (rowid, colid, v))
rowcount = cursor.rowcount
# print("INSERT: %s" % rowcount)
#
input.close()
con.close()
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 check_genoxref(config_dic, marker_dic):
cursor, con = utilities.get_cursor()
sql = """
select GenoXRef.*
from GenoXRef
where GenoXRef.`GenoFreezeId`=%s
AND GenoXRef.`GenoId`=%s
"""
cursor.execute(sql, (config_dic['genofreezeid'], marker_dic['genoid']))
rowcount = cursor.rowcount
return rowcount
def delete_genodata_genoid(genoid, genofreezeid):
cursor, con = utilities.get_cursor()
sql = """
DELETE GenoData
FROM GenoXRef,GenoData
WHERE GenoXRef.`GenoFreezeId`=%s
AND GenoXRef.`GenoId`=%s
AND GenoXRef.`DataId`=GenoData.`Id`
"""
cursor.execute(sql, (genofreezeid, genoid))
con.close()
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 check_genoxref(config_dic, marker_dic):
cursor, con = utilities.get_cursor()
sql = """
select GenoXRef.*
from GenoXRef
where GenoXRef.`GenoFreezeId`=%s
AND GenoXRef.`GenoId`=%s
"""
cursor.execute(sql, (config_dic['genofreezeid'], marker_dic['genoid']))
rowcount = cursor.rowcount
return rowcount
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_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_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 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_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 get_geno(inbredsetid, name):
cursor = utilities.get_cursor()
sql = """
SELECT Geno.`Id`, Geno.`Name`, Geno.`Chr`, Geno.`Mb`
FROM (Geno, InbredSet)
WHERE Geno.`SpeciesId`=InbredSet.`SpeciesId`
AND InbredSet.`Id`=%s
AND Geno.`Name` LIKE %s
"""
cursor.execute(sql, (inbredsetid, name))
return cursor.fetchone()
def delete_publishxref(publishxrefid, inbredsetid):
cursor, con = utilities.get_cursor()
sql = """
DELETE PublishXRef
FROM PublishXRef
WHERE PublishXRef.`Id`=%s
AND PublishXRef.`InbredSetId`=%s
"""
cursor.execute(sql, (publishxrefid, inbredsetid))
rowcount = cursor.rowcount
con.close()
return rowcount
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 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 insert_genoxref(config_dic, marker_dic):
cursor, con = utilities.get_cursor()
sql = """
INSERT INTO GenoXRef
SET
GenoXRef.`GenoFreezeId`=%s,
GenoXRef.`GenoId`=%s,
GenoXRef.`DataId`=%s,
GenoXRef.`cM`=%s,
GenoXRef.`Used_for_mapping`=%s
"""
cursor.execute(sql, (config_dic['genofreezeid'], marker_dic['genoid'], config_dic['dataid'], marker_dic['cm'], 'N'))
rowcount = cursor.rowcount
print("INSERT INTO GenoXRef: %d record" % (rowcount))
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 main():
"""
Drops the current sandbox database and creates it again by copying
maintain. The user required is `maintain_sb`, which has read-only access to
maintain and the ability to drop and create tables in maintain_sb.
"""
msb = get_cursor("maintain_sb")
msb.execute("DROP DATABASE maintain_sb")
msb.execute("CREATE DATABASE maintain_sb")
msb.execute("SHOW TABLES IN maintain")
for table, in msb.fetchall():
if table in ["bandwidth_usage", "session", "host_history"]:
continue
print "Creating %s..." % table
table = "`%s`" % table
sql = "CREATE TABLE maintain_sb.{0} LIKE maintain.{0}".format(table)
msb.execute(sql)
msb.execute("INSERT INTO maintain_sb.{0} "
"SELECT * FROM maintain.{0}".format(table))
def insert_genodata(config, config_dic, marker_dic):
cursor, con = utilities.get_cursor()
for index, strain in enumerate(config_dic['strains']):
strainid = strain[0]
value = utilities.to_db_string(marker_dic['values'][index], None)
if not value:
continue
value = config.get('config', "genovalue_" + value)
try:
number = int(value)
except:
continue
if not number in [-1, 0, 1]:
continue
sql = """
INSERT INTO GenoData
SET
GenoData.`Id`=%s,
GenoData.`StrainId`=%s,
GenoData.`value`=%s
"""
cursor.execute(sql, (config_dic['dataid'], strainid, number))
from utilities import get_cursor
# Set up database connection
cursor = get_cursor('maintain_sb')
def find_or_insert_dname(dname):
search_sql = "SELECT id FROM domain WHERE name = '%s'" % (dname)
if cursor.execute(search_sql):
domain_id, = cursor.fetchone()
return domain_id
cursor.execute("INSERT INTO domain (name, master_domain, enabled) "
"VALUES ('%s', %s, %s)" % (dname, 0, 1))
return cursor.lastrowid
def update_master_domain(domain_id, parent_id):
cursor.execute("UPDATE domain "
"SET master_domain = '%s' "
"WHERE id = '%s'" % (parent_id, domain_id))
return cursor.lastrowid
def is_valid(dname):
if '.' not in dname:
return True
else:
return False
def main(argv):
# config
config = utilities.get_config(argv[1])
print("config:")
for item in config.items('config'):
print(("\t%s" % (str(item))))
# var
inbredsetid = config.get('config', 'inbredsetid')
print(("inbredsetid: %s" % inbredsetid))
species = datastructure.get_species(inbredsetid)
speciesid = species[0]
print(("speciesid: %s" % speciesid))
dataid = datastructure.get_nextdataid_phenotype()
print(("next data id: %s" % dataid))
cursor, con = utilities.get_cursor()
# datafile
datafile = open(config.get('config', 'datafile'), 'r')
phenotypedata = csv.reader(datafile, delimiter='\t', quotechar='"')
phenotypedata_head = next(phenotypedata)
print(("phenotypedata head:\n\t%s" % phenotypedata_head))
strainnames = phenotypedata_head[1:]
strains = datastructure.get_strains_bynames(inbredsetid=inbredsetid, strainnames=strainnames, updatestrainxref="yes")
# metafile
metafile = open(config.get('config', 'metafile'), 'r')
phenotypemeta = csv.reader(metafile, delimiter='\t', quotechar='"')
phenotypemeta_head = next(phenotypemeta)
print(("phenotypemeta head:\n\t%s" % phenotypemeta_head))
print()
# load
for metarow in phenotypemeta:
#
datarow_value = next(phenotypedata)
datarow_se = next(phenotypedata)
datarow_n = next(phenotypedata)
# Phenotype
sql = """
INSERT INTO Phenotype
SET
Phenotype.`Pre_publication_description`=%s,
Phenotype.`Post_publication_description`=%s,
Phenotype.`Original_description`=%s,
Phenotype.`Pre_publication_abbreviation`=%s,
Phenotype.`Post_publication_abbreviation`=%s,
Phenotype.`Lab_code`=%s,
Phenotype.`Submitter`=%s,
Phenotype.`Owner`=%s,
Phenotype.`Authorized_Users`=%s,
Phenotype.`Units`=%s
"""
cursor.execute(sql, (
utilities.to_db_string(metarow[1], None),
utilities.to_db_string(metarow[2], None),
utilities.to_db_string(metarow[3], None),
utilities.to_db_string(metarow[4], None),
utilities.to_db_string(metarow[5], None),
utilities.to_db_string(metarow[6], None),
utilities.to_db_string(metarow[7], None),
utilities.to_db_string(metarow[8], None),
utilities.to_db_string(metarow[9], ""),
utilities.to_db_string(metarow[18], ""),
))
rowcount = cursor.rowcount
phenotypeid = con.insert_id()
print(("INSERT INTO Phenotype: %d record: %d" % (rowcount, phenotypeid)))
# Publication
publicationid = None # reset
pubmed_id = utilities.to_db_string(metarow[0], None)
if pubmed_id:
sql = """
SELECT Publication.`Id`
FROM Publication
WHERE Publication.`PubMed_ID`=%s
"""
cursor.execute(sql, (pubmed_id))
re = cursor.fetchone()
if re:
publicationid = re[0]
print(("get Publication record: %d" % publicationid))
if not publicationid:
sql = """
INSERT INTO Publication
SET
Publication.`PubMed_ID`=%s,
Publication.`Abstract`=%s,
Publication.`Authors`=%s,
Publication.`Title`=%s,
Publication.`Journal`=%s,
Publication.`Volume`=%s,
Publication.`Pages`=%s,
Publication.`Month`=%s,
Publication.`Year`=%s
"""
cursor.execute(sql, (
utilities.to_db_string(metarow[0], None),
utilities.to_db_string(metarow[12], None),
utilities.to_db_string(metarow[10], ""),
utilities.to_db_string(metarow[11], None),
utilities.to_db_string(metarow[13], None),
utilities.to_db_string(metarow[14], None),
utilities.to_db_string(metarow[15], None),
utilities.to_db_string(metarow[16], None),
utilities.to_db_string(metarow[17], ""),
))
rowcount = cursor.rowcount
publicationid = con.insert_id()
print(("INSERT INTO Publication: %d record: %d" % (rowcount, publicationid)))
# data
for index, strain in enumerate(strains):
#
strainid = strain[0]
value = utilities.to_db_float(datarow_value[index+1], None)
se = utilities.to_db_float(datarow_se[index+1], None)
n = utilities.to_db_int(datarow_n[index+1], None)
#
if value is not None:
sql = """
INSERT INTO PublishData
SET
PublishData.`Id`=%s,
PublishData.`StrainId`=%s,
PublishData.`value`=%s
"""
cursor.execute(sql, (dataid, strainid, value))
if se is not None:
sql = """
INSERT INTO PublishSE
SET
PublishSE.`DataId`=%s,
PublishSE.`StrainId`=%s,
PublishSE.`error`=%s
"""
cursor.execute(sql, (dataid, strainid, se))
if n is not None:
sql = """
INSERT INTO NStrain
SET
NStrain.`DataId`=%s,
NStrain.`StrainId`=%s,
NStrain.`count`=%s
"""
cursor.execute(sql, (dataid, strainid, n))
# PublishXRef
sql = """
INSERT INTO PublishXRef
SET
PublishXRef.`InbredSetId`=%s,
PublishXRef.`PhenotypeId`=%s,
PublishXRef.`PublicationId`=%s,
PublishXRef.`DataId`=%s,
PublishXRef.`comments`=%s
"""
cursor.execute(sql, (inbredsetid, phenotypeid, publicationid, dataid, ""))
rowcount = cursor.rowcount
publishxrefid = con.insert_id()
print(("INSERT INTO PublishXRef: %d record: %d" % (rowcount, publishxrefid)))
# for loop next
dataid += 1
print()
# release
con.close()
# run this every 5 minutes
import utilities
import datetime
from recording import Recording
import time
import pytz
# connect to database
cur = utilities.get_cursor()
# select all future scheduled recordings not yet processed
cur.execute('SELECT id, station_name, show_name, time_start, time_stop, stream_url FROM scheduled_recordings WHERE processed = 0')
scheduled_recordings = [{'id': row[0], 'station_name': row[1], 'show_name': row[2], 'time_start': datetime.datetime.strptime(row[3], '%Y-%m-%d %H:%M %z'), 'time_stop': datetime.datetime.strptime(row[4], '%Y-%m-%d %H:%M %z'), 'stream_url': row[5]} for row in list(cur)]
now = datetime.datetime.now(pytz.utc)
# if any recording scheduled within the next 10 minutes, create Recording object and start record()
recordings=[]
for rec in scheduled_recordings:
if rec['time_stop'] < now: # if show has already ended
continue
if rec['time_start'] < now or abs(rec['time_start'] - now).minutes < 10: # if show starts within the next 10 minutes or has already started
id = rec['id']
recordings.append(Recording(stream_url=rec['stream_url'], station_name=rec['station_name'], show_name=rec['show_name'], time_start=rec['time_start'], time_stop=rec['time_stop']).record())
# Update entry with processed = True
cur.execute (f'UPDATE scheduled_recordings SET processed = 1 WHERE id={id}')
# wait until all reacordings are finished to keep streamripper process running
while not all([rec.poll() is not None for rec in recordings]):
time.sleep(1)