data-service/ForexStructure.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

# 初始化外汇表 112002 全部外汇
class ForexStructure:
    def toGet(self):
        try:
            getDataTime = time.strftime('%Y-%m-%d',time.localtime(time.time()))
            data = c.sector("112002", getDataTime)
            if data.ErrorCode != 0:
                print("request sector Error, ", data.ErrorMsg)
            else:
                print("sector输出结果======分隔线======")
                stockPojoList = []
                db = dbOperation(db_config.db_waihui)
                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, "LISTDATE", "TradeDate="+getDataTime+", Ispandas=1")
                            # cssData.reset_index(inplace=True)
                            # jsonData = cssData.to_json()
                            # text = json.loads(jsonData)
                            # for i in range(len(text['LISTDATE'])):
                            #     if(str(text['LISTDATE'][str(i)]) == "None"):
                            #         stockPojo.listDate = "待上市"
                            #     else:
                            #         stockPojo.listDate = text['LISTDATE'][str(i)].replace("/", "-")
                            stockPojo.listDate = '1990-01-01'
                            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,  PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
                            db.created_table(histTableSql, histTableName)
                        else :
                            print("表已存在不需要重新创建" + str(stockPojo.code))
                db.close()
        except Exception as ee:
            print("error >>>",ee)
            traceback.print_exc()
        else:
            print("外汇表初始化完成")