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()
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 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
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 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 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()
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 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 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 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}))
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")))
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 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 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"]
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)
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 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()
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()
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);
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)
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
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
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)
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("“","").replace("”","").replace("‘","").replace("’","").replace(" ","").replace("…","").replace("—","").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)
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
def dataFormatting(self, item):
for key in item.keys():
item[key] = pymysql.escape_string(item[key])
return item
def escape(self, string):
return pymysql.escape_string(string)
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!")
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