def update_cb_index():
mid_price, temp, avg_premium = calc_mid_data_with_avg_premium()
# 检查当天记录已经存在, 存在则更新
cur = get_cursor(
"select count(*) from cb_index_history where strftime('%Y-%m-%d', date) = strftime('%Y-%m-%d', date())"
)
one = cur.fetchone()
if one[0] == 0:
count = execute_sql_with_rowcount(
"""insert into cb_index_history(mid_price, avg_premium) values(:mid_price, :avg_premium)""",
{
'mid_price': mid_price,
'avg_premium': avg_premium
})
if count == 1:
print('insert today\'s mid_price is successful.')
else:
count = execute_sql_with_rowcount(
"""update cb_index_history set mid_price=:mid_price, avg_premium=:avg_premium where strftime('%Y-%m-%d', date) = strftime('%Y-%m-%d', date())""",
{
'mid_price': mid_price,
'avg_premium': avg_premium
})
if count == 1:
print('update today\'s mid_price is successful.')
def do_update_stock_theme(driver, task_name):
# 遍历可转债列表
task = None
try:
# 查询可转债
bond_cursor = get_cursor(
"""SELECT bond_code, cb_name_id, stock_code, stock_name from changed_bond"""
)
rows = bond_cursor.fetchall()
task, status = new_or_update_task(len(rows), task_name)
if status == -1: # 有任务在执行
return
i = 0
for bond_row in rows:
bond_code = bond_row[0]
stock_code = bond_row[2]
stock_name = bond_row[3]
process_task_when_normal(task)
theme = fetch_stock_theme(driver, stock_code)
rowcount = execute_sql_with_rowcount(
"""update changed_bond_extend set theme = :theme where bond_code = :bond_code""",
{
'theme': theme,
'bond_code': bond_code
})
if rowcount == 0:
print("not update stock:" + stock_name +
' in changed_bond_extend')
else:
print("update " + stock_name + " is successful. count:" +
str(i + 1))
# 暂停5s再执行， 避免被网站屏蔽掉
time.sleep(5)
i += 1
ok_desc = "共处理" + str(i) + "条记录"
print(ok_desc)
process_task_when_finish(task, ok_desc)
except Exception as e:
print("db操作出现异常" + str(e), e)
process_task_when_error(task, "db操作出现异常")
except TimeoutError as e:
print("网络超时, 请手工重试")
process_task_when_error(task, "网络超时")
def do_update_data_before_trade_is_start():
# 检查是否交易日
if trade_utils.is_trade_date() is False:
return 'OK'
# 初始化today_sum_buy, 将昨天收盘市值作为今天的开盘市值
rowcount = db_utils.execute_sql_with_rowcount(
"""
update hold_bond set modify_date = :modify_date, today_sum_sell = 0,
today_sum_buy = (select round(c.cb_price2_id*hold_bond.hold_amount,2)
from changed_bond c where c.bond_code = hold_bond.bond_code)
""", {"modify_date": datetime.now()})
if rowcount > 0:
print('init today_sum_buy is successful. count:' + str(rowcount))
# 更新一下正股诊断信息
stock_10jqka.fetch_data()
return 'OK'
def modify_data_unit_error():
# 遍历可转债列表
try:
i = 0
stock_cursor = get_cursor(
"""SELECT avg_net_margin, stock_name from stock_report""")
stocks = list(stock_cursor)
for stock in stocks:
avg_net_margin = stock[1]
stock_name = stock[2]
if not isinstance(avg_net_margin,
str) or not avg_net_margin.endswith('万'):
continue
avg_net_margin = change_data_unit(avg_net_margin, no_clean_text)
rowcount = execute_sql_with_rowcount(
"""update stock_report set avg_net_margin = :avg_net_margin where stock_name = :stock_name""",
{
'avg_net_margin': avg_net_margin,
'stock_name': stock_name
})
i += 1
if rowcount == 0:
print("update " + stock_name + " is failure. count:" + str(i))
else:
print("update " + stock_name + " is successful. count:" +
str(i))
print("共处理" + str(i) + "条记录")
except Exception as e:
print("db操作出现异常" + str(e), e)
except TimeoutError as e:
print("网络超时, 请手工重试")
def update_db(rows):
try:
i = 0
for row in rows:
cur = get_cursor(
"select count(*) from changed_bond where bond_code=:bond_code",
{'bond_code': row['bond_code']})
one = cur.fetchone()
if one[0] == 0:
print("not update cb:" + row['cb_name_id'])
continue
# execute执行脚本
rowcount = execute_sql_with_rowcount(
"""update changed_bond
set cb_price2_id = :cb_price2_id,
cb_mov2_id = :cb_mov2_id,
cb_mov_id = :cb_mov_id,
stock_price_id = :stock_price_id,
cb_premium_id = :cb_premium_id
where bond_code = :bond_code""", {
'cb_price2_id': row.get('cb_price2_id', None),
'cb_mov2_id': row.get('cb_mov2_id', None),
'cb_mov_id': row.get('cb_mov_id', None),
'stock_price_id': row.get('stock_price_id', None),
'cb_premium_id': row.get('cb_premium_id', None),
'bond_code': row['bond_code']
})
if rowcount == 0:
print("not update cb:" + str(row))
else:
print("update cb:" + str(row))
i += 1
print('Successfully updated row count:' + str(i))
except Exception as e:
print("db操作出现异常", e)
raise e
def update_db(rows):
try:
for row in rows:
# execute执行脚本
rowcount = execute_sql_with_rowcount(
"""update changed_bond
set cb_price2_id = :cb_price2_id,
cb_mov2_id = :cb_mov2_id,
cb_mov_id = :cb_mov_id,
stock_price_id = :stock_price_id,
cb_value_id = :cb_value_id,
cb_premium_id = :cb_premium_id,
remain_amount = :remain_amount,
cb_trade_amount_id = :cb_trade_amount_id,
cb_trade_amount2_id = :cb_trade_amount2_id
--,
--bt_yield = :bt_yield
where bond_code = :bond_code""",
{
'cb_price2_id': row['cb_price2_id'],
'cb_mov2_id': row['cb_mov2_id'],
'cb_mov_id': row['cb_mov_id'],
'stock_price_id': row['stock_price_id'],
'cb_value_id': row['cb_value_id'],
'cb_premium_id': row['cb_premium_id'],
'remain_amount': row['remain_amount'],
'cb_trade_amount_id': row['cb_trade_amount_id'],
'cb_trade_amount2_id': row.get('cb_trade_amount2_id'),
#row["bt_yield"],
'bond_code': row['bond_code']
})
if rowcount == 0:
print("not update cb:" + row['cb_name_id'])
except Exception as e:
print("db操作出现异常", e)
raise e
def fetch_data(task_name):
# 遍历可转债列表
stock_name = ''
stock_code = None
earnings = None
task = None
try:
# 当前报告期
report_date = get_report_date()
# 查询可转债
bond_cursor = get_cursor("""
SELECT bond_code, cb_name_id, stock_code, stock_name from changed_bond
""")
rows = bond_cursor.fetchall()
task, status = new_or_update_task(len(rows), task_name)
if status == -1: # 有任务在执行
return
i = 0
for bond_row in rows:
process_task_when_normal(task, 1)
stock_code = bond_row[2]
stock_name = bond_row[3]
stock_cursor = get_cursor(
"SELECT last_date from stock_report where stock_code = '" +
stock_code + "'")
stocks = list(stock_cursor)
# 还没添加正股财务指标信息
if len(stocks) == 0:
earnings = get_earnings(stock_code)
# 新增
rowcount = db_utils.execute_sql_with_rowcount(
"""insert into stock_report(stock_code,stock_name,
last_date,
revenue,qoq_revenue_rate,yoy_revenue_rate,
net,qoq_net_rate,yoy_net_rate,
margin,qoq_margin_rate,yoy_margin_rate,
roe,qoq_roe_rate,yoy_roe_rate,
al_ratio,qoq_rl_ratio_rate,yoy_al_ratio_rate)
values(:stock_code,:stock_name,
:last_date,
:revenue,:qoq_revenue_rate,:yoy_revenue_rate,
:net,:qoq_net_rate,:yoy_net_rate,
:margin,:qoq_margin_rate,:yoy_margin_rate,
:roe,:qoq_roe_rate,:yoy_roe_rate,
:al_ratio,:qoq_rl_ratio_rate,:yoy_al_ratio_rate)""",
{
'stock_code': bond_row[2],
'stock_name': bond_row[3],
'last_date': earnings.lastDate,
'revenue': earnings.revenue,
'qoq_revenue_rate': earnings.qoqRevenueRate,
'yoy_revenue_rate': earnings.yoyRevenueRate,
'net': earnings.net,
'qoq_net_rate': earnings.qoqNetRate,
'yoy_net_rate': earnings.yoyNetRate,
'margin': earnings.margin,
'qoq_margin_rate': earnings.qoqMarginRate,
'yoy_margin_rate': earnings.yoyMarginRate,
'roe': earnings.roe,
'qoq_roe_rate': earnings.qoqRoeRate,
'yoy_roe_rate': earnings.yoyRoeRate,
'al_ratio': earnings.alRatio,
'qoq_rl_ratio_rate': earnings.qoqAlRatioRate,
'yoy_al_ratio_rate': earnings.yoyAlRatioRate,
})
if rowcount == 0:
print("insert " + stock_name + " is failure. count:" +
str(i + 1))
else:
print("insert " + stock_name + " is successful. count:" +
str(i + 1))
else:
# todo 补充前面几年/季度的财务数据
# fixme 为了解决中途被中断， 继续执行时， 简化处理不更新
# continue
last_date = stocks[0][0]
if last_date != report_date:
earnings = get_earnings(stock_code)
rowcount = db_utils.execute_sql_with_rowcount(
"""update stock_report
set last_date = :last_date,
revenue = :revenue,qoq_revenue_rate = :qoq_revenue_rate,yoy_revenue_rate = :yoy_revenue_rate,
net = :net,qoq_net_rate = :qoq_net_rate,yoy_net_rate = :yoy_net_rate,
margin = :margin,qoq_margin_rate = :qoq_margin_rate,yoy_margin_rate = :yoy_margin_rate,
roe = :roe,qoq_roe_rate = :qoq_roe_rate,yoy_roe_rate = :yoy_roe_rate,
al_ratio = :al_ratio,qoq_rl_ratio_rate = :qoq_rl_ratio_rate,yoy_al_ratio_rate = :yoy_al_ratio_rate
where stock_code = :stock_code""", {
'last_date': earnings.lastDate,
'revenue': earnings.revenue,
'qoq_revenue_rate': earnings.qoqRevenueRate,
'yoy_revenue_rate': earnings.yoyRevenueRate,
'net': earnings.net,
'qoq_net_rate': earnings.qoqNetRate,
'yoy_net_rate': earnings.yoyNetRate,
'margin': earnings.margin,
'qoq_margin_rate': earnings.qoqMarginRate,
'yoy_margin_rate': earnings.yoyMarginRate,
'roe': earnings.roe,
'qoq_roe_rate': earnings.qoqRoeRate,
'yoy_roe_rate': earnings.yoyRoeRate,
'al_ratio': earnings.alRatio,
'qoq_rl_ratio_rate': earnings.qoqAlRatioRate,
'yoy_al_ratio_rate': earnings.yoyAlRatioRate,
'stock_code': stock_code
})
if rowcount == 0:
print("update " + stock_name + " is failure. count:" +
str(i + 1))
else:
print("update " + stock_name +
" is successful. count:" + str(i + 1))
# 暂停3s再执行， 避免被网站屏蔽掉
time.sleep(3)
i += 1
ok_desc = "共处理" + str(i) + "条记录"
print(ok_desc)
process_task_when_finish(task, ok_desc)
except Exception as e:
print(
"db操作出现异常" + str(e) + ', stock_code: ' + stock_code +
', stock_name:' + stock_name + ', earnings:' + str(earnings), e)
process_task_when_error(task, "db操作出现异常")
raise e
return 'OK'
def do_fetch_data(driver, task_name):
task = None
try:
# 遍历整个可转债列表, 拿到bond_num
bond_cursor = get_cursor(
"""SELECT data_id, bond_code, cb_name_id from changed_bond""")
rows = bond_cursor.fetchall()
task, status = new_or_update_task(len(rows), task_name)
if status == -1: # 有任务在执行
return
i = 0
j = 0
for bond_row in rows:
process_task_when_normal(task, 1)
num_id = bond_row[0]
bond_code = bond_row[1]
bond_name = bond_row[2]
bond_ex_cursor = get_cursor(
"""SELECT id, bond_name from changed_bond_extend where bond_num = :num_id""",
{
'num_id': num_id,
})
ex_list = list(bond_ex_cursor)
if len(ex_list) == 0:
# 检查是否存在extend信息, 没有则去抓数据
row = getContent(driver, num_id)
# 插入数据
get_cursor(
"""insert into changed_bond_extend(bond_num, bond_code,
interest, ensure, enforce_get_term, buy_back_term, down_revise_term)
values(:bond_num, :bond_code,
:interest, :ensure, :enforce_get_term, :buy_back_term, :down_revise_term)""",
{
'bond_num': num_id,
'bond_code': bond_code,
'interest': row.get('interest'),
'ensure': row.get('ensure'),
'enforce_get_term': row.get('enforce_get_term'),
'buy_back_term': row.get('buy_back_term'),
'down_revise_term': row.get('down_revise_term'),
})
print("insert " + bond_name + " is successful. count:" +
str(i + 1))
i += 1
# 暂停5s再执行， 避免被网站屏蔽掉
time.sleep(5)
elif ex_list[0][1] is None:
rowcount = execute_sql_with_rowcount(
"""update changed_bond_extend set bond_name = :bond_name where bond_num = :num_id""",
{
'bond_name': bond_name,
'num_id': num_id
})
if rowcount == 0:
print("update " + bond_name + " is failure. count:" +
str(j + 1))
else:
print("update " + bond_name + " is successful. count:" +
str(j + 1))
j += 1
ok_desc = "共处理" + str(i + j) + "条记录"
print(ok_desc)
process_task_when_finish(task, ok_desc)
except Exception as e:
print("db操作出现异常" + str(e), e)
process_task_when_error(task, "db操作出现异常")
raise e
except TimeoutError as e:
print("网络超时, 请手工重试")
process_task_when_error(task, "网络超时")
raise e
def update_stock_sum(driver, task_name):
# 遍历可转债列表
task = None
try:
# 查询可转债
bond_cursor = get_cursor(
"""SELECT bond_code, cb_name_id, stock_code, stock_name from changed_bond"""
)
rows = bond_cursor.fetchall()
task, status = new_or_update_task(len(rows), task_name)
if status == -1: # 有任务在执行
return
# 当前日月
y = datetime.datetime.now().year
m = datetime.datetime.now().month
d = datetime.datetime.now().day
t = datetime.datetime(y, m, d)
# 记录更新时间(秒)
s = (t - datetime.datetime(1970, 1, 1)).total_seconds()
i = 0
for bond_row in rows:
process_task_when_normal(task, 1)
stock_code = bond_row[2]
stock_name = bond_row[3]
stock_cursor = get_cursor(
"""SELECT modify_date from stock_report where stock_code = :stock_code""",
{'stock_code': stock_code})
stocks = list(stock_cursor)
if len(stocks) == 0:
continue
# 已经更新了
if stocks[0][0] is not None and stocks[0][0] >= s:
continue
row = get_stock_sum(driver, stock_code)
rowcount = execute_sql_with_rowcount(
"""update stock_report set
stock_total = :stock_total,
stock_level = :stock_level,
trade_suggest = :trade_suggest,
fact_trend = :fact_trend,
fact_money = :fact_money,
fact_news = :fact_news,
fact_industry = :fact_industry,
fact_base = :fact_base,
modify_date = :modify_date where stock_code = :stock_code""", {
'stock_total': row['stock_total'],
'stock_level': row['stock_level'],
'trade_suggest': row['trade_suggest'],
'fact_trend': row['fact_trend'],
'fact_money': row['fact_money'],
'fact_news': row['fact_news'],
'fact_industry': row['fact_industry'],
'fact_base': row['fact_base'],
'modify_date': s,
'stock_code': stock_code
})
if rowcount == 0:
print("not update stock:" + stock_name)
else:
print("update_diagnostic " + stock_name +
" is successful. count:" + str(i + 1))
# 暂停5s再执行， 避免被网站屏蔽掉
time.sleep(3)
i += 1
ok_desc = "共处理" + str(i) + "条记录"
print(ok_desc)
process_task_when_finish(task, ok_desc)
except Exception as e:
print("db操作出现异常" + str(e), e)
process_task_when_error(task, "db操作出现异常")
raise e
except TimeoutError as e:
print("网络超时, 请手工重试")
process_task_when_error(task, "网络超时")
raise e
def update_theme_config(driver):
# 打开配置文件数据库表
# 打开东财题材列表(5日, 10日)页面, 抓取数据
# 删除已有的题材概念信息
# 更新题材概念信息
# 关闭数据库
try:
# 删除已有的数据
rowcount = execute_sql_with_rowcount(
"""delete from config where key = 'gn_1' """)
if rowcount == 0:
print("not delete gn_1 config")
else:
print("delete old gn_1 is successful. count:" + str(rowcount))
# 删除已有的数据
rowcount = execute_sql_with_rowcount(
"""delete from config where key = 'gn_5' """)
if rowcount == 0:
print("not delete gn_5 config")
else:
print("delete old gn_5 is successful. count:" + str(rowcount))
rowcount = execute_sql_with_rowcount(
"""delete from config where key = 'gn_10' """)
if rowcount == 0:
print("not delete gn_10 config")
else:
print("delete old gn_10 is successful. count:" + str(rowcount))
i = 0
# 添加当日数据
rows = fetch_theme_data(driver, 3, '')
for row in rows:
rowcount = execute_sql_with_rowcount(
"""insert into config(order_index,key,value,field_name)values(:order_index, 'gn_1',:value, 'theme')""",
{
'order_index': row[0],
'value': row[1]
})
if rowcount == 0:
print("not insert gn_1 config:" + row)
else:
print("insert gn_1 is successful. count:" + str(i + 1))
i += 1
# 暂停5s再执行， 避免被网站屏蔽掉
time.sleep(5)
# 添加5日数据
rows = fetch_theme_data(5, '_5')
for row in rows:
rowcount = execute_sql_with_rowcount(
"""insert into config(order_index,key,value,field_name)values(:order_index, 'gn_5',:value, 'theme')""",
{
'order_index': row[0],
'value': row[1]
})
if rowcount == 0:
print("not insert gn_5 config:" + row)
else:
print("insert gn_5 is successful. count:" + str(i + 1))
i += 1
# 暂停5s再执行， 避免被网站屏蔽掉
time.sleep(5)
# 添加10日数据
rows = fetch_theme_data(10, '_10')
for row in rows:
rowcount = execute_sql_with_rowcount(
"""insert into config(order_index,key,value,field_name)values(:order_index, 'gn_10',:value, 'theme')""",
{
'order_index': row[0],
'value': row[1]
})
if rowcount == 0:
print("not insert gn_10 config:" + row)
else:
print("insert gn_10 is successful. count:" + str(i + 1))
i += 1
print("共处理" + str(i) + "条记录")
except Exception as e:
print("db操作出现异常" + str(e), e)
except TimeoutError as e:
print("网络超时, 请手工重试")
def do_update_stock_sum(driver, task_name):
# 遍历可转债列表
# 打开文件数据库
task = None
try:
# 查询可转债
bond_cursor = get_cursor(
"""SELECT bond_code, cb_name_id, stock_code, stock_name from changed_bond"""
)
rows = bond_cursor.fetchall()
task, status = new_or_update_task(len(rows), task_name)
if status == -1: # 有任务在执行
return
# 当前日月
y = datetime.now().year
m = datetime.now().month
d = datetime.now().day
t = datetime(y, m, d)
# 记录更新时间(秒)
s = (t - datetime(1970, 1, 1)).total_seconds()
i = 0
for bond_row in rows:
process_task_when_normal(task, 1)
stock_code = bond_row[2]
stock_name = bond_row[3]
stock_cursor = get_cursor(
"""SELECT modify_date from stock_report where stock_code = :stock_code""",
{'stock_code': stock_code})
stocks = list(stock_cursor)
if len(stocks) == 0:
continue
# 已经更新了
if stocks[0][0] is not None and stocks[0][0] >= s:
continue
row = get_stock_sum(driver, stock_code)
rowcount = execute_sql_with_rowcount(
"""update stock_report set pe = :pe,
net_asset = :net_asset,
gross_rate = :gross_rate,
avg_market_cap = :avg_market_cap,
avg_roe = :avg_roe,
avg_net_profit_ratio = :avg_net_profit_ratio,
avg_gross_rate = :avg_gross_rate,
avg_pb = :avg_pb,
avg_pe = :avg_pe,
avg_net_margin = :avg_net_margin,
avg_net_asset = :avg_net_asset,
rank_roe = :rank_roe,
rank_net_profit_ratio = :rank_net_profit_ratio,
rank_gross_rate = :rank_gross_rate,
rank_pb = :rank_pb,
rank_pe = :rank_pe,
rank_net_margin = :rank_net_margin,
rank_net_asset = :rank_net_asset,
rank_market_cap = :rank_market_cap,
level_roe = :level_roe,
level_market_cap = :level_market_cap,
level_net_asset = :level_net_asset,
level_net_margin = :level_net_margin,
level_pe = :level_pe,
level_pb = :level_pb,
level_gross_rate = :level_gross_rate,
level_net_profit_ratio = :level_net_profit_ratio,
modify_date = :modify_date where stock_code = :stock_code""",
({
'pe': row['pe'],
'net_asset': row['net_asset'],
'gross_rate': row['gross_rate'],
'avg_market_cap': row['avg_market_cap'],
'avg_roe': row['avg_roe'],
'avg_net_profit_ratio': row['avg_net_profit_ratio'],
'avg_gross_rate': row['avg_gross_rate'],
'avg_pb': row['avg_pb'],
'avg_pe': row['avg_pe'],
'avg_net_margin': row['avg_net_margin'],
'avg_net_asset': row['avg_net_asset'],
'rank_roe': row['rank_roe'],
'rank_net_profit_ratio': row['rank_net_profit_ratio'],
'rank_gross_rate': row['rank_gross_rate'],
'rank_pb': row['rank_pb'],
'rank_pe': row['rank_pe'],
'rank_net_margin': row['rank_net_margin'],
'rank_net_asset': row['rank_net_asset'],
'rank_market_cap': row['rank_market_cap'],
'level_roe': row['level_roe'],
'level_market_cap': row['level_market_cap'],
'level_net_asset': row['level_net_asset'],
'level_net_margin': row['level_net_margin'],
'level_pe': row['level_pe'],
'level_pb': row['level_pb'],
'level_gross_rate': row['level_gross_rate'],
'level_net_profit_ratio': row['level_net_profit_ratio'],
'modify_date': s,
'stock_code': stock_code
}))
if rowcount == 0:
print("not update stock:" + stock_name + ' in stock_report')
else:
print("update " + stock_name + " is successful. count:" +
str(i + 1))
# 暂停5s再执行， 避免被网站屏蔽掉
time.sleep(5)
i += 1
ok_desc = "共处理" + str(i) + "条记录"
print(ok_desc)
process_task_when_finish(task, ok_desc)
except Exception as e:
print("db操作出现异常" + str(e), e)
process_task_when_error(task, "db操作出现异常")
raise e
except TimeoutError as e:
print("网络超时, 请手工重试")
process_task_when_error(task, "网络超时")
raise e