大家刚接触平台可能对于一些数据的提取方式不甚明了,尤其是对query对象的使用。可能将简单问题复杂化,以下整理了大家使用过程中一般能用到的操作方式以及一些常用的数据获取方式。如果大家对于query对象的使用或者某些数据的提取存在疑问,可以在此贴下留言讨论。

其实对数据的查询过程,filter是最重要的一步,filter可以对多个条件进行过滤,中间用,隔开。
比如查询某只股票某一个时间段,满足特定值的数据,可以用到 in_ ,and_,> , < , == ,!=等符号进行基本的查询

涉及到使用数据库操作的数据有:

• get_fundamentals,get_fundamentals_continuously  (股票单季度财务/估值数据)

• finance           (股票数据,基金数据,舆情数据等)

• opt               (期权数据)

• macro             (宏观数据)

• jy                (聚源数据)

基本的查询方式

• query()  填写需要查询的对象,可以是整张表,也可以是表中的多个字段或计算出的结果

• filter 填写过滤条件,多个过滤条件可以用逗号隔开,或者用and_,or_这样的语法

• order_by()  填写排序条件

• .desc()  降序排列

• .asc()   升序排列

• limit()   限制返回的个数

• group_by()  分组统计

对于数据的查询过滤,一般通过filter函数进行实现,filter可以对多个条件进行过滤,中间用,隔开。
比如查询某只股票某一个时间段,满足特定值的数据,可以用到 in_ ,and_,> , < , == ,!=等符号进行基本的查询

涉及到使用数据库操作的数据有:

• get_fundamentals  (股票单季度财务数据)

• finance           (股票数据,基金数据等)

• opt               (期权数据)

• macro             (宏观数据)

• jy                (聚源数据)

基本的查询方式¶

• query()  填写需要查询的对象,可以是整张表,也可以是表中的多个字段或计算出的结果

• filter 填写过滤条件,多个过滤条件可以用逗号隔开,或者用and,or这样的语法

• order_by  填写排序条件

• .desc()  降序排列

• .asc()   升序排列

• limit   限制返回的个数

• order_by  分组统计

# 查询 000001 2015 - 2017 年的整张合并利润表,且满足 net_profit(净利润) < 0 的条件from jqdata import financeq = query(finance.STK_INCOME_STATEMENT).filter(finance.STK_INCOME_STATEMENT.code=='000783.XSHE',              #选定股票  000783.XSHEfinance.STK_INCOME_STATEMENT.end_date > '2005-01-01',          #指定查询时间段大于2005年1月1日finance.STK_INCOME_STATEMENT.end_date < '2018-01-01',          #指定查询时间段小于2018年1月1日finance.STK_INCOME_STATEMENT.net_profit <0,                    #指定查询到的数据中net_profit为负finance.STK_INCOME_STATEMENT.report_type == 0,                 #指定报告期类型为本期).order_by(finance.STK_INCOME_STATEMENT.end_date.desc()  ).limit(5)   #根据end_date降序排序,并返回前5条数据 finance.run_query(q)

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

5 rows × 60 columns

get_fundamentals 获取某个/多个季度/年度的数据(指定参数statDate)¶

# 查询平安银行2014年3-6月份的单季度报表q = query(  income.statDate,  income.code,  income.basic_eps,  cash_flow.goods_sale_and_service_render_cash
  ).filter(  income.code == '000001.XSHE',
  )ret = get_fundamentals(q, statDate='2014q2')ret

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

# 查询平安银行2014年的年报q = query(  income.statDate,  income.code,  income.basic_eps,  cash_flow.goods_sale_and_service_render_cash
  ).filter(  income.code == '000001.XSHE',
  )ret = get_fundamentals(q, statDate='2014')ret

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

# 查询平安银行2014年四个季度的季报, 放到数组中并拼接为dataframeq = query(  income.statDate,  income.code,  income.basic_eps,  balance.cash_equivalents,  cash_flow.goods_sale_and_service_render_cash
  ).filter(  income.code == '000001.XSHE',
  )rets = [get_fundamentals(q, statDate='2014q'+str(i)) for i in range(1, 5)]import pandas as pdpd.concat(rets)

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

in_  判断某个字段的值是否在列表之中(一般用于查询多个标的)¶

