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`(`code`, `name`, `type`) VALUES ('%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_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 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)
