Esempio n. 1
0
 def commitToDB(
     self,
     aptNum,
     checkInDate,
     checkOutDate,
     price,
     deposit,
     guestCount,
     bookingSource,
     confirmationCode,
     client_id,
     bookingDate,
     note,
 ):
     self.conn = pymysql.connect(
         host="nycapt.db.7527697.hostedresource.com", port=3306, user="******", passwd="Ktxd1976@", db="nycapt"
     )
     sqlcmd = "call pr_insertUpdate_booking(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
     sqlcmd = sqlcmd % (
         pymysql.escape_string(str(aptNum)),
         checkInDate,
         checkOutDate,
         str(price),
         str(deposit),
         str(guestCount),
         pymysql.escape_string(str(bookingSource)),
         pymysql.escape_string(str(confirmationCode).replace("Confirmation Code: ", "")),
         str(client_id),
         bookingDate,
         pymysql.escape_string(note),
     )
     self.dbcur = self.conn.cursor()
     self.dbcur.execute(sqlcmd)
     self.conn.commit()
Esempio n. 2
0
    def change_request_status(self, request_id, status):
        with self.conn:
            cur = self.conn.cursor()
            temp = self.conn.cursor()
            temp2 = self.conn.cursor()

            request_id = pymysql.escape_string( request_id )
            status = pymysql.escape_string( status ) 

            cur.execute("UPDATE client_book_requests SET request_status = %s WHERE request_id = %s",(status, request_id))
            self.conn.commit()
            

            cur.execute("SELECT * FROM book_inventory WHERE isbn = 446 AND book_status='New'")

            # if status was changed to approved, treat it like a client purchase
            if status == 'Approved':
                temp.execute("SELECT * FROM client_book_requests WHERE request_id = %s", (request_id))
                # data is now from the client book request
                data = temp.fetchone()

                isbn = data[2]
                client_id = data[1]
                book_status = data[3]
                quantity = data[4]

                temp.execute("INSERT INTO client_shopping_cart(isbn, book_status, quantity) VALUES (%s, %s, %s)",(isbn, book_status, quantity))
                
                self.checkout(client_id, True)

            self.conn.commit()

            return cur
Esempio n. 3
0
def dealbook():
	rootdir='book'
	prefix='.html'
	database = Mysql(host="localhost", user="******", pwd="6833066", db="doubanbook")
	insertbooksql = "INSERT INTO `bookdetial` (`bookname`,`bookno`,`bookinfo`,`bookintro`,`authorintro`,`peoples`,`starts`,`other`,`mulu`,`comments`) VALUES (" \
							"{0}, {1}, {2},{3},{4},{5},{6},{7},{8},{9})"
	for parent,dirnames,filenames in os.walk(rootdir):
		for filename in filenames:
			if filename.endswith(prefix) :
				path=str(parent)+'/'+filename
				print(path)
				content=open(path,'rb').read()
				try:
					draw=bookdeal.onebook(content)
				except:
					continue
				insert1 = insertbooksql.format(escape_string(draw[1]),draw[0],escape_string(draw[2]),escape_string(draw[3]),\
                                                               escape_string(draw[4]),draw[5],escape_string(draw[6]),escape_string(draw[7]),escape_string(draw[8]),escape_string(draw[9]))
				try:
					database.ExecNonQuery(insert1)
					os.rename(path,path+'lockl')
				except Exception as e:
					print(e)
					continue
			else:
				pass
Esempio n. 4
0
def importCalpendoIntoRMC(monthYear):
    result = run_query("call billingCalpendoByMonth('{monthYear}%')".format(monthYear=monthYear), "calpendo")
    s = db_connect("rmc")

    for row in result:
        row = list(row)
        for idx, val in enumerate(row):
            try:
                row[idx] = pymysql.escape_string(unicode(val))
            except UnicodeDecodeError:
                row[idx] = pymysql.escape_string(val.decode('iso-8859-1'))
        entry = Ris(accession_no=row[0], gco=row[1], project=row[2], MRN=row[3], PatientsName=row[4],
                    BirthDate=row[5], target_organ=row[6], target_abbr=row[7],
                    ScanDate=row[8], referring_md=row[9], Duration=row[10], ScanDTTM=row[11],
                    CompletedDTTM=row[12], Resource=row[13])
        s.add(entry)
        try:
            s.commit()
        except IntegrityError:
            print "Warning: Duplicate row detected in ris table."
            s.rollback()
        else:
            examEntry = Examcodes(target_abbr=row[7], target_organ=row[6])
            s.add(examEntry)
            try:
                s.commit()
            except IntegrityError:
                print "Warning: Examcode already exists."
                s.rollback()
    return result
Esempio n. 5
0
    def new_cash_donation(self, donor_ID, amount, donation_date):
        with self.conn:
            cur = self.conn.cursor()

            donor_ID = pymysql.escape_string( donor_ID )
            amount = pymysql.escape_string( amount )
            amount = float(amount)
            donation_date = pymysql.escape_string( donation_date )

            cur.execute("INSERT INTO cash_donations(donor_id, amount, date_donated) VALUES (%s, %s, %s)",(donor_ID, amount, donation_date))
            cur.execute("SELECT * FROM cash_reserves WHERE cash_id = 1")
            
            data = cur.fetchone()
            current_cash_reserves = data[0]
            new_cash = current_cash_reserves + amount

            cur.execute("UPDATE cash_reserves SET cash_amount = %s WHERE cash_id = 1",(new_cash))

            # threshold for dispersing tokens will be $500
            if new_cash > 500:
                cur.execute("SELECT * FROM clients")
                row = cur.fetchone()
                while row is not None:
                    client_ID = row[0]
                    self.add_tokens(client_ID, 3)
                    row = cur.fetchone()

            self.conn.commit()

            return cur
Esempio n. 6
0
    def add_genre(self, genre, description):
        with self.conn:
            cur = self.conn.cursor()  
            genre = pymysql.escape_string( genre ) 
            description = pymysql.escape_string( description ) 

            cur.execute("INSERT INTO genres(genre_type, description) VALUES (%s, %s)",(genre, description))
            
            self.conn.commit()
            return cur
Esempio n. 7
0
def searchFinances(start, end, resources):
    start = pymysql.escape_string(start)
    end = pymysql.escape_string(end)

    # MySQL Stored Procedure
    query_block = "call revenueByProject('{start}%','{end}%')".format(start=start, end=end)
    result = (run_query(query_block, "calpendo"),
              ('GCO', 'FundNumber', 'Investigator',
               'Revenue', 'totalApprovedHours', 'DevelopmentHours',
               'NonDevelopmentHours', 'risHours', 'CalpendoHours','CalBookingCount','CalNonDevBookingCount', 'CalDevBookingCount','RisBookingCount'))
    return result
