def LeeProvPendP(email, clave): bd = DB(nombrebd="textos") usuario = login(email, clave, bd) if usuario: usuario["pendientes"] = bd.Ejecuta(""" select *, telefono, direccion, pedicab.id as ID, 'X' as despachar from pedicab inner join cli on cli.id=pedicab.idcli where idprov=%s and pendiente=1 """ % usuario['id']) bd.cierra() return usuario bd.cierra() return None # def LeePedidoP(email, clave, idpedido): bd = DB(nombrebd="textos") usuario = login(email, clave, bd) if usuario: tabla = "prod%s" % usuario['id'] rows = bd.Ejecuta(""" select prod.*, cantidad, pedidet.id as idpedidet from pedidet inner join %s as prod on prod.id=pedidet.idproducto where pedidet.idpedicab=%s """ % (tabla, idpedido)) bd.cierra() return rows bd.cierra() return None
def AgregaPreguntaA(email, clave, idtexto): bd = DB(nombrebd="aprende") usuario = login(email, clave, bd) resp = {} if usuario: if duenoPagina(idtexto, bd) == usuario['ID']: rows = bd.Ejecuta( "select orden from preguntas where idtexto=%s order by orden desc limit 1" % idtexto) orden = 1 if rows: orden = rows[0]['orden'] + 1 bd.Ejecuta( "insert into preguntas (idtexto, orden) values(%s, %s)" % (idtexto, orden)) idpregunta = bd.UltimoID() bd.Ejecuta( "insert into posibles (idpregunta, texto) values(%s, 'A')" % idpregunta) bd.Ejecuta( "insert into posibles (idpregunta, texto) values(%s, 'B')" % idpregunta) bd.Ejecuta( "insert into posibles (idpregunta, texto) values(%s, 'C')" % idpregunta) resp['id'] = idpregunta resp['preguntas'] = leePreguntas(idtexto, bd) bd.cierra() return resp
def get_count_domain(): bd = DB() data = bd.Ejecuta( "SELECT idDomain,COUNT(idDomain) AS c FROM consolidatedpagescrawl GROUP BY idDomain" ) bd.cierra() return data
def get_terms_for_vol(): bd = DB() querys = bd.Ejecuta( "SELECT id, term,googleState FROM terms WHERE googleState = 1 LIMIT 0, 900" ) bd.cierra() return querys
def get_page_data(idPositions): bd = DB() data = bd.Ejecuta( "select position, type, text from pagescrawl where idPositions=" + str(idPositions)) bd.cierra() return data
def save_page_optz(idPositions, urlDomain, optzUrl, optzTitle, optzH1, PA): bd = DB() bd.Ejecuta( """insert into consolidatedpagescrawl (idPositions, isHomePage, urlOptz, titleTagOptz, h1Optz, pageAuthority) values(%s,%s,%s,%s,%s,%s)""" % (idPositions, urlDomain, optzUrl, optzTitle, optzH1, PA)) bd.cierra()
def get_page_optz(): bd = DB() toDo = bd.Ejecuta( """SELECT positions.id AS idPositions, positions.term, positions.url FROM positions JOIN pagescrawltext ON positions.id = pagescrawltext.idPositions LEFT JOIN consolidatedpagescrawl ON consolidatedpagescrawl.idPositions=pagescrawltext.idPositions LEFT JOIN badurl ON positions.id = badurl.idPositions WHERE badurl.id IS NULL AND pagescrawltext.idPositions IS NOT NULL AND consolidatedpagescrawl.id IS NULL""") # done = bd.Ejecuta("SELECT idPositions FROM consolidatedPagescrawl GROUP BY idPositions") # badUrl = bd.Ejecuta("SELECT idPositions FROM badurl WHERE fix = 0 GROUP BY idPositions") # for a in done: # for b in toDo: # if a["idPositions"] == b["idPositions"]: # toDo.remove(b) # break # for a in badUrl: # for b in toDo: # if a["idPositions"] == b["idPositions"]: # toDo.remove(b) # break bd.cierra() if toDo: return toDo else: return []
def save_tags(tags): bd = DB() for a in tags: bd.Ejecuta( """insert into pagescrawl (idPositions, position, type, text) values(%s,%s,"%s","%s")""" % (a[0], a[1], a[2].encode("utf-8"), a[3].encode("utf-8"))) bd.cierra()
def LeeProductoP(idprov, idproducto): bd = DB(nombrebd="textos") tabla = "prod%s" % idprov rows = bd.Ejecuta("select * from %s where id='%s'" % (tabla, idproducto)) if rows: return rows[0] bd.cierra()
def get_count_positions(): bd = DB() data = bd.Ejecuta("""SELECT COUNT(positions.id) AS c FROM positions JOIN consolidatedpagescrawl ON positions.id = consolidatedpagescrawl.idPositions""" ) bd.cierra() return data[0]["c"]
def check_domain(domain): bd = DB() data = bd.Ejecuta("SELECT id FROM domains WHERE url='" + domain + "'") bd.cierra() if data: return data[0] else: return []
def save_suggested(idTerm, query, suggested): bd = DB() for a in suggested: bd.Ejecuta( """insert into suggesteds (idTerm,term, position, suggested, date) values(%s,"%s",%s,"%s", UTC_TIMESTAMP())""" % (idTerm, query.encode("utf-8"), a["position"], a["suggested"].encode("utf-8"))) bd.cierra()
def LeeUltTextoJ(num): bd = DB(nombrebd="textos") # rows = bd.Ejecuta("select * from docs where num=%s"%num) rows = bd.Ejecuta( "select parrafos.* from parrafos inner join docs on docs.id=parrafos.iddoc where num=%s" % num) bd.cierra() return rows
def querys_done(): bd = DB() querysDone = bd.Ejecuta("SELECT idTerm FROM positions GROUP BY idTerm") if querysDone: for a in querysDone: bd.Ejecuta("""update terms set state=%s where id = %s """ % (0, int(a["idTerm"]))) bd.cierra()
def Login(email, clave): bd = DB(nombrebd="textos") usuario = login(email, clave, bd) if usuario: bd.cierra() return usuario bd.cierra() return None
def save_positions(idTerm, query, positions): bd = DB() for a in positions: bd.Ejecuta( """insert into positions (idTerm, term, position, title, url, description, date) values(%s,"%s", %s,"%s","%s","%s", UTC_TIMESTAMP())""" % (idTerm, query.encode("utf-8", 'replace'), a["position"], a["title"].encode("utf-8", 'replace'), a["url"].encode("utf-8"), a["description"].encode("utf-8", 'replace'))) bd.cierra()
def querys_done2(): bd = DB() querysDone = bd.Ejecuta("select term from positions group by term") if querysDone: for a in querysDone: bd.Ejecuta( """update terms set term="%s", state=%s where term = "%s" """ % (a["term"].encode("utf-8"), 0, a["term"].encode("utf-8"))) bd.cierra()
def save_statistic(statistic): list = statistic.keys() bd = DB() for d in list: bd.Ejecuta( "REPLACE into statistic (idTerm,nightmare,hardcore,hard,medium,easy) values(%s,%s,%s,%s,%s,%s) " % (int(d), int(statistic[d]["nightmare"]), int(statistic[d]["hardcore"]), int(statistic[d]["hard"]), int(statistic[d]["medium"]), int(statistic[d]["easy"]))) bd.cierra()
def save_terms(data): bd = DB() for d in data: bd.Ejecuta("""INSERT INTO terms (term) SELECT * FROM (SELECT '%s') AS tmp WHERE NOT EXISTS ( SELECT term FROM terms WHERE term = '%s' ) LIMIT 1; """ % (d.encode("utf-8"), d.encode("utf-8"))) bd.cierra()
def get_url_for_domain(): bd = DB() data = bd.Ejecuta( """SELECT positions.id AS idPositions, positions.url, consolidatedpagescrawl.pageAuthority, positions.title FROM positions JOIN consolidatedpagescrawl ON consolidatedpagescrawl.idPositions = positions.id AND consolidatedpagescrawl.idDomain = 0""" ) bd.cierra() return data
def ModificaNivelA(email, clave, idtexto, nivel): bd = DB(nombrebd="aprende") usuario = login(email, clave, bd) if usuario: if duenoPagina(idtexto, bd) == usuario['ID']: bd.Ejecuta("update paginas set idnivel=%s where id=%s" % (nivel, idtexto)) bd.cierra() return None
def LeePaginasA(email, clave, idcuaderno): bd = DB(nombrebd="aprende") usuario = login(email, clave, bd) response = {} if usuario: if usuario['modo'] == 'E': response["paginas"] = bd.Ejecuta( "select *, id as ID, titulo as nombre from paginas where idcuaderno=%s order by nombre" % idcuaderno) bd.cierra() return response
def get_data_terms(): bd = DB() data = bd.Ejecuta( """SELECT positions.idTerm, (consolidatedpagescrawl.isHomePage + consolidatedpagescrawl.titleTagOptz + consolidatedpagescrawl.urlOptz + consolidatedpagescrawl.h1Optz + domains.authority) AS score FROM consolidatedpagescrawl JOIN domains ON domains.id = consolidatedpagescrawl.idDomain JOIN positions ON positions.id = consolidatedpagescrawl.idPositions""" ) bd.cierra() return data
def GrabaNodoRuta(email, ip, funcion, pag): bd = DB() usuario = bd.Ejecuta("select * from usuarios where email=%s" % email) if usuario: usuario = usuario[0] else: usuario = dict() usuario["ID"] = 0 bd.Ejecuta( "insert into rutas (IDusuario, ip, funcion, pag, fecha) values (%s,'%s','%s','%s',sysdate())" % (usuario["ID"], ip, funcion[:255], pag)) bd.cierra()
def CreaUsuarioA(email, clave, nombre, modo, lang): bd = DB(nombrebd="aprende") rows = bd.Ejecuta("select * from usuarios where email='%s'" % email) resp = "x" if not rows: bd.Ejecuta( "insert into usuarios (nombre, email, clave, modo, lang) values ('%s','%s','%s','%s','%s')" % (nombre, email, clave, modo, lang)) resp = "ok" bd.cierra() return resp
def LeePaginaA(email, clave, idtexto): bd = DB(nombrebd="aprende") usuario = login(email, clave, bd) if usuario: response = {} # response['usuario'] = usuario response["texto"] = bd.Ejecuta("select * from paginas where id=%s" % (idtexto))[0] response['preguntas'] = leePreguntas(idtexto, bd) bd.cierra() return response bd.cierra() return None
def ModificaTituloA(email, clave, idtexto, titulo): bd = DB(nombrebd="aprende") usuario = login(email, clave, bd) resp = None if usuario: if duenoPagina(idtexto, bd) == usuario['ID']: bd.Ejecuta("update paginas set titulo='%s' where id=%s" % (titulo, idtexto)) resp = bd.Ejecuta( "select *, id as ID, titulo as nombre from paginas where idusuario=%s" % usuario['ID']) bd.cierra() return resp
def EliminaPosibleA(email, clave, idposible): bd = DB(nombrebd="aprende") resp = None usuario = login(email, clave, bd) if usuario: idtexto, idpregunta = idtexto_idpregunta(idposible, bd) if idtexto: if duenoPregunta(idpregunta, bd) == usuario['ID']: bd.Ejecuta("delete from posibles where id=%s" % idposible) resp = leePreguntas(idtexto, bd) bd.cierra() return resp
def CambiaCampoP(email, clave, datos): # print("llega SubeArchivoP", datos['texto']) bd = DB(nombrebd="textos") if datos['tabla'] == 'prov': usuario = login(email, clave, bd) if usuario: bd.Ejecuta("update prov set %s='%s' where id=%s" % (datos['nombre'], datos['val'], usuario['id'])) elif datos['tabla'] == 'cli': bd.Ejecuta("update cli set %s='%s' where telefono='%s'" % (datos['nombre'], datos['val'], datos['telefono'])) bd.cierra()
def DespacharP(email, clave, idtextosdo): bd = DB(nombrebd="textos") usuario = login(email, clave, bd) if usuario: bd.Ejecuta("update pedicab set pendiente=0 where id=%s" % idpedido) usuario["pendientes"] = bd.Ejecuta(""" select *, telefono, direccion, pedicab.id as ID, 'X' as despachar from pedicab inner join cli on cli.id=pedicab.idcli where idprov=%s and pendiente=1 """ % usuario['id']) bd.cierra() return usuario bd.cierra() return None