data-service/StockStructure.py

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

__author__ = 'weijie'

from EmQuantAPI import *
from datetime import timedelta, datetime
import time
import traceback
from StockPojo import StockPojo
from dbOperation import dbOperation
from dbOperationStock import dbOperationStock
import db_config
import pandas as pd
import json
import array
import requests

class StockStructure:

    def toGet(self):
        try:
            getDataTime = time.strftime('%Y-%m-%d',time.localtime(time.time()))
            #获取A股所有数据 并且入库
            data = c.sector("001004", getDataTime) # 001004 全部A股板块
            if data.ErrorCode != 0:
                print("request sector Error, ", data.ErrorMsg)
            else:
                print("sector输出结果======分隔线======")
                # print(data.Data)
                stockPojoList = []
                db = dbOperation(db_config.db_gupiao)
                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("/", "-")
                            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)
                            
                            # 往业务库中插入一条数据
                            plate = ""
                            if stockPojo.code.startswith('688'):
                                plate = '科创'
                            elif stockPojo.code.startswith('300'):
                                plate = '创业'
                            else:
                                plate = '普通'
                            # dbStock = dbOperationStock(db_config.db_stock)
                            # insertStockSql = "insert into stock(stock_name,stock_code,stock_type,stock_gid,stock_plate,is_show,stock_or_bond,add_time)values('" + stockPojo.name + "','" + stockPojo.code + "','" + stockPojo.code[-2:].lower()+ "','" + stockPojo.code[-2:].lower() + stockPojo.code[:-3].lower() +"','" + plate + "',0,0,now())"
                            # dbStock.insert(insertStockSql)
                            # dbStock.close()
                            url = 'http://taifooksec.com/api/stock/addStock.do'
                            stock = {}
                            stock['stockName'] = stockPojo.name
                            stock['stockCode'] = stockPojo.code
                            stock['stockSpell'] = ''
                            stock['stockType'] = stockPojo.code[-2:].lower()
                            stock['stockGid'] = stockPojo.code[-2:].lower() + stockPojo.code[:-3].lower()
                            stock['stockPlate'] = plate
                            stock['isLock'] = 0
                            stock['isShow'] = 0
                            stock['buyLimit'] = 1
                            stock['isLimitUpBuy'] = 0
                            stock['isStopTradingBuy'] = 0
                            stock['nowPrice'] = ""
                            stock['change'] = ""
                            stock['pctChange'] = ""
                            stock['stockOrBond'] = "0"
                            stock['issueNum'] = ""
                            headers = {'Content-Type': 'application/json'}
                            response = requests.post(url=url, headers=headers, data=json.dumps(stock))
                        else :
                            print("表已存在不需要重新创建" + str(stockPojo.code))
                db.close()
        except Exception as ee:
            print("error >>>",ee)
            traceback.print_exc()
        else:
            print("股票表初始化完成")