Esempio n. 8
0
    def get(self, db, br1, br2):
        id_type = db.split('_')[1] # aba or brainer
        r1ids, r2ids = br1.split(','), br2.split(',')
        q1, q2 = to_query(r1ids, id_type), to_query(r2ids, id_type)
        br1_names = [get_name(int(rid), id_type) for rid in r1ids]
        br2_names = [get_name(int(rid), id_type) for rid in r2ids]

        conn = getMySQLConnection()
        cur = conn.cursor(pymysql.cursors.DictCursor) # in dictionary mode
        cur.execute(DetailsHandler.QUERY.format(escape_string(db), escape_string(db), q1 , q2, q2, q1))
        coocs = cur.fetchall()

        self.write(json.dumps({"coocs": coocs,
            'br1_names': br1_names, 'br2_names': br2_names}, use_decimal=True))
Esempio n. 9
0
def add_numbers():
    search = request.args.get('s')
    if not search or ':' not in search or "'" in search:
        return redirect('/')
    page = request.args.get('p', 1, type=int)
    page = page if page > 0 else 1

    limits = '{},{}'.format((page-1)*show_cnt, show_cnt)
    order = 'id desc'

    search_str = search.split(' ')
    params = {}
    for param in search_str:
        name, value = param.split(':')
        if name not in ['host', 'port', 'status_code','method', 'type', 'content_type', 'scheme', 'extension']:
            return redirect('/')
        params[name] = value
    
    condition = comma = ''
    glue = ' AND '
    for key, value in params.iteritems():
        if ',' in value and key in ['port','status_code','method','type']:
            values = [escape_string(x) for x in value.split(',')]
            condition +=  "{}`{}` in ('{}')".format(comma, key, "', '".join(values))
        elif key in ['host']:
            condition +=  "{}`{}` like '%{}'".format(comma, key, escape_string(value))
        else:
            condition +=  "{}`{}` = '{}'".format(comma, key, escape_string(value))
        comma = glue

    dbconn = connect_db()
    count_sql = 'select count(*) as cnt from capture where {}'.format(condition)
    record_size = int(dbconn.query(count_sql, fetchone=True).get('cnt'))
    
    max_page = record_size/show_cnt + 1
    
    records = dbconn.fetch_rows(
                table='capture',
                condition=condition,
                order=order,
                limit=limits)

    return render_template(
                    'index.html', 
                    records=records, 
                    page=page,
                    search=search,
                    max_page=max_page)
Esempio n. 10
0
def enter_tweet(line):

	# get array specified by the line
	array = line.split("\",\"")

	# get values from array
	tid = int(text.remove_quotes(array[0]))
	tweet = pymysql.escape_string(text.remove_end_quotes(array[1]))

	# get the state id	
	state = get_state(text.strip_punc_all(array[2]))

	# create s, w, and k strings
	s = text.mysql_string_from_array(array[4:9])
	w = text.mysql_string_from_array(array[9:13])
	k = text.mysql_string_from_array(array[13:29])

	# check lengths	
	if (len(s) > 30 or len(w) > 30 or len(k) > 50 or len(tweet) > 200):
		return

	# insert everything into the table
	sql = "INSERT INTO tweets (id,tweet,s,w,k,state) VALUES (" + \
							"'" + str(tid) + "'," + \
							"'" + tweet   + "'," + \
							"'" + s       + "'," + \
							"'" + w       + "'," + \
           						"'" + k       + "'," + \
							"'" + str(state)   + "')"

	# execute the sql
	cur.execute(sql)
	con.commit()
    def import_data(self, file):
        table = file.replace('.csv','')
        ImportMySQL.drop_table(self, table)
        # Open file
        n = 0
        with open(self.path + file) as csvfile:
            fp = csv.reader(csvfile, delimiter=',', quotechar='"')
            for row in fp:

                n += 1
                if n == 1:
                    cols = row
                    # Recreate the table from the first line of the text file (create an id column too)
                    query = "CREATE TABLE " + table + " (id INT NOT NULL AUTO_INCREMENT"
                    for col in cols:
                        query += ", `" + col + "` VARCHAR(30)"
                    query += " , PRIMARY KEY(id))"
                    self.cur.execute(query)
                    self.db.commit()
                else:
                    # Fill table with data
                    query = "INSERT INTO " + table + " VALUES(NULL"
                    for rec in row:
                        rec = pymysql.escape_string(rec)
                        query += ", '" + rec + "'"
                    query += ")"
                    self.cur.execute(query)
                    self.db.commit()
def import_posts(path_to_posts, dbname):
    data = list()
    counter = 0
    for file in os.listdir(path_to_posts):
        if file.endswith(".txt"):
            with open(os.path.join(path_to_posts, file), encoding="utf-8") as f:
                uid = file.split(".")[0]
                posts = f.read().strip("\n").split(">>")[1:]
                for post in posts:
                    timestamp, text = post.split("\n", 1)
                    time_created = datetime.fromtimestamp(int(timestamp)).isoformat()
                    text = pymysql.escape_string(text.strip())
                    data.append("({},'{}','{}')".format(uid, time_created, text))
                    counter += 1

    connection = pymysql.connect(host=HOST,
                                 user=USERNAME,
                                 password=PASSWORD,
                                 db=dbname,
                                 charset='utf8mb4',
                                 cursorclass=pymysql.cursors.DictCursor)
    try:
        with connection.cursor() as cursor:
            sql = "INSERT INTO `posts` (`uid`, `time_created`, `post`) VALUES {}".format(",".join(data))
            cursor.execute(sql)
        connection.commit()
        print("Posts import done. {} rows created.".format(counter))
    except Exception as e:
        print(e)
    finally:
        connection.close()
Esempio n. 13
0
def fnThreadLoop(i, queue, lock):

    s = requests.Session()

    while True:
        #exit Thread when detect signal to quit.
        while libextra.fnExitNow():
            try:
                r = queue.get_nowait()
                break
            except:
                #libextra.fnQueueEmpty(1,lock)
                time.sleep(0.1)
                continue

        if libextra.fnExitNow() == False:
            break

        id = r[0]
        (status, gw_resp, log_msg) = fnJOB_CallAPI(s,r)
        gw_resp = pymysql.escape_string(gw_resp)

        #if (log_msg != ''):
        #    print(log_msg)

        QueueUpdate.put((id,status,gw_resp))
        queue.task_done()
Esempio n. 14
0
    def update_webhook(self, webhook):
        while True:
            try:
                response_code = webhook['response_code']
                error_msg = webhook.get('error_msg', '')[0:255]
                history_id = webhook['history_id']

                if response_code == 'NULL' or response_code > 205:
                    status = 2
                else:
                    status = 1
                query = (
                        """UPDATE `webhook_history` """
                        """SET `status`={0},`response_code`={1}, `error_msg`="{2}" """
                        """WHERE `history_id`=UNHEX('{3}')"""
                ).format(status, response_code, pymysql.escape_string(error_msg), history_id)
                self._db.execute(query)
                break
            except KeyboardInterrupt:
                raise
            except:
                msg = "Webhook update failed, history_id: {0}, reason: {1}"
                msg = msg.format(webhook['history_id'], helper.exc_info())
                LOG.warning(msg)
                time.sleep(5)
