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