def save_to_csv(candle_id, symbol, path, res): """ Save to csv file, but may be it is easy to run error :param candle_id:int :param symbol:str :param path:str :param res:data :return:1 or None """ if candle_id == 1: if res["s"] == "no_data": print(f"The {symbol} stock has not data.") else: candles = pd.DataFrame(res) candles['dt'] = [ dt.datetime.fromtimestamp(x) for x in candles['t'] ] candles['symbol'] = symbol try: candles.to_csv(path, index=False, header=False, mode="a") except Exception as error: send_sms(f"There is an error:{error}") log(error) return None return 1 else: res['symbol'] = symbol profile = pd.DataFrame(res, index=[0]) profile.to_csv(path, index=False, header=False, mode="a") return 1
def connect(): """ Connect to the PostgreSQL database server. :return: conn or None """ try: conn = psycopg2.connect(**param_dic) except (Exception, psycopg2.DatabaseError) as error: print(f"error:{error},param_dic:{param_dic}") oth.log(error) oth.send_sms(f"conn has an error:{error}") sys.exit(1) return None return conn
def add_csv_title(path, title_list): """ 特意读取CSV然后为其添加标题,否则数据库无法准确导入 :param path: :param title_list: :return:1 or None """ # print(f"The path of CSV adding title is:{path}") try: add_column = pd.read_csv(path, header=None) except Exception as error: send_sms(f"There is an error:{error}") log(error) return None add_column.columns = title_list add_column.to_csv(path, index=False, header=True) return 1
def clear_csv(path): """ Clear the content of csv. 如已经有CSV文件,就清空文件,如没有文件则创建一个空CVS文件. 目的是为后面的追加内容做准备. :param cvs file's path: :return:1 or None """ try: a = open(path, mode='w') except Exception as error: send_sms(f"There is an error:{error}") log(error) return None a.truncate() a.close() return 1
def stock_download_import(self): """ 查询出所有的股票代码,然后for循环,逐个下载每个股票的历史日数据 :return: """ csv.clear_csv(self.path) pg_column_dt = pg.column(stock_settings.table_symbol, stock_settings.table_symbol_column) if pg_column_dt is not None: i = 0 a_time = time.time() for symbol in pg_column_dt: self.symbol_name = symbol[0] res = self.download_finnhub() if (res is not None) and (res != {}) and (res != []): csv.save_to_csv(self.candle_id, self.symbol_name, self.path, res) if self.candle_id == 1: if res["s"] != "no_data": pg.pg_to_sql(self.table, self.path, self.columns) print(f" Get {self.symbol_name} .") else: print(f" {self.symbol_name} is no_data.") else: pg.pg_to_sql_company_profile(self.table, self.path, self.symbol_name) print(f" Get {self.symbol_name} .") else: print(f" {self.symbol_name} has no data.") time.sleep(1.5) # send mail&sms msg = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) \ + ": Download and Import " \ + self.table + " successfully.\n" oth.send_sms(msg) oth.send_email(self.table, msg) else: # send mail&sms msg = time.strftime(f"Don't find any symbol or has some problem in {self.table}.") oth.send_sms(msg) oth.send_email(self.table, msg)
def save_to_csv(candle_id, symbol, path, res): """ Save to csv file, but may be it is easy to run error :param candle_id:int :param symbol:str :param path:str :param res:data :return:1 or None """ if candle_id == 1: if res["s"] == "no_data": print(f"The {symbol} stock has not data.") else: try: candles = pd.DataFrame(res) candles['dt'] = [ dt.datetime.fromtimestamp(x) for x in candles['t'] ] candles['symbol'] = symbol candles.to_csv(path, index=False, header=False, mode="a") except ValueError as error: print("类似https://finnhub.io/api/v1/stock/candle?symbol=ZYXI&resolution=D&from=1614349800&to=1614794055的错误。"\ "finnhub提供的数据质量有问题,比如两个C价格,T时间却给了三个,导致数据无法转成DataFrame格式。") send_sms( f"There is an error:{error},arrays must all be same length " ) log(error) pass except Exception as error: send_sms(f"There is an error:{error}") log(error) return None return 1 else: res['symbol'] = symbol profile = pd.DataFrame(res, index=[0]) profile.to_csv(path, index=False, header=False, mode="a") return 1
# Convert the list of dictionnaries into dataframes df_companies = pd.DataFrame(lst_dict_companies) df_companies.sort_values(by=["symbol"], ascending=True, inplace=True, ignore_index=True) # save as CSV df_companies.to_csv(stock_settings.all_symbol_path, index=False, header=True) df = pd.read_csv(stock_settings.all_symbol_path) df = df.rename( columns={ "currency": "currency", "description": "description", "displaySymbol": "displaySymbol", "figi": "figi", "symbol": "symbol", "type": "type" }) print("df===:\n", df) # clear data of symbol table pg.clear_table(stock_settings.all_symbol_table) # import data pg.insert7(stock_settings.all_symbol_table, df) # send mail&sms msg = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ": Download and Import " + stock_settings.all_symbol_table + " successfully.\n" oth.send_sms(stock_settings.all_symbol_table, msg) oth.send_email(stock_settings.all_symbol_table, msg)
# Convert the list of dictionnaries into dataframes df_companies = pd.DataFrame(lst_dict_companies) df_companies.sort_values(by=["symbol"], ascending=True, inplace=True, ignore_index=True) # save as CSV df_companies.to_csv(stock_settings.all_symbol_path, index=False, header=True) df = pd.read_csv(stock_settings.all_symbol_path) df = df.rename( columns={ "currency": "currency", "description": "description", "displaySymbol": "displaySymbol", "figi": "figi", "symbol": "symbol", "type": "type" }) print("df===:\n", df) # clear data of symbol table pg.clear_table(stock_settings.all_symbol_table) # import data pg.insert7(stock_settings.all_symbol_table, df) # send mail&sms msg = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + ": Download and Import " + stock_settings.all_symbol_table + " successfully.\n" oth.send_sms(msg) oth.send_email(stock_settings.all_symbol_table, msg)