使用python對接choice接口，將深股通十大活躍成交股報(bào)表數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫

使用choice命令生成

# 2020-07-06 15:14:31
# 該表根據(jù)匯總表，分別展示交易日期區(qū)間內(nèi)的每日深股通十大活躍成交股（通過深股通買賣成交金額最大） 參數(shù): 交易日期 統(tǒng)計(jì)周期 字段: 交易日期 排名 代碼 名稱 收盤價(jià)格(元) 漲跌幅(%) 買入金額(萬元) 賣出金額(萬元) 深股通成交金額(萬元) 股票成交金額(萬元) 深股通成交占比(%) 總市值(億元) 累計(jì)上榜次數(shù) 幣種 證監(jiān)會(huì)行業(yè)(2012) 東財(cái)行業(yè)
data=c.ctr("ActiveStockTOP10SZInfo","TRADEDATE,DEC_RANK,MSECUCODE,SECURITYSHORTNAME,NEW,CHG,BMONEY,SMONEY,TVAL,ZTVAL,BILI,ZSZ,CNT,CURRENCY,STR_PUBLISHNAMEZJH,STR_PUBLISHNAMEDC3","TradeDate=2018-12-31,period=1")

完整代碼

#choice 深股通十大股東報(bào)表
from EmQuantAPI import *
from datetime import *
from datetime import timedelta, datetime
import traceback
import pymysql
import json
import time
import tushare as ts
import pandas as pd
import numpy as np

def mainCallback(quantdata):
    """
    mainCallback 是主回調(diào)函數(shù)，可捕捉如下錯(cuò)誤
    在start函數(shù)第三個(gè)參數(shù)位傳入，該函數(shù)只有一個(gè)為c.EmQuantData類型的參數(shù)quantdata
    :param quantdata:c.EmQuantData
    :return:
    """
    print("mainCallback", str(quantdata))
    # 登錄掉線或者 登陸數(shù)達(dá)到上線（即登錄被踢下線） 這時(shí)所有的服務(wù)都會(huì)停止
    if str(quantdata.ErrorCode) == "10001011" or str(quantdata.ErrorCode) == "10001009":
        print("Your account is disconnect. You can force login automatically here if you need.")
    # 行情登錄驗(yàn)證失敼煅佟（每次連接行情服務(wù)器時(shí)需要登錄驗(yàn)證）或者行情流量驗(yàn)證失敗時(shí)，會(huì)取消所有訂閱，用戶需根據(jù)具體情況處理
    elif str(quantdata.ErrorCode) == "10001021" or str(quantdata.ErrorCode) == "10001022":
        print("Your all csq subscribe have stopped.")
    # 行情服務(wù)器斷線自動(dòng)重連連續(xù)6次失斍赫肌（1分鐘左右）不過重連嘗試還會(huì)繼續(xù)進(jìn)行直到成功為止，遇到這種情況需要確認(rèn)兩邊的網(wǎng)絡(luò)狀況
    elif str(quantdata.ErrorCode) == "10002009":
        print("Your all csq subscribe have stopped.")
    else:
        pass


def startCallback(message):
    print("[EmQuantAPI Python]", message)
    return 1


def csqCallback(quantdata):
    """
    csqCallback 是EM_CSQ訂閱時(shí)提供的回調(diào)函數(shù)模板懦趋。該函數(shù)只有一個(gè)為c.EmQuantData類型的參數(shù)quantdata
    :param quantdata:c.EmQuantData
    :return:
    """
    print("csqCallback,", str(quantdata))


def cstCallBack(quantdata):
    for i in range(0, len(quantdata.Codes)):
        length = len(quantdata.Dates)
        for it in quantdata.Data.keys():
            print(it)
            for k in range(0, length):
                for j in range(0, len(quantdata.Indicators)):
                    print(quantdata.Data[it][j * length + k], " ", end="")
                print()


loginResult = c.start("ForceLogin=1", '', mainCallback)
if (loginResult.ErrorCode != 0):
    print("login in fail")
    exit()


pro = ts.pro_api()


db = pymysql.connect(host='192.168.0.147', port=3306, user='root', password='123', db='test')
cursor = db.cursor()


data = c.ctr("ActiveStockTOP10SHInfo","TRADEDATE,DEC_RANK,MSECUCODE,SECURITYSHORTNAME,NEW,CHG,BMONEY,SMONEY,TVAL,ZTVAL,BILI,ZSZ,CNT,CURRENCY,STR_PUBLISHNAMEZJH,STR_PUBLISHNAMEDC3",
             "TradeDate=2020-6-15,period=2")
data_dic = data.Data
for k, v in data_dic.items():
    print(v)
    try:
        sql = 'insert into ActiveStockTOP10SH (tradedate,dec_rank,msecucode,securityshortname,new,chg,bmoney,smoney,tval,ztval,bili,zsz,cnt,currency,str_publishnamzjh,str_publishnamedc3) ' \
              'values ("{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}")'.format(v[0], v[1], v[2],v[3],v[4],v[5],v[6],v[7],v[8],v[9],v[10],v[11],v[12],v[13],v[14],v[14])
        cursor.execute(sql)
        db.commit()
        print('上傳完成')
    except:
        db.rollback()
        print('上傳失敗')
    print('深股通十大活躍成交股報(bào)表上傳完成---------------------------')