def getBiscicStock(self):
stockList=[]
connection=Connection()
connect = pymysql.Connect(
host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset
)
cursor = connect.cursor()
allStockBasic = "select * from stock_basic"
# allStockBasic = "select * from stock_basic where ts_code='300377.sz'"
cursor.execute(allStockBasic)
for row in cursor.fetchall():
realCode = self.tuShareCode2BaoStockCode(row[0])
temp=[]
temp.append(realCode)
temp.append(row[2])
temp.append(row[3])
temp.append(row[4])
stockList.append(temp)
cursor.close()
connect.close()
return stockList
def __init__(self):
self.currentPath = os.getcwd()
self.connection = Connection()
if self.connection.savePath != '':
self.currentPath = self.connection.savePath
if not os.path.exists(self.currentPath + "\\temp\\"):
os.makedirs(self.currentPath)
#设置一个默认的图片
if not os.path.exists(self.currentPath + "\\temp\\zMain.png"):
imgHeight = 200
imgWidth = 500
letterHeight = 10
letterWidth = 50
imgSize = (imgWidth, imgHeight)
bg_color = (255, 255, 255)
img = Image.new("RGB", imgSize, bg_color)
drawBrush = ImageDraw.Draw(img)
textY0 = (imgHeight - letterHeight + 1) / 2
textY0 = int(textY0)
textX0 = int((imgWidth - letterWidth + 1) / 2)
print('text location:', (textX0, textY0))
print('text size (width,height):', letterWidth, letterHeight)
print('img size(width,height):', imgSize)
font = ImageFont.truetype("C:\\Windows\\Fonts\\Arial.ttf", size=20)
fg_color = (0, 0, 0)
drawBrush.text((textX0, textY0),
"---zMain---",
fill=fg_color,
font=font)
img.save(self.currentPath + "\\temp\\zMain.png", quality=100)
def parseDataFromCvs(self, path, code, startTime, endTime, bili):
file = code.split(".")[1]
temp = self.tdxData.readData(path + file + ".csv", startTime, endTime)
sql = "insert into noun.`" + code + "` values"
if len(temp) <= 0:
return
sqlTemp = ""
# '2021-07-15', '000002', '23.21', '23.73', '23.01', '23.52', '63981243', '1498254976'
for item in temp:
sqlTemp = sqlTemp + ",('" + item[0] + "','" + code + "','" + item[
2] + "','" + item[3] + "','" + item[4] + "','" + item[
5] + "'," + "0,'" + item[6] + "','" + item[
7] + "',3,'" + str(float(item[6]) / bili) + "',1,0,0)"
sqlTemp = sqlTemp.strip(",")
sql = sql + sqlTemp
connection = Connection()
connect = pymysql.Connect(host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset)
# 获取游标
cursor = connect.cursor()
cursor.execute(sql)
connect.commit()
def queryYouCanBuyStock(self):
# 连接数据库
codes = []
connection = Connection()
connect = pymysql.Connect(host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset)
cursor = connect.cursor()
# 获取游标
today = self.todayIsTrue()[0]
# 查询数据
sql = "SELECT * FROM `candidate_stock` where collect_date='%s' order by cv asc,grad desc"
data = (today)
cursor.execute(sql % data)
for row in cursor.fetchall():
temp = []
temp.append(row[1])
temp.append(row[2])
temp.append(row[3])
temp.append(row[4])
temp.append(row[5])
#主力、散户、反转
temp.append(row[12])
codes.append(temp)
# 关闭连接
cursor.close()
connect.close()
return codes
def queryStockYouBrought(self, statisticSql):
result = []
sql = "select a.* from candidate_stock a inner join (SELECT id,code,min(collect_date),profit from candidate_stock where " + statisticSql + " group by code order by profit desc) b where a.id=b.id"
connection = Connection()
connect = pymysql.Connect(
host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset,
sql_mode=
"STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
)
# 获取游标
cursor = connect.cursor()
# 查询数据
cursor.execute(sql)
for row in cursor.fetchall():
temp = []
temp.append(row[1])
temp.append(row[2])
temp.append(row[3])
temp.append(row[5])
temp.append(row[6])
temp.append(row[7])
temp.append(row[8])
temp.append(row[9])
# temp.append(row[10])
result.append(temp)
# 关闭连接
cursor.close()
connect.close()
return result
def queryStockYouBrought(self):
result = []
sql = "SELECT * FROM candidate_stock where is_down_line=1 and profit!=0 and price<=10 order by profit desc"
sql = "select a.* from candidate_stock a inner join (SELECT id,code,min(collect_date),profit from candidate_stock where is_down_line=1 and profit!=0 and price<=10 and price>3 group by code order by profit desc) b where a.id=b.id"
connection = Connection()
connect = pymysql.Connect(host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset)
# 获取游标
cursor = connect.cursor()
# 查询数据
cursor.execute(sql)
for row in cursor.fetchall():
temp = []
temp.append(row[1])
temp.append(row[2])
temp.append(row[3])
temp.append(row[5])
temp.append(row[6])
temp.append(row[7])
temp.append(row[8])
temp.append(row[9])
# temp.append(row[10])
result.append(temp)
# 关闭连接
cursor.close()
connect.close()
return result
def queryHistoryStock(self):
codeList = []
sql = 'SELECT * FROM candidate_stock'
# 连接数据库
connection = Connection()
connect = pymysql.Connect(host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset)
# 获取游标
cursor = connect.cursor()
# 查询数据
cursor.execute(sql)
for row in cursor.fetchall():
temp = []
temp.append(row[0])
temp.append(row[1])
temp.append(row[2])
temp.append(row[3])
temp.append(row[4])
temp.append(row[5])
codeList.append(temp)
# 关闭连接
cursor.close()
connect.close()
return codeList
def fetchPriceList(self, code, collectTime):
prices = []
sql = "SELECT close,date FROM `" + code + "` where date ='" + collectTime + "' union all (select close,date from `" + code + "` order by date desc limit 1)"
connection = Connection()
connect = pymysql.Connect(host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset)
# 获取游标
cursor = connect.cursor()
# 查询数据
cursor.execute(sql)
for row in cursor.fetchall():
temp = []
temp.append(row[0])
temp.append(row[1])
prices.append(temp)
if len(prices) == 1:
prices.append(prices[0])
# 关闭连接
cursor.close()
connect.close()
return prices
def fetchByStartAndEndTime(self, code, startTime, endTime):
if code == None:
return
if startTime == None:
startTime = '1999-07-01'
if endTime == None:
endTime = time.strftime('%Y-%m-%d', time.localtime(time.time()))
lg = bs.login()
rs = bs.query_history_k_data_plus(
code,
"date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,isST",
start_date=startTime,
end_date=endTime,
frequency="d",
adjustflag="3")
#### 打印结果集 ####
data_list = []
while (rs.error_code == '0') & rs.next():
data_list.append(rs.get_row_data())
result = pd.DataFrame(data_list, columns=rs.fields)
##将数据写入mysql的数据库，但需要先通过sqlalchemy.create_engine建立连接,且字符编码设置为utf8，否则有些latin字符不能处理
connection = Connection()
connectionStr = "mysql+pymysql://" + connection.user + ":" + connection.passwd + "@" + connection.host + ":" + str(
connection.port) + "/" + connection.db + "?charset=utf8"
engine = create_engine(connectionStr, pool_size=20, pool_recycle=60)
# 插入数据库
result.to_sql(name=code,
con=engine,
if_exists='append',
index=False,
index_label=False)
def queryStock(self, stackCode):
# 连接数据库
resultTemp=[]
connection=Connection()
connect = pymysql.Connect(
host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset
)
# 获取游标
cursor = connect.cursor()
# 查询数据
sql = "select * from (SELECT DISTINCT * FROM `"+stackCode+"` where tradestatus=1 and turn is not null order by date desc limit %i) as b order by date asc"
data = (self.window+80)
cursor.execute(sql % data)
fs = cursor.description
filelds = []
for field in fs:
filelds.append(field[0])
rs = cursor.fetchall()
result = pd.DataFrame(list(rs), columns=filelds)
# 关闭连接
cursor.close()
connect.close()
#二维数组
result=result.loc[:,['date','open','high','low','close','volume','turn','tradestatus'] ]
#计算三十日均线
result['M30']=talib.SMA(result['close'],30)
result['T30']=talib.T3(result['close'],timeperiod=30, vfactor=0)
result['tprice']=talib.TYPPRICE(result['high'],result['low'],result['close'])
# slowk, slowd = talib.STOCH(result['high'],result['low'],result['close'], fastk_period=9, slowk_period=3, slowk_matype=0, slowd_period=3, slowd_matype=0)
# slowj= list(map(lambda x,y: 3*x-2*y, slowk, slowd))
# result['k']=slowk
# result['d']=slowd
# result['j']=slowj
zsindex=ZSIndex()
# 主力线，散户线
zz, ss = zsindex.zsLine(result)
mm = zsindex.convertXQH(result)
result['z'] = zz
result['s'] = ss
result['m'] = mm
#神仙趋势线
result['h1']=talib.EMA(result['close'],6)
result['h2']=talib.EMA(result['h1'],18)
result['h3']=talib.EMA(result['close'],108)
maxPrice=talib.MAX(result['close'],data)[len(result)-1]
print(maxPrice)
result.date = range(0, len(result)) # 日期改变成序号
resultTemp.append(result)
resultTemp.append(maxPrice)
return resultTemp
def sendStockInfo(self, codes, currentPath):
con = Connection()
myContent = "<h4><font color = 'red' > " + self.getJingjuNext(
) + " </font ></h4></br>"
imgsOKstr = myContent + "当下可选股票："
count = 60
#前二十的股票提供图片显示
for item in codes:
if count > 0:
imgsOKstr = imgsOKstr + "<p>" + str(item[0]) + " " + str(
item[1]
) + " " + str(item[2]) + " " + str(
item[3]) + " <img src='cid:" + item[0] + "'></p>"
else:
imgsOKstr = imgsOKstr + "<p>" + str(item[0]) + " " + str(
item[1]) + " " + str(item[2]) + "</p>"
count = count - 1
endDate = time.strftime('%Y-%m-%d', time.localtime(time.time()))
my_pass = con.emailPass
my_user = con.emaialUser
sender = con.sender
receivers = con.receivers
msgRoot = MIMEMultipart('related')
msgRoot['From'] = Header(str(endDate) + " 股票推荐", 'utf-8')
msgRoot['To'] = Header("测试", 'utf-8')
subject = str(endDate) + ' 股市有风险，投资需谨慎'
msgRoot['Subject'] = Header(subject, 'utf-8')
msgAlternative = MIMEMultipart('alternative')
msgRoot.attach(msgAlternative)
mail_msg = imgsOKstr
msgAlternative.attach(MIMEText(mail_msg, 'html', 'utf-8'))
# 指定图片为当前目录
count = 20
for item in codes:
if count > 0:
fp = open(currentPath + '\\temp\\' + item[0] + ".png", 'rb')
msgImage = MIMEImage(fp.read())
fp.close()
temp = "<" + item[0] + ">"
# 定义图片 ID，在 HTML 文本中引用
msgImage.add_header('Content-ID', temp)
msgRoot.attach(msgImage)
count = count - 1
try:
users = receivers.split(',')
for item in users:
smtpObj = smtplib.SMTP()
smtpObj.connect('smtp.qq.com', 25) # 25 为 SMTP 端口号
smtpObj.login(my_user, my_pass)
smtpObj.sendmail(sender, item, msgRoot.as_string())
print("邮件发送成功")
except smtplib.SMTPException:
print("Error: 无法发送邮件")
def doSendStockInfoBeautiful(self, codes, currentPath, subject):
con = Connection()
imgsOKstr = "股票总计：" + str(len(codes))
count = 80
#前二十的股票提供图片显示
for item in codes:
if count > 0:
imgsOKstr = imgsOKstr + "<p>" + str(item[0]) + " " + str(
item[1]
) + " " + str(item[2]) + " " + str(
item[3]) + " <img src='cid:" + item[0] + "'></p>"
else:
imgsOKstr = imgsOKstr + "<p>" + str(item[0]) + " " + str(
item[1]) + " " + str(
item[2]) + " " + str(item[3]) + "</p>"
count = count - 1
endDate = time.strftime('%Y-%m-%d', time.localtime(time.time()))
my_pass = con.emailPass
my_user = con.emaialUser
sender = con.sender
receivers = con.receivers
msgRoot = MIMEMultipart('related')
msgRoot['From'] = Header(str(endDate) + subject, 'utf-8')
msgRoot['To'] = Header("测试", 'utf-8')
subject = str(endDate) + subject
msgRoot['Subject'] = Header(subject, 'utf-8')
msgAlternative = MIMEMultipart('alternative')
msgRoot.attach(msgAlternative)
mail_msg = imgsOKstr
msgAlternative.attach(MIMEText(mail_msg, 'html', 'utf-8'))
# 指定图片为当前目录
count = 80
for item in codes:
if count > 0:
fp = open(currentPath + '\\temp\\' + item[0] + ".png", 'rb')
msgImage = MIMEImage(fp.read())
fp.close()
temp = "<" + item[0] + ">"
# 定义图片 ID，在 HTML 文本中引用
msgImage.add_header('Content-ID', temp)
msgRoot.attach(msgImage)
count = count - 1
try:
smtpObj = smtplib.SMTP()
smtpObj.connect('smtp.qq.com', 25) # 25 为 SMTP 端口号
smtpObj.login(my_user, my_pass)
smtpObj.sendmail(sender, receivers, msgRoot.as_string())
print("邮件发送成功")
except smtplib.SMTPException:
print("Error: 无法发送邮件")
def synStockInfo(self):
# 获取游标
connection = Connection()
connect = pymysql.Connect(host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset)
cursor = connect.cursor()
allStockBasic = 'select * from stock_basic'
cursor.execute(allStockBasic)
startTime = ''
endTime = time.strftime('%Y-%m-%d', time.localtime(time.time()))
isToady = False
for row in cursor.fetchall():
isToady = False
print(row[0])
realCode = self.tuShareCode2BaoStockCode(row[0])
tableCheckSql = "show tables like '" + realCode + "'"
cursor.execute(tableCheckSql)
if len(list(cursor)) == 0:
print("no data of " + realCode)
startTime = '1997-07-01'
else:
# 查找股票的最近时间
sql = "SELECT * FROM `%s` order by date desc limit 1;"
data = (realCode)
cursor.execute(sql % data)
#如果没有数据那么设置为1997年开始
for row in cursor.fetchall():
startTime1 = row[0]
if startTime1 == endTime:
isToady = True
continue
str_p = startTime1 + ' 0:29:08'
dateTime_p = datetime.datetime.strptime(
str_p, '%Y-%m-%d %H:%M:%S')
startTime = (
dateTime_p +
datetime.timedelta(days=+1)).strftime("%Y-%m-%d")
if isToady == True:
print(realCode + "--不需要同步了。。")
else:
print("syn " + realCode)
fectExecute = StockFetch()
fectExecute.fetchByStartAndEndTime(realCode, startTime,
endTime)
if self.isJgdy == 'True':
jgdy = JgdyQuery()
jgdy.printJgdyInfo(realCode.split('.')[1], 1)
cursor.close()
connect.close()
def sendStatistic(self, result, title):
successCount = 0
myContent = "<h4><font color = 'red' > " + self.getJingjuNext(
) + " </font ></h4></br>"
htmls = myContent + "<table border='1'>"
htmls = htmls + "<tr><td>代码</td><td>名称</td><td>买入时间</td><td>grad</td><td>cv</td><td>买入价格</td><td>当前价格</td><td>增长幅度100%</td></tr>"
for item in result:
htmls = htmls + "<tr>"
if float(item[7]) > 0:
successCount = successCount + 1
for vo in range(len(item)):
if float(item[7]) > 0:
htmls = htmls + "<td bgcolor='#FFCC66'><font color='red'>" + str(
item[vo]) + "</font></td>"
else:
htmls = htmls + "<td bgcolor='#00FF00'><font color='blue'>" + str(
item[vo]) + "</font></td>"
htmls = htmls + "</tr>"
htmls = htmls + "</table>"
totalCount = len(result)
if totalCount == 0:
totalCount = 1
endHtml = "增长个数:" + str(
successCount) + "    总共个数：" + str(
totalCount) + " </br> 百分比：" + str(
successCount * 100 / totalCount) + "%" + htmls
con = Connection()
endDate = time.strftime('%Y-%m-%d', time.localtime(time.time()))
my_pass = con.emailPass
my_user = con.emaialUser
sender = con.sender
receivers = con.receivers
msgRoot = MIMEMultipart('related')
msgRoot['From'] = Header(str(endDate) + " 股票统计", 'utf-8')
msgRoot['To'] = Header("测试", 'utf-8')
subject = str(endDate) + title
msgRoot['Subject'] = Header(subject, 'utf-8')
msgAlternative = MIMEMultipart('alternative')
msgRoot.attach(msgAlternative)
mail_msg = endHtml
msgAlternative.attach(MIMEText(mail_msg, 'html', 'utf-8'))
try:
users = receivers.split(',')
for item in users:
smtpObj = smtplib.SMTP()
smtpObj.connect('smtp.qq.com', 25) # 25 为 SMTP 端口号
smtpObj.login(my_user, my_pass)
smtpObj.sendmail(sender, item, msgRoot.as_string())
print("邮件发送成功")
except smtplib.SMTPException:
print("Error: 无法发送邮件")
def updateCandidate(self, id, oldPrice, nowPrice, profit, time):
sql = "update candidate_stock set other='" + time + "', price= " + str(
oldPrice) + ",now_price=" + str(nowPrice) + ",profit=" + str(
profit) + " where id ='" + id + "'"
connection = Connection()
connect = pymysql.Connect(host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset)
# 获取游标
cursor = connect.cursor()
# 查询数据
cursor.execute(sql)
connect.commit()
# 关闭连接
cursor.close()
connect.close()
def todayIsTrue(self):
temp = []
sql = "SELECT max(date) FROM `sh.600000`"
connection = Connection()
connect = pymysql.Connect(host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset)
# 获取游标
cursor = connect.cursor()
# 查询数据
cursor.execute(sql)
for row in cursor.fetchall():
temp.append(row[0])
# 关闭连接
cursor.close()
connect.close()
return temp
def todayKlineByCode(self, code):
# 连接数据库
price = 0
connection = Connection()
connect = pymysql.Connect(host=connection.host,
port=connection.port,
user=connection.user,
passwd=connection.passwd,
db=connection.db,
charset=connection.charset)
# 获取游标
cursor = connect.cursor()
# 查询数据
sql = "SELECT * FROM `" + code + "` where tradestatus=1 and turn is not null order by date desc limit 1"
cursor.execute(sql)
for row in cursor.fetchall():
price = float(row[5])
# 关闭连接
cursor.close()
connect.close()
return price
def __init__(self):
self.currentPath = os.getcwd()
self.connection = Connection()
def __init__(self):
self.connection = Connection()