# encoding:utf-8
import tushare as ts
import pandas as pd
import pymysql
import time
import datetime
import os
#节假日,跳过
rest = ["20180924","20181001","20181002","20181003","20181004","20181005","20181231","20190101","20190204","20190205","20190206","20190207",
"20190208"]
for k, v in enumerate(rest):
print( k, v)
config = {
'host':'106.14.145.51',
'port':4000,
'user':'pic98',
'password':'hello',
'database':'Stocks',
}
ts.set_token('你的挖地兔token')
#[挖地兔token获取地址:](https://tushare.pro/register?reg=129295)
pro = ts.pro_api()
#df = ts.get_today_ticks('600309')
#df.head(10)
#df = ts.get_tick_data('000001','2018-10-11',3,0,'tt')
df = pro.stock_basic()
print('-------')
db = pymysql.connect(**config)
cursor = db.cursor()
idx = 0
for dd in df.values:
idx = idx+1
if idx % 50 == 0:
os.system("cls")
try:
today=datetime.date.today()
oneday=datetime.timedelta(days=1)
li=[]
for i in range(0,5):
try:
today=today-oneday
ticktoday=datetime.datetime.strftime(today,'%Y-%m-%d')
ticktodayid=datetime.datetime.strftime(today,'%Y%m%d')
weekd = today.weekday()
if weekd == 5 or weekd == 6:
print(datetime.date.today(),ticktodayid,dd[0],"已有跳过(双休日)...")
continue
continue0_ = False
for k, v in enumerate(rest):
if v == ticktodayid:
continue0_ = True
break
if continue0_ == True:
print(ticktodayid,dd[0],"已有跳过(节假日)...")
continue
#print(ticktoday)
sql = "SELECT dayid,ts_code,value FROM PowerByDay WHERE ts_code = '%s' and dayid='%s' order by dayid desc"
data = (dd[0],ticktodayid)
cursor.execute(sql % data)
if cursor.rowcount > 0:
result = cursor.fetchall()
continue_ = False
for item in result:
if item[0] == int(ticktodayid):
continue_ = True
break
if continue_ == True:
print(ticktodayid,dd[0],"已有跳过(已入库)...")
continue
time.sleep(2)
tick = ts.get_tick_data(dd[1],ticktoday,3,0,'tt')
#print(tick)
if tick is None :
print(i,ticktodayid,dd[0],"无数据!")
continue
powertotal = 0
for tick0 in tick.values:
if tick0[5] == '卖盘' :
powertotal -= tick0[3]
elif tick0[5] == '买盘':
powertotal += tick0[3]
sql = 'INSERT INTO PowerByDay(DayId,ts_code, symbol, name, market,value ) VALUES (%s, "%s", "%s", "%s","%s",%.2f)'
data = (ticktodayid,dd[0],dd[1],dd[2],dd[5],powertotal)
print('%d\t%d\t%s\t%s\t%s\ttickcount:%d' % (idx,i,ticktodayid,dd[0],dd[2],tick.values.size))
print(datetime.date.today(),sql % data)
cursor.execute(sql % data)
db.commit()
except Exception as e:
print(e)
db.rollback()
except Exception as e:
print(e)
# 关闭数据库连接
db.close()
print('-------')
#df = pro.query('suspend', ts_code='', suspend_date='20180927', resume_date='', fiedls='')
#df = pro.suspend(ts_code='600848.SH', suspend_date='', resume_date='', fiedls='')
#print(df)
print(ts.__version__)