Esempio n. 15
0
def parse_post(data, dict_fields, location_fields):
    parsed_posts = []
    for i in range(len(data['postings'])):
        my_dict = {}
        for field in dict_fields:
            if field == 'location':
                for location_field in location_fields:
                    if location_field in data['postings'][i]['location']:
                        my_dict[location_field] = data['postings'][i][field][location_field]
                    else:
                        my_dict[location_field] = None
            elif field == 'images':
                 my_dict[field] = len(data['postings'][i][field])
            elif field == 'external_url':
                    my_dict[field] = mdb.escape_string(data['postings'][i][field])
            elif field == 'flagged_status':
                url = data['postings'][i]['external_url']
                #my_dict[field] = check_flag(url)
                my_dict[field] = 2
            elif field in data['postings'][i]:
                my_dict[field] = data['postings'][i][field]
            else:
                 my_dict[field] = None
        parsed_posts.append(my_dict)

    return parsed_posts
Esempio n. 16
0
    def process_purchase(self, check_list):
        with self.conn:
            cur = self.conn.cursor()
            for check in check_list:
                new_check = pymysql.escape_string(check)
                quantity_selected, isbn, status = new_check.split("_")
                isbn = int(isbn)
                quantity_selected = int(quantity_selected)

                cur.execute("SELECT * FROM book_inventory WHERE isbn = %s AND book_status = %s",(isbn,status))
                book = cur.fetchone()
                book_isbn = book[0]
                title = book[1]
                reading_level = book[2]
                genre_type = book[3]
                book_status = book[4]
                edition = book[5]
                publisher = book[6]
                quantity = book[7]
                cur.execute("SELECT * FROM client_shopping_cart WHERE isbn = %s AND book_status=%s",(isbn,book_status))
                exists_in_cart = cur.fetchone()
               
                # if this book isn't already in the client's shopping cart, add it
                if exists_in_cart == None:
                    cur.execute("INSERT INTO client_shopping_cart(isbn, book_status, quantity) VALUES (%s, %s, 1)",(book_isbn, book_status))
                #if it does, just increment the quantity
                else:
                    existing_quant_in_cart = exists_in_cart[2]
                    new_quantity = existing_quant_in_cart + quantity_selected
                    cur.execute("UPDATE client_shopping_cart SET quantity = %s WHERE isbn = %s AND book_status=%s",(new_quantity, isbn, book_status))

            self.conn.commit()
            return cur
Esempio n. 17
0
    def get(self, db):
        id_type = db.split('_')[1] # aba or brainer
        size = int(self.get_argument('size', 30))
        conn = getMySQLConnection()
        cur = conn.cursor(pymysql.cursors.DictCursor) #in dictionary mode
        cur.execute(MatrixHandler.QUERY.format(escape_string(db)))

        # count all co-occurences
        counter = Counter() # to count mentions of a br
        coocs = defaultdict(lambda : defaultdict(int))
        for c in cur.fetchall():
            cnt = c['cnt']
            frm = int(c['e1'].split(':')[1]) # comes as 'aba:123', 'brainer:123'
            to =  int(c['e2'].split(':')[1])
            counter[frm] +=cnt
            counter[to] +=cnt
            if frm > to: # symetrize
                frm, to = to, frm
            if frm != to:
                coocs[frm][to] += cnt

        # a tuple of top br (aba_id, name)
        top_brs = [(i[0], get_name(i[0], id_type), counter[i[0]]) for i in counter.most_common(size)]

        self.write(json.dumps({"coocs": coocs, "top_brs": top_brs}))
Esempio n. 18
0
    def insert_reply(self, item):
        reply_time_str = item.get("reply_time")
        reply_time_sec = time.mktime(time.strptime(reply_time_str, "%Y-%m-%d %H:%M"))
        reply_time_ms = int(reply_time_sec) * 1000

        insert_sql = "insert into hupu_post_reply(hupu_reply_id,author,hupu_post_id,reply_time,like_count,content,floor_num, gmt_created)" \
                     " values (%s,'%s',%s,%s,%s,'%s',%s,%s)" \
                     % (item.get("hupu_reply_id"), item.get("author"), item.get("hupu_post_id"), reply_time_ms,
                        item.get("like_count"), pymysql.escape_string(item.get("content")), item.get("floor_num"),
                        int(time.time()) * 1000)

        try:
            with pool.get_db_connect() as db:
                db.cursor.execute(insert_sql)
                db.conn.commit()
        except:
            print("error reply content:" + pymysql.escape_string(item.get("content")))
Esempio n. 19
0
	def findGameIdFromTitle(self, gameTitle):
		sql = "SELECT game_id FROM Games WHERE Title='{0}'".format(pymysql.escape_string(gameTitle))
		result = self.runSQL(sql).fetchone()
		if result is None:
			print("Couldn't find game id for '%s'" % gameTitle)
			return None
		else:
			return result["game_id"]
Esempio n. 20
0
 def join_field_value(self, data, glue = ', '):
     sql = comma = ''
     for key, value in data.iteritems():
         if isinstance(value, str):
             value = pymysql.escape_string(value)
         sql +=  "{}`{}` = '{}'".format(comma, key, value)
         comma = glue
     return sql
Esempio n. 21
0
	def findSourceIdFromTitle(self, sourceName):
		sql = "SELECT source_id FROM Sources WHERE Name='{0}'".format(pymysql.escape_string(sourceName))
		result = self.runSQL(sql).fetchone()
		if result is None:
			print("Couldn't find source id for '%s'" % sourceName)
			return None
		else:
			return result["source_id"]
Esempio n. 22
0
 def create_insert_sql(table, pair):
     key_str = ",".join(["`%s`" % key for key in pair.keys()])
     value_str = ",".join(["{%s}" % key for key in pair.keys()])
     sql = r"insert into %s (%s) values (%s)" % (table, key_str, value_str)
     temp_data = {}
     for key, value in pair.items():
         temp_data[key] = SqlUtil.safestr(pymysql.escape_string(str(value)), 'utf-8')
     return sql.format(**temp_data)
Esempio n. 23
0
	def escape_string(self, string):
		"""
		This is just an adapter function to allow previus users of MySQLdb. 
		To be familier with there names of functions.
		
		@see: escapeString
		"""
		return pymysql.escape_string(string)
