def bkvol(conn,bkid,param,date=None):
if date == None:
date = tools.getlastday()
cursor = conn.cursor()
sql = "SELECT * FROM code"
o = cursor.execute(sql)
if o > 0:
res = cursor.fetchall()
cursor.close()
bkid = str(bkid)
list = []
for row in res:
code = row[0]
bk = row[9]
bk = bk.strip('[]')
arr = bk.split(',')
for _row in arr:
if _row == bkid:
list.append(code)
break
rsum = 0
for code in list:
rres = vol(conn, code, param, date)
rsum = rsum + rres
return round(rsum / len(list),2)
else:
cursor.close()
return None
def xuangu():
conn = pymysql.connect(host='192.168.1.103',
user='******',
password='******',
db='gp',
port=3306,
charset='utf8')
#conn = pymysql.connect(host='localhost', user='******', password='******', db='gp', port=3306, charset='utf8')
cursor = conn.cursor()
cursor.execute("SELECT * FROM code")
res = cursor.fetchall()
stocks = {}
for row in res:
code = row[0]
stocks[code] = {}
daytime = tools.getlastday()
results = []
for stock in stocks:
cursor.execute("SELECT day,open,close,high,low,volume FROM `" + stock +
"` p ORDER BY p.day DESC LIMIT 30")
data = cursor.fetchall()
ret = xgcl1(stock, data, daytime)
if ret:
results.append(stock)
print('结果:')
print(results)
conn.close()
conn = None
'''
def bkzd(conn, bkid, date=None):
if date == None:
date = tools.getlastday()
cursor = conn.cursor()
sql = "SELECT * FROM code"
o = cursor.execute(sql)
if o > 0:
res = cursor.fetchall()
cursor.close()
bkid = str(bkid)
upnum,midnum,downnum = 0,0,0
for row in res:
percent = row[2]
bk = row[9]
bk = bk.strip('[]')
arr = bk.split(',')
for _row in arr:
if _row == bkid:
if percent > 0:
upnum = upnum + 1
elif percent == 0:
midnum = midnum + 1
else:
downnum = downnum + 1
break
return upnum, midnum, downnum
else:
cursor.close()
return None
def macd(conn,code,date=None):
if date == None:
date = tools.getlastday()
cursor = conn.cursor()
sql = "SELECT * FROM `" + code + "` WHERE day = '" + date + "'"
o = cursor.execute(sql)
if o > 0:
res = cursor.fetchall()
cursor.close()
global INDEX_MACD
return res[0][INDEX_MACD]*0.01
else:
cursor.close()
return None
def boll(conn,code,date=None):
if date == None:
date = tools.getlastday()
cursor = conn.cursor()
sql = "SELECT * FROM `" + code + "` WHERE day = '" + date + "'"
o = cursor.execute(sql)
if o > 0:
res = cursor.fetchall()
cursor.close()
global INDEX_BOLL
_boll = res[0][INDEX_BOLL]
_boll = _boll.strip('[]')
arr = _boll.split(',')
arr[0] = float(arr[0])
arr[1] = float(arr[1])
arr[2] = float(arr[2])
return arr
else:
cursor.close()
return None
def kpl_gg(code):
lastday = tools.getlastday()
param = kpltools.build_kpl_gg(code, lastday)
res = -1
while (res == -1 or isinstance(res['pankou'], dict) == False
or isinstance(res['pankou']['real'], dict) == False):
res = net.kpl(param)
#timeout
if res == -2:
return -1
info = {}
real = res['pankou']['real']
info['pb'] = real['dyn_pb_rate'] #市净率
info['percent'] = real['px_change_rate'] #涨跌幅
info['turnover'] = real['turnover_ratio'] #换手率
info['sjlt'] = int(real['sjlt']) #真实流通市值
info['nmc'] = real['circulation_value'] #流通市值
info['mktcap'] = real['market_value'] #总市值
info['high'] = real['high_px'] #最高价
info['low'] = real['low_px'] #最低价
info['rvol'] = real['vol_ratio'] #量比
#所属板块及板块涨幅
bklist = []
stockplate = res['stockplate']
if stockplate == None:
print('[code] ' + res['pankou']['name'] + ' stockplate is NoneType')
else:
for row in stockplate:
bk_name = row[0] #板块name
bk_zd = row[1] # 涨跌幅
bk_id = row[8] # 板块id
#更新bk表数据
updateBK(bk_name, bk_id, bk_zd)
bklist.append(bk_id)
info['bk'] = str(bklist) #','.join()# str.split(',')
return info
def vol(conn,code,param,date=None):
if date == None:
date = tools.getlastday()
cursor = conn.cursor()
sql = "SELECT * FROM `" + code + "` WHERE day = '" + date + "'"
o = cursor.execute(sql)
if o > 0:
res = cursor.fetchall()
cursor.close()
global INDEX_MACD
index = INDEX_VOL
if param == 5:
index = INDEX_VOL5
elif param == 10:
index = INDEX_VOL10
elif param == 20:
index = INDEX_VOL20
return res[0][index]
else:
cursor.close()
return None
def tushare_updategg(conn):
global GLOBAL_TUSHARE_DATALEN
cursor = conn.cursor()
cursor.execute("SELECT * FROM code")
res = cursor.fetchall()
index = 1
rlen = len(res)
currtime = tools.getlastday().strftime("%Y%m%d")
starttime = tools.getnday(GLOBAL_TUSHARE_DATALEN).strftime("%Y%m%d")
fields = "turnover_rate,turnover_rate_f,volume_ratio,pe,pb"
for row in res:
# 代码(id) 是否st(st 1.是 0.不是) 涨跌(percent) 市净率(pb) 市盈率(per) 换手(turnover) 总市值(mktcap) 流通市值(nmc) 真实市值(sjlt) 板块[名字1,名字2...](bk)
code = row[0]
print('[GG] loading:(' + str(index) + '/' + str(rlen) + ') - ' + code)
index = index + 1
#1.tushare
ret = -1
while ret == -1:
ret, df = net.tushare_history(code, starttime, currtime)
list = {}
for _i, _row in df.iterrows():
info = {}
date = _row['trade_date']
info['open'] = _row['open'] #开盘价
info['high'] = _row['high'] #最高价
info['low'] = _row['low'] #最低价
info['close'] = _row['close'] #收盘价
info['pre_close'] = _row['pre_close'] #昨收价
#change = _row['change'] #涨跌额
#info['pct_chg'] = _row['pct_chg'] #涨跌幅
info['volume'] = _row['vol'] #成交量（手）
info['amount'] = _row['amount'] #成交额（千元）
ret = -1
while ret == -1:
ret, fdf = net.tushare_history_fields(code, date, fields)
#print(fdf)
for __i, __row in fdf.iterrows():
info['turn'] = __row['turnover_rate'] #换手率
info['turnover'] = __row['turnover_rate_f'] #换手率（自由流通股）
info['volume_ratio'] = __row['volume_ratio'] #量比
info['pe'] = __row['pe'] #市盈率（总市值/净利润）
info['pb'] = __row['pb'] #市净率（总市值/净资产）
list[date] = info
#macd
#macd = getmacd(df)
#macddata = macd.set_index('date')
#boll
#upper, middle, lower = getboll(df)
if tools.table_exists(cursor, code) == 0:
csql = "CREATE TABLE IF NOT EXISTS `" + code + "`(day date,open mediumint unsigned,high mediumint unsigned,low mediumint unsigned,close mediumint unsigned,volume bigint unsigned,amount bigint unsigned,turn mediumint unsigned,turnover mediumint unsigned,vol mediumint unsigned,pe mediumint unsigned,pb mediumint unsigned,preclose mediumint unsigned)"
cursor.execute(csql)
#idx = len(upper) - 1
for key, value in list.items():
date = key
open = value['open'] #开盘价
high = value['high'] #最高价
low = value['low'] #最低价
close = value['close'] #收盘价
pre_close = value['pre_close'] #昨收价
#pct_chg = value['pct_chg'] #涨跌幅
volume = value['volume'] #成交量（手）
amount = value['amount'] #成交额（千元）
turn = value['turn'] #换手率
turnover = value['turnover'] #换手率（自由流通股）
vol = value['volume_ratio'] #量比
pe = value['pe'] #市盈率（总市值/净利润）
pb = value['pb'] #市净率（总市值/净资产）
if open is None:
open = 0
if high is None:
high = 0
if low is None:
low = 0
if close is None:
close = 0
if pre_close is None:
pre_close = 0
if volume is None:
volume = 0
if open is None:
open = 0
if amount is None:
amount = 0
if turn is None:
turn = 0
if turnover is None:
turnover = 0
if vol is None:
vol = 0
if pe is None:
pe = 0
if pb is None:
pb = 0
#print(value)
ssql = "SELECT * FROM `" + code + "` WHERE day = '" + date + "'"
has = cursor.execute(ssql)
if has == 0:
s = "INSERT INTO `" + code + "`(day,open,high,low,close,volume,amount,turn,turnover,vol,pe,pb,preclose) VALUES('%s','%d','%d','%d','%d','%d','%d','%d','%d','%d','%d','%d','%d')"
sql = s % (date, int(open * 100), int(
high * 100), int(low * 100), int(
close * 100), int(volume), int(amount), int(
turn * 100), int(turnover * 100), int(vol * 100),
int(pe * 100), int(pb * 100), int(pre_close * 100))
else:
s = "UPDATE `" + code + "` SET open=%d,high=%d,low=%d,close=%d,volume=%d,amount=%d,turn=%d,turnover=%d,vol=%d,pe=%d,pb=%d,preclose=%d WHERE day = '" + date + "'"
sql = s % (int(open * 100), int(high * 100), int(
low * 100), int(close * 100), int(volume), int(amount),
int(turn * 100), int(turnover * 100), int(
vol * 100), int(pe * 100), int(
pb * 100), int(pre_close * 100))
cursor.execute(sql)
conn.commit()
cursor.close()
return 0