コード例 #1
0
def select_all(table):
    from db import conn, cur
    sql = "select * from " + table
    cur.execute(sql)
    conn.commit()
    lines = cur.fetchall()
    return lines
コード例 #2
0
ファイル: userinfo.py プロジェクト: tjcsl/wedge
def uinfo():
    if request.method == "POST":
        form = request.form
        username = session["username"]
        if "email" in form:
            curr.execute("UPDATE users SET email=%s WHERE username=%s", [form["email"],username])
        else:
            if not "newpassword" in form or not "oldpassword" in form or not "password2" in form:
                flash("Please fill in all three entries to change your password", "danger")
                return render_template("userinfo.html", thispage='userinfo', username=session["username"])
            if form["newpassword"] != form["password2"]:
                flash("Your new and confirmation passwords do not match", "danger")
                return render_template("userinfo.html", thispage='userinfo', username=session["username"])
            curr.execute("SELECT * FROM users WHERE username=%s AND passwd_hash=%s", [username, hashlib.sha256(form["oldpassword"]).hexdigest()])
            if not curr.fetchone():
                flash("Your old password is incorrect", "danger")
                return render_template("userinfo.html", thispage='userinfo', username=session["username"])
            curr.execute("UPDATE users SET passwd_hash=%s WHERE username=%s", [hashlib.sha256(form["newpassword"]).hexdigest(), username])
        conn.commit()
    username = session["username"]
    curr.execute("SELECT * FROM users WHERE username=%s", [username])
    u = curr.fetchone()
    wp_username = u[2]
    email = u[4]
    return render_template("userinfo.html", thispage='userinfo', username=session["username"], wp_username=wp_username, email=email)
コード例 #3
0
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 + "/")
コード例 #4
0
ファイル: auth.py プロジェクト: tjcsl/wedge
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
コード例 #5
0
ファイル: achievements.py プロジェクト: tjcsl/wedge
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()
コード例 #6
0
def save_student(student_name, point, major, student_class, age):
    sql = """
         insert into students(student_name, point, major, student_class, age)
         VALUES(%s, %s,%s,%s,%s);
         """


    cur.execute(sql, (student_name, point, major, student_class, age))
    conn.commit()
コード例 #7
0
ファイル: memory.py プロジェクト: chrisbrownnwyc/snarkbot
	def commit_history(self):
		# first delete any old stored history
		# note that if the max lines config variable is shortened that some history will hang around
		# I don't see this as a huge problem
		session.execute('DELETE FROM history LIMIT %d' % len(self.history) )

		# now insert everything we have stored so far
		session.executemany( 'INSERT INTO history VALUES (?,?,?)', self.history)
		conn.commit()
コード例 #8
0
ファイル: bot.py プロジェクト: chrisbrownnwyc/snarkbot
	def shutdown(self):
		self.proto.send('Exiting. Good bye.','channel')
		self.proto.close()

		self.mem.commit_history() # make sure we store all the history of this session

		# close up anything in the db as well
		conn.commit()
		conn.close()
コード例 #9
0
ファイル: rdbms.py プロジェクト: abdulvahab/mega_course
def get_meanings(word):
    query = f"SELECT meaning FROM dictionary WHERE expression='{word.lower()}'"
    print(query)
    cur.execute(query)
    conn.commit()
    meanings = cur.fetchone()
    if meanings:
        meanings = meanings[0].replace('{', '').replace('}', '').split(',')
    return meanings
コード例 #10
0
def materials_mining():
    cursor.execute("SELECT metal, id FROM user_game")
    metal_db = cursor.fetchall()
    for x in metal_db:
        metal_value = 0.2
        metal = float(x[0]) + metal_value
        cursor.execute("UPDATE game SET metal = " + str(metal) +
                       " WHERE id = " + str(x[1]) + "")
        conn.commit()
コード例 #11
0
ファイル: memory.py プロジェクト: chrisbrownnwyc/snarkbot
	def forget_all(self,keyvalue):
		phrase = self.get_phrase_by_text(keyvalue)

		try:
			session.execute('DELETE FROM responses WHERE phrase_id=?',[phrase['id']])
			session.execute('DELETE FROM phrase WHERE phrase_id=?',[phrase['id']] )
			conn.commit()
		except (AttributeError,KeyError):
			raise PhraseNotFound('That phrase does not exist')

		return True
