1.创建表
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE valuation CASCADE CONSTRAINT';
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
create table valuation(
code VARCHAR2(30) NOT NULL ,--股票代码
day VARCHAR2(10) NOT NULL ,--日期
capitalization NUMBER(19,4) default 0 NOT NULL ,--总股本(万股)
circulating_cap NUMBER(19,4) default 0 NOT NULL ,--流通股本(万股)
market_cap NUMBER(19,4) default 0 NOT NULL ,--总市值(亿元)
circulating_market_cap NUMBER(19,4) default 0 NOT NULL ,--流通市值(亿元)
turnover_ratio NUMBER(19,4) default 0 NOT NULL ,--换手率(%)
pe_ratio NUMBER(19,4) default 0 NOT NULL ,--市盈率(PE, TTM)
pe_ratio_lyr NUMBER(19,4) default 0 NOT NULL ,--市盈率(PE)
pb_ratio NUMBER(19,4) default 0 NOT NULL ,--市净率(PB)
ps_ratio NUMBER(19,4) default 0 NOT NULL ,--市销率(PS, TTM)
pcf_ratio NUMBER(19,4) default 0 NOT NULL ,--市现率(PCF, 现金净流量TTM)
CONSTRAINT pk_valuation PRIMARY KEY(code)
)TABLESPACE ts_sqp2018today
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE indicator CASCADE CONSTRAINT';
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
create table indicator(
f1 varchar2(30) default '' NOT NULL ,--股票代码
f2 varchar2(10) default '' NOT NULL ,--日期
f3 varchar2(10) default '' NOT NULL ,--日期
f4 number(19,4) default 0 NOT NULL ,--每股收益EPS(元)
f5 number(19,4) default 0 NOT NULL ,--扣除非经常损益后的净利润(元)
f6 number(19,4) default 0 NOT NULL ,--经营活动净收益(元)
f7 number(19,4) default 0 NOT NULL ,--价值变动净收益(元)
f8 number(19,4) default 0 NOT NULL ,--净资产收益率ROE(%)
f9 number(19,4) default 0 NOT NULL ,--净资产收益率(扣除非经常损益)(%)
f10 number(19,4) default 0 NOT NULL ,--总资产净利率ROA(%)
f11 number(19,4) default 0 NOT NULL ,--销售净利率(%)
f12 number(19,4) default 0 NOT NULL ,--销售毛利率(%)
f13 number(19,4) default 0 NOT NULL ,--营业总成本/营业总收入(%)
f14 number(19,4) default 0 NOT NULL ,--营业利润/营业总收入(%)
f15 number(19,4) default 0 NOT NULL ,--净利润/营业总收入(%)
f16 number(19,4) default 0 NOT NULL ,--营业费用/营业总收入(%)
f17 number(19,4) default 0 NOT NULL ,--管理费用/营业总收入(%)
f18 number(19,4) default 0 NOT NULL ,--财务费用/营业总收入(%)
f19 number(19,4) default 0 NOT NULL ,--经营活动净收益/利润总额(%)
f20 number(19,4) default 0 NOT NULL ,--价值变动净收益/利润总额(%)
f21 number(19,4) default 0 NOT NULL ,--扣除非经常损益后的净利润/净利润(%)
f22 number(19,4) default 0 NOT NULL ,--销售商品提供劳务收到的现金/营业收入(%)
f23 number(19,4) default 0 NOT NULL ,--经营活动产生的现金流量净额/营业收入(%)
f24 number(19,4) default 0 NOT NULL ,--经营活动产生的现金流量净额/经营活动净收益(%)
f25 number(19,4) default 0 NOT NULL ,--营业总收入同比增长率(%)
f26 number(19,4) default 0 NOT NULL ,--营业总收入环比增长率(%)
f27 number(19,4) default 0 NOT NULL ,--营业收入同比增长率(%)
f28 number(19,4) default 0 NOT NULL ,--营业收入环比增长率(%)
f29 number(19,4) default 0 NOT NULL ,--营业利润同比增长率(%)
f30 number(19,4) default 0 NOT NULL ,--营业利润环比增长率(%)
f31 number(19,4) default 0 NOT NULL ,--净利润同比增长率(%)
f32 number(19,4) default 0 NOT NULL ,--净利润环比增长率(%)
f33 number(19,4) default 0 NOT NULL ,--归属母公司股东的净利润同比增长率(%)
f34 number(19,4) default 0 NOT NULL ,--归属母公司股东的净利润环比增长率(%)
CONSTRAINT pk_indicator PRIMARY KEY(code)
)TABLESPACE ts_sqp2018today
/
2.写pyhon脚本
# -*- coding: utf-8 -*- import sys import cx_Oracle from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() from sqlalchemy import Column, Integer, String, Float from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.sql.expression import or_, and_ engine = create_engine('oracle://yugy:yugy@127.0.0.1:1521/yugy') Session = sessionmaker(bind=engine) session = Session() query = None class indicator(Base): __tablename__ = 'indicator' code = Column('f1',String(30), primary_key=True) pubDate = Column('f2',String(10), primary_key=True) statDate = Column('f3',String(10), primary_key=True) eps = Column('f4',Float) adjusted_profit = Column('f5',Float) operating_profit = Column('f6',Float) value_change_profit = Column('f7',Float) roe = Column('f8',Float) inc_return = Column('f9',Float) roa = Column('f10',Float) net_profit_margin = Column('f11',Float) gross_profit_margin = Column('f12',Float) expense_to_total_revenue = Column('f13',Float) operation_profit_to_total_revenue = Column('f14',Float) net_profit_to_total_revenue = Column('f15',Float) operating_expense_to_total_revenue = Column('f16',Float) ga_expense_to_total_revenue = Column('f17',Float) financing_expense_to_total_revenue = Column('f18',Float) operating_profit_to_profit = Column('f19',Float) invesment_profit_to_profit = Column('f20',Float) adjusted_profit_to_profit = Column('f21',Float) goods_sale_and_service_to_revenue = Column('f22',Float) ocf_to_revenue = Column('f23',Float) ocf_to_operating_profit = Column('f24',Float) inc_total_revenue_year_on_year = Column('f25',Float) inc_total_revenue_annual = Column('f26',Float) inc_revenue_year_on_year = Column('f27',Float) inc_revenue_annual = Column('f28',Float) inc_operation_profit_year_on_year = Column('f29',Float) inc_operation_profit_annual = Column('f30',Float) inc_net_profit_year_on_year = Column('f31',Float) inc_net_profit_annual = Column('f32',Float) inc_net_profit_to_shareholders_year_on_year = Column('f33',Float) inc_net_profit_to_shareholders_annual = Column('f34',Float) def __repr__(self): return "<Indicator(code='%s', " \ "pubDate='%s', " \ "statDate='%s'," \ "eps = '%s'," \ "adjusted_profit = '%s'," \ "operating_profit = '%s'," \ "value_change_profit = '%s'," \ "roe = '%s''," \ "inc_return = '%s'," \ "roa = '%s'," \ "net_profit_margin = '%s' ," \ "gross_profit_margin = '%s'," \ "expense_to_total_revenue = '%s'," \ "operation_profit_to_total_revenue = '%s'," \ "net_profit_to_total_revenue = '%s'," \ "operating_expense_to_total_revenue = '%s'," \ "ga_expense_to_total_revenue = '%s', " \ "financing_expense_to_total_revenue = '%s', " \ "operating_profit_to_profit = '%s', " \ "invesment_profit_to_profit = '%s', " \ "adjusted_profit_to_profit = '%s', " \ "goods_sale_and_service_to_revenue = '%s', " \ "ocf_to_revenue = '%s', " \ "ocf_to_operating_profit = '%s', " \ "inc_total_revenue_year_on_year = '%s', " \ "inc_total_revenue_annual = '%s', " \ "inc_revenue_year_on_year = '%s', " \ "inc_revenue_annual = '%s', " \ "inc_operation_profit_year_on_year = '%s', " \ "inc_operation_profit_annual = '%s', " \ "inc_net_profit_year_on_year = '%s', " \ "inc_net_profit_annual = '%s', " \ "inc_net_profit_to_shareholders_year_on_year = '%s', " \ "inc_net_profit_to_shareholders_annual = '%s' )>" % ( self.code, self.pubDate, self.statDate, self.eps, self.adjusted_profit, self.operating_profit, self.value_change_profit, self.roe, self.inc_return, self.roa, self.net_profit_margin, self.gross_profit_margin, self.expense_to_total_revenue, self.operation_profit_to_total_revenue, self.net_profit_to_total_revenue, self.operating_expense_to_total_revenue, self.ga_expense_to_total_revenue, self.financing_expense_to_total_revenue, self.operating_profit_to_profit, self.invesment_profit_to_profit, self.adjusted_profit_to_profit, self.goods_sale_and_service_to_revenue, self.ocf_to_revenue, self.ocf_to_operating_profit, self.inc_total_revenue_year_on_year, self.inc_total_revenue_annual, self.inc_revenue_year_on_year, self.inc_revenue_annual, self.inc_operation_profit_year_on_year, self.inc_operation_profit_annual, self.inc_net_profit_year_on_year, self.inc_net_profit_annual, self.inc_net_profit_to_shareholders_year_on_year, self.inc_net_profit_to_shareholders_annual) # class valuation(Base): __tablename__ = 'valuation' code = Column(String(30), primary_key=True) day = Column(String(10)) capitalization= Column(Float) circulating_cap= Column(Float) market_cap= Column(Float) circulating_market_cap= Column(Float) turnover_ratio= Column(Float) pe_ratio= Column(Float) pe_ratio_lyr= Column(Float) pb_ratio= Column(Float) ps_ratio= Column(Float) pcf_ratio= Column(Float) def __repr__(self): return "<Valuation(code='%s', day='%s', capitalization='%s', circulating_cap = '%s'," \ " market_cap = '%s', circulating_market_cap = '%s', turnover_ratio = '%s'," \ "pe_ratio = '%s',pe_ratio_lyr = '%s',pb_ratio = '%s',ps_ratio = '%s',pcf_ratio = '%s' )>" % ( self.code, self.day,self.capitalization, self.circulating_cap, self.market_cap, self.circulating_market_cap,self.turnover_ratio, self.pe_ratio, self.pe_ratio_lyr, self.pb_ratio,self.ps_ratio, self.pcf_ratio) def mytest(): for s in session.query(indicator): print(s) for s in session.query(valuation): print(s) print(financial_data_filter_dayu('000002.XSHE', valuation.pe_ratio, -1)) def financial_data_filter_dayu(security_list, field, v1 = 0): rs = [] code = str(field).split('.')[0] + '.code' for s in session.query(code).filter( and_( field > v1, valuation.code == '000001.XSHE') ): rs.append(s) return rs def query(*obj): query = session.query(*obj) return session.query(*obj) mytest()
3.测试
2107
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
