# coding=utf-8
import pymysql
import traceback
import threading
import time
from SendEmail import Email
class MY_SQL:
def __init__(self):
self.db = pymysql.connect('localhost', 'root', 'eagle', 'db_east_money', charset='utf8')
# 写入分类表
def save_code_table(self, data):
cursor = self.db.cursor()
# cursor.execute('DROP TABLE IF EXISTS t_code')
#
# sql = '''
# CREATE TABLE `t_code` (
# `id` int(11) NOT NULL AUTO_INCREMENT,
# `code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '代码',
# `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '中文名称',
# `type` int(11) DEFAULT NULL COMMENT '0：上证 1：深证',
# PRIMARY KEY (`id`)
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
# '''
try:
# 创建表
# cursor.execute(sql)
for i in data:
code = str(i['f12'])
name = str(i['f14'])
type = str(i['f13'])
sql = "REPLACE INTO `t_code`(`id`, `code`, `name`, `type`) VALUES (uuid(), '%s', '%s', %s)" % (code, name, type)
cursor.execute(sql)
self.db.commit()
except Exception as e:
self.db.rollback()
Email.sendErr()
print(e)
# 写入个股详细数据
def save_code_info_table(self, sql):
cursor = self.db.cursor()
try:
cursor.execute(sql)
self.db.commit()
except Exception as e:
self.db.rollback()
print(e)
# 写入板块分类表
def save_bankuai_table(self, data):
cursor = self.db.cursor()
try:
for i in data:
cursor.execute(i)
self.db.commit()
except Exception as e:
self.db.rollback()
Email.sendErr()
print(e)
# 写入板块分类表
def save_day_line_table(self, data):
cursor = self.db.cursor()
try:
for i in data:
day = str(i[0])
kai = str(i[1])
shou = str(i[2])
gao = str(i[3])
di = str(i[4])
liang = str(i[5])
er = str(i[6])
fu = str(i[7])
code = str(i[8])
name = str(i[9])
market = str(i[10])
percent = str(i[11])
sql = "REPLACE INTO `t_day_line` " \
"(`code`, `name`, `market`, `riqi`, `kaipan`, `shoupan`, `zuigao`, `zuidi`, `chengjiaoliang`, `chengjiaoer`, `zhenfu`, `percent`) VALUES " \
"('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \
(code, name, market, day, kai, shou, gao, di, liang, er, fu, percent)
cursor.execute(sql)
self.db.commit()
except Exception as e:
self.db.rollback()
Email.sendErr()
print(e)
# 写入每日时间线表
def save_time_line_table(self, data, currentTime):
cursor = self.db.cursor()
try:
for i in data:
# 过滤今日没有行情的记录
if i['f2'] != '-' and i['f2'] != '':
code = str(i['f12'])
name = str(i['f14'])
market = str(i['f13'])
shijian = str(currentTime)
zuixinjia = str(i['f2'])
percent = str(i['f3'])
chengjiaoliang = str(i['f5'])
chengjiaoer = str(i['f6'])
zhenfu = str(i['f7'])
huanshoulv = str(i['f8'])
shiyinglv = str(i['f9'])
shijinglv = str(i['f10'])
jinkai = str(i['f17'])
zuoshou = str(i['f18'])
zuigao = str(i['f15'])
zuidi = str(i['f16'])
sql = "REPLACE INTO `t_time_line`(`code`, `name`, `market`, `shijian`, `zuixinjia`, `percent`, `chengjiaoliang`, `chengjiaoer`, `zhenfu`, `huanshoulv`, `shiyinglv`, `shijinglv`, `jinkai`, `zuoshou`, `zuigao`, `zuidi`) " \
"VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \
(code, name, market, shijian, zuixinjia, percent, chengjiaoliang, chengjiaoer, zhenfu, huanshoulv, shiyinglv, shijinglv, jinkai, zuoshou, zuigao, zuidi)
cursor.execute(sql)
self.db.commit()
except Exception as e:
self.db.rollback()
Email.sendErr()
print(e)
# 写入日线中的今日数据
def save_today_table(self, data, today):
cursor = self.db.cursor()
try:
for i in data:
day = today
kai = str(i['f17'])
shou = str(i['f2'])
gao = str(i['f15'])
di = str(i['f16'])
liang = str(i['f5'])
er = str(i['f6'])
fu = str(i['f7'])
code = str(i['f12'])
name = str(i['f14'])
market = str(i['f13'])
percent = str(i['f3'])
sql = "REPLACE INTO `t_day_line` " \
"(`code`, `name`, `market`, `riqi`, `kaipan`, `shoupan`, `zuigao`, `zuidi`, `chengjiaoliang`, `chengjiaoer`, `zhenfu`, `percent`) VALUES " \
"('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \
(code, name, market, day, kai, shou, gao, di, liang, er, fu, percent)
cursor.execute(sql)
self.db.commit()
except Exception as e:
self.db.rollback()
Email.sendErr()
print(e)
# 写入每日的实时板块资金流变动
def save_zijinliu_bankuai_table(self, data, type):
bkList = []
cursor = self.db.cursor()
try:
for i in data:
code = str(i['f12'])
name = str(i['f14'])
zhangfu = str(i['f3'])
level_zhuli = str(i['f62'])
level_zhuli_per = str(i['f184'])
level_chaoda = str(i['f66'])
level_chaoda_per = str(i['f69'])
level_da = str(i['f72'])
level_da_per = str(i['f75'])
level_zhong = str(i['f78'])
level_zhong_per = str(i['f81'])
level_xiao = str(i['f84'])
level_xiao_per = str(i['f87'])
shijian = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime((i['f124'])))
first_code = str(i['f205'])
first_name = str(i['f204'])
sql = "REPLACE INTO `t_zijinliu_bankuai` " \
"(`code`, `name`, `type`, `zhangfu`, `shijian`, `level_zhuli`, `level_zhuli_per`, `level_chaoda`, " \
"`level_chaoda_per`, `level_da`, `level_da_per`, `level_zhong`, `level_zhong_per`, `level_xiao`, " \
"`level_xiao_per`, `first_code`, `first_name`) VALUES " \
"('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \
(code, name, type, zhangfu, shijian, level_zhuli, level_zhuli_per, level_chaoda, level_chaoda_per,
level_da, level_da_per, level_zhong, level_zhong_per, level_xiao, level_xiao_per, first_code, first_name)
cursor.execute(sql)
info = {}
info[0] = code
info[1] = name
info[2] = type
bkList.append(info)
self.db.commit()
return bkList
except Exception as e:
self.db.rollback()
Email.sendErr()
print(e)
# 写入每日的实时个股资金流变动
def save_zijinliu_gegu_table(self, data):
cursor = self.db.cursor()
try:
for i in data:
cursor.execute(i)
self.db.commit()
except Exception as e:
self.db.rollback()
Email.sendErr()
print(e)
# 查询个股最新价
def getLastPrice(self, code):
cursor = self.db.cursor()
sql = 'SELECT shijian, zuixinjia, percent FROM t_time_line WHERE `code` = '+code+' ORDER BY shijian desc limit 1; '
try:
cursor.execute(sql)
results = cursor.fetchone()
return results
except Exception as e:
print(e)
# 查询待通知列表
def getNotifyList(self):
cursor = self.db.cursor()
sql = 'SELECT * FROM t_notify t WHERE t.maxType = 0 or t.minType = 0; '
try:
cursor.execute(sql)
results = cursor.fetchall()
return results
except Exception as e:
print(e)
# 更新通知列表个股的状态
def updateNotifyType(self, id, max, min, percent):
cursor = self.db.cursor()
sql = 'UPDATE t_notify SET maxType = %s, minType = %s, percent = %s, updateTime = NOW() WHERE id= %s;' % \
(max, min, percent, id)
try:
cursor.execute(sql)
self.db.commit()
except Exception as e:
self.db.rollback();
print(e)
# 更新通知列表个股的状态
def resetNotifyList(self):
cursor = self.db.cursor()
sql = 'SELECT id FROM t_notify; '
try:
cursor.execute(sql)
results = cursor.fetchall()
for i in results:
s = 'UPDATE t_notify SET maxType = 0, minType = 0, percent = 0 WHERE id = %s;' % \
(i[0])
cursor.execute(s)
self.db.commit()
except Exception as e:
self.db.rollback();
print(e)
# 写入临时个股连续2日高涨幅表
def save_tmp_day_line_table(self, data):
cursor = self.db.cursor()
try:
for i in data:
id = str(i[0])
code = str(i[1])
name = str(i[2])
market = str(i[3])
day = str(i[4])
kai = str(i[5])
shou = str(i[6])
gao = str(i[7])
di = str(i[8])
liang = str(i[9])
er = str(i[10])
fu = str(i[11])
percent = str(i[12])
sql = "REPLACE INTO `t_tmp_day_line` " \
"(`id`, `code`, `name`, `market`, `riqi`, `kaipan`, `shoupan`, `zuigao`, `zuidi`, `chengjiaoliang`, `chengjiaoer`, `zhenfu`, `percent`) VALUES " \
"('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \
(id, code, name, market, day, kai, shou, gao, di, liang, er, fu, percent)
cursor.execute(sql)
self.db.commit()
except Exception as e:
self.db.rollback()
Email.sendErr()
print(e)
# 查询00,60开头的code
def get3060CodeList(self):
cursor = self.db.cursor()
sql = "SELECT code FROM t_code where code like '00%' or code like '60%';"
try:
cursor.execute(sql)
results = cursor.fetchall()
return results
except Exception as e:
print(e)
# 查询code的日线
def getDayLineList(self, code, fromDay):
cursor = self.db.cursor()
sql = "SELECT * FROM t_day_line WHERE code = '%s' and riqi >= '%s' order by riqi DESC;" % \
(code, fromDay)
try:
cursor.execute(sql)
results = cursor.fetchall()
return results
except Exception as e:
print(e)
# 查询code的交易日期
def getTradeDayFromCode(self, code):
cursor = self.db.cursor()
sql = "SELECT DATE_FORMAT(shijian,'%%Y-%%m-%%d') day FROM t_time_line WHERE `code` = '%s' GROUP BY DATE_FORMAT(shijian, '%%Y-%%m-%%d') ORDER BY shijian;" % code
try:
cursor.execute(sql)
return cursor.fetchall()
except Exception as e:
print(e)
# 查询code的某一交易日的资金流主力记录
def getZhuLiPerFromDay(self, code, day):
cursor = self.db.cursor()
sql = "SELECT t.zuixinjia, z.zhangfu, z.level_zhuli_per, z.shijian FROM t_zijinliu_gegu z LEFT JOIN t_time_line t ON z.`code` = t.`code` AND DATE_FORMAT(z.shijian,'%Y-%m-%d') = DATE_FORMAT(t.shijian,'%Y-%m-%d') AND z.zhangfu = t.percent WHERE z.`code`='"+code+"' and z.shijian LIKE '"+day+"%' ORDER BY z.shijian;"
try:
cursor.execute(sql)
return cursor.fetchall()
except Exception as e:
print(e)
# 保存yanzheng1的计算结果
def saveYanZheng1(self, code, earn, price, time):
cursor = self.db.cursor()
sql = "INSERT INTO `t_yanzheng1` " \
"(`code`, `earn`, `price`, `time`) VALUES " \
"('%s', '%s', '%s', '%s')" % \
(code, earn, price, time)
cursor.execute(sql)
self.db.commit()
# 获取yanzheng1涨幅靠前的code
def calcYanZheng1Code(self):
cursor = self.db.cursor()
sql = "SELECT t3.CODE FROM (SELECT t.CODE,sum(t.earn) AS e,t.price FROM t_yanzheng1 t WHERE t.`code` IN (SELECT t2.CODE FROM t_yanzheng1 t2 GROUP BY t2.`code`) GROUP BY t.CODE) AS t3 WHERE t3.e> 0 AND t3.e/t3.price> 0.5;"
try:
cursor.execute(sql)
return cursor.fetchall()
except Exception as e:
print(e)
# 保存yanzheng1的结果code
def saveYanZheng1Code(self, code):
cursor = self.db.cursor()
sql = "INSERT INTO t_yanzheng_notify (code, STATUS) VALUES ('%s', 0);" % code
cursor.execute(sql)
self.db.commit()
def updateYanZheng1Code(self, id):
cursor = self.db.cursor()
sql = "UPDATE t_yanzheng_notify SET STATUS = 1 WHERE id = '%s';" % id
cursor.execute(sql)
self.db.commit()
def saveYanZheng1History(self, data):
cursor = self.db.cursor()
sql = "INSERT INTO t_yanzheng1_history (code, name, shijian, zhangfu, level_zhuli_per) VALUES ('%s', '%s','%s','%s','%s');" % \
(data[0], data[1], data[2], data[3], data[4])
cursor.execute(sql)
self.db.commit()
def getYanZheng1Code(self):
cursor = self.db.cursor()
sql = "SELECT id, code FROM t_yanzheng_notify WHERE STATUS = 0;"
cursor.execute(sql)
return cursor.fetchall()
def getLastZiJinGeGu(self, code, day):
cursor = self.db.cursor()
sql = "SELECT code, name, shijian, zhangfu, level_zhuli_per FROM t_zijinliu_gegu WHERE `code` = '%s' and DATE_FORMAT(shijian, '%%Y-%%m-%%d') = '%s' ORDER BY shijian DESC limit 1;" % \
(code, day)
cursor.execute(sql)
return cursor.fetchone()
def resetYanzhengNotify(self):
cursor = self.db.cursor()
sql = 'SELECT id FROM t_yanzheng_notify where status = 1; '
try:
cursor.execute(sql)
results = cursor.fetchall()
for i in results:
s = 'UPDATE t_yanzheng_notify SET status = 0 WHERE id = %s;' % \
(i[0])
cursor.execute(s)
self.db.commit()
except Exception as e:
self.db.rollback();
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)