コード例 #12
0
ファイル: memory.py プロジェクト: chrisbrownnwyc/snarkbot
	def forget_response(self,keyvalue,response):
		phrase = self.get_phrase_by_text(keyvalue)
		try:
			response = self.get_response_by_text(response, phrase['id'])
			try:
				response_id = response['id']
				session.execute('DELETE FROM responses WHERE id=?',[response_id])
				conn.commit()
			except (AttributeError,KeyError):
				raise ResponseNotFound('That response does not exist')

		except (AttributeError,KeyError):
			raise PhraseNotFound('That phrase does not exist')
コード例 #13
0
ファイル: update_training_data.py プロジェクト: tjcsl/wedge
def compile_training_data():
    curr.execute("SELECT added, deled, is_good FROM training_diffs")
    #format {"word": [a_spam, d_spam, a_good, d_good]}
    words = {}
    print "Begin sum words"
    for row in curr.fetchall():
        added = row[0].lower()
        deled = row[1].lower()
        added_words = re.findall(r'[\w]+', added)
        deled_words = re.findall(r'[\w]+', deled)
        for word in added_words:
            if is_blacklisted(word):
                continue
            if word not in words:
                words[word] = get_word_status(word)
            if row[2] == 0:
                words[word][0] += 1
            else:
                words[word][2] += 1
        for word in deled_words:
            if is_blacklisted(word):
                continue
            if word not in words:
                words[word] = get_word_status(word)
            if row[2] == 0:
                words[word][1] += 1
            else:
                words[word][3] += 1
    print "Begin commit word updates"
    curr.execute("DELETE FROM training_words")
    for word in words:
        word_data = words[word]
        curr.execute("INSERT INTO training_words (word, add_spam, add_good, del_spam, del_good) \
                    VALUES (%(word)s, %(aspam)s, %(agood)s, %(dspam)s, %(dgood)s)", 
                {"word":word, "aspam": word_data[0], "dspam":word_data[1], "agood":word_data[2], "dgood":word_data[3]})

    conn.commit()
    print "Begin commiting probabilities"
    curr.execute("SELECT sum(add_spam) + sum(add_good) + sum(del_spam) + sum(del_good) FROM training_words LIMIT 1")

    zum = curr.fetchone()
    zum = zum[0]

    curr.execute("DELETE FROM classifier_cache")
    curr.execute("SELECT word, add_spam, add_good, del_spam, del_good FROM training_words")
    for row in curr.fetchall():
        curr.execute("INSERT INTO classifier_cache (word, p_add_spam, p_add_good, p_del_spam, p_del_good) VALUES \
                (%(word)s, %(aspam)s::float/%(sum)s, %(agood)s::float/%(sum)s, %(dspam)s::float/%(sum)s, %(dgood)s::float/%(sum)s)",
                {"word" : row[0], "aspam":row[1]*1000.0, "agood":row[2]*1000.0, "dspam":row[3]*1000.0, "dgood":row[4]*1000.0, "sum":zum})
    conn.commit()
    print "Done"
コード例 #14
0
ファイル: harambe.py プロジェクト: AlexPietz/moobot
    async def harambe(self, message: discord.Message) -> None:
        """forever in our ~~memes~~ hearts"""

        if message.server is None:
            return

        server_id = message.server.id
        c.execute('select * from harambe where server=?', (server_id, ))
        result = c.fetchone()

        # result: [ server_id, last, number, chain, max ]

        if result is not None:
            # previous server
            # message.timestamp is a native datetime.datetime object
            # so in the database we are storing a datetime.datetime object
            # (=> str(datetime.datetime), it's a string-encoded ISO-based date)
            d_last = datetime.datetime.strptime(result[1],
                                                '%Y-%m-%d %H:%M:%S.%f')
            d_new = message.timestamp
            d_diff = d_new - d_last

            if d_diff.days >= 2:
                # update last in db
                c.execute(
                    '''update harambe
                                set last=?, number=number+1, chain=0
                                where server=?''', (str(d_new), server_id))
                conn.commit()
                await self.bot.send_message(
                    message.channel, 'days since harambe was last mentioned: '
                    '%s --> 0' % d_diff.days)

            elif d_diff.days == 1:
                if result[3] >= result[4]:
                    c.execute('update harambe set max=?', (result[3] + 1, ))

                c.execute(
                    '''update harambe
                                set last=?, number=number+1, chain=chain+1
                                where server=?''', (str(d_new), server_id))
                conn.commit()
                await self.bot.send_message(
                    message.channel,
                    'daily harambe chain: %s' % str(result[3] + 1))

            else:
                c.execute(
                    '''update harambe
                                set number=number+1
                                where server=?''', (server_id, ))
                conn.commit()

        else:
            d_new = message.timestamp
            c.execute(
                '''insert into harambe
                            values (?, ?, 1, 0, 0)''', (server_id, str(d_new)))

            conn.commit()