Esempio n. 24
0
    def insert_default_user_for_customer(self, data ={}, to_table = "", map_dict={}, s_type=0):
        self.new_cur.execute("delete FROM yehnet_customer_user where u_id = 0")
        self.new_conn.commit()
        self.new_cur.execute("select * from yehnet_customer yc left join yehnet_customer_user ycu on ycu.c_id = yc.id where ycu.u_id = 0 or ycu.u_id is null")
        data = self.new_cur.fetchall()
        print(data)
        for each in data:
            #sql = "update yehnet_list set `ProjGUID` =  \"%s\" where module_id = 3 and title LIKE \"%%%s%%\"" % (each['ProjGUID'], each['ProjName'])UserGUID

            #self.new_cur.execute("update yehnet_admin set `UserGUID` =  \"%s\" where username LIKE \"%%%s%%\"" % (each['UserGUID'], each['UserName']))
            #self.new_cur.execute("update yehnet_list set `ProjGUID` =  \"%s\" where module_id = 3 and title LIKE \"%%%s%%\"" % (each['ProjGUID'], each['ProjName']))

            #print(list(map(f,list(each.keys()))))
            sql_key = ""
            sql_value = ""
            for from_cols, to_cols in map_dict.items():
                #print(from_cols,to_cols)
                key = to_cols[1]
                value = each.get(from_cols,'NULL')
                if value is None:
                    pass
                else:
                    if to_cols[0] is 1:
                        if isinstance(value, str):
                            value = int(value)
                        value = datetime.datetime.fromtimestamp(value).strftime("%Y-%m-%d %H:%M:%S")
                    if to_cols[0] is 2:
                        value = to_cols[2]
                    if to_cols[0] is 3:
                        value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    if to_cols[0] is 4:
                        value = "%s-%s-%s %s:%s:%s" % (value['year']+1900, value["month"]+1, value['date'], value["hours"], value['minutes'], value['seconds'])
                    if to_cols[0] is 41:
                        value = each[to_cols[2]]
                        value = "%s-%s-%s %s:%s:%s" % (value['year']+1900, value["month"]+1, value['date'], value["hours"], value['minutes'], value['seconds'])
                    if to_cols[0] is 5:
                        projname_map = {"丁香雅苑":"玫瑰庄园"}
                        if value in projname_map:
                            value = projname_map[value]
                    sql_key += "`%s`," % key
                    if isinstance(value, str):
                        sql_value += "\"%s\"," % pymysql.escape_string(value)
                    else:
                        sql_value += "\"%s\"," % value
                    # if isinstance(value, str):
                    #     sql += "`%s` = \"%s\"," % (key, pymysql.escape_string(value))
                    # else:
                    #     sql += "`%s` = \"%s\"," % (key, value)
            sql = "INSERT INTO %s (%s) VALUES (%s)" % (to_table,sql_key[:-1],sql_value[:-1])
            try:
                print(sql)
                self.new_cur.execute(sql)
            except pymysql.err.IntegrityError as e:
                print(e)
                #print(sql)
            except pymysql.err.ProgrammingError as e: #捕捉除0异常
                print(e)
        self.new_conn.commit()
Esempio n. 25
0
    def process_insert(self, from_table='yehnet_sales', to_table="yehnet_admin", set_dict={}, project=False):
        map_dict = {"username":(0,"username"),
                    "email":(0,"email"),"phone":(0,"phone"),"company":(0,"company"),"department":(0,"department"),"job":(0,"job"),"add_time":(1,"add_time")}
        if set_dict:
            map_dict = set_dict
        if project:
            sql = "select * from %s " % (from_table)
            sql += project
            self.old_cur.execute(sql)
        else:
            self.old_cur.execute("select * from %s" % from_table)

        qs_old = self.old_cur.fetchall()

        # self.new_cur.execute("select * from yehnet_admin")
        # qs_new = self.new_cur.fetchall()
        # new_dict = {}
        # for each in qs_new:
        #     new_dict[each['admin_id']] = each

        for each in qs_old:
            #print(list(map(f,list(each.keys()))))
            sql_key = ""
            sql_value = ""
            for from_cols, to_cols in map_dict.items():
                #print(from_cols,to_cols)
                key = to_cols[1]
                value = each[from_cols]
                if value is None:
                    pass
                else:
                    if to_cols[0] is 1:
                        if isinstance(value, str):
                            value = int(value)
                        value = datetime.datetime.fromtimestamp(value).strftime("%Y-%m-%d %H:%M:%S")
                    if to_cols[0] is 2:
                        value = to_cols[2]
                    if to_cols[0] is 3:
                        value = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                    sql_key += "`%s`," % key
                    if isinstance(value, str):
                        sql_value += "\"%s\"," % pymysql.escape_string(value)
                    else:
                        sql_value += "\"%s\"," % value
                    # if isinstance(value, str):
                    #     sql += "`%s` = \"%s\"," % (key, pymysql.escape_string(value))
                    # else:
                    #     sql += "`%s` = \"%s\"," % (key, value)
            sql = "INSERT INTO %s (%s) VALUES (%s)" % (to_table,sql_key[:-1],sql_value[:-1])
            try:
                #print(f(sql))
                self.new_cur.execute(sql)
            except pymysql.err.IntegrityError as e:
                print(e)
                #print(sql)
            except pymysql.err.ProgrammingError as e: #捕捉除0异常
                print(e)
        self.db_commit()
Esempio n. 26
0
 def __escape_tuple(self, *input_tuple):
     '''
     [private] escape all args.
     '''
     escaped_arr = [];
     
     for i in input_tuple:
         escaped_arr.append(MySQLdb.escape_string(str(i)));
     
     return tuple(escaped_arr);
Esempio n. 27
0
def lookup_beer(search_term, is_id):
    """Queries the MySQL database for a specific beer by name or id"""
    attr = 'id' if is_id is True else 'name'
    query = """
            SELECT `id`, `name`, `styleid`, `abv`
            FROM `beers`
            WHERE `%s` = '%s'
            """ % (attr, escape_string(search_term))

    return mysql.query(query)
Esempio n. 28
0
    def add_reading_level(self, reading_level):
        with self.conn:
            cur = self.conn.cursor()  
            reading_level = pymysql.escape_string( reading_level ) 

            cur.execute("INSERT INTO reading_levels(reading_level) VALUES (%s)",(reading_level))
            
            self.conn.commit()

            return cur
Esempio n. 29
0
    def add_volunteer(self, firstName, lastName, DOB, gender, phoneNum, email, streetAddress, city, state, zipCode):
        with self.conn:
            cur = self.conn.cursor( )

            firstName = pymysql.escape_string( firstName )
            lastName = pymysql.escape_string( lastName )
            DOB = pymysql.escape_string( DOB )
            gender = pymysql.escape_string( gender )
            phoneNum = pymysql.escape_string( phoneNum )
            email = pymysql.escape_string( email )
            streetAddress = pymysql.escape_string( streetAddress )
            city = pymysql.escape_string( city )
            state = pymysql.escape_string( state )
            zipCode = pymysql.escape_string( zipCode )

            cur.execute("INSERT INTO volunteers(volunteer_first_name, volunteer_last_name, volunteer_dob, volunteer_gender, vounteer_phone_num, volunteer_email, vounteer_street_address, volunteer_city, volunteer_state, volunteer_zipcode) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )",( firstName, lastName, DOB, gender, phoneNum, email, streetAddress, city, state, zipCode))

            self.conn.commit()
            return cur
