def getIndustryData(code='', date='', index=True):
result = UpdateIndustryData()
print('result')
print(result)
if (not result.empty and code != ''):
return result
if (code == ''):
configger.init()
indutrysql = configger.industrySQL
print(indutrysql)
if (index):
data = pd.read_sql(sql=indutrysql, con=configger.engine)
else:
data = pd.read_sql(sql=indutrysql, con=configger.engine)
else:
configger.init()
if (index):
indutrysql = configger.industrySQL2
data = pd.read_sql(
sql=indutrysql.format(code),
con=configger.engine,
)
else:
indutrysql = configger.industrySQL2
data = pd.read_sql(sql=indutrysql.format(code),
con=configger.engine)
return data
def getmarketValue(data=None, highTh=None, lowTh=None):
billion = 100000000
sql = 'select code,totalmarketvalue,liquidmarketvalue from tb_basic_information'
if (highTh != None or lowTh != None):
sql += ' where'
list = []
if (highTh != None):
highTh = highTh * billion
list.append(' totalmarketvalue < {}'.format(highTh))
if (lowTh != None):
lowTh = lowTh * billion
list.append(' totalmarketvalue > {}'.format(lowTh))
sql += ' and'.join(list)
print(sql)
configger.init()
engine = configger.engine
res = pd.read_sql(con=engine, sql=sql, index_col='code')
if (data != None):
try:
if (data.index == 'code'):
res = res.loc[res.index.isin(data.index)]
elif ('code' in (data.columns.tolist())):
res = res.loc[res.index.isin(data['code'])]
except:
print(res.index.isin(data))
res = res.loc[res.index.isin(data)]
return res
def todayStock(table='tb_today_stock'):
configger.init()
engine = configger.engine
if (timeUtil.tableNeedUpdate(table) == False):
return pd.read_sql(con=engine, sql='select * from {}'.format(table))
nowtime = datetime.datetime.now()
endDate = nowtime.strftime('%Y%m%d')
createTimeSql = " SELECT CREATE_TIME from information_schema.`TABLES` WHERE `information_schema`.`TABLES`.`TABLE_SCHEMA` = '{}' and `information_schema`.`TABLES`.`TABLE_NAME` = '{}' ".format(
'stock', table)
lastUpdateTime = pd.read_sql(con=engine, sql=createTimeSql).iloc[0, 0]
try:
##排除五点后获取数据
print('needUpdate')
print(needUpdate(lastUpdateTime, nowtime, isWorkDay=True))
if (needUpdate(lastUpdateTime, nowtime)):
stockData = ak.stock_zh_a_spot()
stockData['symbol'] = stockData['symbol'].map(
lambda x: getdotCodeBysymbol(x))
stockData.to_sql(table, con=engine, if_exists='replace')
else:
stockData = pd.read_sql(con=engine,
sql='select * from {}'.format(table))
except:
# traceback.print_exc()
stockData = pd.read_sql(con=engine,
sql='select * from {}'.format(table))
timeUtil.saveOperationTime(table)
return stockData
def save_north_data(hold_stock_table='tb_ak_north_hold_stock',hold_board_rank_table='tb_ak_north_board_rank',way='byboot'):
configger.init()
engine=configger.engine
# north_net_in=query_north_net_in()#北上资金净流入
# north_cash_balance=query_north_net_balance()#北上资金净流入
# north_cash_acc_flow_in=query_north_acc_flow_in()
symbol_list=["北向资金增持行业板块排行", "北向资金增持概念板块排行", "北向资金增持地域板块排行"]
market_list=["北向", "沪股通", "深股通"]
indicator_list=["今日排行", "3日排行", "5日排行", "10日排行", "月排行", "季排行", "年排行"]
if(utils.timeUtil.tableNeedUpdate(hold_stock_table)==False):
return
for market in market_list:
for indicator in indicator_list:
try:
hold_stock = query_north_hold_stock(market,indicator)
hold_stock['updatetime']=datetime.datetime.now().date()
print(hold_stock)
hold_stock.to_sql(hold_stock_table,con=engine,index=False,if_exists='append')
except:
pass
indicator_list = ["今日", "3日", "5日", "10日", "1月", "1季", "1年"]
for symbol in symbol_list:
for indicator in indicator_list:
try:
board_rank=query_north_board_rank(symbol,indicator)
board_rank['updatetime']=datetime.datetime.now().date()
print(board_rank)
board_rank.to_sql(hold_board_rank_table,con=engine,index=False,if_exists='append')
except:
pass
utils.timeUtil.saveOperationTime(hold_stock_table)
def select_growth_data(code='', n=12):
configger.init()
engine = configger.engine
sql = configger.all_growthSQL
if (code == ''):
data = pd.read_sql(sql=sql, con=engine)
return data
def save_index_data(index_table='tb_ak_index_names',
index_content='tb_ak_index_content',
index_his='tb_ak_his_data'):
configger.init()
engine = configger.getEngine()
all_index = query_all_index_spot()
all_index_clone = a_dot_to_code(all_index.copy(), 'code')
datalist = [i for i in all_index['code']]
content_df = pd.DataFrame()
all_index_clone.to_sql(index_table, con=engine, if_exists='replace')
for i in datalist:
try:
c = query_index_content(i)
print(c)
except:
continue
if (content_df.empty):
content_df = c
else:
content_df = content_df.append(c)
content_df.to_sql(index_content, con=engine, if_exists='replace')
his_df = pd.DataFrame()
for i in datalist:
try:
his = query_his_index_by_code(i)
print(his)
except:
continue
if (his_df.empty):
his_df = his
else:
his_df = his_df.append(his)
his_df.to_sql(index_content, con=engine, if_exists='replace')
configger.colseEngine(engine)
def findDataBymarkevalueTH(totalMarketValuelowTh,
totalMarketValueHighTh=0,
liquidMarketValueLowTh=0,
liquidMarketValueHighTh=0):
configger.init()
engine = configger.engine
sqlBasicInformation = 'select * from tb_basic_information'
basicinformation = pd.read_sql(sql=sqlBasicInformation,
con=engine,
index_col='code')
# print(basicinformation)
totalMarketValuelowTh, totalMarketValueHighTh, liquidMarketValueHighTh, liquidMarketValueLowTh = totalMarketValuelowTh * 100000000, totalMarketValueHighTh * 100000000, liquidMarketValueHighTh * 100000000, liquidMarketValueLowTh * 100000000
basicinformation=basicinformation.loc[ \
basicinformation['totalMarketValue']>totalMarketValuelowTh]
if (totalMarketValueHighTh != 0):
basicinformation=basicinformation.loc[ \
basicinformation['totalMarketValue']<totalMarketValueHighTh]
# basicinformation['liquidMarketVAlue']>liquidMarketValueTh]
# path=r'D:\onedrive\OneDrive - ncist.edu.cn\选股\{}'.format(date)
# if(not os.path.exists(path)):
# os.mkdir(path)
# filepath=os.path.join(path,'结果{}-{}.xlsx'.format(str(k),str(growth)))
# myEmail.send.send_mail(filepath)
return basicinformation
def select_yjbb_by_date(tablename, date):
sql = 'select * from {} where date ={}'.format(tablename, date)
try:
configger.init()
engine = configger.engine
result = pd.read_sql(sql=sql, con=engine)
return result
except:
print('select_yjbb_by_date error {}'.format(date))
def getRecentGrowth(code='', n=1):
configger.init()
growthSQl = configger.growthSQl
data = pd.read_sql(sql=growthSQl, con=configger.mysql, index_col='code')
print(data)
data = data.groupby(data.index).apply(lambda x: getRecnetN(x, n))
data.index.names = ['code', 'index']
data.reset_index(inplace=True)
data.set_index('code', drop=True, inplace=True)
return data
def select_all_CI(key):#key : 行业/概念
configger.init()
con=configger.engine
if(key=='概念'):
sql='select * from tb_ak_concept_infos where convert(update_time,date)=convert((select max(update_time) from tb_ak_concept_infos),date)'
if (key == '行业'):
sql='select * from tb_ak_industry_infos where convert(update_time,date)=convert((select max(update_time) from tb_ak_industry_infos),date)'
data=pd.read_sql(sql=sql,con=con,index_col='序号')
data.rename(columns={'代码':'code'},inplace=True)
return data
def saveOperationTime(name):
configger.init()
con = configger.engine
operation = pd.DataFrame()
operation.loc[0, 'name'] = name
operation.loc[0, 'updateTime'] = datetime.datetime.now()
operation.to_sql(name='tb_operation_time',
con=con,
if_exists='append',
index=False)
return True
def select_growth_data(code='', n=12):
configger.init()
engine = configger.getEngine()
sql = configger.all_growthSQL
if (code == ''):
data = pd.read_sql(sql=sql, con=engine)
recent_data = data.groupby('code').apply(lambda x: getRecnetN(x, n))
recent_data.index.names = ['code', 'index']
recent_data.reset_index(inplace=True)
recent_data.drop(columns='index', inplace=True)
print(recent_data)
return recent_data
def industryPe():
configger.init()
engine=configger.engine
peSQl=configger.peSQL
print(peSQl)
industryData=getIndustryData(index=True)
print(industryData)
peData=pd.read_sql(con=engine,sql=peSQl,index_col='code')
data=pd.merge(left=industryData,right=peData,left_index=True,right_index=True,how='inner')
res=data.groupby('行业')[['peTTM','pbMRQ','psTTM','pcfNcfTTM']].agg('mean')
print(data)
print(res)
return res
def industryMinPE():
configger.init()
engine=configger.engine
peSQl=configger.peSQL
print(peSQl)
industryData=getIndustryData(index=True)
print(industryData)
peData=pd.read_sql(con=engine,sql=peSQl,index_col='code')
peData=peData.loc[peData['peTTM']>0]
data=pd.merge(left=industryData,right=peData,left_index=True,right_index=True,how='inner')
print(data)
res=data.groupby('行业').apply(lambda x:findmin(x,'peTTM'))
return res
def getResultFile(th=1000,growth=0.3,pe=20,ByMACD=False,BySMA=False):
configger.init()
date=configger.date
data=findStockList(100,th,growth,pe,ByMACD,BySMA)
path=configger.path
if(not os.path.exists(path)):
os.mkdir(path)
filepath=os.path.join(path,'市值{}市盈率{}增长率{}.xlsx'.format('500亿','25','25%'))
data.to_excel(filepath)
print(filepath)
# myEmail.send.send_mail(filepath)
return filepath
def dataimport(table, fun, if_exists='append'):
logger = logging.getLogger(__name__)
logger.setLevel(level=logging.INFO)
handler = logging.FileHandler("log.txt")
handler.setLevel(logging.INFO)
formatter = logging.Formatter(
'%(asctime)s - %(name)s - %(levelname)s - %(message)s')
handler.setFormatter(formatter)
logger.addHandler(handler)
logger.info(table + ' start')
configger.init()
engine = configger.engine
data = todayStock()
lg = bs.login()
i = 0
symbols = tqdm(data['symbol'])
now = datetime.datetime.now()
sql = 'select code,max(date) as date,max(updateTime)as updateTime from {} GROUP BY code'.format(
table)
try:
timeData = pd.read_sql(con=engine, sql=sql, index_col='code')
except:
timeData = pd.DataFrame()
for code in symbols:
i = i + 1
# code=util.getdotCodeBysymbol(symbol)
if (not timeData.empty and code in timeData.index
and needUpdate(timeData.loc[code, 'updateTime'],
now,
isWorkDay=True) == False):
continue
try:
start_date = pd.to_datetime(
timeData.loc[code, 'date']) + datetime.timedelta(days=90)
start_date = start_date.strftime('%Y-%m-%d')
# start_date=start_date.date()
except:
start_date = '1990-01-01'
# print(start_date,end_date)
# print(start_date,end_date)
result = fun(code, start_date)
result['updateTime'] = now
# print(result)
result.to_sql(table, con=engine, if_exists=if_exists, index=False)
symbols.set_description("查询代码为：{},数据条数为{}".format(
code, len(result.index)))
if (i % 1000 == 0):
bs.logout()
time.sleep(5)
bs.login()
bs.logout()
def constantGrowth(codes='all',
times=8,
allAbove=True,
accumulate=False,
WeightAccumulate=False):
configger.init()
growthSQl = configger.growthSQl
data = pd.read_sql(sql=growthSQl, con=configger.mysql, index_col='code')
data.dropna(subset=['净利润'], inplace=True)
print(data)
if (codes != 'all'):
isIn = data.index.isin(codes)
data = data.loc[isIn]
above = data.groupby(data.index).apply(lambda x: findAboveZero(x, times))
print(above)
above = pd.DataFrame(above)
above.columns = ['above']
if (allAbove == True):
res = above == times
print('res')
print(res)
res = res.loc[res['above'] == True]
data = data.loc[data.index.isin(res.index)]
above = above.loc[above.index.isin(res.index)]
print('data')
print(data)
weightAverageData = data.groupby(
data.index,
as_index=False).apply(lambda x: findWeightAverage(x, times))
print(weightAverageData)
weightAverageData.index.names = ['num', 'code']
weightAverageData.reset_index(inplace=True)
weightAverageData.set_index('code', inplace=True, drop=True)
weightAverageData.drop(columns=['num'], inplace=True)
print('weightAverageData')
print(weightAverageData)
print('above')
print(above)
result = pd.concat([weightAverageData, above], axis=1)
print(result)
result.dropna(how='any', axis=1, inplace=True)
result.dropna(how='all', axis=0, inplace=True)
result.sort_values(by=['above', 'weightAverage'],
inplace=True,
ascending=False)
return result
def save_concept_data(seconds=100,way='byboot'):
configger.init()
engine = configger.engine
wait_days=configger.constant_variables['low_ferquency_update_days']
if(not utils.timeUtil.tableNeedUpdate('tb_akshare_concept_names')):
return
maintable = 'tb_ak_concept_names'
indextable = 'tb_ak_concept_index'
infotable = 'tb_ak_concept_infos'
names = query_concept_names()
names.to_sql(maintable, con=engine, if_exists='replace', index=False)
print(names)
if(not utils.timeUtil.tableNeedUpdate(infotable) or way=='byhand'):
flag=True
dataList=pd.DataFrame()
for name in names['name']:
print(name)
# if (data_need_update(infotable, 'update_time', '概念', name) == False):
# continue
try:
data = retry(query_concept_infos, name)
except:
continue
# data = query_concept_infos(name)
if(flag and not data.empty):
dataList=data
flag = False
else:
# print(dataList)
dataList=dataList.append(data,ignore_index=True)
if (data_need_update(infotable + '_his', 'update_time', '概念', name) == True):
data.to_sql(infotable+'_his', con=engine, if_exists='append', index=False)
print(dataList)
dataList.to_sql(infotable, con=engine, if_exists='replace', index=False)
utils.timeUtil.saveOperationTime(infotable)
if(not utils.timeUtil.tableNeedUpdate(indextable,wait_days) or way=='byhand' ):
for name in names['name']:
if (data_need_update(indextable, 'update_time', '概念', name) == False):
continue
data = retry(query_concept_index, name)
try:
sql = 'select * from {} where 概念="{}"'.format(indextable, name)
saved_data = pd.read_sql(sql=sql, con=engine)
data = data.loc[~ data['日期'].isin(saved_data['日期'])]
except:
traceback.print_exc()
data.to_sql(indextable, con=engine, if_exists='append', index=False)
print(data)
utils.timeUtil.saveOperationTime(indextable)
utils.timeUtil.saveOperationTime('tb_ak_concpet_names')
def getKBySymbol(symobl, days=None):
configger.init()
engine = configger.engine
if (days == None):
sql = 'select * from tb_stock_hisotry_detatil where code="{}" order by date desc'.format(
symobl)
else:
sql = 'select * from tb_stock_hisotry_detatil where code="{}" order by date desc limit 0,{}'.format(
symobl, days)
# print(sql)
data = pd.read_sql(sql, con=engine)
data.set_index('date', inplace=True)
data.sort_index(ascending=True, inplace=True)
return data
def findVolumeCountByData(data, days=100):
configger.init()
engine = configger.engine
list = data.index.tolist()
for i in range(len(list)):
list[i] = "'" + list[i] + "'"
codes = ','.join(list)
sql = "select * from tb_stock_hisotry_detatil WHERE code in ({})".format(
codes)
volumes = pd.read_sql(sql=sql, con=engine, index_col='code')
data['count'] = volumes.groupby(['code'
]).apply(lambda x: findVolume(x, days))
data.sort_values(by=['YOYNI', 'count'])
return data
def insertTodayValue(data,table):
today = datetime.datetime.now()
configger.init()
engine=configger.engine
i=0
symbols = tqdm(data['symbol'])
now=datetime.datetime.now()
sql='select code,max(date) as date,max(updateTime)as updateTime from {} GROUP BY code'.format(table)
# print(sql)
try:
timeData=pd.read_sql(con=engine,sql=sql,index_col='code')
except:
timeData=pd.DataFrame()
# print(timeData)
for code in symbols:
code=util.removedotBysymbol(code)
i=i+1
# code=util.getdotCodeBysymbol(symbol)
try:
if(not timeData.empty and code in timeData.index and (now-timeData.loc[code,'updateTime']).seconds<3600*24):
continue
except:
pass
try:
start_date= pd.to_datetime(timeData.loc[code,'date']) + datetime.timedelta(days=1)
start_date=start_date.strftime('%Y%m%d')
# start_date=start_date.date()
except:
start_date='19900101'
# traceback.print_exc()
end_date=today.strftime('%Y-%m-%d')
# print(start_date,end_date)
if(start_date>end_date):
# print(code,start_date,'已存在')
continue
# print('star_Date')
# print(start_date)
try:
result=ak.stock_zh_a_daily(symbol=code, start_date=start_date, end_date=end_date, adjust="qfq")
result.reset_index(inplace=True)
result.drop(columns='index',inplace=True)
result['updateTime']=now
result['code']=code
print(result)
result.to_sql(table,con=engine,if_exists='append',index=False)
symbols.set_description("查询代码为：{},数据条数为{}".format(code,len(result.index)))
except:
traceback.print_exc()
def data_need_update(tablename, time, keyname, key) -> bool:
configger.init()
engine = configger.engine
try:
sql = 'select max({}) from {} where {}="{}"'.format(
time, tablename, keyname, key)
date = pd.read_sql(sql=sql, con=engine).iloc[0, 0]
now = datetime.datetime.now().date()
date = pd.to_datetime(date).date()
if (now - date).days > 0:
return True
else:
return False
except:
return True
def update_yjbb_to_db(tablename='', fun=ak.stock_em_yjbb, way='byboot'):
configger.init()
engine = configger.engine
now = datetime.datetime.now()
if (timeUtil.tableNeedUpdate(tablename) == False and way == 'byboot'):
return False
try:
sql = 'select max(date) from {}'.format(tablename)
print(sql)
last_update_date = pd.read_sql(sql=sql, con=engine).iloc[0, 0]
print('last_update_date', last_update_date)
last_update_date = timeUtil.get_this_end_quarter_day(last_update_date)
except:
last_update_date = pd.to_datetime('1991-1-1')
last_update_date = timeUtil.get_this_end_quarter_day(last_update_date)
print(last_update_date, last_update_date)
this_end_season = timeUtil.get_this_end_quarter_day(now)
#处理部分未更新数据
#处理全量数据
season_end = pd.period_range(start=last_update_date,
end=this_end_season,
freq='q')
season_end = pd.to_datetime(season_end.values.astype('datetime64[D]'))
print(season_end)
resultlist = []
for date in season_end:
data = get_yjbb_by_date(date, fun)
print(data)
if (data is None):
continue
data['update_time'] = now
try:
sql = 'select * from {} where date="{}"'.format(
tablename, date.date())
print(sql)
saved_data = pd.read_sql(sql=sql, con=engine)
print(data['code'].isin(saved_data['code']))
data = data = data.loc[~data['code'].isin(saved_data['code'])]
print(data)
resultlist.append(data)
except:
resultlist.append(data)
for data in resultlist:
if (not data.empty):
print(data)
data.to_sql(tablename, con=engine, if_exists='append', index=False)
timeUtil.saveOperationTime(tablename)
def importHistory(data, table):
today = datetime.datetime.now()
configger.init()
engine = configger.engine
lg = bs.login()
i = 0
symbols = tqdm(data['symbol'])
now = datetime.datetime.now()
sql = 'select code,max(date) as date,max(updateTime)as updateTime from {} GROUP BY code'.format(
table)
try:
timeData = pd.read_sql(con=engine, sql=sql, index_col='code')
except:
timeData = pd.DataFrame()
for code in symbols:
i = i + 1
# code=util.getdotCodeBysymbol(symbol)
if (not timeData.empty and code in timeData.index
and needUpdate(timeData.loc[code, 'updateTime'],
now,
isWorkDay=True) == False):
continue
try:
start_date = pd.to_datetime(
timeData.loc[code, 'date']) + datetime.timedelta(days=1)
start_date = start_date.strftime('%Y-%m-%d')
# start_date=start_date.date()
except:
start_date = '1990-01-01'
# traceback.print_exc()
end_date = today.strftime('%Y-%m-%d')
# print(start_date,end_date)
if (start_date > end_date):
# print(code,start_date,'已存在')
continue
# print(start_date,end_date)
result = importBycode(code, start_date, end_date)
result['updateTime'] = now
# print(result)
result.to_sql(table, con=engine, if_exists='append', index=False)
symbols.set_description("导入日K线：{},数据条数为{}".format(
code, len(result.index)))
if (i % 1000 == 0):
bs.logout()
time.sleep(5)
bs.login()
bs.logout()
def getMarketValueBySymbol(symbol):
configger.init()
engine = configger.engine
sql1 = "select totalShare from tb_profit WHERE code='{}' ORDER BY date desc limit 0,1".format(
symbol)
sql1.format(symbol)
sql2 = "select close from tb_stock_hisotry_detatil WHERE code='{}' ORDER BY date desc limit 0,1".format(
symbol)
sql2.format(symbol)
totalShare = np.float64(pd.read_sql(con=engine, sql=sql1).iloc[0, 0])
price = np.float64(pd.read_sql(con=engine, sql=sql2).iloc[0, 0])
# print(totalShare)
# print(price)
marketValue = totalShare * price
return marketValue
def getRecentDataBydata(data):
configger.init()
engine = configger.engine
if (data.index.name != 'code'):
data.set_index('code', inplace=True)
list = data.index.tolist()
for i in range(len(list)):
list[i] = "'" + list[i] + "'"
codes = ','.join(list)
sql="select t.* from (" \
"SELECT max(date) as date,code FROM tb_stock_hisotry_detatil e where code in " \
"({}) GROUP BY code)o , tb_stock_hisotry_detatil t where t.code=o.code and t.date=o.date".format(codes)
result = pd.read_sql(sql=sql, con=engine, index_col='code')
result = pd.concat([data, result], axis=1)
# print(result)
return result
def inportIndustryData(name='tb_today_industry', code=None, date=None):
configger.init()
# 显示登陆返回信息
# 结果集输出到csv文件
con = configger.engine
try:
sql = configger.lastOperateTimeSql.format(name)
lastTime = pd.read_sql(sql=sql, con=con).iloc[0, 0]
print(lastTime)
except:
print('没有操作数据')
if (lastTime == None):
lastTime = pd.to_datetime('1990-1-1 00:00:00')
if (needUpdate(lastTime, datetime.now(), True) == True):
lg = bs.login()
# 获取行业分类数据
rs = bs.query_stock_industry()
# rs = bs.query_stock_basic(code_name="浦发银行")
# 打印结果集
industry_list = []
while (rs.error_code == '0') & rs.next():
# 获取一条记录，将记录合并在一起
industry_list.append(rs.get_row_data())
result = pd.DataFrame(industry_list, columns=rs.fields)
result['date'] = datetime.now().date()
result.to_sql(name=name, con=con, if_exists='append', index=False)
bs.logout()
operation = pd.DataFrame()
operation.loc[0, 'name'] = name
operation.loc[0, 'updateTime'] = datetime.now().date()
operation.to_sql(name='tb_operation_time',
con=con,
if_exists='append',
index=False)
print('industry_data_import_ok')
else:
print('无需插入')
def getAllMarketValue():
configger.init()
engine = configger.engine
sqlCloseValue='select t.* from (' \
'SELECT max(date) as date,code FROM tb_stock_hisotry_detatil GROUP BY code) o , tb_stock_hisotry_detatil t where t.code=o.code and t.date=o.date'
sqlShare='select t.* from ' \
'(SELECT max(date) as date,code FROM tb_profit GROUP BY code) o , tb_profit t where t.code=o.code and t.date=o.date'
priceData = pd.read_sql(
con=engine,
sql=sqlCloseValue,
index_col='code',
)
ShareData = pd.read_sql(con=engine, sql=sqlShare, index_col='code')
marketData = pd.DataFrame(index=priceData.index)
nowdate = datetime.datetime.now().date()
print('priceData')
print(priceData)
priceData['close'] = priceData['close'].astype(float)
pdUtil.fillNullColumn(ShareData, 'totalShare', 0)
ShareData['totalShare'] = ShareData['totalShare'].astype(float)
pdUtil.fillNullColumn(ShareData, 'liqaShare', 0)
ShareData['liqaShare'] = ShareData['liqaShare'].astype(float)
print('shareData')
print(ShareData)
marketData[
'totalMarketValue'] = priceData['close'] * ShareData['totalShare']
marketData[
'liquidMarketValue'] = priceData['close'] * ShareData['liqaShare']
marketData['roeAvg'] = ShareData['roeAvg']
marketData['npMargin'] = ShareData['npMargin']
marketData['netProfit'] = ShareData['netProfit']
marketData['epsTTM'] = ShareData['epsTTM']
marketData['MBRevenue'] = ShareData['MBRevenue']
marketData['totalShare'] = ShareData['totalShare']
marketData['liqaShare'] = ShareData['liqaShare']
marketData['updateTime'] = nowdate
marketData.to_sql(con=engine,
name='tb_basic_information',
if_exists='replace',
dtype={'code': VARCHAR(32)})
return marketData
def save_secotr_Data(sector_list_table='tb_ak_sector_spot',sector_detail_table='tb_ak_sector_detail'):
configger.init()
engine=configger.engine
sector_type_list=["新浪行业", "启明星行业", "概念", "地域", "行业"]
for type in sector_type_list:
time.sleep(random.randint(5))
sector_list=query_sector_list(indicator=type)
sector_list['update_time']=datetime.datetime.now().date()
sector_list.to_sql(sector_list_table,con=engine,if_exists='append',index=False)
for secotr in sector_list['label']:
try:
time.sleep(random.randint(3))
data=query_secotr_detail(secotr)
data['update_time'] = datetime.datetime.now().date()
data['label']=secotr
data['type']=type
print(data)
data.to_sql(sector_detail_table,con=engine,if_exists='append',index=False)
except:
print(type+" "+secotr+"error")
def tableNeedUpdate(tableName, days=1, isWorkDay=True):
configger.init()
# 显示登陆返回信息
# 结果集输出到csv文件
con = configger.engine
try:
sql = configger.lastOperateTimeSql.format(tableName)
lastTime = pd.read_sql(sql=sql, con=con).iloc[0, 0]
now = datetime.datetime.now()
print(lastTime)
except:
return True
if (lastTime == None):
return True
if (days == 1):
if (needUpdate(lastTime, now, isWorkDay=isWorkDay) == False):
return False
return needUpdate(lastTime, now, isWorkDay=isWorkDay)
else:
return (pd.to_datetime(now.date()) -
pd.to_datetime(lastTime)).days > days
