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