python拉取股票数据存入mysql

用python拉取 https://tushare.pro/register?reg=129295 中的股票数据并存入mysql.

# 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__)