stocks = ['000001.XSHE','600741.XSHG','600507.XSHG']q = query(balance.code,balance.pubDate,balance.statDate,    # 指定返回的字段只包括code,pubDate,statDate,total_assets及total_sheet_owner_equities  balance.total_assets,balance.total_sheet_owner_equities).filter( balance.code.in_(stocks)   #指定查询到的数据只包括code在 stocks中的数据)get_fundamentals(q,date='2018-01-05')    #查询单季度数据中在2018-01-05之前发布的数据,没有未来函数

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

distinct 去重,用于查看数据库中某个字段都存在哪些值¶

#  查看十大流通股东中都有哪些类别from jqdata import financeq = query( finance.STK_SHAREHOLDER_FLOATING_TOP10.shareholder_class_id.distinct(),  #提取ID不同的数据finance.STK_SHAREHOLDER_FLOATING_TOP10.shareholder_class, ).order_by(finance.STK_SHAREHOLDER_FLOATING_TOP10.change_reason_id)  df = finance.run_query(q)df.tail()

~ 反向查询符号¶

q = query( finance.STK_SHAREHOLDER_FLOATING_TOP10.shareholder_class_id.distinct(),finance.STK_SHAREHOLDER_FLOATING_TOP10.shareholder_class, ).filter(~finance.STK_SHAREHOLDER_FLOATING_TOP10.shareholder_class_id.in_(['307007','307001']),#等同于notin_)finance.run_query(q)

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

运算和命名(label)¶

# label的作用是命名获得数据的标签,一般用于直接运算后的重命名q = query(indicator.code.label('股票代码'),indicator.operating_profit.label('get_operating_profit'), (income.total_operating_revenue - income.total_operating_cost).label('my_operating_profit') ).filter(indicator.code=='600507.XSHG')get_fundamentals(q)

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

or_ 或查询¶

from sqlalchemy.sql.expression import or_get_fundamentals(query(  valuation.code  ).filter(  or_(valuation.market_cap < 10,valuation.pe_ratio > 1000))).tail()  # 查询当天总市值小于1000亿或pe大于10亿的所有股票

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

contains/like/ilike  数据库中的字符串模糊匹配¶

• %  百分号通配符: 表示任何字符出现任意次数(可以是0次).

• _  下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符.

from jqdata import finance# 获取000001.XSHG每一年的年报   df = finance.run_query(query(finance.FINANCE_INCOME_STATEMENT   ).filter(finance.FINANCE_INCOME_STATEMENT.code.in_(['000001.XSHE']),#                 finance.FINANCE_INCOME_STATEMENT.end_date.like('%-12-31%'),    #sqlalchemy的版本,数据库的构建等问题可能导致查询报错,可以换以下两种方法尝试#               finance.FINANCE_INCOME_STATEMENT.end_date.contains('12-31') ,      finance.FINANCE_INCOME_STATEMENT.end_date.ilike('_____12-31'),finance.FINANCE_INCOME_STATEMENT.report_type==0   ))df.tail()

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

5 rows × 66 columns

简化计算的方法(sqlalchemy.sql.func)  和  group_by¶

func中的函数可以完成常用的一些计算,配合group_by完成简单的统计
下边以简单计算股票池/全市场每天的的等权重pe,pb为例
(当然,这个计算中一些细节并没有考虑到,实际还是以官方数据为准,可以通过聚源数据获取,具体下边会有演示)

from sqlalchemy.sql import funcfrom jqdata import financestocks = get_index_stocks('000016.XSHG')q = query(valuation.day,#           func.count('*'),       # 信息条数(也就是股票数量)  (func.count()/func.sum(1/valuation.pe_ratio)).label('*g_pe'),  # 等权重pe  (func.count()/func.sum(1/valuation.pb_ratio)).label('*g_pb'),  # 等权重pb ).group_by(valuation.day   ).filter(valuation.pe_ratio>0,   #排除亏损的valuation.pb_ratio>0,   valuation.day>'2009-12-01',  #设置一个时间valuation.code.in_(stocks)   #设置股票池,注释即为全市场)df = finance.run_query(q)df.set_index('day',inplace=True)df.tail()

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

更多关于query的可导入函数,可以使用以下方法查看,配合官网文档使用:

import sqlalchemy
dir(sqlalchemy.sql.expression)

其他常用数据获取方式整理¶

获取旧的申万指数列表和新的申万指数列表(14年有过改动)¶

from jqdata import *def get_sw_quote(day=None):'''获取指定日期的申万指数列表'''day = get_trade_days(end_date=day,count=1)[-1]df = jy.run_query(query( jy.QT_SYWGIndexQuote.InnerCode.distinct().label('InnerCode')).filter(jy.QT_SYWGIndexQuote.TradingDay==day,))code_df = jy.run_query(query( jy.SecuMain.SecuCode,jy.SecuMain.ChiName).filter(jy.SecuMain.InnerCode.in_(df.InnerCode)))return code_dfdf = get_sw_quote('2013-02-05')df.tail()

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

查询申万行情( 包含行业pe/pb)¶

from jqdata import jyfrom jqdata import *import pandas as pd#注意申万指数在2014年有一次大改,聚源使用的是为改变之前的代码,官网包含更改前和更改后的代码,如果遇到找不到的标的可以根据需求自行查找#如801124 >>801121食品加工IIdef get_sw_quote(code,end_date=None,count=None,start_date=None):'''获取申万指数行情,返回panel结构'''if isinstance(code,str):code=[code]days = get_trade_days(start_date,end_date,count)code_df = jy.run_query(query( jy.SecuMain.InnerCode,jy.SecuMain.SecuCode,jy.SecuMain.ChiName).filter(jy.SecuMain.SecuCode.in_(code)))df = jy.run_query(query( jy.QT_SYWGIndexQuote).filter(jy.QT_SYWGIndexQuote.InnerCode.in_(code_df.InnerCode),jy.QT_SYWGIndexQuote.TradingDay.in_(days),))df2  = pd.merge(code_df, df, on='InnerCode').set_index(['TradingDay','SecuCode'])df2.drop(['InnerCode','ID','UpdateTime','JSID'],axis=1,inplace=True)return df2.to_panel()code = get_industries(name='sw_l2').index[:5]df = get_sw_quote(code,end_date='2018-01-01',count=10)df.to_frame(False).tail()

/opt/conda/envs/python2new/lib/python2.7/site-packages/ipykernel_launcher.py:23: DeprecationWarning: 
Panel is deprecated and will be removed in a future version.
The recommended way to represent these types of 3-dimensional data are with a MultiIndex on a DataFrame, via the Panel.to_frame() method
Alternatively, you can use the xarray package http://xarray.pydata.org/en/stable/.
Pandas provides a `.to_xarray()` method to help automate this conversion.

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

def get_zz_quote(code,end_date=None,count=None,start_date=None):'''获取中证指数行情,返回panel结构'''if isinstance(code,str):code=[code]code.sort()code = [x[:6] for x in code]days = get_trade_days(start_date,end_date,count)code_df = jy.run_query(query( jy.SecuMain.InnerCode,jy.SecuMain.SecuCode,jy.SecuMain.ChiName).filter(jy.SecuMain.SecuCode.in_(code)).order_by(jy.SecuMain.SecuCode))df = jy.run_query(query( jy.QT_CSIIndexQuote).filter(jy.QT_CSIIndexQuote.IndexCode.in_(code_df.InnerCode),jy.QT_CSIIndexQuote.TradingDay.in_(days),))df2  = pd.merge(code_df, df, left_on='InnerCode',right_on='IndexCode').set_index(['TradingDay','SecuCode'])df2.drop(['InnerCode','IndexCode','ID','UpdateTime','JSID','OpenInterest','SettleValue','IndexCSIType'],axis=1,inplace=True)return df2.to_panel()panel = get_zz_quote(['000016.XSHG','000001.XSHG'],end_date='2019-01-21',count=10)panel.ClosePrice.tail()

/opt/conda/envs/python2new/lib/python2.7/site-packages/ipykernel_launcher.py:19: DeprecationWarning: 
Panel is deprecated and will be removed in a future version.
The recommended way to represent these types of 3-dimensional data are with a MultiIndex on a DataFrame, via the Panel.to_frame() method
Alternatively, you can use the xarray package http://xarray.pydata.org/en/stable/.
Pandas provides a `.to_xarray()` method to help automate this conversion.

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

查询股息率(近12个月)¶

from jqdata import *def DividendRatio(security_list,end_date,count=1):'''查询股息率(日更新)    输入:股票池,截止日期,获取数量    输出:panel结构,单位:1'''trade_days = get_trade_days(end_date=end_date,count = count)security_list.sort()secu_list = [x[:6] for x in security_list]code_df = jy.run_query(query( jy.SecuMain.InnerCode,jy.SecuMain.SecuCode,#     jy.SecuMain.ChiName,jy.SecuMain.CompanyCode).filter(jy.SecuMain.SecuCode.in_(secu_list),jy.SecuMain.SecuCategory==1).order_by(jy.SecuMain.SecuCode))code_df['code'] = security_listdf = jy.run_query(query(#         jy.LC_DIndicesForValuation    #得到整表jy.LC_DIndicesForValuation.InnerCode,jy.LC_DIndicesForValuation.TradingDay, jy.LC_DIndicesForValuation.DividendRatio,).filter(jy.LC_DIndicesForValuation.InnerCode.in_(code_df.InnerCode),jy.LC_DIndicesForValuation.TradingDay.in_(trade_days)))f_df = df.merge(code_df,on='InnerCode').set_index(['TradingDay','code']).drop(['InnerCode','SecuCode'],axis=1)panel = f_df.to_panel()return panelsecuritys =get_index_stocks('399015.XSHE')date='2018-01-01'DividendRatio(securitys,date)

/opt/conda/envs/python2new/lib/python2.7/site-packages/ipykernel_launcher.py:24: DeprecationWarning: 
Panel is deprecated and will be removed in a future version.
The recommended way to represent these types of 3-dimensional data are with a MultiIndex on a DataFrame, via the Panel.to_frame() method
Alternatively, you can use the xarray package http://xarray.pydata.org/en/stable/.
Pandas provides a `.to_xarray()` method to help automate this conversion.

<class 'pandas.core.panel.Panel'>
Dimensions: 1 (items) x 1 (major_axis) x 492 (minor_axis)
Items axis: DividendRatio to DividendRatio
Major_axis axis: 2017-12-29 00:00:00 to 2017-12-29 00:00:00
Minor_axis axis: 000007.XSHE to 300735.XSHE

获取期货合约的基本信息（合约乘数、商品报价的计数单位、最小变动单位）¶

# lru缓存可以不要，加上后查询相同的合约可以避免重复数据库请求from fastcache import clru_cache as lru_cache@lru_cache(maxsize=128)def future_basic_info(future):from jqdata import jyfrom numpy import nanimport reif "9999" in future or "8888" in future:match = re.match(r"(?P<underlying_symbol>[A-Z]{1,})", future)if not match:raise ValueError("未知期货标的：{}".format(future))else:future = get_dominant_future(match.groupdict()["underlying_symbol"])q = query(jy.Fut_ContractMain).filter(jy.Fut_ContractMain.ContractCode == future.split(".")[0])result = jy.run_query(query_object=q).to_dict("record")if result:result = result.pop()min_point = re.match("(?P<value>^[0-9]+([.]{1}[0-9]+){0,1})", result["LittlestChangeUnit"]).groupdict(nan)["value"]return {"ContractUnit": result["CMValue"],   "PriceScale": float(str(min_point)[:-1] + "1") if float(min_point) < 1 else 1,   "MinPoint": float(min_point)}else:return {"ContractUnit": nan,"PriceScale": nan,"MinPoint": nan}future_basic_info('IF1801.CCFX')

{'ContractUnit': 300, 'MinPoint': 0.2, 'PriceScale': 0.1}

多个dataframe的合并 (数据拼接) 获取多份财务报表¶

#获取多年的季度度数据import pandas as pddef get_more_state_fund(q_object,year_list):df_list = []for year in year_list:rets = [get_fundamentals(q, statDate=str(year)+'q'+str(i)) for i in range(1, 5)]df = pd.concat(rets).set_index('statDate')  #个人习惯df_list.append(df)df_ = pd.concat(df_list,keys=year_list,axis=0) #axis=1或axis=0,依据个人习惯return df_q = query(indicator.code,  indicator.statDate,indicator.roe,indicator.inc_return,indicator.pubDate,
  ).filter(  income.code.in_(['000001.XSHE','600507.XSHG']))df = get_more_state_fund(q,['2017','2018'])# df.loc[('2014',slice(None))]df

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

#h获取多个年度财务数据import pandas as pdstock_list=['000001.XSHE','000527.XSHE','600970.XSHG','600875.XSHG','600880.XSHG']years = range(2005,2018)df_list=[]for year in years:df_year= get_fundamentals(query( indicator.code, indicator.inc_return,indicator.roe   ).filter( indicator.code.in_(stock_list)   ),statDate=year).set_index('code')df_list.append(df_year)name = [str(x) for x in range(2005,2017)]df=pd.concat(df_list,axis=1,keys=name)df

.dataframe thead tr:only-child th {        text-align: right;    }    .dataframe thead th {        text-align: left;    }    .dataframe tbody tr th {        vertical-align: top;    }

5 rows × 24 columns

     需要注意的:  finance,jy,opt,macro有一次性获取数据不超过3000条的限制(主要是为了提速,get_fundamentals的限制为10000条),

   所以当获取的数据超过这个限制时,需要对股票池或者时间段进行分割,多次获取。