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

    def toGet(self):
        try:
            getDataTime = time.strftime('%Y-%m-%d',time.localtime(time.time()))
            data = c.sector("610020", getDataTime)
            menu_one = "概念板块"
            menu_two = "可转债新"
            if data.ErrorCode != 0:
                print("request sector Error, ", data.ErrorMsg)
            else:
                print("sector输出结果======分隔线======")
                # print(data.Data)
                stockPojoList = []
                db = dbOperation(db_config.db_zhaiquan)
                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 + "' and name = '"+stockPojo.name+"' ")
                        if int(countNum['count']) == 0:
                            #判断是否是改名字的
                            countNumn = db.query_one("select count(1) count from t_stock_base_info where code = '" + stockPojo.code + "'  ")
                            if int(countNumn['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,menu_one,menu_two) values('" + stockPojo.code + "', '" + stockPojo.name + "','" + stockPojo.listDate +  "','" + menu_one + "','" + menu_two +"')")
                                # 创建实时表
                                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 = ""
                                # 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,1,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'] = "1"
                                stock['issueNum'] = ""
                                headers = {'Content-Type': 'application/json'}
                                response = requests.post(url=url, headers=headers, data=json.dumps(stock))
                            else:
                                db.update("update  t_stock_base_info  set name = '"+stockPojo.name+"' where code = '" + stockPojo.code + "' ")
                                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+"_edit"
                                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("债券表初始化完成")