def save_sample_plant(sample_id, plant_id, date): if add_plant(plant_id): progress('Plant %d added' % plant_id) if not sql.exists('samples', sample_id): sample = { 'id': sample_id, 'created': date, } if sql.insert('samples', sample): progress('Sample %d added' % sample['id']) sample_plants = { 'sample_id': sample_id, 'plant_id': plant_id } if sql.insert('sample_plants', sample_plants): progress('Sample_plants (%d, %d) added' % (sample_id, plant_id)) else: # check if there is already a link between sample and plant samples = sql.fetch_all('sample_plants', { 'sample_id': sample_id, 'plant_id': plant_id }) if samples: for sample in samples: if sample['plant_id'] == 1: if sql.update('sample_plants', { 'sample_id': sample_id }, { 'plant_id': plant_id }): progress('Updated %d sample plant_id from %d to %d' % (sql.lastrowid(), 1, plant_id)) else: sample_plants = { 'sample_id': sample_id, 'plant_id': plant_id } if sql.insert('sample_plants', sample_plants): progress('Sample_plants (%d, %d) added' % (sample_id, plant_id))
def add_plant(plant_id): if not sql.exists('plants', plant_id): ora_sql.set_formatting(False) plant = ora_sql.get_plant_information(plant_id) ora_sql.set_formatting(True) if plant == None: # fcuk! not found in LIMS, add a dummy :/ plant = { 'id': plant_id, 'culture_id': DUMMY_CULTURE_ID, 'subspecies_id': DUMMY_SUBSPECIES_ID } # culture_id 1 is the dummy culture else: # prepare the plant plant = dict( (k.lower(), v) for k,v in plant.iteritems() ) plant['subspecies_id'] = plant['u_subspecies_id'] plant['lineid'] = plant['line_id'] plant['id'] = plant['aliquot_id'] plant['culture_id'] = plant['u_culture'] del(plant['u_subspecies_id']) del(plant['line_id']) del(plant['aliquot_id']) del(plant['u_culture']) del(plant['location_id']) # add the culture if not exists if plant != None: if not sql.exists('cultures', plant['culture_id']): ora_sql.set_formatting(False) culture = ora_sql.get_culture_information(plant['culture_id']) ora_sql.set_formatting(True) if culture: # prepare the culture culture = dict( (k.lower(), v) for k,v in culture.iteritems() ) culture['experiment_id'] = 1 culture['id'] = culture['study_id'] del(culture['study_id']) else: # prepare dummy culture = { 'id': DUMMY_CULTURE_ID, 'name': 'placeholder' } if sql.insert('cultures', culture): progress('Culture %d added' % culture['id']) # add the subspecies if not exists if plant['subspecies_id'] == None: plant['subspecies_id'] = DUMMY_SUBSPECIES_ID if not sql.exists('subspecies', plant['subspecies_id']): subspecies = { 'species_id': SPECIES_ID, 'id': plant['subspecies_id'] } if sql.insert('subspecies', subspecies): progress('Subspecies %d added' % subspecies['id']) return sql.insert('plants', plant)
def f(): q = sql.get_f('s.id', 'store s', ['p.id = s.id_product', 's.quantity > 0']) q = sql.get_f('p.id', 'product p', sql.exists(q)) q = sql.order(q, 'p.id') pids = db.fetchvals(q) idmax = len(pids) - 1 idl = map(lambda x: str(pids[random.randint(0, idmax)]), xrange(10)) idl = ",".join(idl) site.config.set("RANDOM_PRODUCTS", idl) site.config.set("STATIC_PRODUCT", pids[0])
def f(): f = ['ps.id_product = p.id', 'pr.id_prop = ps.id_prop1', 'pr.name = "ETA"'] q = sql.get_f('pr.val1', ['prop_set ps', 'param pr'], f) q = '(%s)' % sql.limit(q, 0, 1) q = sql.update('eta', 'product p') % q db.execute(q) f = ['s.id_product = p.id', 'pr.id_prop = s.id_store', 'pr.name = "ETA"'] q = sql.get_f('pr.val1', ['store s', 'param pr'], f) q = sql.order(q, 'pr.val1') q = '(%s)' % sql.limit(q, 0, 1) fu = sql.get_f('id', 'store s', 's.id_product = p.id') fu = sql.exists(fu) q = sql.update('eta', 'product p', filter = fu) % q db.execute(q) db.commit()
def sqlpids(self, pview, ppair, adfilter = [], usemem = False): f = self.sqlppair(ppair) f += ["ps.id_product = p.id"] f += ["ps.pview = '%s'" % pview] + adfilter if self.request('show_store'): fstore = sql.get_f('s.id', 'store s', ['s.id_product = p.id', 's.quantity > 0']) fstore = sql.exists(fstore) f += [fstore] if usemem: tpropset = const.MEM_SEARCH + ' ps' else: tpropset = self.tpropset field = 'ps.id_product' if not self.cat.isfiche: field = 'DISTINCT ' + field q = sql.get_f([field], tpropset + ', ' + self.tproduct, f) return q
def main(argv): argparser = argparse.ArgumentParser(description='') argparser.add_argument('files', nargs='+') args = argparser.parse_args(argv) for fn in args.files: if isdir(fn): continue # read in file print 'opening %s' % fn f = open(fn, 'r') lines_raw = f.readlines() raw_id = None if sql.insert('raws', { 'data': ''.join(lines_raw), 'filename': basename(fn) }): raw_id = sql.lastrowid() progress("Added %d to raws" % raw_id) # preprocess lines = [] try: line_nr = 0 for line in lines_raw: line_nr += 1 line = line.rstrip('\r\n') line = re.split(r'\t|;', line) line = preprocess_line(line) lines.append(line) except: print "%d: %s" % (line_nr, line) raise # add a dummy plant/culture/subspecies, just in case samples can't be connected just yet. if not sql.exists('cultures', 1): sql.insert('cultures', {'id': DUMMY_CULTURE_ID, 'name': 'placeholder' }) if not sql.exists('plants' , 1): sql.insert('plants' , {'id': DUMMY_PLANT_ID, 'name': 'placeholder', 'culture_id': DUMMY_CULTURE_ID }) if not sql.exists('subspecies' , 1): sql.insert('subspecies' , {'id': DUMMY_SUBSPECIES_ID, 'species_id': SPECIES_ID }) # some lines need to be sent back to LIMS, this is where we store them lims_lines = [] # save! line_nr = 0 try: for line in lines: line_nr += 1 program_id = get_program_id(line) if is_sample_plant(line): line[8] = int(line[8]) # plant_id is still str #save_sample_plant(sample_id=line[7], plant_id=line[8], date=date) # skipped because made redundant when preloading all samples/plants lims_lines.append("\t".join([ str(item) for item in line ])) #elif program_id == 1 and is_freshweight_between(line): # lims_lines.append("\t".join([ str(item) for item in line ])) else: phenotype = format_line(line) # create a readable program # add the actual phenotype phenotype_id = None if sql.insert('phenotypes', { 'version': phenotype['version'], 'object' : phenotype['object'], 'program_id': phenotype['program_id'], 'date': phenotype['date'], 'time': phenotype['time'], 'entity_id': phenotype['entity_id'], 'value_id': phenotype['value_id'], 'number': phenotype['number'] }): phenotype_id = sql.lastrowid() progress('Added %d to phenotype' % phenotype_id) # if plant, add it to plants, otherwise to samples if ora_sql.is_plant(phenotype['sample_id']) or ora_sql.was_plant(phenotype['sample_id']): sql.insert('phenotype_plants', { 'phenotype_id': phenotype_id, 'plant_id': phenotype['sample_id'] }) elif ora_sql.is_sample(phenotype['sample_id']): sql.insert('phenotype_samples', { 'phenotype_id': phenotype_id, 'sample_id': phenotype['sample_id'] }) elif ora_sql.is_aliquot(phenotype['sample_id']): sql.insert('phenotype_aliquots', { 'phenotype_id': phenotype_id, 'aliquot_id': phenotype['sample_id'] }) else: print "%s NOT found!!" % phenotype['sample_id'] sql.insert('phenotype_raws' , { 'phenotype_id': phenotype_id, 'raw_id': raw_id, 'line_nr': line_nr }) if program_id > 1: sql.insert('phenotype_bbches', { 'phenotype_id': phenotype_id, 'bbch_id': phenotype['bbch_id']}) except: progress("%d: %s" % (line_nr, line)) raise # save the current saved lines for LIMS write_lims_lines(lims_lines, fn) sql.commit()
def __sqlstore(self): fstore = sql.get_f('s.id', 'store s', ['s.id_product = p.id', 's.quantity > 0']) fstore = sql.exists(fstore) return fstore
# Loop inside all the data from Ottawa c.execute(sql.search(provider, city)) for row in c.fetchall(): location = row['location'] lng = row['lng'] lat = row['lat'] # Provider if bool(location and lng and lat): # Remove white spaces in location name location = location.strip() # Check if location already exists in Geocoder DB c.execute(sql.exists(), (provider, location)) if not c.fetchone(): # Select your provider for geocoding if provider == 'Bing': g = geocoder.bing(location) elif provider == 'Google': g = geocoder.google(location) # Geocode must be correct if g.ok: # Calculate Distance c.execute(sql.distance(lat, lng, g.lat, g.lng)) distance = c.fetchone()['distance'] # Insert Into Rows fieldnames = [
# Loop inside all the data from Ottawa c.execute(sql.search(provider, city)) for row in c.fetchall(): location = row['location'] lng = row['lng'] lat = row['lat'] # Provider if bool(location and lng and lat): # Remove white spaces in location name location = location.strip() # Check if location already exists in Geocoder DB c.execute(sql.exists(), (provider, location)) if not c.fetchone(): # Select your provider for geocoding if provider == 'Bing': g = geocoder.bing(location) elif provider == 'Google': g = geocoder.google(location) # Geocode must be correct if g.ok: # Calculate Distance c.execute(sql.distance(lat, lng, g.lat, g.lng)) distance = c.fetchone()['distance'] # Insert Into Rows
def parsefilter(self, ftext): """Метод, преобразовывающий метаописание в набор SQL-фильтров Функция преобразует созданный по определенным правилам текстовый шаблон в фильтр SQL-запроса (where), который обнулит наличие некоторых товаров на складе. Используется для удобного описания правил в заголовках Excel и Google Doc таблиц, по которым обновляется наличие товаров """ # список строк SQL-фильтра для отбора товаров # K - компоненты (свойства), характеризующие товар. # Таких свойств может быть до 7 на каждое представление (View) товара. # Например, товар относится к категории "звезды" (k1=zvezdi), # и характеризуется некоторыми свойствами k2-k7 - цвет, размер и т.п. # Один товар может иметь несколько представлений, т.е. обладать разным # набором свойств K kfilter = [] # список строк SQL-фильтра для отбора складов товаров sfilter = [] cat = False ftext = real_translit(ftext.lower()) # Объект <группа складов> - получается из базы вызовом # функции Get Property Group By Translit - # получить группу свойств по коду свойства whgroup = db.fetchval('SELECT getpgbytr("stores")') for val in re.split(r'\,\s*', ftext): # Если в метаописании найдена инструкция по компонентному фильтру, # накладываемому для ограничения выборки товаров - расшифровать эту # инструкцию, и привести ее к виду строк SQL-фильтра if re.match(r'k\d=', val): propindex = toint(val[1:2]) if propindex == 1: cat = True propname = val[3:] propt = real_translit(propname) prop = db.fetchobject('prop', propt, 'translit') if prop: log("FILTER [K%d], ID %d = %s" % (propindex, prop.id, translit(propname))) else: raise Exception("Property %s not found" % translit(propname)) kfilter.append('ps.id_prop%d = %s' % (propindex, prop.id)) # Если в метаописании найдена инструкция по очистке единиц хранения # товара перед загрузкой новых товаров - добавить ее в фильтр, # ограничивающий выбор товаров elif re.match(r'clear=', val): slist = [] for store in re.split(r';\s*', val[6:]): slist.append('translit RLIKE "%s"' % store) f = [sql.f_or(slist), 'id_group = %s' % whgroup] q = sql.get_f('id', 'prop', f) sfilter = db.fetchvals(q) # Если в метаописании найдена инструкция склада по умолчанию, # то установить склад по умолчанию elif re.match(r'default=', val): t = real_translit(val[8:]) self.whdef = db.fetchobject('prop', t, 'translit') # Ограничить список товаров их главным представлением kfilter.append('ps.pview = "%s"' % gvar.view.pview) if not cat: kfilter.append('ps.id_prop1 = %s' % gvar.cat.id) # Список строк итогового SQL-фильтра f = [] # Если есть ограничение по складам товаров - добавить его if sfilter: f.append(sql.fin('s.id_store', sfilter)) # Если есть ограничение по компонентному фильтру - добавить его if kfilter: q = sql.get_f('ps.id', 'prop_set ps', kfilter + ['ps.id_product = s.id_product']) f.append(sql.exists(q)) self.kfilter = kfilter return f