def get_data_from_db_by_date_list(self, ts_code, date_list): # get tick data from database ''' :param cursor: 游标 :param table_name: 表名 :param start: YYYY-MM-DD :param end: YYYY-MM-DD :param is_asc: 排序 :return: 数据库股票数据 ''' cursor = self.g_connection.cursor() table_name = self.get_table_name() date_list = ['\'{}\''.format(a) for a in date_list] condaiton_str = ','.join(date_list) select = 'SELECT * FROM {} WHERE cal_date in ({}) and ts_code = \'{}\';'\ .format(table_name, condaiton_str, ts_code) # tm_print(select) df = [] try: cursor.execute(select) result = cursor.fetchall() df = pd.DataFrame(list(result), columns=self.trade_has_data_columns) except pymysql.Warning as msg: tm_print(msg) except pymysql.Error as e: tm_print(e) return df
def create_table(self, ts_code): cursor = self.g_connection.cursor() # # 创建Table table_name = self.get_table_name(ts_code) element = ''' {} varchar(255) NOT NULL, {} datetime NOT NULL, {} decimal(19,4) NULL, {} decimal(19,4) NULL, {} decimal(19,4) NULL, {} decimal(19,4) NULL, {} bigint NULL, {} bigint NULL, {} varchar(255) NOT NULL, {} decimal(19,4) NULL, PRIMARY KEY (trade_time) '''.format(*self.oms_columns) sql_create_table_cmd = 'CREATE TABLE IF NOT EXISTS {} ({}) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;'.format( table_name, element) try: cursor.execute(sql_create_table_cmd) except pymysql.Warning as msg: tm_print(msg) except pymysql.Error as e: tm_print(e)
def get_data_from_db(self, ts_code, start, end, is_asc): start = start[0:4] + '-' + start[4:6] + '-' + start[6:8] end = end[0:4] + '-' + end[4:6] + '-' + end[6:8] # get tick data from database ''' :param cursor: 游标 :param table_name: 表名 :param start: YYYY-MM-DD :param end: YYYY-MM-DD :param is_asc: 排序 :return: 数据库股票数据 ''' cursor = self.g_connection.cursor() table_name = self.get_table_name(ts_code) order = 'ASC' if is_asc else 'DESC' # end = tmd.date_add_days(end, 1) select = 'SELECT * FROM {} WHERE trade_time>\'{}\' and trade_time<\'{}\' ORDER BY trade_time {};'.format( table_name, start, end, order) # tm_print(select) df = [] try: cursor.execute(select) result = cursor.fetchall() df = pd.DataFrame(list(result), columns=self.oms_columns) except pymysql.Warning as msg: tm_print(msg) except pymysql.Error as e: tm_print(e) return df
def get_data_from_db(self, ts_code, start, end): ''' :param cursor: 游标 :param table_name: 表名 :param start: YYYY-MM-DD :param end: YYYY-MM-DD :param is_asc: 排序 :return: 数据库股票数据 ''' # get tick data from database cursor = self.g_connection.cursor() table_name = self.get_table_name() select = 'SELECT * FROM {} WHERE cal_date>=\'{}\' and cal_date<=\'{}\';'.format( table_name, start, end) # tm_print(select) df = [] try: cursor.execute(select) result = cursor.fetchall() df = pd.DataFrame(list(result), columns=self.trade_has_data_columns) except pymysql.Warning as msg: tm_print(msg) except pymysql.Error as e: tm_print(e) return df
def get_table_name(self, ts_code): # ts_code改表名 arr = ts_code.split('.') if len(arr) != 2: tm_print('Ts_code Type Error') return '' table_name = arr[1] + arr[0] return table_name
def get_exist_trade_date_index(self, ts_code, start, end): start = start[0:4] + '-' + start[4:6] + '-' + start[6:8] end = end[0:4] + '-' + end[4:6] + '-' + end[6:8] cursor = self.g_connection.cursor() table_name = self.get_table_name(ts_code) select = 'SELECT DISTINCT trade_date FROM {} \ WHERE trade_time>\'{}\' and trade_time<\'{}\' \ ORDER BY trade_date ASC;'.format(table_name, start, end) result = [] # print(select) try: cursor.execute(select) # result = pd.DataFrame(list(cursor.fetchall())) result = [date[0] for date in cursor.fetchall()] except pymysql.Warning as msg: tm_print(msg) except pymysql.Error as e: tm_print(e) return result
def create_table(self): cursor = self.g_connection.cursor() # # 创建Table table_name = self.get_table_name() element = ''' {} varchar(255) NOT NULL, {} varchar(255) NOT NULL, {} datetime NOT NULL, {} bool NULL, {} bool NULL, PRIMARY KEY (cal_date), KEY index_ts_code (ts_code) '''.format(*self.trade_has_data_columns) # sql_create_table_cmd = 'CREATE TABLE IF NOT EXISTS {} ({}) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8MB4;'.format( sql_create_table_cmd = 'CREATE TABLE IF NOT EXISTS {} ({}) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;'.format( table_name, element) try: cursor.execute(sql_create_table_cmd) except pymysql.Warning as msg: tm_print(msg) except pymysql.Error as e: tm_print(e)
def connect_db(self): conf_dict = tmc.get_mysql_config_dict() # 连接数据库 host = conf_dict['host'] port = int(conf_dict['port']) user = conf_dict['user'] password = conf_dict['password'] db_name = conf_dict['db'] self.g_connection = pymysql.connect( port=port, host=host, user=user, password=password, # db='demo', charset='utf8') cursor = self.g_connection.cursor() sql_create_db_cmd = 'CREATE DATABASE IF NOT EXISTS {};'.format(db_name) sql_use_db = 'USE {};'.format(db_name) try: cursor.execute(sql_create_db_cmd) cursor.execute(sql_use_db) except Exception as msg: tm_print(msg)
def insert_data(self, df): cursor = self.g_connection.cursor() table_name = self.get_table_name() if len(df) == 0: tm_print('No Data') return # df = df.fillna(None) res = zip(*(df[a] for a in self.trade_has_data_columns)) sql = 'INSERT IGNORE INTO {} VALUES '.format(table_name) for i in res: sql = sql + '{}'.format(i) + ',' sql = sql.strip(',') try: cursor.execute(sql) # cursor.executeMany(sql) self.g_connection.commit() except pymysql.Warning as msg: tm_print(msg) except pymysql.Error as e: tm_print(e) return False # cursor.close() return True