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)
