def get_stock_basic(stock_num):
if stock_num.find("00") == 0 or stock_num.find("30") == 0:
stock_num = 'sz' + stock_num
elif stock_num.find("60") == 0:
stock_num = 'sh' + stock_num
url = "http://gu.qq.com/" + stock_num + "/gp"
funcset.log("读取URL:" + url)
ws = ws_base.WS(url, get_data_func, None, None, None, "utf-8")
return ws.get_data()
def insert_org(orgname):
conn = opendb()
cur = conn.cursor()
sql = ""
try:
sql = "INSERT INTO ORG(ORGNAME) VALUES (?)"
cur.execute(sql, (orgname, ))
conn.commit()
except Exception as e:
conn.rollback()
funcset.log("Execution is failed: " + sql + " with (" + orgname + ")")
funcset.log(e)
finally:
cur.close()
conn.close()
def cleanup_stock():
conn = opendb()
cur = conn.cursor()
sql = ""
try:
sql = "DELETE FROM stock"
cur.execute(sql)
conn.commit()
except Exception as e:
conn.rollback()
funcset.log("Execution is failed: " + sql)
funcset.log(e)
finally:
cur.close()
conn.close()
def insert_stock(stock_dict):
conn = opendb()
cur = conn.cursor()
sql = ""
try:
sql = "INSERT INTO stock VALUES (?,?)"
for k, v in stock_dict.items():
cur.execute(sql, (k, v))
conn.commit()
except Exception as e:
conn.rollback()
funcset.log("Execution is failed: " + sql + " with stock_dict")
funcset.log(e)
finally:
cur.close()
conn.close()
def delete_stock_records(start_date, end_date):
conn = opendb()
cur = conn.cursor()
sql = ""
try:
sql = "DELETE FROM RECORDS WHERE RECDATE >= ? AND RECDATE <= ?"
cur.execute(
sql,
(start_date.strftime("%Y-%m-%d"), end_date.strftime("%Y-%m-%d")))
conn.commit()
except Exception as e:
conn.rollback()
funcset.log("Execution is failed: " + sql)
funcset.log(e)
finally:
cur.close()
conn.close()
def insert_stock_records(stock_records_list):
for sr in stock_records_list:
conn = opendb()
cur = conn.cursor()
sql = ""
try:
sql = "INSERT INTO RECORDS VALUES (?,?,?,?,?,?)"
cur.execute(sql, (sr.get_date(), sr.get_stockid(), sr.get_orgid(),
sr.get_reason(), sr.get_url(), sr.get_source()))
conn.commit()
except Exception as e:
conn.rollback()
funcset.log("Execution is failed: " + sql)
funcset.log(e)
finally:
cur.close()
conn.close()
def get_51pdf_buy(url, html_text, start_date, end_date, stock_dict, org_dict):
final = []
stop = False
try:
bs = BeautifulSoup(html_text, "html.parser")
body = bs.body
data = body.find('div', {'class': 'morelist'})
table = data.find('table')
tr = table.find_all("tr")
org_list = get_stock_list.get_existing_org_list()
if len(tr) == 2:
return [final, True]
for item in tr:
td = item.find_all("td")
if len(td) != 4:
continue
aa = td[0].find("a")
if True:
temp_date = td[3].text.strip()
temp_date = temp_date[0:4] + "-" + temp_date[
4:6] + "-" + temp_date[6:8]
aarray = aa.text.strip().split("-", 1)
if len(aarray) == 1:
funcset.log("something wrong on: " + aa.text)
continue
sa = get_stock_list.check_stock_exists_in_string(
stock_dict, aarray[1].strip(),
"http://www.51pdf.cn" + aa["href"])
if len(sa) == 0:
continue
organization = aarray[0].strip()
orgid = get_stock_list.check_valid_org_num(
org_dict, organization)
if orgid is None:
funcset.log("Add new org: " + organization)
wssrdb.insert_org(organization)
orgid = wssrdb.get_orgid(organization)
org_dict[
orgid] = organization # add new org into the org_dict
report_date = datetime.datetime.strptime(temp_date, "%Y-%m-%d")
stop = report_date < start_date
if end_date >= report_date >= start_date:
for s in sa:
s.set_orgid(orgid)
s.set_date(temp_date)
s.set_source("51pdf report")
final.append(s)
wssrdb.insert_stock_records(final)
except Exception as e:
funcset.log(url + "崩溃了")
funcset.log(e)
return [final, stop]
def get_org():
org_list = {}
conn = opendb()
cur = conn.cursor()
sql = ""
try:
sql = "SELECT ORGID, ORGNAME FROM ORG"
results = cur.execute(sql)
all = results.fetchall()
for r in all:
org_list[r[0]] = r[1]
conn.commit()
except Exception as e:
conn.rollback()
funcset.log("Execution is failed: " + sql)
funcset.log(e)
finally:
cur.close()
conn.close()
return org_list
def get_stock():
stock_list = {}
conn = opendb()
cur = conn.cursor()
sql = ""
try:
sql = "SELECT STOCKID, STOCKNAME FROM STOCK WHERE SUBSTR(STOCKID, 1, 2) IN ('00', '60', '30')"
results = cur.execute(sql)
all = results.fetchall()
for r in all:
stock_list[r[0]] = r[1]
conn.commit()
except Exception as e:
conn.rollback()
funcset.log("Execution is failed: " + sql)
funcset.log(e)
finally:
cur.close()
conn.close()
return stock_list
def get_trend(stockid, start_date, end_date):
trend_info = {}
conn = opendb()
cur = conn.cursor()
sql = ""
try:
sql = "SELECT RECDATE, RECCOUNT FROM STOCK_REC WHERE RECDATE >= ? AND RECDATE <= ? AND STOCKID = ? ORDER BY RECDATE"
results = cur.execute(sql, (start_date.strftime("%Y-%m-%d"),
end_date.strftime("%Y-%m-%d"), stockid))
all = results.fetchall()
for r in all:
trend_info[r[0]] = r[1]
conn.commit()
except Exception as e:
conn.rollback()
funcset.log("Execution is failed: " + sql)
funcset.log(e)
finally:
cur.close()
conn.close()
return trend_info
def top_recommend_stock_info(stockid, org_dict, start_date, end_date):
top_stock_org_info_list = []
conn = opendb()
cur = conn.cursor()
sql = ""
try:
sql = "SELECT RECDATE, ORGID, REASON, URL FROM (SELECT * FROM RECORDS WHERE RECDATE >= ? AND RECDATE <= ? AND STOCKID = ?) GROUP BY RECDATE, ORGID, REASON, URL ORDER BY RECDATE DESC"
results = cur.execute(sql, (start_date.strftime("%Y-%m-%d"),
end_date.strftime("%Y-%m-%d"), stockid))
all = results.fetchall()
for r in all:
top_stock_org_info_list.append((r[0], org_dict[r[1]], r[2], r[3]))
conn.commit()
except Exception as e:
conn.rollback()
funcset.log("Execution is failed: " + sql)
funcset.log(e)
finally:
cur.close()
conn.close()
return top_stock_org_info_list
def top_recommend(stock_dict, start_date, end_date, top_count):
top_list = []
conn = opendb()
cur = conn.cursor()
sql = ""
try:
sql = "SELECT STOCKID, SUM(RECCOUNT) RC FROM (SELECT * FROM STOCK_REC WHERE RECDATE >= ? AND RECDATE <= ?) GROUP BY STOCKID ORDER BY RC DESC LIMIT ?"
results = cur.execute(sql, (start_date.strftime("%Y-%m-%d"),
end_date.strftime("%Y-%m-%d"), top_count))
all = results.fetchall()
for r in all:
top_list.append((r[0], stock_dict[r[0]], r[1]))
conn.commit()
except Exception as e:
conn.rollback()
funcset.log("Execution is failed: " + sql)
funcset.log(e)
finally:
cur.close()
conn.close()
return [top_list, (start_date, end_date)]
def get_orgid(org_name):
conn = opendb()
cur = conn.cursor()
sql = ""
orgid = 0
orgid_arr = ()
try:
sql = "SELECT ORGID FROM ORG WHERE ORGNAME=?"
results = cur.execute(sql, (org_name, ))
orgid_arr = results.fetchone()
conn.commit()
except Exception as e:
conn.rollback()
funcset.log("Execution is failed: " + sql)
funcset.log(e)
finally:
cur.close()
conn.close()
if len(orgid_arr) > 0:
orgid = orgid_arr[0]
return orgid
def get_stock(url, html_text, start_date, end_date, stock_list):
final = {}
try:
bs = BeautifulSoup(html_text, "html.parser") # 创建BeautifulSoup对象
body = bs.body # 获取body部分
data = body.find('div', {'id': 'quotesearch'}) # 找到id为quotesearch的div
ul = data.find_all('ul') # 获取ul部分
for site in ul:
li = site.find_all('li') # 获取所有的li
for stock in li:
a = stock.find_all("a")
for valid_item in a:
temp = []
vi = valid_item.string.split("(")
if len(vi) == 2:
temp.append(vi[1].replace(")", "").strip())
temp.append(vi[0].strip())
final[temp[0]] = temp[1]
wssrdb.cleanup_stock()
wssrdb.insert_stock(final)
except Exception as e:
funcset.log("Error happened " + str(e))
return final