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 insert_news_table(self, time, info, date):
self.db.ping(reconnect=True)
cursor = self.db.cursor()
try:
sql = "REPLACE INTO `tushare`.`t_news`(`n_time`, `n_info`, `n_hash`, `n_date`) VALUES ('"+time+"', '"+info+"', md5('"+info+"'), '"+date+"');"
# threading.Lock().acquire()
cursor.execute(sql)
# threading.Lock().release()
cursor.close()
self.db.commit()
except Exception as e:
traceback.print_exc()
# 关闭数据库连接
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)