コード例 #15
0
def read_from_sql(token_id, begin, end, query):
    params = {
        "token_id": token_id,
        "begin": parse_timestamp(begin),
        "end": parse_timestamp(end),
        "default_status": default_status
    }
    df = pd.read_sql(con=conn,
                     sql=query,
                     params=params,
                     parse_dates=True,
                     index_col='createdAt')
    conn.commit()
    return df
コード例 #16
0
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)
コード例 #17
0
def fill_table(name, passwd):
    metal = "1"
    stone = "5"
    hel3 = "8"
    rubbish = "22"
    gold = "0"
    silicon = "0"
    premium = str(datetime.date(2019, 8, 12))

    cursor.execute(
        "INSERT INTO user_game (login, password, metal, stone, hel3, rubbish, gold, silicon, premium, experience) VALUES "
        "('" + name + "','" + passwd + "','" + metal + "','" + stone + "','" +
        hel3 + "','" + rubbish + "','" + gold + "','" + silicon + "','" +
        premium + "','0')")
    conn.commit()
コード例 #18
0
 async def set_message(self, from_chat_id: int, to_chat_id: int,
                       from_message_id: int, to_message_id: int):
     """
     :param from_chat_id:
         chat_id of the origin message.
     :param to_chat_id:
         chat_id of the message the bot just forwarded.
     :param from_message_id:
         message_id of the origin message.
     :param to_message_id:
         message_id of the message the bot just forwarded.
     :return: True
     """
     cur.execute(f"INSERT INTO msgs_to_{to_chat_id} VALUES (?,?,?)",
                 (from_chat_id, from_message_id, to_message_id))
     conn.commit()
コード例 #19
0
ファイル: test_bot.py プロジェクト: Austin-Bluethy/open-bot
async def writeNote(event):
    if '/note@best_open_bot' in event.raw_text or '/note' in event.raw_text:
        text = event.raw_text.split('/note')[1]
        if text == '':
            await event.reply(
                'Write name and note!\n\n/note <name_of_note> <note>')
        elif len(text.split(' ')) == 2:
            await event.reply('Write note!\n\n/note <name_of_note> <note>')
        else:
            name = text.split(' ')[1]
            note_text = text.split(name + ' ')[1]
            cur.execute(
                f"""INSERT INTO notes (NAME, TEXT) VALUES('{name}', '{note_text}');"""
            )
            conn.commit()
            await event.reply('Note has been saved')
コード例 #20
0
ファイル: verifyemail.py プロジェクト: tjcsl/wedge
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")
コード例 #21
0
    async def f(self, ctx: commands.Context) -> None:
        """pays respects"""
        u = (ctx.message.author.id, )
        c.execute('select * from respect where user=?', u)
        u_ = c.fetchone()

        if u_ is not None:  # user exists in db; increase respect
            c.execute('update respect set f=f+1 where user=?', u)
            conn.commit()

        else:  # user does not exist in db
            c.execute('insert into respect values(?, 1)', u)
            conn.commit()

        await self.bot.say(
            '%s p-p-pays their respects... all that respect just for little me...'
            % ctx.message.author.mention)
コード例 #22
0
ファイル: script_dao.py プロジェクト: KelwinKomka/API_CUP
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)
コード例 #23
0
ファイル: utils.py プロジェクト: tjcsl/wedge
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)
コード例 #24
0
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()
コード例 #25
0
def getNewMessages():
    cur.execute(
        ''' SELECT time_stamp, message_type, message FROM emergency_message WHERE reviewed = FALSE; '''
    )
    messages = cur.fetchall()
    message_count = len(messages)
    if message_count == 0:
        return False
    message_string = '%s new emergency messages have been added to the database!\n\n----------------------------------------\n' % (
        str(message_count))
    for message in messages:
        message_string += '''message time: {0}\n message type: {1}\n message: {2}\n------------------------------------\n '''.format(
            *message)
    cur.execute(''' UPDATE emergency_message SET reviewed = TRUE; ''')
    conn.commit()
    cur.close()
    conn.close()
    return message_string
