data-service/getStockList_meiguData.py

# -*- coding:utf-8 -*-

__author__ = 'weijie'

from EmQuantAPI import *
from datetime import timedelta, datetime
# import time as _time
import time
import traceback
from StockPojo import StockPojo
from StockHistPojo import StockHistPojo
from dbOperation import dbOperation
import db_config
import pandas as pd
import json
import array


print("开始了")
def mainCallback(quantdata):
    """
    mainCallback 是主回调函数，可捕捉如下错误
    在start函数第三个参数位传入，该函数只有一个为c.EmQuantData类型的参数quantdata
    :param quantdata:c.EmQuantData
    :return:
    """
    print ("mainCallback",str(quantdata))
    print("开始了2222")
    #登录掉线或者 登陆数达到上线（即登录被踢下线） 这时所有的服务都会停止
    if str(quantdata.ErrorCode) == "10001011" or str(quantdata.ErrorCode) == "10001009":
        print ("Your account is disconnect. You can force login automatically here if you need.")
    #行情登录验证失败（每次连接行情服务器时需要登录验证）或者行情流量验证失败时，会取消所有订阅，用户需根据具体情况处理
    elif str(quantdata.ErrorCode) == "10001021" or str(quantdata.ErrorCode) == "10001022":
        print ("Your all csq subscribe have stopped.")
    #行情服务器断线自动重连连续6次失败（1分钟左右）不过重连尝试还会继续进行直到成功为止，遇到这种情况需要确认两边的网络状况
    elif str(quantdata.ErrorCode) == "10002009":
        print ("Your all csq subscribe have stopped, reconnect 6 times fail.")
        # 行情订阅遇到一些错误(这些错误会导致重连，错误原因通过日志输出，统一转换成EQERR_QUOTE_RECONNECT在这里通知)，正自动重连并重新订阅,可以做个监控
    elif str(quantdata.ErrorCode) == "10002012":
        print ("csq subscribe break on some error, reconnect and request automatically.")
        # 资讯服务器断线自动重连连续6次失败（1分钟左右）不过重连尝试还会继续进行直到成功为止，遇到这种情况需要确认两边的网络状况
    elif str(quantdata.ErrorCode) == "10002014":
        print ("Your all cnq subscribe have stopped, reconnect 6 times fail.")
    # 资讯订阅遇到一些错误(这些错误会导致重连，错误原因通过日志输出，统一转换成EQERR_INFO_RECONNECT在这里通知)，正自动重连并重新订阅,可以做个监控
    elif str(quantdata.ErrorCode) == "10002013":
        print ("cnq subscribe break on some error, reconnect and request automatically.")
    # 资讯登录验证失败（每次连接资讯服务器时需要登录验证）或者资讯流量验证失败时，会取消所有订阅，用户需根据具体情况处理
    elif str(quantdata.ErrorCode) == "10001024" or str(quantdata.ErrorCode) == "10001025":
        print("Your all cnq subscribe have stopped.")
    else:
        pass