Esempio n. 30
0
 def update_content(self, hupu_post_id, content, post_time_str):
     post_time_sec = time.mktime(time.strptime(post_time_str, "%Y-%m-%d %H:%M"))
     post_time_ms = int(post_time_sec) * 1000
     update_sql = "update hupu_post set post_time=%s,content='%s',content_is_set=1 where hupu_post_id=%s" % (
         post_time_ms, pymysql.escape_string(content), hupu_post_id)
     try:
         with pool.get_db_connect() as db:
             db.cursor.execute(update_sql)
             db.conn.commit()
     except:
         print("error content:" + content)
Esempio n. 31
0
class GuoJi():

    proxy={"http": "http://37.59.35.174:1080", "https": "http://37.59.35.174:1080"}
    headers={
        "User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/39.0.2171.95 Safari/537.36 MicroMessenger/6.5.2.501 NetType/WIFI WindowsWechat QBCore/3.43.901.400 QQBrowser/9.0.2524.400"
    }
    nowtime = str(int(time.time()))
    cuntrylist= {'���˰����Լ': pymysql.escape_string('[\"2\",\"7\",\"308\",\"309\"]'),
              '�ձ�': pymysql.escape_string('[\"2\",\"7\",\"308\",\"310\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"311\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"312\"]'),
              'Ӣ��': pymysql.escape_string('[\"2\",\"7\",\"308\",\"313\"]'),
              '����ʱ':pymysql.escape_string('[\"2\",\"7\",\"308\",\"314\"]'),
              '�¹�': pymysql.escape_string('[\"2\",\"7\",\"308\",\"315\"]'),
              '��������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"316\"]'),
              'Ų��': pymysql.escape_string('[\"2\",\"7\",\"308\",\"317\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"318"])'),
              '�¼���': pymysql.escape_string('[\"2\",\"7\",\"308\",\"319\"]'),
              '���ô�': pymysql.escape_string('[\"2\",\"7\",\"308\",\"320\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"321\"]'),
              '���':pymysql.escape_string('[\"2\",\"7\",\"308\",\"322\"]'),
              '������':pymysql.escape_string('[\"2\",\"7\",\"308\",\"323\"]'),
              '̩��': pymysql.escape_string('[\"2\",\"7\",\"308\",\"324\"]'),
              '�����':pymysql.escape_string('[\"2\",\"7\",\"308\",\"325\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"326\"]'),
              '�ݿ�˹�工�ˣ�������˹�工�ˣ�':pymysql.escape_string('[\"2\",\"7\",\"308\",\"327\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"328\"]'),
              '�Ĵ�����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"329\"]'),
              '��˹���������ڲ�˹���Ǻͺ�����ά�ǣ�':pymysql.escape_string('[\"2\",\"7\",\"308\",\"330\"]'),
              '��������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"331\"]'),
              '�ͻ�˹̹':pymysql.escape_string('[\"2\",\"7\",\"308\",\"332\"]'),
              '������':pymysql.escape_string('[\"2\",\"7\",\"308\",\"333\"]'),
              '��ʿ': pymysql.escape_string('[\"2\",\"7\",\"308\",\"334\"]'),
              '����·˹':pymysql.escape_string('[\"2\",\"7\",\"308\",\"335\"]'),
              '������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"336\"]'),
              '��������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"337\"]'),
              '�µ���': pymysql.escape_string('[\"2\",\"7\",\"308\",\"338\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"339\"]'),
              '�ɹ�': pymysql.escape_string('[\"2\",\"7\",\"308\",\"340\"]'),
              '������':pymysql.escape_string('[\"2\",\"7\",\"308\",\"341\"]'),
              '�����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"342\"]'),
              '������':pymysql.escape_string('[\"2\",\"7\",\"308\",\"343\"]'),
              '¬ɭ��':pymysql.escape_string('[\"2\",\"7\",\"308\",\"344\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"345\"]'),
              '����˹': pymysql.escape_string('[\"2\",\"7\",\"308\",\"346\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"347\"]'),
              'ӡ��':pymysql.escape_string('[\"2\",\"7\",\"308\",\"348\"]'),
              'ë����˹': pymysql.escape_string('[\"2\",\"7\",\"308\",\"349\"]'),
              '���޵���': pymysql.escape_string('[\"2\",\"7\",\"308\",\"350\"]'),
              '�׶���˹': pymysql.escape_string('[\"2\",\"7\",\"308\",\"351\"]'),
              '˹��������':pymysql.escape_string('[\"2\",\"7\",\"308\",\"352\"]'),
              '��ɫ��': pymysql.escape_string('[\"2\",\"7\",\"308\",\"353\"]'),
              'Խ��': pymysql.escape_string('[\"2\",\"7\",\"308\",\"354\"]'),
              '������':pymysql.escape_string('[\"2\",\"7\",\"308\",\"355\"]'),
              '�ڿ���': pymysql.escape_string('[\"2\",\"7\",\"308\",\"356\"]'),
              '��������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"357\"]'),
              '�����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"358\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"359\"]'),
              '������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"360\"]'),
              '������':pymysql.escape_string('[\"2\",\"7\",\"308\",\"361\"]'),
              '���ȱ��˹̹': pymysql.escape_string('[\"2\",\"7\",\"308\",\"362\"]'),
              '�ϼ�����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"363\"]'),
              '��˹�������ˣ�����������ά�Ǻͺ�ɽ��': pymysql.escape_string('[\"2\",\"7\",\"308\",\"364\"]'),
              '�յ�': pymysql.escape_string('[\"2\",\"7\",\"308\",\"365\"]'),
              '�����':pymysql.escape_string('[\"2\",\"7\",\"308\",\"366\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"367\"]'),
              '������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"368\"]'),
              '��ɳ����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"369\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"370\"]'),
              '�����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"371\"]'),
              '���ɱ�THE': pymysql.escape_string('[\"2\",\"7\",\"308\",\"372\"]'),
              '������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"373\"]'),
              '�Ϸ�':pymysql.escape_string('[\"2\",\"7\",\"308\",\"374\"]'),
              '�ͰͶ�˹':pymysql.escape_string('[\"2\",\"7\",\"308\",\"375\"]'),
              'Ħ������':pymysql.escape_string('[\"2\",\"7\",\"308\",\"376\"]'),
              '��������':pymysql.escape_string('[\"2\",\"7\",\"308\",\"377\"]'),
              '��': pymysql.escape_string('[\"2\",\"7\",\"308\",\"378\"]'),
              '������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"379\"]'),
              '�Ჴ��': pymysql.escape_string('[\"2\",\"7\",\"308\",\"380\"]'),
              '������˹̹': pymysql.escape_string('[\"2\",\"7\",\"308\",\"381\"]'),
              'ӡ��������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"382\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"383\"]'),
              '��������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"384\"]'),
              'ͻ��˹': pymysql.escape_string('[\"2\",\"7\",\"308\",\"385\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"386\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"387\"]'),
              'ϣ��': pymysql.escape_string('[\"2\",\"7\",\"308\",\"388\"]'),
              '������˹': pymysql.escape_string('[\"2\",\"7\",\"308\",\"389\"]'),
              'Ħ���': pymysql.escape_string('[\"2\",\"7\",\"308\",\"390\"]'),
              '˹������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"391\"]'),
              '�������Ͷ�͸�': pymysql.escape_string('[\"2\",\"7\",\"308\",\"392\"]'),
              '����������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"393\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"394\"]'),
              '�����ݽ�': pymysql.escape_string('[\"2\",\"7\",\"308\",\"395\"]'),
              '��³����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"396\"]'),
              'ī����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"397\"]'),
              'ɳ�ذ�����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"398\"]'),
              '����������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"399\"]'),
              '������˹̹': pymysql.escape_string('[\"2\",\"7\",\"308\",\"400\"]'),
              '���������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"401\"]'),
              '������˹̹': pymysql.escape_string('[\"2\",\"7\",\"308\",\"402\"]'),
              '�ݿ�': pymysql.escape_string('[\"2\",\"7\",\"308\",\"403\"]'),
              '�ޱ���':pymysql.escape_string('[\"2\",\"7\",\"308\",\"404\"]'),
              '������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"405\"]'),
              '�ڸɴ�': pymysql.escape_string('[\"2\",\"7\",\"308\",\"406\"]'),
              '��������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"407\"]'),
              '��϶��': pymysql.escape_string('[\"2\",\"7\",\"308\",\"408\"]'),
              '����': pymysql.escape_string('[\"2\",\"7\",\"308\",\"409\"]'),
              '��Ͳ�Τ':pymysql.escape_string('[\"2\",\"7\",\"308\",\"410\"]'),
              '����կ': pymysql.escape_string('[\"2\",\"7\",\"308\",\"411\"]'),
              '������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"412\"]'),
              '����':pymysql.escape_string('[\"2\",\"7\",\"308\",\"413\"]'),
              '�չ�������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"414\"]'),
              '������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"415\"]'),
              '����͢': pymysql.escape_string('[\"2\",\"7\",\"308\",\"416\"]'),
              '����ر�������': pymysql.escape_string('[\"2\",\"7\",\"308\",\"417\"]'),
              '�����ر�������':pymysql.escape_string('[\"2\",\"7\",\"308\",\"418\"]'),
              '̨��': pymysql.escape_string('[\"2\",\"7\",\"308\",\"419\"]')}   #cuntrylist={'���˰����Լ': pymysql.escape_string('[\"49\"]'), '����ر�������': pymysql.escape_string('[\"50\"]'), '�����ر�������': pymysql.escape_string('[\"51\"]'), '̨��': pymysql.escape_string('[\"52\"]'), '�ձ�': pymysql.escape_string('[\"53\"]'), '����': pymysql.escape_string('[\"54\"]'), '����': pymysql.escape_string('[\"55\"]'), 'Ӣ��': pymysql.escape_string('[\"56\"]'), '����ʱ': pymysql.escape_string('[\"57\"]'), '�¹�': pymysql.escape_string('[\"58\"]'), '��������': pymysql.escape_string('[\"59\"]'), 'Ų��': pymysql.escape_string('[\"60\"]'), '����': pymysql.escape_string('[\"61\"]'), '�¼���': pymysql.escape_string('[\"62\"]'), '���ô�': pymysql.escape_string('[\"63\"]'), '����': pymysql.escape_string('[\"64\"]'), '���': pymysql.escape_string('[\"65\"]'), '������': pymysql.escape_string('[\"66\"]'), '̩��': pymysql.escape_string('[\"67\"]'), '�����': pymysql.escape_string('[\"68\"]'), '����': pymysql.escape_string('[\"69\"]'), '�ݿ�˹�工��': pymysql.escape_string('[\"70\"]'), '����': pymysql.escape_string('[\"71\"]'), '�Ĵ�����': pymysql.escape_string('[\"72\"]'), '��˹����': pymysql.escape_string('[\"73\"]'), '��������': pymysql.escape_string('[\"74\"]'), '�ͻ�˹̹': pymysql.escape_string('[\"75\"]'), '������': pymysql.escape_string('[\"76\"]'), '��ʿ': pymysql.escape_string('[\"77\"]'), '����·˹': pymysql.escape_string('[\"78\"]'), '������': pymysql.escape_string('[\"79\"]'), '��������': pymysql.escape_string('[\"80\"]'), '�µ���': pymysql.escape_string('[\"81\"]'), '����': pymysql.escape_string('[\"82\"]'), '�ɹ�': pymysql.escape_string('[\"83\"]'), '������': pymysql.escape_string('[\"84\"]'), '�����': pymysql.escape_string('[\"85\"]'), '������': pymysql.escape_string('[\"86\"]'), '¬ɭ��': pymysql.escape_string('[\"87\"]'), '����': pymysql.escape_string('[\"88\"]'), '����˹': pymysql.escape_string('[\"89\"]'), '����': pymysql.escape_string('[\"90\"]'), 'ӡ��': pymysql.escape_string('[\"91\"]'), 'ë����˹': pymysql.escape_string('[\"92\"]'), '���޵���': pymysql.escape_string('[\"93\"]'), '�׶���˹': pymysql.escape_string('[\"94\"]'), '˹��������': pymysql.escape_string('[\"95\"]'), '��ɫ��': pymysql.escape_string('[\"96\"]'), 'Խ��': pymysql.escape_string('[\"97\"]'), '������': pymysql.escape_string('[\"98\"]'), '�ڿ���': pymysql.escape_string('[\"99\"]'), '��������': pymysql.escape_string('[\"100\"]'), '�����': pymysql.escape_string('[\"101\"]'), '����': pymysql.escape_string('[\"102\"]'), '������': pymysql.escape_string('[\"103\"]'), '����ά��': pymysql.escape_string('[\"104\"]'), '���ȱ��˹̹': pymysql.escape_string('[\"105\"]'), '�ϼ�����': pymysql.escape_string('[\"106\"]'), '��˹��������': pymysql.escape_string('[\"107\"]'), '�յ�': pymysql.escape_string('[\"108\"]'), '�����': pymysql.escape_string('[\"109\"]'), '����': pymysql.escape_string('[\"111\"]'), '������': pymysql.escape_string('[\"112\"]'), '��ɳ����': pymysql.escape_string('[\"113\"]'), '����': pymysql.escape_string('[\"114\"]'), '�����': pymysql.escape_string('[\"115\"]'), '���ɱ�THE': pymysql.escape_string('[\"116\"]'), '������': pymysql.escape_string('[\"117\"]'), '�Ϸ�': pymysql.escape_string('[\"118\"]'), '�ͰͶ�˹': pymysql.escape_string('[\"119\"]'), 'Ħ������': pymysql.escape_string('[\"120\"]'), '��������': pymysql.escape_string('[\"121\"]'), '�Ű�': pymysql.escape_string('[\"122\"]'), 'ί������': pymysql.escape_string('[\"123\"]'), '�Ჴ��': pymysql.escape_string('[\"124\"]'), '������˹̹': pymysql.escape_string('[\"125\"]'), 'ӡ��������': pymysql.escape_string('[\"126\"]'), '����': pymysql.escape_string('[\"127\"]'), '��������': pymysql.escape_string('[\"128\"]'), 'ͻ��˹': pymysql.escape_string('[\"129\"]'), '����': pymysql.escape_string('[\"130\"]'), '����': pymysql.escape_string('[\"131\"]'), 'ϣ��': pymysql.escape_string('[\"132\"]'), '������˹': pymysql.escape_string('[\"133\"]'), 'Ħ���': pymysql.escape_string('[\"134\"]'), '˹������': pymysql.escape_string('[\"135\"]'), '�������Ͷ�͸�': pymysql.escape_string('[\"136\"]'), '����������': pymysql.escape_string('[\"137\"]'), '����': pymysql.escape_string('[\"138\"]'), '�����ݽ�': pymysql.escape_string('[\"139\"]'), '��³����': pymysql.escape_string('[\"140\"]'), 'ī����': pymysql.escape_string('[\"141\"]'), 'ɳ�ذ�����': pymysql.escape_string('[\"142\"]'), '����������': pymysql.escape_string('[\"143\"]'), '������˹̹': pymysql.escape_string('[\"144\"]'), '���������': pymysql.escape_string('[\"145\"]'), '������˹̹': pymysql.escape_string('[\"146\"]'), '�ݿ�': pymysql.escape_string('[\"147\"]'), '�ޱ���': pymysql.escape_string('[\"148\"]'), '������': pymysql.escape_string('[\"149\"]'), '�ڸɴ�': pymysql.escape_string('[\"150\"]'), '��������': pymysql.escape_string('[\"151\"]'), '��϶��': pymysql.escape_string('[\"152\"]'), '����': pymysql.escape_string('[\"153\"]'), '��Ͳ�Τ': pymysql.escape_string('[\"154\"]'), '����կ': pymysql.escape_string('[\"155\"]'), '������': pymysql.escape_string('[\"156\"]'), '����': pymysql.escape_string('[\"157\"]'), '�չ�������': pymysql.escape_string('[\"158\"]'), '������': pymysql.escape_string('[\"159\"]'), '����͢': pymysql.escape_string('[\"160\"')}
    def __init__(self):
        self.session=requests.session()
    def geturls(self):
        res=self.session.get("http://www.chinatax.gov.cn/n810341/n810770/index.html",headers=self.headers)
        res.encoding="utf-8"
        time.sleep(6)
        #<p class="ntop4" align="center"><a target="_blank" href="/n810341/n810770/c1794734/content.html">̨��<br />
        urls=re.findall('<a target="_blank" href="/n810341/n810770/.*?content.html".*?<',res.text)
        #print(res.text)
        return urls

    def getpage(self,urls):
        for url in urls:
            time.sleep(3)
            try:
                time.sleep(10)
                item_url=re.findall('href="(.*?)".*?>(.*?)<',url)[0]
                if item_url[1]:
                    cuntry=item_url[1]
                    item_url="http://www.chinatax.gov.cn"+item_url[0]

                    res=self.session.get(item_url,headers=self.headers)
                    res.encoding="utf-8"
                    item=self.getdetail(res)

                    item["type_list"] = self.cuntrylist[cuntry]
                    item["type_name"]='�����,ȫ������,���ʷ�Χ,'+cuntry
                    Db = Dbmysql()
                    res = Db.dbs(item)
                    if res == 0:
                        db = dbmysql()
                        db.dbs(item)
            except Exception as e:
                logging.info(e)

    def getdetail(self,res):

        #print(res.text)
        try:
            content=re.findall('<li class="sv_texth3" id="tax_content">(.*?)</li>',res.text,re.S)[0]
            #print(content)
            content=content.replace('../../../',"http://www.chinatax.gov.cn/").replace("\n","").replace("\r\n","")
            title=re.search('<li class="sv_texth1" >(.*?)</li>',res.text).group(1)
            date=re.search('<div class="zuo1">(.*?)��',res.text).group(1)
            date=date.replace("��","-").replace("��","-")
            uid = str(int(random.random() * 1000000000000))
            item = {}
            item["library"] = pymysql.escape_string('[\"5\"]')
            item["url"] = res.url
            item["title"] = title
            item["content"] = content.replace('"',"'").replace("&ldquo;","").replace("&rdquo;","").replace("&lsquo;","").replace("&rsquo;","").replace("&ensp;","").replace("&hellip;","").replace("&mdash;","").replace("\r\n","")
            item["describes"] = title
            item["type"] = pymysql.escape_string('[\"44\"]')
            item["column_list_id"] = pymysql.escape_string('[\"100\"]')
            item["need_login"] = 0
            item["date"] = date
            item["uid"] = uid
            item["create_time"] = self.nowtime
            item["update_at"] = self.nowtime
            item["source"]="����˰���ܾ�"
            return item
        except Exception as e:
            logging.info(e)