コード例 #26
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)
コード例 #27
0
def create_table():
    cursor.execute("CREATE TABLE IF NOT EXISTS user_game "
                   "(id SERIAL PRIMARY KEY, "
                   "login TEXT, "
                   "password TEXT, "
                   "metal TEXT, "
                   "stone TEXT, "
                   "hel3 TEXT, "
                   "rubbish TEXT, "
                   "gold TEXT, "
                   "silicon TEXT, "
                   "premium TEXT,"
                   "experience TEXT)")

    cursor.execute("CREATE TABLE IF NOT EXISTS builds "
                   "(id TEXT, "
                   "name_1 TEXT, "
                   "name_2 TEXT)")
    conn.commit()
コード例 #28
0
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)
コード例 #29
0
ファイル: training.py プロジェクト: tjcsl/wedge
def train(revid2=None):
    if request.method=="POST":
        form = request.form
        constructive = form["constructive"]
        revid = form["diffid"]
        addedstring, deledstring, title, summary = get_diff_for_diffid(revid)
        cur.execute("INSERT INTO training_diffs (added, deled, is_good) VALUES (%s, %s, %s)", (addedstring, deledstring, constructive))
        conn.commit()

    while not revid2:
        revid2 = randint(45000000, 58500000)
        c = urlopen("http://en.wikipedia.org/w/api.php?action=query&prop=revisions&format=json&rvdiffto=prev&revids=%s" % revid2).read()
        c = json.loads(c)
        if "pages" not in c["query"]:
            revid2 = None
            continue
        pages = c["query"]["pages"]
        diff = pages.values()[0]["revisions"][0]["diff"]["*"]
        comment = pages.values()[0]["revisions"][0]["comment"]
        print comment
        return render_template("train.html", diff=diff, diffid=revid2, editsummary=comment)
コード例 #30
0
    def build(self):
        cursor.execute("SELECT * FROM builds")
        result = cursor.fetchall()

        if not result:
            cursor.execute(
                "INSERT INTO builds (id, name_1, name_2) VALUES ('" +
                str(self.id) + "','0','0')")
            conn.commit()

        else:
            for x in result:
                if int(x[0]) == self.id:
                    self.name_1 = x[1]
                    self.name_2 = x[2]
                break
            else:
                print("erroro")
                cursor.execute(
                    "INSERT INTO builds (id, name_1, name_2) VALUES ('" +
                    str(self.id) + "','0','0')")
                conn.commit()
コード例 #31
0
ファイル: extractor.py プロジェクト: gregkarlin/extractor
 def saveXML(self):
     print('Storing new xml elements into the database')
     for insert_dict in self.insert_dict_list:
         insert_string = ''' INSERT INTO emergency_message 
                              (message_id,
                               time_stamp,
                               message_type,
                               region_area,
                               canceled_time_stamp,
                               message) 
                       VALUES ('{messageId}',
                               '{timestamp}',
                               '{message_type}',
                               '{regionArea}',
                               '{canceledTimestamp}',
                               '{message}'
                              )  '''.format(**insert_dict)
         try:
             cur.execute(insert_string)
         except:
             print('duplicate entry')
     conn.commit()
コード例 #32
0
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)
コード例 #33
0
ファイル: crwaler.py プロジェクト: bunnyxt/eggprice-backend
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))
コード例 #34
0
        )  #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()  #закрывает коннект
except MySQLdb.Error as err:
    print("Connection error: {}".format(err))
    conn.close()
コード例 #35
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()


コード例 #36
0
def save_users(email, password):
    sql = "insert into users(email, password)VALUES(%s, %s)" % (email, password)
    cur.execute(sql)
    conn.commit()
コード例 #37
0
def del_student(id):
    sql = "delete from students where id=%s"
    cur.execute(sql,(id,))
    conn.commit()
コード例 #38
0
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)