from jqdatasdk import *
import numpy as np
import pandas as pd
from datetime import datetime
from datetime import timedelta
import pyodbc
SQLServer，数据库信息根据实际情况自行设置
conn = pyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=XXXX;DATABASE=XXXX;UID=XXX;PWD=XXXX')
cursor = conn.cursor()
聚宽用户登录
auth('XXXX','XXXX')
设置取数据时间段
curDate = datetime.now().strftime('%Y-%m-%d')
startDate=datetime.now() - timedelta(days=7)
startDate=startDate.strftime('%Y-%m-%d')
待处理日期列表
str_qdate=query(finance.STK_EXCHANGE_LINK_CALENDAR).filter(
finance.STK_EXCHANGE_LINK_CALENDAR.link_id==310001,
finance.STK_EXCHANGE_LINK_CALENDAR.type_id==312001,
finance.STK_EXCHANGE_LINK_CALENDAR.day>=startDate,
finance.STK_EXCHANGE_LINK_CALENDAR.day<=curDate,
).order_by(
finance.STK_EXCHANGE_LINK_CALENDAR.day.desc()
)
df_date=finance.run_query(str_qdate)
逐日提取数据
for index, row in df_date.iterrows():
trade_day=row['day']
str_quo=query(finance.STK_ML_QUOTA).filter(finance.STK_ML_QUOTA.day==trade_day).limit(3000)
df_trade=finance.run_query(str_quo)
df_trade
#存入数据库
for index, row in df_trade.iterrows():
strSql="exec proc_jqdata_shszsc_quo_crawl_add @trade_dt ='" + str(row['day']) + "'"
strSql=strSql + ",@link_id=" + str(row['link_id'] )
strSql=strSql + ",@link_name='" + str(row['link_name'] ) +"'"
strSql=strSql + ",@currency_id=" + str(row['currency_id'] )
strSql=strSql + ",@currency_name='" + str(row['currency_name'] ) +"'"
strSql=strSql + ",@buy_amount=" + str(row['buy_amount'] )
strSql=strSql + ",@buy_volume=" + str(row['buy_volume'] )
strSql=strSql + ",@sell_amount=" + str(row['sell_amount'] )
strSql=strSql + ",@sell_volume=" + str(row['sell_volume'] )
strSql=strSql + ",@sum_amount=" + str(row['sum_amount'] )
strSql=strSql + ",@sum_volume=" + str(row['sum_volume'] )
strSql=strSql + ",@quota=" + str(row['quota'] )
strSql=strSql + ",@quota_balance=" + str(row['quota_balance'] )
strSql=strSql + ",@quota_daily=" + str(row['quota_daily'] )
strSql=strSql + ",@quota_daily_balance=" + str(row['quota_daily_balance'] )
strSql=strSql.replace('None', '0')
print(strSql)
cursor.execute(strSql)
conn.commit()
SQL数据库建表语句
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.shszsc_quo') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.shszsc_quo
GO
CREATE TABLE dbo.shszsc_quo (
rec_id int identity(1,1) NOT NULL , -- 序号
trade_dt date not null, --交易日期
link_id int not null, --市场通编码
link_name varchar(32) not null, --市场通名称(包括以下四个名称： 沪股通， 深股通， 港股通(沪)， 港股通(深))
currency_id int not null, --货币编码
currency_name varchar(16) not null, --货币名称
buy_amount decimal(20,4) not null, --买入成交额(亿)
buy_volume decimal(20,4) not null, --买入成交数(笔)
sell_amount decimal(20,4) not null, --卖出成交额(亿)
sell_volume decimal(20,4) not null, --卖出成交数(笔)
sum_amount decimal(20,4) not null, --累计成交额(买入成交额+卖出成交额)
sum_volume decimal(20,4) not null, --累计成交数目(买入成交量+卖出成交量)
quota decimal(20, 4) not null, --总额度(亿（2016-08-16号起，沪港通和深港通不再设总额度限制）)
quota_balance decimal(20, 4) not null, --总额度余额(亿)
quota_daily decimal(20, 4) not null, --每日额度(亿)
quota_daily_balance decimal(20, 4) not null, --每日额度余额(亿)
first_input_userid int not null default 0, --首次录入人员
first_input_time datetime not null default getdate(), --首次录入时间
last_input_userid int not null default 0, --最后更新人员
last_input_time datetime not null default getdate(), --最后更新时间
constraint pk_shszsc_quo primary key(rec_id)
)
GO
create index idx_shszsc_quo_title on shszsc_quo(trade_dt)
go
保存数据至数据库的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.proc_jqdata_shszsc_quo_crawl_add') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.proc_jqdata_shszsc_quo_crawl_add
go
CREATE procedure [dbo].[proc_jqdata_shszsc_quo_crawl_add](
)
as
begin
if not exists(select * from shszsc_quo where trade_dt=@trade_dt and link_id=@link_id)
insert into shszsc_quo(trade_dt,link_id,link_name,currency_id,currency_name,
buy_amount,buy_volume,sell_amount,sell_volume,sum_amount,sum_volume,
quota,quota_balance,quota_daily,quota_daily_balance)
values