# -*- 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("外汇表初始化完成")