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 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_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_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 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 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)