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