Esempio n. 32
0
                            password="******",
                            db="runoob")

cursor = db.cursor()
remote_cursor = remote_db.cursor()

sql = "show tables;"
cursor.execute(sql)

result = cursor.fetchall()
for i in result[:-1]:
    print(i["Tables_in_runoob"])

create_sql = """create table  %s (`id` int auto_increment, `question` text, `answer` text, `link` text, primary key(`id`)) engine = Innodb default charset = utf8;"""
truncate_sql = """truncate table %s"""
insert_sql = """insert into %s (question ,answer, link) value ("%s","%s", "%s")"""

for i in result[:-1]:
    table_name = i['Tables_in_runoob']
    remote_cursor.execute(truncate_sql % table_name)
    print(table_name)
    sql2 = "select question,answer,link from %s" % table_name
    cursor.execute(sql2)
    items = cursor.fetchall()
    for j in items:
        # print(j['question'], j['answer'], j['link'])
        remote_cursor.execute(
            insert_sql % (table_name, pymysql.escape_string(
                j['question']), pymysql.escape_string(j['answer']), j['link']))
    remote_db.commit()
    # break
Esempio n. 33
0
 def dataFormatting(self, item):
     for key in item.keys():
         item[key] = pymysql.escape_string(item[key])
     return item
Esempio n. 34
0
 def escape(self, string):
     return pymysql.escape_string(string)
