def dealbook():
	rootdir='book'
	prefix='.html'
	database = Mysql(host="localhost", user="root", 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

    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

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

 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="nycapt", passwd="[email protected]", 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()

    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

    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

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

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

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)

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

    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

    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)

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

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

	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"]

	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)

 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

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