def buscar_scripts(versao_id, banco, tipo_script=None): try: cur = conn.cursor() if tipo_script: cur.execute( "SELECT t.DESCRICAO TIPOSCRIPT, s.DESCRICAO, s.CODIGO, s.SKIP, " + "s.TIPOBANCO, s.OLDCODIGO, s.BANCO " + "from SCRIPT s inner join TIPOSCRIPT t on s.TIPOSCRIPTOID = t.TIPOSCRIPTOID " + "where s.VERSAOOID = %s::bigint " + "and s.TIPOBANCO = %s " + "and t.DESCRICAO = %s " + "order by t.ORDEM", (versao_id, banco, tipo_script)) else: cur.execute( "SELECT t.DESCRICAO TIPOSCRIPT, s.DESCRICAO, s.CODIGO, s.SKIP, " + "s.TIPOBANCO, s.OLDCODIGO, s.BANCO " + "from SCRIPT s inner join TIPOSCRIPT t on s.TIPOSCRIPTOID = t.TIPOSCRIPTOID " + "where s.VERSAOOID = %s::bigint " + "and s.TIPOBANCO = %s " + "order by t.ORDEM", (versao_id, banco)) scripts_data = cur.fetchall() cur.close() scripts = [] for script_data in scripts_data: script = montar_script(script_data) scripts.append(script) return scripts except (Exception, psycopg2.DatabaseError) as error: logging.error(error) return None
def load(proj_n): # function to load a project global current c = conn.cursor() c.execute("SELECT * FROM project;") rows = c.fetchall() current = rows[int(proj_n)] print_logo() print("load project") print("------------------------------------------") print("project: " + current[1] + " loaded") print("") print("options: ") print("") print("0 : scan all") print("1 : resume") print("2 : nmap only") print("3 : create report") print("------------------------------------------") i_nr = input("option: ") # choose one option if i_nr == "0": # scan all nmap_hack.run_nmap() scan.scan() ########### elif i_nr == "1": # resume scan.scan() elif i_nr == "2": # nmap only nmap_hack.run_nmap() elif i_nr == "3": # create report report.create(current[1]) elif (i_nr != "0" or "1" or "2" or "3"): print("enter a valid value!")
def ustats(): cur = conn.cursor() if not request.args or not "username" in request.args: cur.execute("SELECT page_title, summary, score FROM edits WHERE username=(SELECT wp_username from users where uid=%s)", [str(session["uid"])]) else: cur.execute("SELECT page_title, summary, score FROM edits WHERE username=(SELECT wp_username from users where username=%s)", [request.args["username"]]) edit = cur.fetchall() edits = [{'page': i[0], 'summary': i[1], 'score': i[2]} for i in edit] ssum = sum([i["score"] for i in edits]) cur.execute("SELECT username FROM users") users = cur.fetchall() rusers = [] for i in users: rusers.append(str(i)[2:len(str(i)) - 3]) if "username" in request.args: if request.args["username"] not in rusers: flash("This user doesn't exist.", "danger") return str(rusers) #return redirect("/userstats") elif len(edits) == 0: flash("This user doesn't have any edits.", "warning") return redirect("/userstats") return render_template("userstats.html", thispage='userstats', edits=edits, uname=(session["username"] if not "username" in request.args else request.args["username"]))
def delete(name): #This function should return a value indicating success of failure sql = "DELETE FROM project WHERE name='" + name + "';" c = conn.cursor() c.execute(sql) conn.commit() shutil.rmtree("./projects/" + name + "/")
def get_user_achievements(user): cur = conn.cursor() cur.execute("SELECT uid FROM users WHERE username=%s", (user,)) uid = str(cur.fetchone()[0]) cur.execute("SELECT name FROM achievements WHERE uid=%s", (uid,)) achievements = [i[0] for i in cur.fetchall()] return achievements
def create_account(username, password, email, wp_username): """ Create an account given a username/password combination. """ cur = conn.cursor() cur.execute("SELECT username, email FROM users") fall = cur.fetchall() usernames = [matching[0] for matching in fall] emails = [matching[1] for matching in fall] if username in usernames or len(username) < 4 or len(email) < 7 or email in emails: return False registration_uuid = uuid.uuid1() emsg = PMMail(api_key = os.environ.get('POSTMARK_API_KEY'), subject = "Welcome to wedge!", sender = "*****@*****.**", to = email, text_body = """Hello! Welcome to wedge, the Wikipedia game. You can get going immediately; wedge will track all your Wikipedia edits and automatically classify them. Good luck, and edit well! In order to use your account, please click this link to verify your account: http://wedge.csssuf.net/verifyemail?key=%s -- The wedge team""" % (registration_uuid), tag = "welcome") emsg.send() passwd_hash = hashlib.sha256(password).hexdigest() cur.execute("INSERT INTO users (username, passwd_hash, email, wp_username, enabled, reguuid) VALUES (%s,%s,%s,%s,%s,%s)", (username, passwd_hash, email, wp_username, False, registration_uuid.hex)) conn.commit() cur.close() return True
def mm_createProj(): #This is horrible practice you should create a submenu for this instead and this dialogue should be a project menu or whatever i_abfrage = "c" # so that the program can enter the while loop once. while (i_abfrage.lower() == "c"): # to create another project print_logo() print("create project") print("------------------------------------------") i_proj = input("project name: ") i_hosts = input("hosts (ip): ") i_prot = input("TCP (0) or UDP (1): ") i_port = input("ports: ") i_http_s = input("http (0) or https (1): ") print("") project.create(i_proj, i_hosts, i_port, i_prot, i_http_s) print_logo() print("create a project") print("------------------------------------------") print("project: " + i_proj + " successfully create") print("") print("press 'r' to return menu:") # open the complete py script print("press 'l' to load the project:") print("press 'c' to create another project:") # while i_abfrage = c i_abfrage = input() if (i_abfrage.lower() == "r" ): # after creating a project, the py script starts against return elif (i_abfrage.lower() == "l"): c = conn.cursor() c.execute("SELECT COUNT(*) FROM project;") project.load(str(int(c.fetchone()[0]) - 1)) # load project created above
def buscar_versao(versao): try: valores_versao = versao.split('.') major = valores_versao[0] minor = valores_versao[1] banco = valores_versao[2] except (Exception, ) as error: return None try: cur = conn.cursor() cur.execute( "SELECT VERSAOOID, MAJOR, MINOR, BANCO, DATA, DESENVOLVIMENTO, PRODUCAO, HABILITADA " + "from VERSAO v " + "where v.MAJOR = %s::bigint " + "and v.MINOR = %s::bigint " + "and v.BANCO = %s::bigint", (major, minor, banco)) versao_data = cur.fetchone() cur.close() if versao_data: return montar_versao(versao_data) except (Exception, psycopg2.DatabaseError) as error: logging.error(error) return None
def ach(wpusername): ach.name = name cur = conn.cursor() cur.execute("SELECT sum(score) FROM edits WHERE username=%s", (wpusername,)) row = cur.fetchone() cur.execute("SELECT 1 FROM achievements WHERE \ uid=(SELECT uid FROM users WHERE wp_username=%s) AND name=%s", (wpusername, ach.name)) return row[0] > score and cur.fetchone() is None
def check(f, wpusername): result = f(wpusername) if result: name = f.name query = "INSERT INTO achievements (uid, name) VALUES ((SELECT uid FROM users WHERE wp_username=%s), %s)" cur = conn.cursor() cur.execute(query, (wpusername, name)) conn.commit()
def first_edit(wpusername): first_edit.name = "Your First Edit" achievement_metadata[first_edit.name] = "Awarded for your first edit after signing up for wedge" query = "SELECT score FROM edits WHERE username=%s" cur = conn.cursor() cur.execute(query, [wpusername]) meetsreqs = (len(cur.fetchall()) == 1) cur.close() return meetsreqs
def root(): ret = ["<html><body><table>"] cur = conn.cursor() cur.execute("SELECT * FROM images LIMIT 50") for name, data, track in cur.fetchall(): img = base64.b64encode(open(os.path.join(DATA_PATH, name)).read()) print name ret.append('<tr><td><img src="data:image/png;base64,'+img+'" /></td><td><img src="'+data+'" /></td></tr>') return ''.join(ret)
def root(): ret = ["<html><body><table>"] cur = conn.cursor() cur.execute("SELECT * FROM images LIMIT 50") for name, data, track in cur.fetchall(): img = base64.b64encode(open(os.path.join(DATA_PATH, name)).read()) print name ret.append('<tr><td><img src="data:image/png;base64,' + img + '" /></td><td><img src="' + data + '" /></td></tr>') return ''.join(ret)
def get_edits(user=None): cur = conn.cursor() if user is None: cur.execute("SELECT score FROM edits") edits = cur.fetchall() else: cur.execute("SELECT score FROM edits WHERE username=(SELECT wp_username from users where uid=%s)", [str(user)]) edits = cur.fetchall() cur.close() return edits
def getusers(): cur = conn.cursor() cur.execute("SELECT username, wp_username FROM users WHERE wp_username LIKE %(search)s\ or username LIKE %(search)s LIMIT 10 ", {"search":request.form['search'] + "%"}) unames = [] for r in cur.fetchall(): unames.append({"title":r[0], "url":"/userstats/?username=%s" % r[0], "text":r[1]}) resp = {} resp['results'] = unames return json.dumps(resp)
def parse_kill(row): victim = row.find('victim') attackers, items = row.findall('rowset') if attackers.get('name') != 'attackers' or items.get('name') != 'items': raise RuntimeError('unexpected rowsets on kill ' + row.get('killID')) m_kill = models.Kill(**row.attrib) try: m_kill.save() except oursql.IntegrityError as e: if e.errno != oursql.errnos['ER_DUP_ENTRY']: raise return m_victim = models.Character(killID=m_kill.killID, victim=True, weaponTypeID=None, finalBlow=None, securityStatus=None, damageDone=None, **victim.attrib) m_victim.save() for a in attackers: m_attacker = models.Character(killID=m_kill.killID, victim=False, damageTaken=None, **a.attrib) m_attacker.save() for i in items: m_item = models.Item(killID=m_kill.killID, **i.attrib) m_item.save() with conn.cursor() as c: c.execute('SELECT cost from pkItemCosts WHERE typeID = ?', (m_victim.shipTypeID, )) r = c.fetchone() if r: cost = r[0] c.nextset() else: cost = 0 c.execute( ''' SELECT SUM(cost * (qtyDropped + qtyDestroyed)) FROM pkItems AS i JOIN pkItemCosts AS ic ON i.typeID = ic.typeID WHERE killID = ? ''', (m_kill.killID, )) r = c.fetchone() if r[0]: cost += r[0] m_killcost = models.KillCost(killID=m_kill.killID, cost=cost) m_killcost.save() return m_kill.killID
def get_vehicle_locations(): cur = conn.cursor() cur.execute( "SELECT ts, ST_AsText(geom) FROM muni_locations WHERE vehicle_id=1486 ORDER BY ts ASC" ) rows = cur.fetchall() return [{ "timestamp": row[0], "geom": wkt.loads(row[1]), "lat": wkt.loads(row[1]).coords[0][0], "lon": wkt.loads(row[1]).coords[0][1], } for row in rows]
def inserir_versao(versao): try: cur = conn.cursor() cur.execute( "insert into VERSAO (MAJOR, MINOR, BANCO, DATA, DESENVOLVIMENTO, " + "PRODUCAO, HABILITADA) " + "values (%s::bigint, %s::bigint, %s::bigint, %s, %s, " + "%s, %s)", (versao.major, versao.minor, versao.banco, versao.data, versao.dev, versao.prod, versao.habilitada)) cur.close() conn.commit() return "Ok" except (Exception, psycopg2.DatabaseError) as error: logging.error(error) return str(error)
def parse_kill(row): victim = row.find('victim') attackers, items = row.findall('rowset') if attackers.get('name') != 'attackers' or items.get('name') != 'items': raise RuntimeError('unexpected rowsets on kill ' + row.get('killID')) m_kill = models.Kill(**row.attrib) try: m_kill.save() except oursql.IntegrityError as e: if e.errno != oursql.errnos['ER_DUP_ENTRY']: raise return m_victim = models.Character( killID=m_kill.killID, victim=True, weaponTypeID=None, finalBlow=None, securityStatus=None, damageDone=None, **victim.attrib ) m_victim.save() for a in attackers: m_attacker = models.Character(killID=m_kill.killID, victim=False, damageTaken=None, **a.attrib) m_attacker.save() for i in items: m_item = models.Item(killID=m_kill.killID, **i.attrib) m_item.save() with conn.cursor() as c: c.execute('SELECT cost from pkItemCosts WHERE typeID = ?', (m_victim.shipTypeID,)) r = c.fetchone() if r: cost = r[0] c.nextset() else: cost = 0 c.execute(''' SELECT SUM(cost * (qtyDropped + qtyDestroyed)) FROM pkItems AS i JOIN pkItemCosts AS ic ON i.typeID = ic.typeID WHERE killID = ? ''', (m_kill.killID,)) r = c.fetchone() if r[0]: cost += r[0] m_killcost = models.KillCost(killID=m_kill.killID, cost=cost) m_killcost.save() return m_kill.killID
def buscar_dev(login): try: cur = conn.cursor() cur.execute( "SELECT DEVOID, NOME, ID, SENHA, HABILITADO " + "from DEV " + "where ID = %s", (login, )) dev_data = cur.fetchone() cur.close() if dev_data: return montar_dev(dev_data) except (Exception, psycopg2.DatabaseError) as error: logging.error(error) return None
def buscar_tipo_script_by_desc(descricao): try: cur = conn.cursor() cur.execute( "SELECT TIPOSCRIPTOID, ORDEM, DESCRICAO " + "from TIPOSCRIPT " + "where DESCRICAO = %s ", (descricao, )) tipo_script_data = cur.fetchone() cur.close() if tipo_script_data: return montar_tipo_script(tipo_script_data) except (Exception, psycopg2.DatabaseError) as error: logging.error(error) return None
def verifye(): if not request.args or not "key" in request.args: return redirect("/") cur = conn.cursor() cur.execute("SELECT reguuid, username, enabled FROM users where reguuid::text=%s", (request.args["key"],)) results = cur.fetchone() if not results: flash("UUID not found in database; if you came here from a registration email, please check that you have entered the URL correctly. %s" % (request.args["key"]), "danger") return redirect("/") elif results[2] == 1: flash("Account already enabled.", "warning") return redirect("/") cur.execute("UPDATE users SET enabled=%s WHERE reguuid=%s", (True, results[0])) conn.commit() cur.close() flash("%s, your account has been successfully activated. Please log in." % (results[1]), "success") return redirect("/login")
def inserir_script(script): try: cur = conn.cursor() cur.execute( "insert into SCRIPT (VERSAOOID, TIPOSCRIPTOID, DESCRICAO, CODIGO, SKIP, " + "TIPOBANCO, OLDCODIGO, ORDEM, BANCO) " + "values (%s::bigint, %s::bigint, %s, %s, %s, " + "%s, %s, %s::bigint, %s)", (script.versao_id, script.tipo_script_id, script.descricao, script.codigo, script.skip, script.tipobanco, script.oldcodigo, script.ordem, script.banco)) cur.close() conn.commit() return "Ok" except (Exception, psycopg2.DatabaseError) as error: logging.error(error) return str(error)
def mm_delProj(): print_logo() print("delete project") print("------------------------------------------") print("which project would you like to delete?") print("") i = 0 c = conn.cursor() c.execute("SELECT * FROM project ORDER BY id") rows = c.fetchall() for row in rows: print(str(i) + " : " + row[1]) i = i + 1 i_nr = input("projectname: ") # choose a projekt to delete project.delete(rows[i_nr])
def is_valid_login(username, password): """ Checks if a given username/password combination exists. If it does, returns a two-tuple (username, user id). If it's not, return False. """ cur = conn.cursor() passwd_hash = hashlib.sha256(password).hexdigest() cur.execute("SELECT uid, enabled FROM users where username=%s and passwd_hash=%s", (username, passwd_hash)) matching = cur.fetchone() cur.close() if matching is not None: if not matching[1]: flash("Your account is locked, or you have not completed email verification. Please contact an administrator.", "danger") return False return (username, matching[0]) return False
def process_diff(diffiduser): try: diffid, user = diffiduser except: return cur = conn.cursor() cur.execute("SELECT wp_username FROM users") rusers = [r[0] for r in cur.fetchall()] if user not in rusers: return diff = get_diff_for_diffid(diffid) zum = 0 for f in score_funcs: zum += f(diff[0], diff[1]) cur.execute("INSERT INTO edits (username, added, deled, score, page_title, summary) VALUES (%s, %s, %s, %s, %s, %s)", (user, diff[0], diff[1], zum, diff[2], diff[3])) conn.commit() cur.close() achievements.check_all_achievements(user)
def mm_loadProj(): print_logo() print("load project") print("------------------------------------------") print("which project would you like to load?") print("") i = 0 c = conn.cursor() c.execute("SELECT * FROM project") rows = c.fetchall() for row in rows: print(str(i) + " : " + row[1]) i = i + 1 print("") i_proj = input("projectname: ") project.load(i_proj)
def fetch_type_ids(): with conn.cursor() as c: if len(sys.argv) > 1 and sys.argv[1] in ['-q', '--quick']: c.execute(''' SELECT i.typeID FROM pkItems AS i JOIN invTypes AS t ON i.typeID = t.typeID WHERE marketGroupID is NOT NULL UNION SELECT shipTypeID FROM pkCharacters JOIN invTypes ON shipTypeID = typeID WHERE victim AND marketGroupID is NOT NULL ''') else: c.execute('SELECT typeID FROM invTypes WHERE marketGroupID IS NOT NULL') while True: r = c.fetchone() if not r: break yield r[0]
def inserir_item_versao(item_versao): try: cur = conn.cursor() cur.execute( "insert into ITENSVERSAO (VERSAOOID, DEVOID, TICKETOID, PROJECTOID, MODULO, " + "APLICACAO, RELEASE, DESCRICAO, TESTE, DATA) " + "values (%s::bigint, %s::bigint, %s, %s, %s, " + "%s, %s::bigint, %s, %s, %s)", (item_versao.versao_id, item_versao.dev_id, item_versao.ticket, item_versao.projeto, item_versao.modulo, item_versao.aplicacao, item_versao.release, item_versao.descricao, item_versao.teste, item_versao.data)) cur.close() conn.commit() return "Ok" except (Exception, psycopg2.DatabaseError) as error: logging.error(error) return str(error)
def run_nmap(): # function to run nmap scan c = conn.cursor() return #TODO: Remove this, only here because the standard nmap package doesn't seem to implement this functionality nm = nmap.PortScanner() # init #Hardcode this or employ proper configuration for stuff nm.scan(hosts=project.current[2], ports=project.current[3], arguments=settings[int(project.current[4])]) iternmcsv = iter(nm.csv().splitlines()) next(iternmcsv) for row in iternmcsv: if(row.split(';')[6] == "open"): cmd = "INSERT INTO r_nmap VALUES (NULL, " + str(project.current[0]) + ", datetime('now', 'localtime')" for entr in row.split(';'): cmd += ", '" + entr + "'" cmd += ", 0);" c.execute(cmd) conn.commit()
def fetch(cls, kill_id): with conn.cursor() as c: c.execute( ''' SELECT i.typeID as typeID, typeName, categoryID, flag, qtyDropped, qtyDestroyed, singleton, ic.cost FROM pkItems as i JOIN invTypes AS t ON i.typeID = t.typeID JOIN pkItemCosts AS ic ON i.typeID = ic.typeID JOIN invGroups AS g ON t.groupID = g.groupID WHERE i.killID = ? ORDER BY flag DESC, qtyDropped + qtyDestroyed ASC ''', (kill_id, )) while True: attribs = objectify(c) if attribs is None: break yield attribs
def fetch_type_ids(): with conn.cursor() as c: if len(sys.argv) > 1 and sys.argv[1] in ['-q', '--quick']: c.execute(''' SELECT i.typeID FROM pkItems AS i JOIN invTypes AS t ON i.typeID = t.typeID WHERE marketGroupID is NOT NULL UNION SELECT shipTypeID FROM pkCharacters JOIN invTypes ON shipTypeID = typeID WHERE victim AND marketGroupID is NOT NULL ''') else: c.execute( 'SELECT typeID FROM invTypes WHERE marketGroupID IS NOT NULL') while True: r = c.fetchone() if not r: break yield r[0]
def fetch(cls, kill_id): with conn.cursor() as c: c.execute( ''' SELECT killTime, characterID, characterName, corporationID, corporationName, allianceID, allianceName, t.typeID as shipTypeID, typeName as shipTypeName, groupName, ic.cost as shipCost, damageTaken, s.solarSystemName as systemName, s.security as systemSecurity FROM pkKillmails AS k JOIN pkCharacters AS c ON k.killID = c.killID and c.victim = true JOIN invTypes AS t ON c.shipTypeID = t.typeID LEFT JOIN pkItemCosts AS ic ON c.shipTypeID = ic.typeID JOIN invGroups as g ON t.groupID = g.groupID JOIN mapSolarSystems as s ON k.solarSystemID = s.solarSystemID WHERE k.killID = ? ''', (kill_id, )) return objectify(c)
def fetch_list(cls, offset, count): with conn.cursor() as c: c.execute( ''' SELECT k.killID, killTime, characterName, corporationID, corporationName, allianceID, allianceName, shipTypeID, typeName as shipTypeName, cost FROM pkKillmails AS k JOIN pkCharacters AS c ON k.killID = c.killID and c.victim = true JOIN invTypes AS t ON c.shipTypeID = t.typeID JOIN pkKillCosts AS kc ON k.killID = kc.killID ORDER BY killTime DESC LIMIT ?, ? ''', (offset, count)) while True: attribs = objectify(c) if attribs is None: break yield attribs
def load_muni_locations(): files = os.listdir("../munipings") for f in files: timestamp, _ = f.split(".") content = open("../munipings/" + f, 'r').read() try: parsed_data = parse_muni_data(content) for vehicle in parsed_data: cur = conn.cursor() cur.execute( "INSERT INTO muni_locations VALUES (%i, to_timestamp(%i), '%s', '%s', ST_GeographyFromText('SRID=4326;POINT(%f %f)'), %i, %i, %i)" % (int(vehicle.vehicle_id), int(timestamp), vehicle.route_tag, vehicle.direction_tag, float(vehicle.lon), float(vehicle.lat), int(vehicle.heading), int( vehicle.speed), int(vehicle.secs_since_report))) conn.commit() except Exception as e: print(e) print("Broken file: %s" % timestamp)
def fetch_attackers(cls, kill_id): with conn.cursor() as c: c.execute( ''' SELECT characterID, characterName, finalBlow, corporationID, corporationName, allianceID, allianceName, damageDone, securityStatus, shipTypeID, t1.typeName as shipTypeName, weaponTypeID, t2.typeName as weaponTypeName FROM pkCharacters AS c JOIN invTypes AS t1 ON c.shipTypeID = t1.typeID JOIN invTypes AS t2 ON c.weaponTypeID = t2.typeID WHERE c.killID = ? AND c.victim = false ORDER BY c.finalBlow DESC, c.damageDone DESC ''', (kill_id, )) while True: attribs = objectify(c) if attribs is None: break yield attribs
def update_kill(kill_id): with conn.cursor() as c: c.execute(''' SELECT cost from pkCharacters as c JOIN pkItemCosts AS ic ON c.shipTypeID = ic.typeID WHERE killId = ? AND victim ''', (kill_id,)) r = c.fetchone() if r: cost = r[0] c.nextset() else: cost = 0 c.execute(''' SELECT SUM(cost * (qtyDropped + qtyDestroyed)) FROM pkItems AS i JOIN pkItemCosts AS ic ON i.typeID = ic.typeID WHERE killID = ? ''', (kill_id,)) r = c.fetchone() if r[0]: cost += r[0] c.execute('UPDATE pkKillCosts SET cost = ? WHERE killID = ?', (cost, kill_id))
def buscar_itens_versao_by_versao_id(versao_id): try: cur = conn.cursor() cur.execute( "SELECT i.VERSAOOID, i.DEVOID, d.NOME, i.TICKETOID, i.PROJECTOID, i.MODULO, i.APLICACAO, " + "i.RELEASE, i.DESCRICAO, i.TESTE, i.DATA" + "from ITENSVERSAO i inner join DEV d on i.DEVOID = d.DEVOID " + "where i.VERSAOOID = %s::bigint", (versao_id, )) itens_versao_data = cur.fetchall() cur.close() itens_versao = [] for item_versao_data in itens_versao_data: item_versao = montar_item_versao(item_versao_data) itens_versao.append(item_versao) return itens_versao except (Exception, psycopg2.DatabaseError) as error: logging.error(error) return None
def process_results(queue, args): conn = psycopg2.connect(**db_args) cur = conn.cursor() while True: item = queue.get() if item is None: return boxes, meta, VERSION = item (roff, coff, filename, valid_geom, done, height, width, img_geom) = meta img_geom = shape(img_geom) bounds = img_geom.bounds ref_point = (bounds[3], bounds[0]) # top left corner for _, r in boxes.iterrows(): minx, miny = raster_to_proj(r.x1 + coff, r.y1 + roff, img_geom, ref_point) maxx, maxy = raster_to_proj(r.x2 + coff, r.y2 + roff, img_geom, ref_point) building = box(minx, miny, maxx, maxy) cur.execute( """ INSERT INTO buildings.buildings (filename, minx, miny, maxx, maxy, roff, coff, score, project, ts, version, geom) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s::uuid, ST_GeomFromText(%s, 4326)) """, (filename, int(r.x1), int(r.y1), int(r.x2), int(r.y2), roff, coff, r.score, args.country, args.ts, VERSION, building.wkt)) if done: cur.execute( "UPDATE buildings.images SET last_tested=%s WHERE project=%s AND filename=%s", (args.ts, args.country, filename)) conn.commit() print('Committed image: %s' % filename)
def update_kill(kill_id): with conn.cursor() as c: c.execute( ''' SELECT cost from pkCharacters as c JOIN pkItemCosts AS ic ON c.shipTypeID = ic.typeID WHERE killId = ? AND victim ''', (kill_id, )) r = c.fetchone() if r: cost = r[0] c.nextset() else: cost = 0 c.execute( ''' SELECT SUM(cost * (qtyDropped + qtyDestroyed)) FROM pkItems AS i JOIN pkItemCosts AS ic ON i.typeID = ic.typeID WHERE killID = ? ''', (kill_id, )) r = c.fetchone() if r[0]: cost += r[0] c.execute('UPDATE pkKillCosts SET cost = ? WHERE killID = ?', (cost, kill_id))
) #Отправляем гет запрос регарду на получение информации с помощью реквеста soup = BeautifulSoup( r.text, 'lxml' ) #BeautifulSoup - это обертка для парсинга, он принимает два значения: 1)html код парсинга 2)тип парсера который следует использовать lxml - Python биндинг популярнойи и очень быстрой С библиотеки collect = soup.find_all( 'a', class_='link_gtm-js link_pageevents-js ddl_product_link') #product_links = (tag.get('href') for tag in collect.find('a'))#Берем ссылки классов тегов, как бы обошли сам тег а и взли только его ссылки for link in collect: linka = link.get('href') print(linka) get_product_info(linka) #вызываем функцию try: if data['price'] == -1: continue cur = conn.cursor() # создаём объект для работы с запросами o = int( data['price'].replace(" ", "") ) # на сайте цена в виде строки мы приводим её к числовому типу и удаляем пробелы из неё cur.execute( sql, (data['link'], data['name'], o) ) # преобразуем строку, попутно подставляя в неё переменные в запрос conn.commit( ) #отправляем запрос в базу данных, для изменения данных time.sleep(20) except MySQLdb.Error as err: print("Query error: {}".format( err)) #выводит ошибку если она есть conn.rollback() #отменяет изменения cur.close() #закрывает курсор conn.close() #закрывает коннект
r = c.fetchone() if r: cost = r[0] c.nextset() else: cost = 0 c.execute(''' SELECT SUM(cost * (qtyDropped + qtyDestroyed)) FROM pkItems AS i JOIN pkItemCosts AS ic ON i.typeID = ic.typeID WHERE killID = ? ''', (kill_id,)) r = c.fetchone() if r[0]: cost += r[0] c.execute('UPDATE pkKillCosts SET cost = ? WHERE killID = ?', (cost, kill_id)) parambatch = [] for type_id in fetch_type_ids(): value = query(type_id) parambatch.append((type_id, value, value)) with conn.cursor() as c: c.executemany(''' INSERT INTO pkItemCosts (typeID, cost) VALUES(?, ?) ON DUPLICATE KEY UPDATE cost = ? ''', parambatch) c.execute('SELECT killID from pkKillmails') while True: r = c.fetchone() if r is None: break update_kill(r[0])
#!/usr/bin/env python from db import conn import os, sys, json, base64 TABLE = os.getenv("TABLE", 'data') assert TABLE == "data" or TABLE == "suggestions" if __name__ == "__main__": for f in sys.argv[1:]: bn = os.path.basename(f) print "uploading", f, "as", bn dat = "data:image/png;base64,"+base64.b64encode(open(f).read()) cur = conn.cursor() cur.execute("INSERT into "+TABLE+" (name, data) VALUES (%s, %s)", (bn, dat)) conn.commit() cur.close()
def index(): cur = conn.cursor() cur.execute("SELECT username, sum(score) FROM edits GROUP BY username ORDER BY sum(score) DESC LIMIT 10") users = cur.fetchall() return render_template("index.html", thispage='home', lb=users)
import sys import pandas as pd from db import conn if __name__ == "__main__": if len(sys.argv) > 1: location_name = sys.argv[1] print("Now get {0} data from db".format(location_name)) data = pd.DataFrame(columns=['date', 'price']) # fetch from db with conn.cursor() as cursor: sql = 'select * from egg_price where location_name="{0}";'.format( location_name) cursor.execute(sql) result = cursor.fetchall() for row in result: newrow = pd.DataFrame({ 'date': row[1], 'price': row[3] }, index=[1]) data = data.append(newrow, ignore_index=True) conn.commit() data.to_csv("data.csv", index=0) print("Save {0} data to data.csv".format(location_name)) else: print("No location name assigned.")
def create(name, hosts, ports, prots, http_s): # function to create a project c = conn.cursor() c.execute("INSERT INTO project VALUES (NULL, '" + name + "', '" + hosts + "', '" + ports + "', '" + prots + "', '" + http_s + "');") conn.commit() os.makedirs("./projects/" + name)
def crwal_main(): start_ts = get_timestamp_s() try: with conn.cursor() as cursor: # get last task id try: sql = 'select * from crwal_task order by id desc limit 1;' cursor.execute(sql) result = cursor.fetchone() last_task_id = result[0] conn.commit() except Exception as e: conn.rollback() raise e task_id = last_task_id + 1 # get last url id try: sql = 'select * from egg_price order by url_id desc limit 1;' cursor.execute(sql) result = cursor.fetchone() last_url_id = result[0] conn.commit() except Exception as e: conn.rollback() raise e # insert task into db sql = 'insert into crwal_task values ({0},{1},{2},{3},"crwaling","")'.format( task_id, start_ts, 0, last_url_id, task_id) try: cursor.execute(sql) conn.commit() except Exception as e: conn.rollback() raise e print("Crwal task " + str(task_id) + " start at " + get_time_str(start_ts)) print("Last url id is " + str(last_url_id)) # try crwal url_id = last_url_id + 1 err_count = 0 while True: # exit if err_count > 10: print( "More than 10 errors occurr continuously, end crwal task " + str(task_id)) break # make soup and find string html = urlopen("http://www.cnjidan.com/jiage/" + str(url_id)).read().decode("GBK") soup = BeautifulSoup(html, features="lxml") string = soup.find('div', {"class": "sner"}) if string: string = string.get_text() else: print("Fail to obtain data from url_id " + str(url_id) + ", ec: " + str(err_count)) err_count += 1 time.sleep(1) continue # pick date location price from string date = re.findall(r"据鸡蛋价格网统计,(\d{4})年(\d{2})月(\d{2})日", string) location = re.findall(r"据鸡蛋价格网统计,\d{4}年\d{2}月\d{2}日(.+)鸡蛋价格", string) price = re.findall( r"据鸡蛋价格网统计,\d{4}年\d{2}月\d{2}日.+鸡蛋价格为:(\d+.?\d+)元/公斤", string) # try pick try: date_str = '{0}-{1}-{2}'.format(date[0][0], date[0][1], date[0][2]) location_str = location[0] price_str = price[0] except Exception as e: print(e) print("Fail to obtain data from url_id " + str(url_id) + ", ec: " + str(err_count)) err_count += 1 time.sleep(1) continue # insert price into db try: sql = 'insert into egg_price values ({0},"{1}","{2}",{3},{4})'.format( url_id, date_str, location_str, price_str, task_id) cursor.execute(sql) conn.commit() except Exception as e: conn.rollback() raise e print("Successfully added data " + date_str + " " + location_str + " " + price_str + " from url_id " + str(url_id)) url_id += 1 err_count = 0 time.sleep(0.1) # crwal end end_ts = get_timestamp_s() try: sql = 'update crwal_task set end_time = {0}, status = "finished" where id = {1}'.format( end_ts, task_id) cursor.execute(sql) conn.commit() except Exception as e: conn.rollback() raise e print("Crwal task " + str(task_id) + " end at " + get_time_str(end_ts)) except Exception as e: print(e) end_ts = get_timestamp_s() print("Crwal task " + str(task_id) + " end at " + get_time_str(end_ts))