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
