def get_index_info(conn, modalidade): """ Get basic information about product or L20 """ query = Queries().IndexInfoProd().replace('\n', '').strip() prod_code = None try: # try to find data for l20 if product wasnt found result, conn = execute_query_temp(query, conn) result = result[0] if result[0] is None: query = Queries().IndexInfoL20().replace('\n', '').strip() result, conn = execute_query_temp(query, conn) result = result[0] if result[0] is None: raise Exception() except Exception as e: print("\n**** Too Bad! It seems your l20 wasnt found either! ****") raise L20notfound( "Nao encontrado dados para os seguintes parametros: {}".format( str(modalidade))) # return information about l20 or product return { 'units_per_visit': result[0], 'min_perc_disc': result[1], 'weight_flag': result[2], 'is_alcohol': result[3] }
def getQueryLaunchProduct(l30, target_code, target, str_un, un_code, str_filters, str_dna, loyalty_program, regions, str_join, filters): """ Lealdade 10 Compra lancamentos e minha categoria """ query, table = Queries().LealdadeLaunchProduct() queries = [] str_join_item = "" query, table = Queries().LealdadeLaunchProduct() if len(str_dna) > 0: str_join_item = str_join.format(table=table, filter_personas=str_dna) queries.append( query.format(lcode=target_code, filters=str_filters, program=loyalty_program, regions=regions, joins=str_join_item)) final_query = "select count(*) from ({queries}) AS T;".format( queries=" union ".join(queries)) return final_query
def getQueryDoesntBuyProduct(l30, l20, target, str_un, un_code, str_filters, str_dna, loyalty_program, regions, str_join, filters): """ lealdade 5 """ operator = ' AND ' if 'operador' in filters: operator = filters['operador'] # filters_l30_table = str_filters.replace('AND ', 'AND t_audience_l21_{l30}.'.format(l30=l30)) # loyalty_program_code_l30_table = loyalty_program.replace('AND ', 'AND t_audience_l21_{l30}.'.format(l30=l30)) # regions_l30_table = regions.replace('AND ', 'AND t_audience_l21_{l30}.'.format(l30=l30)) filters_un_table = str_filters.replace('AND ', 'AND u.') loyalty_program_code_un_table = loyalty_program.replace('AND ', 'AND u.') regions_un_table = regions.replace('AND ', 'AND u.') if not str_un and not len(str_dna) > 0: audienceQuery = getQueryAudiencenoUN(l30, target, str_filters, str_dna, loyalty_program, regions, str_join) query = Queries().LealdadeDoesntBuyProductNoUN()[0] query = query.format(l30=l30, filters=str_filters, filters_un=str_filters[4:], loyalty_program=loyalty_program, regions=regions, audience=audienceQuery) query = query.lower().replace('where and', 'where').replace( 'where and', 'where').replace('where and', 'where') else: un = '' if str_un: un = str_un[4:len(str_un)] un = un.replace('and', operator) un = 'AND ({un}) '.format(un=un) str_join_un = "" if len(str_dna) > 0: str_join_un = str_join.format(table='u', filter_personas=str_dna) audienceQuery = getQueryAudienceUN(l30, target, str_filters, str_dna, loyalty_program, regions, str_join) query = Queries().LealdadeDoesntBuyProductUN() query = query[0].format( condition=un, filters_un_table=filters_un_table, loyalty_program_code_un_table=loyalty_program_code_un_table, regions_un_table=regions_un_table, join_persona=str_join_un, audience=audienceQuery) # print query return query
def get_redemption(banner, level_code, audience_type, vendor, conn): query = Queries().conversion_rates().format(level=level_code) if banner: query += " AND banner ='{}' ".format(banner) if audience_type: query += " AND aba = '{}'".format(audience_type) if vendor != "": query += " AND cr.supplier_code = '{}'".format(vendor) result, conn = execute_query_temp(query, conn) result = pd.DataFrame(result) if result['conversion_rate'][0] == 0: message = "Redemption rate not found for level {l}, aba {a}, banner {b}".format( l=level_code, a=audience_type, b=banner) print(message) try: now = datetime.now() f = open("L20ConversionNotfound.log", "a") f.write("\n{0} - Erro: {1} ".format( now.strftime("%d/%m/%Y %H:%M:%S"), message)) f.flush() f.close() finally: r = False else: r = result['conversion_rate'][0] r = format(r, '.4f') return r
def getQueryAudienceUN(l30, target, str_filters, str_dna, loyalty_program, regions, str_join): """ get audience query for loyalty 5 with UN """ query, table = Queries().LealdadeDoesntBuyProductAudience() queries = [] str_join_item = "" if target == 'SUB-CATEGORIA': loyalty = 'is_loyal_l30' elif target == 'CATEGORIA': loyalty = 'is_loyal_l40' else: loyalty = 'is_loyal_b' for l30_item in l30: # check if l30 table exists if checkl30(l30_item): raise Exception('Nao existe cubo para:{}'.format(l30_item)) if len(str_dna) > 0: str_join_item = str_join.format(table=table.format(l30=l30_item), filter_personas=str_dna) queries.append( query.format(l30=l30_item, filters=str_filters, program=loyalty_program, regions=regions, joins=str_join_item, loyalty=loyalty)) final_queries = " {queries} ".format(queries=" union ".join(queries)) return final_queries
def post(self): # Region Set Up #create resultService object _resultsService = ResultsService() #load queries q = Queries() # create connection to postgres database _connector = Connector() conn = _connector.connect(1) # create cursor cur = conn.cursor() #define query parameters _failID = self.get_argument("_failureId") # endregion # Region main block cur.execute(q._email_failure, (_failID, )) _result = cur.fetchall() _result_to_json = _resultsService.parse(_result) self.render("emailMonitor.html", results=_result) cur.close() conn.close
def classificacao(): q = Queries() resultado = None while not resultado: id_aleatorio = random.randrange(1324911, 5324911) query_txt = 'SELECT id, tribunal, texto_decisao from jurisprudencia_2_inst.jurisprudencia_2_inst where lower(texto_decisao) like "%saúde%" and classificacao is null and id = "{}";'.format( str(id_aleatorio)) # id_aleatorio = random.randrange(19803,321358) # ID ALEATÓRIO STF # QUERY STF # query_txt = 'SELECT id, texto_decisao from stf.decisoes where lower(texto_decisao) like "%saúde%" and classificacao is null and id = "{}";'.format(str(id_aleatorio)) # id_aleatorio = random.randrange(448,299891) # ID ALEATÓRIO STj # QUERY STJ # query_txt = 'SELECT id, voto from stj.votos where lower(voto) like "%saúde%" and classificacao is null and id = "{}";'.format(str(id_aleatorio)) dados = q.query_padrao(query_text=query_txt) if dados: resultado = 1 session['id_p'] = dados[0][0] id_p = dados[0][0] # 2ª INSTÂNCIA tribunal = dados[0][1] texto_decisao = dados[0][2].replace('\n', '</p>\n<p>') # STF # tribunal = 'STF' # texto_decisao = dados[0][1].replace('\n', '</p>\n<p>') # STj # tribunal = 'STj' # texto_decisao = dados[0][1].replace('\n', '</p>\n<p>') return render_template('classificacao.html', texto_decisao=texto_decisao, id_p=id_p, tribunal=tribunal)
def getQueryLaunchProduct(l30, target_code, target, str_un, un_code, str_filters, str_dna, loyalty_program, regions, str_join, filters): """ Lealdade 10 Compra lancamentos e minha categoria """ query, table = Queries().LealdadeLaunchProduct() queries = [] str_join_item = "" if len(str_dna) > 0: str_join_item = str_join.format(table=table, filter_personas=str_dna) queries.append( query.format(lcode=target_code, filters=str_filters, program=loyalty_program, regions=regions, joins=str_join_item)) subtract_query = getQuerySpendMostloyaty10(l30, target_code, target, str_un, un_code, str_filters, str_dna, loyalty_program, regions, str_join, filters) final_query = "SELECT (select sum(qty) from ({queries}) as t ) - ({subquery} as d) as c".format( queries=" union ".join(queries), subquery=subtract_query) return final_query
def post(self): # Region Set Up _resultsService = ResultsService() _q = Queries() _connector = Connector() conn = _connector.connect(2) cur = conn.cursor() # endregion # Region Main Block cur.execute(_q._get_all_users) _result = cur.fetchall() _result_to_json = _resultsService.parse(_result) for item in _result: _rID = str(item[3]) _id = self.get_arguments("_checkbox") if _rID in _id: print("Deleting " + str(item[0])) cur.execute(_q._delete_user, (item[3], )) conn.commit() cur.close() conn.close() self.redirect("/users")
def post(self): # Region Set Up #create resultService object _resultsService = ResultsService() #load queries q = Queries() # create connection to postgres database _connector = Connector() conn = _connector.connect(2) # create cursor cur = conn.cursor() #define query parameters _firstname = self.get_argument("_firstname_field") _lastname = self.get_argument("_lastname_field") _timenow = datetime.datetime.now() # endregion # Region main block _query = q._get_all_users cur.execute(_query) _result = cur.fetchall() _result_to_json = _resultsService.parse(_result) _query = q._create_new_user cur.execute(_query, (_firstname, _lastname, _timenow)) conn.commit() cur.close() conn.close self.redirect("/users")
def getQueryAudienceUN(l30, target, str_filters, str_dna, loyalty_program, regions, str_join): """ get audience query for loyalty 5 with UN """ query, table = Queries().LealdadeDoesntBuyProductAudience() queries = [] str_join_item = "" if target == 'SUB-CATEGORIA': loyalty = 'is_loyal_l30' elif target == 'CATEGORIA': loyalty = 'is_loyal_l40' else: loyalty = 'is_loyal_b' if loyalty_program == "AND loyalty_program_code in ('3')": loyalty_program == "" for l30_item in l30: if len(str_dna) > 0: str_join_item = str_join.format(table=table.format(l30=l30_item), filter_personas=str_dna) queries.append( query.format(l30=l30_item, filters=str_filters, program=loyalty_program, regions=regions, joins=str_join_item, loyalty=loyalty)) final_queries = " {queries} ".format(queries=" union ".join(queries)) return final_queries
def checkl30(l30): query = Queries().TableExists().format('t_audience_l21_{}'.format(l30)) result = pd.DataFrame(execute_query(query), columns=['Table']) return result['Table'][0] == None
def getQuerySpendMostloyaty10(l30, target_code, target, str_un, un_code, str_filters, str_dna, loyalty_program, regions, str_join, filters): """ Lealdade 6 gasta mais com meu produto usado na lealdade 10 """ query, table = Queries().LealdadeSpendMostMyProductLoyalty10() queries = [] str_join_item = "" if loyalty_program == "AND loyalty_program_code in ('3')": loyalty_program = "" for l30_item in l30: if len(str_dna) > 0: str_join_item = str_join.format(table=table.format(l30=l30_item), filter_personas=str_dna) queries.append( query.format(l30=l30_item, filters=str_filters, program=loyalty_program, regions=regions, joins=str_join_item, loyalty='')) final_queries = "select count(distinct prsn_id) from ({queries})".format( queries=" union ".join(queries)) return final_queries
def getQueryDoesntSpendMost(l30, target_code, target, str_un, un_code, str_filters, str_dna, loyalty_program, regions, str_join, filters): """ Lealdade 7 Gasta mais com produtos concorrentes do que com meu produto """ query, table = Queries().LealdadeSpendMostMyProduct() queries = [] str_join_item = "" if target == 'SUB-CATEGORIA': loyalty = 'is_loyal_l30' elif target == 'CATEGORIA': loyalty = 'is_loyal_l40' else: loyalty = 'is_loyal_b' for l30_item in l30: if len(str_dna) > 0: str_join_item = str_join.format(table=table.format(l30=l30_item), filter_personas=str_dna) queries.append( query.format(l30=l30_item, filters=str_filters, loyal='false', program=loyalty_program, regions=regions, joins=str_join_item, loyalty=loyalty)) final_query = "select count(*) from ({queries}) AS T;".format( queries=" union ".join(queries)) return final_query
def get_template(template_type, keywords_dict): queries = Queries() try: character = keywords_dict['CHARACTER'] except KeyError: character = None try: place = keywords_dict['PLACE'] except KeyError: place = None try: loyalty = keywords_dict['LOYALTY'] except KeyError: loyalty = None try: gender = keywords_dict['GENDER'] except KeyError: gender = None try: fictional = keywords_dict['FICTIONAL'] except KeyError: fictional = None try: event = keywords_dict['EVENT'] except KeyError: event = None try: chapter = keywords_dict['CHAPTER'] except KeyError: chapter = None try: time = keywords_dict['TIME'] except KeyError: time = None if template_type == 'BIRTHPLACE': queries.birthplace(character, place) elif template_type == 'LOYALTY': queries.loyalty(character, loyalty) elif template_type == 'LIFESPAN': queries.lifespan(character) elif template_type == 'GENDER': queries.gender(character, gender) elif template_type == 'CHARACTER_INFO': queries.about_character(character) elif template_type == 'CHAPTER': queries.which_chapter(event, chapter) elif template_type == 'EVENT_INFO': queries.event_info(event) elif template_type == 'EVENT_HISTORY': queries.event_history(event) elif template_type == 'EVENT_LOCATION': queries.event_location(event, place) elif template_type == 'EVENT_DATE': queries.event_time(event, time) elif template_type == 'EVENT_INVOLVED': queries.event_involved(event, character) elif template_type == 'FICTIONAL': queries.fictional(character, fictional)
def pesquisa(): if request.method == 'POST': try: q = Queries() return str(q.query_tribunais(request.form['query'],tribunal='segunda_inst')) except Exception as e: return e else: return 'Preencha o formulário corretamente, por favor.'
def createQueries(config): q = Queries(int(config['query']['runs']), config['others']['output-dir'], config['query']['output-dir'], config['observation']['start_timestamp'], int(config['observation']['days']), int(config['query']['num-locations']), int(config['query']['num-sensors']), int(config['query']['time-delta'])) q.generateQueries()
def QRIS(pcap, website, chinese, queryset, bigrams, trident, topk, verbose): ''' Query Recognition in Incremental Search. ''' # Load network packets packets = Packets(pcap, website) if website is None: print('Detected website:', packets.website) if verbose: print('Server IP:', packets.packets.loc[0, 'dst']) # Correlate keystroke packets with DFA states keystrokes = packets.correlate_state(chinese, trident) if verbose: print('Keystrokes:\n', keystrokes) # Load query set and metadata queries = Queries(packets.website, chinese, queryset, bigrams, verbose) if verbose: print('Number of queries:', len(queries.queries)) # Filter by query length (keystroke number) candidates = queries.filter_by_length(keystrokes) if verbose: print('Filtered by length:', [len(x) for x in candidates]) # Filter by query token length (delimiter sequence) keystrokes = packets.delimit_token(chinese) candidates = queries.filter_by_token(keystrokes) if verbose: print('Filtered by token:', [len(x) for x in candidates]) # Filter by query compressed size pattern keystrokes = packets.size_pattern() candidates = queries.filter_by_pattern(keystrokes) if verbose: print('Filtered by pattern:', [len(x) for x in candidates]) # Rank by query typing rhythm (keystroke timing) keystrokes = packets.timing_interval() candidates = queries.rank_by_rhythm(keystrokes) if verbose: print('Ranked by rhythm:\n', [x.sort_values('rank') if len(x) > 0 else x for x in candidates]) # Return inferred queries querylist = [] for group in candidates: if len(group) == 0: continue ranked = group.sort_values('rank')['query'] if len(ranked) < topk: querylist += ranked.tolist() else: querylist += ranked[:topk].tolist() return querylist
def __init__(self): AuctionScanner.__init__(self) self.q = Queries() self.base_url = 'https://www.tutti.ch/api/v10/list.json?aggregated=1&limit=50&o=1&q=lego&with_all_regions=true' self.headers = { 'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64; rv:80.0) Gecko/20100101 Firefox/80.0', 'Accept': 'application/json, text/plain, */*', 'X-Tutti-Hash': '858521e7-f8d9-40ef-a534-6ae88d93165c' }
def get_average_price(self, conn): """ return average price for the products, need the connection that holds product temporary table """ query = Queries().AveragePrice() result, conn = execute_query_temp(query, conn) result = pd.DataFrame( result, columns=['avg_prod_price_clex', 'avg_prod_price_pa']) return result['avg_prod_price_clex'][0], result['avg_prod_price_pa'][ 0],
def main(): # Input: SQLite3 conn = sqlite3.connect( '../ExampleData/example_data_before_migration.sqlite3') conn.row_factory = sqlite3.Row # Output: MongoDB client = MongoClient('localhost', 27017) db = client.db_before_migration if db.user.count_documents({}) == 0: print('Loading data\n') data_loader.populate_database(conn, db) else: print('Warning: Database already exists; not reloading data\n') # Read parameters and reference results tc_file = open('../ExampleData/query_results_before_migration.json') test_cases = TestCases(tc_file) queries = Queries(db, test_cases) results, metrics = queries.execute() failed = False # Write complexity metrics to file metrics.to_csv('metrics.csv') if len(test_cases) != len(results): raise RuntimeError('There are {} queries but {} tests'.format( len(results), len(test_cases))) for result in results: if len(result.violation_indexes) == 0: # Success print( '{0.name} Succeeded in {0.elapsed_time:.4f} s\n----------------' .format(result)) else: # Error actual = pretty_print_json(result.actual) expected = pretty_print_json(result.expected) print('''{result.name} FAILED Mismatch indexes: {result.violation_indexes} Actual results: {actual} Expected results: {expected} ----------------'''.format(result=result, actual=actual, expected=expected)) failed = True if failed: raise RuntimeError('Some tests failed')
def get(self): _q = Queries() _resultsService = ResultsService() # create connection to postgres database _connector = Connector() conn = _connector.connect(2) # create cursor cur = conn.cursor() cur.execute(_q._get_all_ship_classes) _result = cur.fetchall() _result_to_json = _resultsService.parse(_result) self.render("shipClass.html", result=_result)
def getl20_30subgrupo(modalidade, vendor): """ Create a temporary table with the products of this target by a subgroup return L20 and l30 of the product """ query = Queries().ProductStructure() depart = modalidade._value.get('departamento') categoria = modalidade._value.get('categoria') subcategoria = modalidade._value.get('subcategoria') grupo = modalidade._value.get('grupo') subgrupo = modalidade._value.get('subgrupo') code = (depart, categoria, subcategoria, grupo, subgrupo) str_codes = "".join(code) query = Queries().ProductStructure() query = query.format(wheres=" prod_hier_l10_code in ('{subgrupo}')".format( subgrupo=str_codes), vendor=vendor) return query
def classificacao(): q = Queries() resultado = None while not resultado: id_aleatorio = random.randrange(1324911,5324911) query_txt = 'SELECT id, tribunal, texto_decisao from jurisprudencia_2_inst.jurisprudencia_2_inst where lower(texto_decisao) like "%saúde%" and classificacao is null and id = "{}";'.format(str(id_aleatorio)) dados = q.query_padrao(query_text=query_txt) if dados: resultado = 1 session['id_p'] = dados[0][0] id_p = dados[0][0] tribunal = dados[0][1] texto_decisao = dados[0][2].replace('\n', '</p>\n<p>') return render_template('classificacao.html', texto_decisao = texto_decisao, id_p = id_p, tribunal = tribunal)
def getl20_30Plu(modalidade, vendor): """ Create a temporary table with the products of this target by a PLU list return L20 and l30 of the product """ query = Queries().ProductStructure() str_prod_codes = list2str(modalidade._value.get("produto")) query = query.format( wheres=" prod_code in ({prods})".format(prods=str_prod_codes), vendor=vendor) return query
def getQueryDoesntBuyL20(l21, l20, target, str_un, un_code, str_filters, str_dna, loyalty_program, regions, str_join, filters): query, table = Queries().LealdadeDoesntBuyl20() if len(str_dna) > 0: str_join = str_join.format(table=table, filter_personas=str_dna) query = query.format(condition=str_un, filters=str_filters, un_code=un_code, program=loyalty_program, regions=regions, joins=str_join) return query
def __init__(self, set_number, condition='new'): self._url = 'http://www.bricklink.com/catalogPG.asp?S={}-1&ColorID=0'.format( set_number) self._headers = { 'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36' } self.condition = condition self.prices = {} self.set_number = set_number self.scan_date = datetime.now() self.exchange_rates = {} self.toCurrency = 'CHF' self.result = {} self.db = MySQLDatabase() self.q = Queries() self._parse_prices()
def getQueryLoyaltyUnder50(l21, l20, target, str_un, un_code, str_filters, str_dna, loyalty_program, regions, str_join, filters): query, table = Queries().Lealdade50() if len(str_dna) > 0: str_join = str_join.format(table=table.format(l21=l21), filter_personas=str_dna) query = query.format(l21=l21, filters=str_filters, loyal='false', program=loyalty_program, regions=regions, joins=str_join) return query
def getl20_30Familia(modalidade, vendor): """ Create a temporary table with the products of this target by a PLU and Familia return L20 and l30 of the product """ query = Queries().ProductStructureFamily() family_code = modalidade._value.get("familia") #subgrupo = modalidade._value.get("subgrupo") #modalidade.subgrupo query = query.format(subgrupo=modalidade.subgrupo, family=family_code, vendor=vendor) return query
def getQueryRecover(l30, target_code, target, str_un, un_code, str_filters, str_dna, loyalty_program, regions, str_join, filters): """ Lealdade 20 Compra lancamentos e minha categoria """ query, table = Queries().LealdadeRecoverProduct() #query = query.format(l21=l21, # filters=str_filters, loyal='true', # program=loyalty_program, regions=regions, # joins=str_join) queries = [] str_join_item = "" if target == 'SUB-CATEGORIA': loyalty = 'is_loyal_l30' elif target == 'CATEGORIA': loyalty = 'is_loyal_l40' else: loyalty = 'is_loyal_b' if loyalty_program == "AND loyalty_program_code in ('3')": loyalty_program == "" for l30_item in l30: if len(str_dna) > 0: str_join_item = str_join.format(table=table.format(l30=l30_item), filter_personas=str_dna) queries.append( query.format(l30=l30_item, filters=str_filters, loyal='true', program=loyalty_program, regions=regions, joins=str_join_item, loyalty=loyalty)) final_query = "select count(distinct t.prsn_id) from ({queries}) AS T;".format( queries=" union ".join(queries)) return final_query