Esempio n. 35
0
def update_chapter(data):
    sql = "update chapter set chapter_id = '{0}',chapter_name='{1}' WHERE zj_chapter_id = {2};"
    for d in data:
        print(sql.format(d[1], pymysql.escape_string(d[0]), d[2]))
def insert_data_to_mysql(asin_dict):
    try:
        try:
            asin = asin_dict["asin"]
            print(asin)
        except:
            pass
        try:
            url = asin_dict["url"]
            print(url)
        except:
            pass
        try:
            brand = asin_dict["brand"]
            brand = pymysql.escape_string(brand)
            print(brand)
        except:
            pass
        try:
            badge = asin_dict["badge"]
            badge = pymysql.escape_string(badge)
            print("bage: ", badge)
        except:
            pass
        try:
            title = asin_dict["title"]
            title = pymysql.escape_string(title)
            print("title: ", title)
        except:
            pass
        try:
            variation_name = asin_dict["variation_name"]
            variation_name = pymysql.escape_string(variation_name)
            print(variation_name)
        except:
            pass
        try:
            price = asin_dict["price"]
            print(price)
        except:
            pass
        try:
            sold_by = asin_dict["sold_by"]
            sold_by = pymysql.escape_string(sold_by)
            print(sold_by)
        except:
            pass
        try:
            how_many_sellers = asin_dict["how_many_sellers"]
            how_many_sellers = pymysql.escape_string(how_many_sellers)
        except:
            pass
        try:
            bullets = asin_dict["bullets"]
            # print(bullets)

            bullet_1 = " "
            bullet_2 = " "
            bullet_3 = " "
            bullet_4 = " "
            bullet_5 = " "
            if bullets:
                try:
                    bullet_1 = bullets[0]
                    bullet_1 = pymysql.escape_string(bullet_1)
                except:
                    pass
                try:
                    bullet_2 = bullets[1]
                    bullet_2 = pymysql.escape_string(bullet_2)
                except:
                    pass
                try:
                    bullet_3 = bullets[2]
                    bullet_3 = pymysql.escape_string(bullet_3)
                except:
                    pass
                try:
                    bullet_4 = bullets[3]
                    bullet_4 = pymysql.escape_string(bullet_4)
                except:
                    pass
                try:
                    bullet_5 = bullets[4]
                    bullet_5 = pymysql.escape_string(bullet_5)
                except:
                    pass
        except:
            pass

        print("bullet_1: ", bullet_1)
        print("bullet_2: ", bullet_2)
        print("bullet_3: ", bullet_3)
        print("bullet_4: ", bullet_4)
        print("bullet_5: ", bullet_5)
        try:
            description = asin_dict["description"]
            description = pymysql.escape_string(description)
            print(description)
        except:
            pass
        try:
            salesrank = asin_dict["salesrank"]
            print(salesrank)
        except:
            pass
        try:
            review_num = asin_dict["review_num"]
            print(review_num)
        except:
            pass
        try:
            review_value = asin_dict["review_value"]
            print(review_value)
        except:
            pass
        try:
            qa_num = asin_dict["qa_num"]
            print(qa_num)
        except:
            pass
        try:
            picture_url = asin_dict["picture_url"]
            print(picture_url)
        except:
            pass
        try:
            insert_datetime = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            print("insert_datetime: ", insert_datetime)

            insert_into_sql = "INSERT INTO " + table_name + \
            " (asin, insert_datetime, url, brand, badge, title, variation_name, price, sold_by, how_many_sellers, bullet_1, bullet_2, bullet_3, bullet_4, bullet_5, description, salesrank, review_num, review_value, qa_num, picture_url ) \
            VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ) "                                                                                                                                                     % \
            (asin, insert_datetime, url, brand, badge, title, variation_name, price, sold_by, how_many_sellers, bullet_1, bullet_2, bullet_3, bullet_4, bullet_5, description, salesrank, review_num, review_value, qa_num, picture_url )

            cursor.execute(insert_into_sql)
            print(cursor)
            conn.commit()
            print("success to insert asin_dict to mysql")
        except:
            pass
    except:
        print("fail to insert asin_dict to mysql!")
