def insert7(table, df):
"""
插入数据，数据表为7列
:param table: str
:param df: data
:return: 1 or None
"""
# Using cursor.executemany() to insert the dataframe
# Create a list of tuples from the dataframe values
tuples = [tuple(x) for x in df.to_numpy()]
# Comma-separated dataframe columns
cols = ','.join(list(df.columns))
len_cols = len(list(df.columns))
col_str = ""
for i in range(len_cols):
if (i==0):
col_str = col_str + "%%s"
else:
col_str = col_str + ",%%s"
sql_str = "INSERT INTO %s(%s) VALUES("+col_str+")"
query = sql_str % (table, cols)
conn = connect()
cur = conn.cursor()
try:
cur.executemany(query, tuples)
conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
print(f"Error: {error}")
oth.log(error)
conn.rollback()
cur.close()
return None
print("execute_many() done")
cur.close()
return 1
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 max_t(table, symbol_name):
"""
Query the max timestamp in one table
:param table: str
:param symbol_name: str
:return: pg_max_dt:int
"""
try:
conn_db = connect()
except IOError as error:
print("There is an IO error when connecting to the database.")
oth.log(error)
return None
cursor = conn_db.cursor()
# sql = "select * from "+table+" where symbol='"+symbol_name+"' order by series desc limit 1"
sql = f"select * from {table} where symbol='{symbol_name}' order by series desc limit 1"
cursor.execute(sql)
results = cursor.fetchall()
# print (results)
if results == []:
# print ("empety table")
pg_max_dt = 0
else:
pg_max_dt = results[0][6]
conn_db.commit()
cursor.close()
conn_db.close()
return pg_max_dt
def clear_table(table):
"""
Clear one table's data
:param table: str
:return: 1 or None
"""
try:
conn_db = connect()
except IOError as error:
print("There is an IO error when connecting to the database.")
oth.log(error)
return None
cur = conn_db.cursor()
sql = f"delete from {table} where 1=1"
try:
cur.execute(sql)
except(Exception, psycopg2.DatabaseError) as error:
oth.log(error)
cur.close()
conn_db.close()
return None
conn_db.commit()
cur.close()
conn_db.close()
print("Clear table done.")
return 1
def column(table, col):
"""
Query column in one table
:param table: str
:param col: str
:return: pg_column_dt:data or None
"""
try:
conn_db = connect()
except IOError as error:
print(f"There is an IO error when connecting to the database.{error}")
oth.log(error)
return None
cursor = conn_db.cursor()
# sql = f"select distinct {col} from {table} order by {col} "
sql = f"select {col} from {table} order by {col} "
# sql = "select distinct symbol from stock_symbol_thousand where symbol='ZYXI' order by symbol "
try:
cursor.execute(sql)
except (Exception, psycopg2.errors.SyntaxError) as error:
oth.log(error)
cursor.close()
conn_db.close()
return None
results = cursor.fetchall()
if results == []:
print(f"The {table} is empty")
pg_column_dt = None
else:
print(type(results))
pg_column_dt = results # this is a list
conn_db.commit()
cursor.close()
conn_db.close()
return pg_column_dt
def pg_to_sql_company_profile(table, path, symbol=""):
"""
Only used by import company profile data to database.
:param table: str
:param path: str
:param symbol: str
:return: 1 or None
"""
csv.add_csv_title(path, stock_settings.profile_title)
print(path)
dataset = pd.read_csv(path)
begin_time = time.time()
pq_info = "postgresql+psycopg2://" + param_dic["user"] + ":" + param_dic[
"password"] + "@" + param_dic["host"] + "/" + param_dic["database"]
engine = create_engine(pq_info)
try:
dataset.to_sql(table,
engine,
index=False,
chunksize=None,
if_exists='append',
method="multi")
except (Exception) as error:
print(f"Company profile to_sql error:{error}")
oth.log(error, symbol)
return None
csv.clear_csv(path)
end_time = time.time()
print("Import to database cost :%.2f " % round(end_time - begin_time, 2))
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 download_finnhub(self):
"""
Download company profile by API.
:return: res:data or None
"""
finnhub_client = finnhub.Client(api_key=stock_settings.api_key)
try:
res = finnhub_client.company_profile2(symbol=self.symbol_name)
except (Exception, finnhub.exceptions.FinnhubAPIException) as error:
print(f" API limit reached. error is: {error}")
oth.log(error)
sys.exit()
return None
except Exception as error:
oth.log(error)
return None
return res
def execute_insert_sql(sql):
"""
Query any sql to insert data to table
:param sql: str
:return: results:data or None
"""
conn_db = connect()
cursor = conn_db.cursor()
try:
cursor.execute(sql)
except Exception as error:
print(f"error:{error}")
oth.log(error)
cursor.close()
conn_db.close()
return None
conn_db.commit()
cursor.close()
conn_db.close()
def execute_sql(sql):
"""
Query any sql in one table
:param sql: str
:return: results: list,但是类似含有元祖的列表，格式类似[('A',), ('AA',), ('AAAIF',), ('AAALF',)]
"""
conn_db = connect()
cursor = conn_db.cursor()
try:
cursor.execute(sql)
except Exception as error:
print(f"error:{error}")
oth.log(error)
cursor.close()
conn_db.close()
return None
results = cursor.fetchall()
conn_db.commit()
cursor.close()
conn_db.close()
return results
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
def pg_to_sql(table, path, columns, symbol=""):
"""
Import dataset to PostregSQL.very fast than old version.
:param table: str
:param path: str
:param columns: list
:param symbol: str
:return: 1 or None
"""
print("Begin import data.")
begin_time = time.time()
f = open(path, 'r')
conn = connect()
cursor = conn.cursor()
# 使用copy_from 数据必须是无标题
try:
cursor.copy_from(f, table, sep=",", columns=columns)
conn.commit()
except psycopg2.errors.InvalidTextRepresentation as error:
print(
f"Invalid input syntax for type:{error}. Symbol:{symbol}. But pass to continue."
)
oth.log(error, symbol)
conn.rollback()
cursor.close()
csv.clear_csv(path)
return None
except psycopg2.DatabaseError as error:
# clear_csv(path)
print(f"Import database error:{error}.Symbol:{symbol}")
oth.log(error, symbol)
conn.rollback()
cursor.close()
csv.clear_csv(path)
return None
except Exception as error:
print(f"Import error:{error}.Symbol:{symbol}")
oth.log(error, symbol)
conn.rollback()
cursor.close()
csv.clear_csv(path)
return None
cursor.close()
end_time = time.time()
print(f"Import to database cost :{round(end_time-begin_time, 2)}")
csv.clear_csv(path)
return 1
def download_finnhub(self):
"""
Only for daily.Search daily or minutes database to find max time.
:return:res
"""
max_t = pg.max_t(self.table, self.symbol_name)
from_t = max_t + 86400 # the seconds of one day are 86400
to_t = int(time.time())
try:
finnhub_client = finnhub.Client(api_key=stock_settings.api_key)
except ConnectionResetError as error:
print(f"ConnectionResetError={str(error)}")
oth.log(error, self.symbol_name)
return None
except requests.exceptions.ReadTimeout as error:
print(f"Connection Read Timeout=:{str(error)}")
oth.log(error, self.symbol_name)
return None
except requests.exceptions.ConnectionError as error:
print(f"ConnectionError=:{str(error)}")
oth.log(error, self.symbol_name)
return None
except Exception as error:
oth.log(error, self.symbol_name)
return None
try:
res = finnhub_client.stock_candles(self.symbol_name, self.item, from_t, to_t)
except finnhub.exceptions.FinnhubAPIException as error:
print(f"Bad gateway or limit reached. error=:{str(error)} ")
oth.log(error, self.symbol_name)
return None
except ValueError as error:
print(f"invalid=:{str(error)}")
oth.log(error, self.symbol_name)
return None
except Exception as error:
oth.log(error, self.symbol_name)
return None
return res
def download_finnhub(self):
"""
Download minute data using API
:return: res:data or None
"""
#
max_t = pg.max_t(self.table, self.symbol_name)
remark = self.symbol_name
try:
finnhub_client = finnhub.Client(api_key=stock_settings.api_key)
except ConnectionResetError as error:
print(f"ConnectionResetError=:{str(error)}")
oth.log(error, remark)
return None
except requests.exceptions.ReadTimeout as error:
print(f"Connection Read Timeout=:{str(error)}")
oth.log(error, remark)
return None
except requests.exceptions.ConnectionError as error:
print(f"ConnectionError=:{str(error)}")
oth.log(error, remark)
return None
except Exception as error:
oth.log(error, remark)
return None
one_year_ago_time = oth.get_day_time(365)
if max_t == 0:
from_t = one_year_ago_time
else:
from_t = max_t + 1
to_t = from_t + 86400 * 25 # about one month
# print(from_t, to_t)
try:
res = finnhub_client.stock_candles(self.symbol_name, self.item,
from_t, to_t)
except finnhub.exceptions.FinnhubAPIException as error:
print(f"Bad gateway or limit reached. error=:{str(error)} ")
oth.log(error, remark)
return None
except ValueError as error:
print(f"invalid=:{str(error)}")
oth.log(error, remark)
return None
except Exception as error:
oth.log(error, remark)
return None
return res
