def buy(code, threadName=None):
# loadDataSql = "select * " \
# "from stock_daily_macd_deviate d " \
# "left join stock_daily_data sd on sd.sCode = d.sCode " \
# "where d.iDirectionType=2 " \
# "and d.sCode='" + code + "' " \
# "and d.tDeviateDateTime>='2008-01-01' " \
# "and sd.iOpeningPrice is not null " \
# "group by d.tDeviateDateTime "
# # " and tApexDateTime='2017-04-11'"
engine = sql_model.get_conn()
loadDataSql = "SELECT d.id, d.sCode, d.tDeviateDateTime FROM stock_daily_macd_deviate d LEFT JOIN stock_daily_macd_deviate_buy buy ON buy.iDeviateId = d.id " \
"WHERE d.sCode = '" + code + "' AND d.iDirectionType=2 AND buy.id IS NULL"
# print(loadDataSql)
df = pd.read_sql(loadDataSql, engine)
if len(df) < 1:
return
# exit()
# row_array = sql_model.getAll(loadDataSql)
# for a in row_array:
# print(a)
# exit()
# 最终结果列表
dataList = pd.DataFrame(columns=['sCode', 'iDeviateId', 'tDateTime'])
for index, row in df.iterrows():
tDeviateDateTime = str(row['tDeviateDateTime'])
#确认买入点
loadDataSql = "select m.tDateTime, d.iOpeningPrice, d.iClosingPrice " \
"from stock_daily_macd m " \
"left join stock_daily_data d on m.tDateTime=d.tDateTime and m.sCode=d.sCode " \
"where m.tDateTime > '" + tDeviateDateTime +"' " \
"and m.iBar > 0 " \
"and m.sCode='" + code + "' " \
"and d.iOpeningPrice is not null " \
"limit 1"
# print(loadDataSql)
buy_df = pd.read_sql(loadDataSql, engine)
_dataList = pd.DataFrame(
[[code, str(row['id']), buy_df['tDateTime'][0]]],
columns=['sCode', 'iDeviateId', 'tDateTime'])
dataList = dataList.append(_dataList)
result = sql_model.loadData('stock_daily_macd_deviate_buy',
dataList.keys(), dataList.values, threadName)
print(result)
import tushare as ts
import sql_model
from debug import p
# 行业分类
df = ts.get_industry_classified()
df.rename(columns={
'c_name': 'sIndustryName',
'name': 'sName',
'code': 'sCode'
},
inplace=True)
result = sql_model.loadData('stock_sector_industry', df.keys(), df.values)
print("行业: %s" % result)
# 概念分类
df = ts.get_concept_classified()
df.rename(columns={
'c_name': 'sConceptName',
'name': 'sName',
'code': 'sCode'
},
inplace=True)
result = sql_model.loadData('stock_sector_concept', df.keys(), df.values)
print("概念: %s" % result)
# 地域分类
df = ts.get_area_classified()
df.rename(columns={
'area': 'sAreaName',
'name': 'sName',
# 网址
url = "http://q.stock.sohu.com/hisHq?code=cn_601766,cn_600077&start=19860101&end=20170901"
# url = "http://q.stock.sohu.com/hisHq?code=cn_601766,cn_600077&start=20170701&end=20170705"
# 请求
request = urllib.request.Request(url)
# 爬取结果
response = urllib.request.urlopen(request)
data = response.read()
# 设置解码方式
data = data.decode('utf-8')
# 讲string转成jsonlist
array = json.loads(data)
# 遍历jsonlist
aDataInfo = []
for i in array:
# 取出股票编码
code = i['code'][3:9]
# 遍历股票详情
for j in i['hq']:
# 拼装格式为
# 编号 日期 开盘 收盘 涨跌 涨幅(%) 最低 最高 总手 金额 换手率(%)
thisDataInfo = [code]
for k in j:
thisDataInfo.append(k.replace("%", ""))
aDataInfo.append(thisDataInfo)
aField = ['sCode', 'tDateTime', 'iOpeningPrice', 'iClosingPrice', 'iTodayPrice', 'iPriceChangeRatio', 'iMinimumPrice', 'iMaximumPrice', 'iVol', 'iTurnover', 'iTurnoverRate']
sql_model.loadData("stock_daily_data", aField, aDataInfo)
print("ok")
print(loadDataSql)
df = pd.read_sql(loadDataSql, conn)
if df.empty:
continue
# 判断是否有历史数据
if not history_macd_df.empty:
df.at[0, 'iEmaShort'] = history_macd_df.ix[0, 'iEmaShort']
df.at[0, 'iEmaLong'] = history_macd_df.ix[0, 'iEmaLong']
df.at[0, 'iDif'] = history_macd_df.ix[0, 'iDif']
df.at[0, 'iDea'] = history_macd_df.ix[0, 'iDea']
df.at[0, 'iBar'] = history_macd_df.ix[0, 'iBar']
macd_data = get_MACD(df, 12, 26, 9)
macd_data = macd_data[[
'sCode', 'tDateTime', 'iEmaShort', 'iEmaLong', 'iDif', 'iDea', 'iBar'
]]
# p(macd_data)
# aField = ['sCode', 'tDateTime', 'iEmaShort', 'iEmaLong', 'iDif', 'iDea', 'iBar']
result = sql_model.loadData('stock_daily_macd', macd_data.keys(),
macd_data.values)
print(s + " end ")
#
# debug.p(macd_data)
# # result = sql_model.loadData('stock_daily_macd', aField, dataList)
# aField = ['sCode', 'tDateTime', 'iEmaShort', 'iEmaLong', 'iDif', 'iDea', 'iBar']
# result = sql_model.loadData('stock_daily_macd', macd_data.keys(), macd_data.values)
#
# print(result)
# # print(loadDataSql)
# # print(historyDateTime)
# # print(historyDeviateRowArray)
# # for h in historyDeviateRowArray:
# # print(h[0])
# for a in dataList:
# print(a)
# exit()
engine = sql_model.get_conn()
sql = "select * from stock_basics order by code asc"
# sql = "select * from stock_basics where code > '601312' order by code asc"
# sql = "select * from stock_basics where code= '000002' order by code asc"
df = pd.read_sql(sql, engine)
code_list = df['code']
macd_data_deviate = pd.DataFrame()
for s in code_list:
print(s + " begin ")
macd_data_deviate = get_macd_deviate(s)
if len(macd_data_deviate) > 0:
# aField = ['sCode', 'tDateTime', 'iEmaShort', 'iEmaLong', 'iDif', 'iDea', 'iBar']
result = sql_model.loadData('stock_daily_macd_deviate',
macd_data_deviate.keys(),
macd_data_deviate.values)
print(s + " end ")
# aField = ['sCode', 'tBeginDateTime', 'iBeginDif', 'tDeviateDateTime', 'iDeviateDif', 'iDirectionType']
# result = sql_model.loadData('stock_daily_macd_deviate', aField, dataList)
# print(result)
# encoding: utf-8
import sql_model
import pandas as pd
import common
import debug
import numpy as np
stock_data = common.get_average_line('600077')
stock_data = stock_data.fillna(value=0)
# df = stock_data.loc[:, ['sCode', 'tDateTime', 'ma5', 'ma10', 'ma20', 'ma30', 'ma60', 'ma120']]
df = stock_data.astype(np.str)
# debug.p(df.dtypes)
dataList = df.values
aField = ['id', 'sCode', 'tDateTime', 'iOpeningPrice', 'iClosingPrice', 'iTodayPrice',
'iPriceChangeRatio', 'iMinimumPrice', 'iMaximumPrice', 'iVolume', 'iAmount', 'iTurnoverRate',
'ma5', 'ma10', 'ma20', 'ma30', 'ma60', 'ma120']
result = sql_model.loadData('stock_daily_data', aField, dataList)
# engine = mysql_model.get_conn()
# stock_data.to_sql('stock_daily_data', engine, if_exists='append', index=False)
macd_data_apex = macd_data_apex.append(data_list, ignore_index=True)
# debug.p(macd_data_apex[[ 'tBeginDateTime', 'tApexDateTime','tEndDateTime', 'iDirectionType']])
return macd_data_apex
# for a in dataList:
# print(a)
# exit()
engine = sql_model.get_conn()
sql = "select * from stock_basics order by code asc"
# sql = "select * from stock_basics where code > '601312' order by code asc"
df = pd.read_sql(sql, engine)
code_list = df['code']
macd_data_apex = pd.DataFrame()
for s in code_list:
print(s + " begin ")
macd_data_apex = get_mack_apex(s)
# aField = ['sCode', 'tDateTime', 'iEmaShort', 'iEmaLong', 'iDif', 'iDea', 'iBar']
result = sql_model.loadData('stock_daily_macd_apex', macd_data_apex.keys(),
macd_data_apex.values)
print(s + " end ")
#
# macd_data_apex = get_mack_apex('600077')
# debug.p(macd_data_apex)
# debug.p(macd_data_apex)
# aField = ['sCode', 'tBeginDateTime', 'iBeginDif', 'tApexDateTime', 'iApexDif', 'tEndDateTime', 'iEndDif', 'iDirectionType']
# result = sql_model.loadData('stock_daily_macd_apex', aField, dataList)
# print(result)