Esempio n. 37
0
def parse_html(html_str, court_name, dc):
    try:
        soup = BeautifulSoup(html_str, "html.parser")
        tr_list = soup.find_all('tr')

        tr_count = 0
        for tr in tr_list:

            emergency_exit = select_one_query(
                "SELECT emergency_exit FROM Tracker WHERE Name='" +
                court_name + "'")
            if emergency_exit is not None:
                if emergency_exit['emergency_exit'] == 1:
                    break

            tr_count += 1
            if tr_count == 1:
                continue

            case_no = "NULL"
            petitioner = "NULL"
            respondent = "NULL"
            judgment_date = "NULL"
            corrigendum = "NULL"
            pdf_data = "NULL"
            pdf_file = "NULL"
            # insert_check = False

            tr_soup = BeautifulSoup(str(tr), "html.parser")
            td_list = tr_soup.find_all('td')

            i = 0
            for td in td_list:
                i += 1
                if i == 1:
                    continue

                if i == 2:
                    a_tag = BeautifulSoup(str(td), "html.parser").a
                    pdf_file = base_url + a_tag.get('href')
                    case_no = str(a_tag.text).replace("\n", "")
                    pdf_data = escape_string(
                        request_pdf(pdf_file, case_no, court_name))

                if i == 3:
                    span_tag = BeautifulSoup(str(td), "html.parser").span
                    judgment_date = escape_string(
                        str(span_tag.decode_contents()))

                # if select_count_query(str(court_name), str(case_no), 'judgment_date', judgment_date):
                #     insert_check = True

                if i == 5:
                    span_tag = BeautifulSoup(str(td), "html.parser").span
                    corrigendum = escape_string(str(
                        span_tag.decode_contents()))

                if i == 4:
                    td_soup = BeautifulSoup(str(td), "html.parser")
                    span_list = td_soup.find_all('span')

                    j = 0
                    for span in span_list:
                        j += 1

                        if j == 1:
                            petitioner = escape_string(
                                str(span.decode_contents()))
                        if j == 3:
                            respondent = escape_string(
                                str(span.decode_contents()))

            # if case_no != "NULL" and insert_check:
            if case_no != "NULL":
                sql_query = "INSERT INTO " + str(court_name) + " (case_no, petitioner, respondent, judgment_date, " \
                                                               "corrigendum, pdf_file, bench_code, pdf_filename) VALUE"\
                                                               " ('" + case_no + "', '" + petitioner + "', '" + \
                            respondent + "', '" + judgment_date + "', '" + corrigendum + "', '" + pdf_file + "', " + \
                            str(dc) + ", '" + court_name + "_" + slugify(case_no) + ".pdf')"
                insert_query(sql_query)

                update_query("UPDATE " + court_name + " SET pdf_data = '" +
                             str(pdf_data) + "' WHERE case_no = '" +
                             str(case_no) + "'")
                update_query(
                    "UPDATE Tracker SET No_Cases = No_Cases + 1 WHERE Name = '"
                    + str(court_name) + "'")

        return True

    except Exception as e:
        logging.error("Failed to parse the html: %s", e)
        update_query(
            "UPDATE Tracker SET No_Error = No_Error + 1 WHERE Name = '" +
            str(court_name) + "'")
        return False