def update_stock_concept():
"""
获取概念分类
:return:
"""
stocks = crawl.get_stock_concept()
params_list = []
for i in stocks.index:
data = tuple(stocks.iloc[i].values.astype(str),)
params_list.append(data)
try:
con = cn.connection()
cur = con.cursor()
cur.execute(cn.DEL_T_CHN_STOCK_CONCEPT)
cur.executemany(cn.ISR_T_CHN_STOCK_CONCEPT, params_list)
res = cur.rowcount
con.commit()
return res
except Exception as e:
con.rollback()
print('更新股票概念事务处理失败', e)
return -1
finally:
cur.close()
con.close()
print('更新股票概念连接关闭')
def update_stock_pca(pca):
"""
获取沪深300分类 上证50 中证500
:return:
"""
if pca == 'hs300':
stocks = crawl.get_stock_hs300()
elif pca == 'sz50':
stocks = crawl.get_stock_sz50()
stocks['weight'] = 0
else:
stocks = crawl.get_stock_zz500()
param_list1 = (pca,)
params_list2 = []
for i in stocks.index:
data = tuple(stocks.iloc[i].values.astype(str),) + (pca,)
params_list2.append(data)
try:
con = cn.connection()
cur = con.cursor()
cur.execute(cn.DEL_T_CHN_STOCK_PCA, param_list1)
cur.executemany(cn.ISR_T_CHN_STOCK_PCA, params_list2)
res = cur.rowcount
con.commit()
return res
except Exception as e:
con.rollback()
print('更新', pca, '事务处理失败', e)
return -1
finally:
cur.close()
con.close()
print('更新', pca, '连接关闭')
def update_stock_xsgjj(nd, yf):
"""
获取限售股解禁
:return:
"""
stocks = crawl.get_stock_xsgjj(nd, yf)
if stocks is None:
return -1
params_list2 = []
param_list1 = (nd, ) + (yf, )
for i in stocks.index:
data = (nd, ) + (yf, ) + tuple(stocks.iloc[i].values.astype(str), )
params_list2.append(data)
try:
con = cn.connection()
cur = con.cursor()
cur.execute(cn.DEL_T_CHN_STOCK_XSGJJ, param_list1)
cur.executemany(cn.ISR_T_CHN_STOCK_XSGJJ, params_list2)
res = cur.rowcount
con.commit()
return res
except Exception as e:
con.rollback()
print('更新业限售股解禁事务处理失败', e)
return -1
finally:
cur.close()
con.close()
print('更新限售股解禁连接关闭')
def update_stock_winner(datetime):
"""
获取每日龙虎榜列表
:return:
"""
stocks = crawl.get_stock_winner(datetime)
if stocks is None:
return -1
#print(stocks.columns.values.tolist())
param_list1 = (datetime, )
params_list2 = []
for i in stocks.index:
data = (datetime, ) + tuple(stocks.iloc[i].values.astype(str), )
params_list2.append(data)
try:
con = cn.connection()
cur = con.cursor()
cur.execute(cn.DEL_T_CHN_STOCK_WINER, param_list1)
cur.executemany(cn.ISR_T_CHN_STOCK_WINER, params_list2)
res = cur.rowcount
con.commit()
return res
except Exception as e:
con.rollback()
print('更新龙虎榜列表事务处理失败', e)
return -1
finally:
cur.close()
con.close()
print('更新龙虎榜列表连接关闭')
def init_index_list():
"""
初始化指数列表并记录
:param code:
:param start:
:param end:
:return:
"""
idxs = crawl.get_all_index_list()
params_list = []
for i in idxs.index:
data = tuple(idxs.iloc[i].values.astype(str), )
params_list.append(data)
try:
con = cn.connection()
cur = con.cursor()
cur.execute(cn.DEL_T_CHN_INDEX_LIST)
cur.executemany(cn.ISR_T_CHN_INDEX_LIST, params_list)
res = cur.rowcount
con.commit()
return res
except Exception as e:
con.rollback()
print('初始化指数列表事务处理失败', e)
return -1
finally:
cur.close()
con.close()
print('初始化指数列表连接关闭')
def init_stock_list():
"""
抓取所有上市股票并初始化股票代码记录
:return:
"""
stocks = crawl.get_all_stock_list()
params_list = []
for i in stocks.index:
data = (str(i), ) + tuple(stocks.loc[str(i)].values[0:].astype(str), )
params_list.append(data)
try:
con = cn.connection()
cur = con.cursor()
cur.execute(cn.DEL_T_CHN_STOCK_LIST)
cur.executemany(cn.ISR_T_CHN_STOCK_LIST, params_list)
res = cur.rowcount
con.commit()
return res
except Exception as e:
con.rollback()
print('初始化股票代码事务处理失败', e)
return -1
finally:
cur.close()
con.close()
print('初始化股票代码连接关闭')
def update_stock_report(report, nd, jd):
"""
获取业绩报告（主表)
:return:
"""
if report == 'report':
stocks = crawl.get_stock_report(nd, jd)
sql1 = cn.DEL_T_CHN_STOCK_REPORT
sql2 = cn.ISR_T_CHN_STOCK_REPORT
elif report == 'profit':
stocks = crawl.get_stock_profit(nd, jd)
sql1 = cn.DEL_T_CHN_STOCK_PROFIT
sql2 = cn.ISR_T_CHN_STOCK_PROFIT
elif report == 'operation':
stocks = crawl.get_stock_operation(nd, jd)
sql1 = cn.DEL_T_CHN_STOCK_OPERATION
sql2 = cn.ISR_T_CHN_STOCK_OPERATION
elif report == 'growth':
stocks = crawl.get_stock_growth(nd, jd)
sql1 = cn.DEL_T_CHN_STOCK_GROWTH
sql2 = cn.ISR_T_CHN_STOCK_GROWTH
elif report == 'debtpaying':
stocks = crawl.get_stock_debtpaying(nd, jd)
sql1 = cn.DEL_T_CHN_STOCK_DEBTPAYING
sql2 = cn.ISR_T_CHN_STOCK_DEBTPAYING
else:
stocks = crawl.get_stock_cashflow(nd, jd)
sql1 = cn.DEL_T_CHN_STOCK_CASHFLOW
sql2 = cn.ISR_T_CHN_STOCK_CASHFLOW
if stocks is None:
return -1
params_list2 = []
param_list1 = (nd, ) + (jd, )
for i in stocks.index:
data = (nd, ) + (jd, ) + tuple(stocks.iloc[i].values.astype(str), )
params_list2.append(data)
try:
con = cn.connection()
cur = con.cursor()
cur.execute(sql1, param_list1)
cur.executemany(sql2, params_list2)
res = cur.rowcount
con.commit()
return res
except Exception as e:
con.rollback()
print('更新', report, '事务处理失败', e)
return -1
finally:
cur.close()
con.close()
print('更新', report, '连接关闭')
def clean_his_logger():
try:
con = cn.connection()
cur = con.cursor()
cur.execute(cn.DEL_HIS_T_LOGGER)
con.commit()
return 1
except Exception:
con.rollback()
return -1
finally:
cur.close()
con.close()
def logger(code, operat, descrpt, content, lrrq, ywlx):
try:
con = cn.connection()
cur = con.cursor()
param = (code, ) + (operat, ) + (descrpt, ) + (content, ) + (
lrrq, ) + (ywlx, )
cur.execute(cn.ISR_T_LOGGER, param)
con.commit()
return 1
except Exception:
con.rollback()
return -1
finally:
cur.close()
con.close()
def get_update_stock_code_list():
"""
从记录中获取要更新的所有股票代码
:return:
"""
try:
con = cn.connection()
cur = con.cursor()
cur.execute(cn.QUE_T_CHN_STOCK_CODE_UPDATE_LIST)
res = cur.fetchall()
con.commit()
return res
except Exception as e:
con.rollback()
print('获取要更新股票代码事务处理失败', e)
return []
finally:
cur.close()
con.close()
print('获取要更新股票代码连接关闭')
def update_stock_data(code, start, end):
"""
根据股票代码列表抓取历史交易数据并记录
:param code:
:param start:
:param end:
:return:
"""
trades = crawl.get_stock_by_code(code, start, end)
if trades is None:
return -1
param_list1 = (code, ) + (start + ' 00:00:00', ) + (end + ' 00:00:00', )
le = len(trades.index)
param_list2 = []
for i in trades.index:
# 才上市数据只有一条的情况
if le < 2:
data = (str(i), ) + tuple(
trades.loc[str(i)].values[0:].astype(str), )
else:
data = (str(i), ) + tuple(
trades.loc[str(i)].values[0:].astype(str)[0], )
param_list2.append(data)
try:
con = cn.connection()
cur = con.cursor()
cur.execute(cn.DEL_T_CHN_STOCK_TRADES, param_list1)
cur.executemany(cn.ISR_T_CHN_STOCK_TRADES, param_list2)
res = cur.rowcount
con.commit()
return res
except Exception as e:
con.rollback()
print('更新个股行情事务处理失败', e)
return -1
finally:
cur.close()
con.close()
print('更新个股行情连接关闭')