try:
    #调用登录函数（激活后使用，不需要用户名密码）
    loginResult = c.start("ForceLogin=1", '', mainCallback)
    if(loginResult.ErrorCode != 0):
        print("login in fail")
        exit()
    
    getDataTime = time.strftime('%Y-%m-%d',time.localtime(time.time()))
    #获取全部基金 并且入库
    data = c.sector("202001004", getDataTime) # 507013
    if data.ErrorCode != 0:
        print("request sector Error, ", data.ErrorMsg)
    else:
        print("sector输出结果======分隔线======")
        # print(data.Data)
        stockPojoList = []
        db = dbOperation(db_config.db_meigu)
        for inx, code in enumerate(data.Data):
            if(inx % 2 != 1):
                stockPojo = StockPojo()
                stockPojo.code = code
            else:
                stockPojo.name = code
                stockPojoList.append(stockPojo)
                countNum = db.query_one("select count(1) count from t_stock_base_info where code = '" + stockPojo.code + "'")
                # 数量不为0 表示已经插入过了
                if int(countNum['count']) == 0:
                     # css使用范例
                    cssData = c.css(stockPojo.code, "FOUNDDATE", "TradeDate="+getDataTime+", Ispandas=1")
                    cssData.reset_index(inplace=True)
                    jsonData = cssData.to_json()
                    text = json.loads(jsonData)
                    for i in range(len(text['FOUNDDATE'])):
                        if(str(text['FOUNDDATE'][str(i)]) == "None"):
                            stockPojo.listDate = "待上市"
                        else:
                            stockPojo.listDate = text['FOUNDDATE'][str(i)].replace("/", "-")
                    db.insert("insert into t_stock_base_info(code,name,list_date) values('" + stockPojo.code + "', '" + stockPojo.name + "','" + stockPojo.listDate + "')")
                    # 创建实时表
                    restTableName = "data_rt_" + stockPojo.code.replace(".", "_").lower()
                    restTableSql = "CREATE TABLE `" + restTableName + "` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `date` varchar(128) NOT NULL COMMENT '日期',  `time` varchar(128) NOT NULL COMMENT '交易时间',  `now` decimal(64,6) NOT NULL COMMENT '最新价',  `high` decimal(64,6) NOT NULL COMMENT '最高价',  `low` decimal(64,6) NOT NULL COMMENT '最低价',  `open` decimal(64,6) NOT NULL COMMENT '开盘价',  `preclose` decimal(64,6) NOT NULL COMMENT '前收盘价',  `roundlot` varchar(128) NOT NULL COMMENT '现手',  `change` varchar(128) NOT NULL COMMENT '涨跌',  `pctchange` varchar(128) NOT NULL COMMENT '涨跌幅',  `volume` varchar(128) NOT NULL COMMENT '成交量',  `amount` varchar(128) NOT NULL COMMENT '成交额',  `volumeratio` varchar(128) NOT NULL COMMENT '量比',  `commissionratio` varchar(128) NOT NULL COMMENT '委比',  `commissiondiff` varchar(128) NOT NULL COMMENT '委差',  `tradestatus` varchar(128) NOT NULL COMMENT '交易状态',  `outvolume` varchar(128) NOT NULL COMMENT '外盘',  `involume` varchar(128) NOT NULL COMMENT '内盘',  `highlimit` varchar(128) NOT NULL COMMENT '涨停价',  `lowlimit` varchar(128) NOT NULL COMMENT '跌停价',  `speed` varchar(128) NOT NULL COMMENT '涨速',  `averageprice` varchar(128) NOT NULL COMMENT '均价',  `buyprice1` varchar(128) NOT NULL COMMENT '买1价',  `buyprice2` varchar(128) NOT NULL COMMENT '买2价',  `buyprice3` varchar(128) NOT NULL COMMENT '买3价',  `buyprice4` varchar(128) NOT NULL COMMENT '买4价',  `buyprice5` varchar(128) NOT NULL COMMENT '买5价',  `buyvolume1` varchar(128) NOT NULL COMMENT '买1量',  `buyvolume2` varchar(128) NOT NULL COMMENT '买2量',  `buyvolume3` varchar(128) NOT NULL COMMENT '买3量',  `buyvolume4` varchar(128) NOT NULL COMMENT '买4量',  `buyvolume5` varchar(128) NOT NULL COMMENT '买5量',  `sellprice1` varchar(128) NOT NULL COMMENT '卖1价',  `sellprice2` varchar(128) NOT NULL COMMENT '卖2价',  `sellprice3` varchar(128) NOT NULL COMMENT '卖3价',  `sellprice4` varchar(128) NOT NULL COMMENT '卖4价',  `sellprice5` varchar(128) NOT NULL COMMENT '卖5价',  `sellvolume1` varchar(128) NOT NULL COMMENT '卖1量',  `sellvolume2` varchar(128) NOT NULL COMMENT '卖2量',  `sellvolume3` varchar(128) NOT NULL COMMENT '卖3量',  `sellvolume4` varchar(128) NOT NULL COMMENT '卖4量',  `sellvolume5` varchar(128) NOT NULL COMMENT '卖5量',  `closedtime` varchar(128) NOT NULL COMMENT '盘后时间',  `closedvolume` varchar(128) NOT NULL COMMENT '盘后成交量',  `closedamount` varchar(128) NOT NULL COMMENT '盘后成交额',  `addTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `dateOne` bigint(15) DEFAULT '0',  `dateFive` bigint(15) DEFAULT '0',  `dateFifteen` bigint(15) DEFAULT '0',  `dateThirty` bigint(15) DEFAULT '0',  `dateSixty` bigint(15) DEFAULT '0', `realTime` bigint(15) DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
                    db.created_table(restTableSql, restTableName)
                    # 创建历史表
                    histTableName = "data_hist_" + stockPojo.code.replace(".", "_").lower()
                    histTableSql = "CREATE TABLE `" + histTableName + "` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',  `open` decimal(64,6) NOT NULL COMMENT '开盘价',  `close` decimal(64,6) NOT NULL COMMENT '收盘价',  `high` decimal(64,6) NOT NULL COMMENT '最高价',  `low` decimal(64,6) NOT NULL COMMENT '最低价',  `preclose` decimal(64,6) NOT NULL COMMENT '前收盘价',  `average` varchar(128) NOT NULL COMMENT '均价',  `change` varchar(128) NOT NULL COMMENT '涨跌',  `pctchange` varchar(128) NOT NULL COMMENT '涨跌幅',  `volume` varchar(128) NOT NULL COMMENT '成交量',  `highlimit` varchar(128) NOT NULL COMMENT '是否涨停',  `amount` varchar(128) NOT NULL COMMENT '成交金额',  `turn` varchar(128) NOT NULL COMMENT '换手率',  `tradestatus` varchar(128) NOT NULL COMMENT '交易状态',  `lowlimit` varchar(128) NOT NULL COMMENT '是否跌停',  `amplitude` varchar(128) NOT NULL COMMENT '振幅',  `tnum` varchar(128) NOT NULL COMMENT '成交笔数',  `tafactor` varchar(128) NOT NULL COMMENT '复权因子(后)',  `fronttafactor` varchar(128) NOT NULL COMMENT '前复权因子（定点复权）',  `isststock` varchar(128) NOT NULL COMMENT '是否为ST股票',  `isxststock` varchar(128) NOT NULL COMMENT '是否为*ST股票',  `date_year` bigint(20) DEFAULT '0' COMMENT '年期',  `date_month` bigint(20) DEFAULT '0' COMMENT '月期',  `date_week` bigint(20) DEFAULT '0' COMMENT '周', `realTime` bigint(20) DEFAULT '0' COMMENT '日',  `dates` varchar(128) DEFAULT NULL, `holdvol` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '持股数量',`holdpct` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '持股占比', PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
                    db.created_table(histTableSql, histTableName)
                else :
                    print("表已存在不需要重新创建" + str(stockPojo.code))
        db.close()

#退出
    data = logoutResult = c.stop()
except Exception as ee:
    print("error >>>",ee)
    traceback.print_exc()
else:
    print("demo end")