import pymysql import traceback import threading class MY_SQL: def __init__(self): self.db = pymysql.connect('localhost', 'root', 'eagle', 'tushare', charset='utf8') # # 写入分类表 # def save_cate_table(self, data): # # cursor = self.db.cursor() # # cursor.execute('DROP TABLE IF EXISTS t_cate') # # sql = ''' # CREATE TABLE `tushare`.`t_cate` ( # `id` int(0) NOT NULL AUTO_INCREMENT, # `code` varchar(255) NULL, # `name` varchar(255) NULL, # `cate` varchar(255) NULL, # PRIMARY KEY (`id`) # ); # ''' # # try: # # 创建表 # cursor.execute(sql) # # for i in data.index: # code = data.loc[i]['code'] # name = data.loc[i]['name'] # cate = data.loc[i]['c_name'] # # sql = "INSERT INTO `t_cate`(`code`, `name`, `cate`) VALUES ('%s', '%s', '%s')" % (code, name, cate) # # cursor.execute(sql) # self.db.commit() # # except Exception as e: # self.db.rollback() # print(e) # 写入分类表 def save_cate_table(self, data): cursor = self.db.cursor() cursor.execute('DROP TABLE IF EXISTS t_cate') sql = ''' CREATE TABLE `tushare`.`t_cate` ( `id` int(0) NOT NULL AUTO_INCREMENT, `code` varchar(255) NULL, `name` varchar(255) NULL, `cate` varchar(255) NULL, `ts` varchar(255) NULL, PRIMARY KEY (`id`) ); ''' try: # 创建表 cursor.execute(sql) for i in data.index: code = data.loc[i]['symbol'] name = data.loc[i]['name'] cate = data.loc[i]['industry'] ts = data.loc[i]['ts_code'] ts = ts.split('.')[1] sql = "INSERT INTO `t_cate`(`code`, `name`, `cate`, `ts`) VALUES ('%s', '%s', '%s', '%s')" % (code, name, cate, ts) cursor.execute(sql) self.db.commit() except Exception as e: self.db.rollback() print(e) # 按行业分类查询票 def getListForCate(self, catename): cursor = self.db.cursor() if catename == '': sql = 'select code, name, cate, ts from t_cate ' else: sql = 'select code, name, cate, ts from t_cate where cate = "' + catename + '"' try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() # self.db.close() return results except Exception as e: self.db.rollback() print(e) # 按行业分类查询票 def getPageForCate(self, fromID, toID): cursor = self.db.cursor() sql = 'select code, name, cate, ts from t_cate order by id limit %s, %s' % (fromID, toID) try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() return results except Exception as e: self.db.rollback() print(e) # 初始化实时行情表 def init_now_table(self): cursor = self.db.cursor() cursor.execute('DROP TABLE IF EXISTS t_now') sql = ''' CREATE TABLE `tushare`.`t_now` ( `id` int(0) NOT NULL AUTO_INCREMENT, `code` varchar(255) NULL, `name` varchar(255) NULL, `percent` varchar(255) NULL, `price` varchar(255) NULL, `turn` varchar(255) NULL, PRIMARY KEY (`id`) ); ''' try: # 创建表 cursor.execute(sql) except Exception as e: self.db.rollback() print(e) # 写入单条实时行情 def insert_now_table(self, code, name, percent, price, turn): cursor = self.db.cursor() try: sql = "INSERT INTO `t_now`(`code`, `name`, `percent`, `price`, `turn`) VALUES ('%s', '%s', '%s', '%s', '%s')" % ( code, name, percent, price, turn) cursor.execute(sql) self.db.commit() except Exception as e: print(e) # 初始化实时行情表 def init_now_fp_table(self): cursor = self.db.cursor() cursor.execute('DROP TABLE IF EXISTS t_now_fp') sql = ''' CREATE TABLE `tushare`.`t_now_fp` ( `id` int(0) NOT NULL AUTO_INCREMENT, `code` varchar(255) NULL, `name` varchar(255) NULL, `percent` varchar(255) NULL, `price` varchar(255) NULL, `turn` varchar(255) NULL, PRIMARY KEY (`id`) ); ''' try: # 创建表 cursor.execute(sql) except Exception as e: self.db.rollback() print(e) # 写入单条实时行情 def insert_now_fp_table(self, code, name, percent, price, turn): self.db.ping(reconnect=True) cursor = self.db.cursor() try: sql = "INSERT INTO `t_now_fp`(`code`, `name`, `percent`, `price`, `turn`) VALUES ('%s', '%s', '%s', '%s', '%s')" % ( code, name, percent, price, turn) # threading.Lock().acquire() cursor.execute(sql) # threading.Lock().release() cursor.close() self.db.commit() except Exception as e: traceback.print_exc() # 查询单票实时行情 def get_now_fp(self, code): cursor = self.db.cursor() if code == '': sql = 'select * from t_now_fp ' else: sql = 'select * from t_now_fp where code = "' + code + '"' try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() return results except Exception as e: print(e) # 写入实时行情 def save_now_table(self, data): cursor = self.db.cursor() cursor.execute('DROP TABLE IF EXISTS t_now') sql = ''' CREATE TABLE `tushare`.`t_now` ( `id` int(0) NOT NULL AUTO_INCREMENT, `code` varchar(255) NULL, `name` varchar(255) NULL, `percent` varchar(255) NULL, `price` varchar(255) NULL, `turn` varchar(255) NULL, PRIMARY KEY (`id`) ); ''' try: # 创建表 cursor.execute(sql) for i in data.index: code = data.loc[i]['code'] name = data.loc[i]['name'] percent = data.loc[i]['changepercent'] price = data.loc[i]['trade'] turn = data.loc[i]['turnoverratio'] sql = "INSERT INTO `t_now`(`code`, `name`, `percent`, `price`, `turn`) VALUES ('%s', '%s', '%s', '%s', '%s')" % (code, name, percent, price, turn) cursor.execute(sql) self.db.commit() except Exception as e: self.db.rollback() print(e) # 查询单票实时行情 def get_now(self, code): cursor = self.db.cursor() if code == '': sql = 'select * from t_now ' else: sql = 'select * from t_now where code = "' + code + '"' try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() return results except Exception as e: print(e) # 查询单票实时涨幅 def getfpPercent(self, code): cursor = self.db.cursor() sql = 'select percent from t_now_fp where code = "' + code + '"' try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() # self.db.close() return results except Exception as e: self.db.rollback() print(e) # 查询单票实时涨幅 def getPercent(self, code): cursor = self.db.cursor() sql = 'select percent from t_now where code = "' + code + '"' try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() # self.db.close() return results except Exception as e: self.db.rollback() print(e) # 清空历史表 def clear_history(self): cursor = self.db.cursor() cursor.execute('DROP TABLE IF EXISTS t_history') sql = ''' CREATE TABLE `tushare`.`t_history` ( `id` int(0) NOT NULL AUTO_INCREMENT, `code` varchar(255) NULL, `name` varchar(255) NULL, `cate` varchar(255) NULL, `his_percent` double(255, 2) NULL, `his_date` varchar(255) NULL, PRIMARY KEY (`id`) ); ''' try: # 创建表 cursor.execute(sql) except Exception as e: self.db.rollback() print(e) # 插入历史行情数据 def insertHistory(self, code, name, cate, date, percent): cursor = self.db.cursor() try: sql = "INSERT INTO `t_history`(`code`, `name`, `cate`, `his_percent`, `his_date`) VALUES ('%s', '%s', '%s', '%s', '%s')" % (code, name, cate, percent, date) cursor.execute(sql) cursor.close() self.db.commit() except Exception as e: # self.db.rollback() print(e) # 查询历史数据 def selectDay(self, code): cursor = self.db.cursor() sql = 'select code, name, cate, his_percent, his_date from t_history where code = "' + code + '"' try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() return results except Exception as e: self.db.rollback() print(e) # 根据日期和行业查询历史数据 def selectRelationStocks(self, day, cate): cursor = self.db.cursor() sql = 'select code from t_history where his_date = "' + day + '" and cate = "'+ cate +'"' try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() return results except Exception as e: self.db.rollback() print(e) # 查询历史数据 def selectCount(self, code): cursor = self.db.cursor() sql = 'select count(*) from t_history where code = "' + code + '"' try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchone() return results except Exception as e: self.db.rollback() print(e) # 清空历史表 def clear_history_fp(self): cursor = self.db.cursor() cursor.execute('DROP TABLE IF EXISTS t_history_fp') sql = ''' CREATE TABLE `tushare`.`t_history_fp` ( `id` int(0) NOT NULL AUTO_INCREMENT, `code` varchar(255) NULL, `name` varchar(255) NULL, `cate` varchar(255) NULL, `his_percent` double(255, 2) NULL, `his_date` varchar(255) NULL, PRIMARY KEY (`id`) ); ''' try: # 创建表 cursor.execute(sql) except Exception as e: self.db.rollback() print(e) # 插入历史行情数据 def insertHistoryFp(self, code, name, cate, date, percent): cursor = self.db.cursor() try: sql = "INSERT INTO `t_history_fp`(`code`, `name`, `cate`, `his_percent`, `his_date`) VALUES ('%s', '%s', '%s', '%s', '%s')" % ( code, name, cate, percent, date) cursor.execute(sql) cursor.close() self.db.commit() except Exception as e: # self.db.rollback() print(e) # 查询历史数据 def selectDayFp(self, code): cursor = self.db.cursor() sql = 'select code, name, cate, his_percent, his_date from t_history_fp where code = "' + code + '"' try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() return results except Exception as e: self.db.rollback() print(e) # 根据日期和行业查询历史数据 def selectRelationStocksFp(self, day, cate): cursor = self.db.cursor() sql = 'select code from t_history_fp where his_date = "' + day + '" and cate = "' + cate + '"' try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchall() return results except Exception as e: self.db.rollback() print(e) # 查询历史数据 def selectCountFp(self, code): cursor = self.db.cursor() sql = 'select count(*) from t_history_fp where code = "' + code + '"' try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchone() return results except Exception as e: self.db.rollback() print(e) # 保存结果 def save_result(self, time, sort_name, stock): cursor = self.db.cursor() try: sql = "INSERT INTO `t_result`(`result_time`, `result_sort`, `result_stock`, `create_time`) VALUES (\""+time+"\", \""+sort_name+"\", \""+stock+"\", now())" cursor.execute(sql) cursor.close() self.db.commit() except Exception as e: print(e) # 保存筛选结果到profit表 def save_to_profit(self, day, code, name, cate, price): cursor = self.db.cursor() try: sql = "INSERT INTO `t_profit`(`day`, `code`, `name`, `cate`, `price`, `profit`) VALUES (\"" + day + "\", \"" + code + "\", \"" + name + "\", \"" + cate + "\", \"" + price + "\", 0)" cursor.execute(sql) cursor.close() self.db.commit() except Exception as e: print(e) # 取某票某日的profit def get_pre_day_data(self, code, from_day): cursor = self.db.cursor() sql = 'select * from t_profit where code = "' + code + '" and day= \''+from_day + '\'' print(sql) try: # 执行SQL语句 cursor.execute(sql) # 获取所有记录列表 results = cursor.fetchone() return results except Exception as e: self.db.rollback() print(e) # 更新某票某日的profit def cale_profit(self, pid, profit): cursor = self.db.cursor() try: sql = "UPDATE t_profit set `profit` = %s where id = %s" % (profit, pid) cursor.execute(sql) cursor.close() self.db.commit() except Exception as e: print(e) # 保存5分钟线结果 def save_time_line(self, code, name, data): cursor = self.db.cursor() try: for i in data: sql = "INSERT INTO `t_time_line`(`code`, `name`, `day`, `open`, `close`, `high`, `low`, `volume`) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \ (code, name, i['day'], i['open'], i['close'], i['high'], i['low'], i['volume']) cursor.execute(sql) cursor.close() self.db.commit() except Exception as e: print(e) # 关闭数据库连接 def closeDB(self): self.db.close() # my = MY_SQL() # my.save_to_profit("2019-04-15", "000014", "ssss", "ffff", "12.96") # print(my.get_pre_day_data('000957', '2019-04-15')) # my.cale_profit(10, 6)