class TsDailyBasic(Base):
__tablename__ = 'ts_daily_basic'
__table_args__ = (Index('date', 'trade_date'),
Index('code_date', 'ts_code', 'trade_date'), {
'mysql_engine': 'InnoDB',
'mysql_charset': 'utf8'
})
id = Column('id',
BIGINT,
primary_key=True,
comment='id',
autoincrement=True)
ts_code = Column('ts_code', String(10), comment='TS股票代码')
trade_date = Column('trade_date', String(10), comment='交易日期')
close = Column('close', DECIMAL(30, 10), comment='当日收盘价')
turnover_rate = Column('turnover_rate', DECIMAL(30, 10), comment='换手率（%）')
turnover_rate_f = Column('turnover_rate_f',
DECIMAL(30, 10),
comment='换手率（自由流通股）')
volume_ratio = Column('volume_ratio', DECIMAL(30, 10), comment='量比')
pe = Column('pe', DECIMAL(30, 10), comment='市盈率（总市值/净利润）')
pe_ttm = Column('pe_ttm', DECIMAL(30, 10), comment='市盈率（TTM）')
pb = Column('pb', DECIMAL(30, 10), comment='市净率（总市值/净资产）')
ps = Column('ps', DECIMAL(30, 10), comment='市销率')
ps_ttm = Column('ps_ttm', DECIMAL(30, 10), comment='市销率（TTM）')
dv_ratio = Column('dv_ratio', DECIMAL(30, 10), comment='股息率 （%）')
dv_ttm = Column('dv_ttm', DECIMAL(30, 10), comment='股息率（TTM）（%）')
total_share = Column('total_share', DECIMAL(30, 10), comment='总股本 （万股）')
float_share = Column('float_share', DECIMAL(30, 10), comment='流通股本 （万股）')
free_share = Column('free_share', DECIMAL(30, 10), comment='自由流通股本 （万）')
total_mv = Column('total_mv', DECIMAL(30, 10), comment='总市值 （万元）')
circ_mv = Column('circ_mv', DECIMAL(30, 10), comment='流通市值（万元）')
nullable=False,
index=True), Column('match_list', LONGBLOB),
Column('fasta', LONGBLOB),
Column('type', Enum('rfamseq', 'genome', 'refseq'), nullable=False))
t_motif_family_stats = Table(
'motif_family_stats', metadata,
Column('rfam_acc',
ForeignKey('family.rfam_acc'),
nullable=False,
index=True),
Column('motif_acc',
ForeignKey('motif_old.motif_acc'),
nullable=False,
index=True), Column('num_hits', INTEGER(11)),
Column('frac_hits', DECIMAL(4, 3)), Column('sum_bits', DECIMAL(12, 3)),
Column('avg_weight_bits', DECIMAL(12, 3)))
t_motif_matches = Table(
'motif_matches', metadata,
Column('motif_acc',
ForeignKey('motif_old.motif_acc',
ondelete='CASCADE',
onupdate='CASCADE'),
nullable=False,
index=True),
Column('rfam_acc',
ForeignKey('family.rfam_acc',
ondelete='CASCADE',
onupdate='CASCADE'),
nullable=False,
class Market(Base):
__tablename__ = 'sk_daily_price_new'
__table_args__ = (
Index('id', 'trade_date', 'security_code', unique=True),
)
id = Column(VARCHAR(32), primary_key=True)
# symbol = Column(VARCHAR(24), primary_key=True)
security_code = Column(VARCHAR(24), primary_key=True)
trade_date = Column(DATE, primary_key=True)
name = Column(VARCHAR(50))
pre_close = Column(DECIMAL(15, 6))
open = Column(DECIMAL(15, 6))
close = Column(DECIMAL(15, 6))
high = Column(DECIMAL(15, 6))
low = Column(DECIMAL(15, 6))
volume = Column(DECIMAL(20, 2))
money = Column(DECIMAL(18, 3))
deals = Column(DECIMAL(10, 0))
change = Column(DECIMAL(9, 4))
change_pct = Column(DECIMAL(8, 4))
tot_mkt_cap = Column(DECIMAL(18, 4))
turn_rate = Column(DECIMAL(9, 4))
pre_factor = Column(DECIMAL(9, 4))
lat_factor = Column(DECIMAL(9, 4))
Table,
)
from datetime import datetime
from databases import Database
DATABASE_URI = os.getenv("DATABASE_URI")
engine = create_engine(DATABASE_URI)
metadata = MetaData()
orders = Table(
"order",
metadata,
Column("id", Integer, primary_key=True),
Column("order_date",
Date,
nullable=False,
index=True,
default=datetime.now),
Column("delivery_date",
Date,
nullable=False,
index=True,
default=datetime.now),
Column("order_no", String(50), nullable=False),
Column("amount", DECIMAL(10, 7), nullable=False),
)
database = Database(DATABASE_URI)
DECIMAL,
NVARCHAR,
Column,
DateTime,
Integer,
Table,
)
from .dsx_read_schema_base import DsxReadSchemaBase
#: Table schema definition for reading raw inputs from dsx_read database.
DsxInput = Table(
DSX_INPUT_TABLE,
DsxReadSchemaBase.metadata,
Column("enditemid", Integer, nullable=False),
Column("itemname", NVARCHAR(4000), nullable=False),
Column("Ship To", NVARCHAR(63), nullable=True),
Column("projectid", NVARCHAR(63), nullable=True),
Column("binid", NVARCHAR(63), nullable=True),
Column("branch", NVARCHAR(63), nullable=True),
Column("shortid", NVARCHAR(63), nullable=True),
Column("masterpart", NVARCHAR(63), nullable=True),
Column("Cost", NVARCHAR(1000), nullable=True),
Column("contractid", NVARCHAR(63), nullable=True),
Column("projecttype", NVARCHAR(63), nullable=True),
Column("binstatus", NVARCHAR(63), nullable=True),
Column("lowlevelcust", NVARCHAR(63), nullable=True),
Column("perioddate", DateTime, nullable=False),
Column("Adjusted History", DECIMAL(precision=38, scale=7), nullable=True),
)
class CreditPubBase(NXBase):
u"""
对公授信基础信息表
"""
__tablename__ = 'cr_creditpubbase'
id = Column(BigInteger, doc=u'ID', primary_key=True)
custno = Column(String(64), doc=u'客户内码')
customerno = Column(String(64), doc=u'客户号')
custname = Column(String(512), doc=u'客户名称')
fr_custno = Column(String(64), doc=u'法人客户内码')
fr_customerno = Column(String(64), doc=u'法人客户号')
fr_custname = Column(String(64), doc=u'法人客户名称')
main_industry = Column(String(64), doc=u'主行业')
child_industry = Column(String(64), doc=u'子行业')
comp_attr = Column(String(64), doc=u'企业性质')
build_year = Column(DECIMAL(18, 6), doc=u'企业设立年份')
rejistry_captial = Column(DECIMAL(18, 6), doc=u'注册资本(万元)')
employee_num = Column(DECIMAL(18, 6), doc=u'职工人数')
cust_grade = Column(String(64), doc=u'信用等级')
data_time = Column(Date, doc=u'数据时段')
total_asset = Column(DECIMAL(18, 6), doc=u'资产总额(元)')
total_debt = Column(String(64), doc=u'负债总额(元)')
other_bank_loan = Column(DECIMAL(18, 6), doc=u'他行现有贷款(元)')
other_bank_loan_ld = Column(DECIMAL(18, 6), doc=u'他行流动资金贷款(元)')
other_loan = Column(DECIMAL(18, 6), doc=u'其他贷款(元)')
exp_lose = Column(DECIMAL(18, 6), doc=u'预计或有负债损失额(元)')
main_income = Column(DECIMAL(18, 6), doc=u'期末主营业务收入(元)')
st_av1 = Column(DECIMAL(18, 6), doc=u'期初-存货余额(元)')
st_av2 = Column(DECIMAL(18, 6), doc=u'期末-存货余额(元)')
sh_income_av1 = Column(DECIMAL(18, 6), doc=u'期初-应收账款余额(元)')
sh_income_av2 = Column(DECIMAL(18, 6), doc=u'期末-应收账款余额(元)')
ex_income_av1 = Column(DECIMAL(18, 6), doc=u'期初-预收账款余额(元)')
ex_income_av2 = Column(DECIMAL(18, 6), doc=u'期末-预收账款余额(元)')
sh_outcome_av1 = Column(DECIMAL(18, 6), doc=u'期初-应付账款余额(元)')
sh_outcome_av2 = Column(DECIMAL(18, 6), doc=u'期末-应付账款余额(元)')
ex_outcome_av1 = Column(DECIMAL(18, 6), doc=u'期初-预付账款余额(元)')
ex_outcome_av2 = Column(DECIMAL(18, 6), doc=u'期末-预付账款余额(元)')
ly_sc = Column(DECIMAL(18, 6), doc=u'上年度销售收入(元)')
ly_cb = Column(DECIMAL(18, 6), doc=u'上年度销售成本(元)')
operate_amount = Column(DECIMAL(18, 6), doc=u'运营资金量(元)')
ly_spr = Column(DECIMAL(18, 6), doc=u'上年度销售利润率')
ex_sc1 = Column(DECIMAL(18, 6), doc=u'上年-预计销售收入(元)')
ex_sc2 = Column(DECIMAL(18, 6), doc=u'本年-预计销售收入(元)')
ex_scr = Column(DECIMAL(18, 6), doc=u'预计销售年增长率(元)')
from_Date = Column(Date, doc=u'生效日期')
end_Date = Column(Date, doc=u'失效日期')
class GradePubBase(NXBase):
u"""
对公评级基础信息表
"""
__tablename__ = 'cr_gradepubbase'
id = Column(BigInteger, doc=u'ID', primary_key=True)
customerno = Column(String(64), doc=u'客户号')
custno = Column(String(64), doc=u'客户内码')
custname = Column(String(512), doc=u'客户姓名')
custtype = Column(String(64), doc=u'公私标志')
loantype = Column(String(64), doc=u'贷款类型')
man_age = Column(DECIMAL(18, 2), doc=u'经营者年龄')
man_edu = Column(String(64), doc=u'经营者学历')
main_industry = Column(String(64), doc=u'主行业')
detail_industry = Column(String(64), doc=u'子行业')
comp_age = Column(DECIMAL(18, 2), doc=u'企业经营年限')
comp_num = Column(DECIMAL(18, 2), doc=u'企业职工人数')
elect_value = Column(DECIMAL(18, 2), doc=u'用电量')
wate_ect_pay = Column(DECIMAL(18, 6), doc=u'水电费支出同比增长率')
taxes_pay = Column(DECIMAL(18, 6), doc=u'增值销项税同比增长率')
act_cash_in = Column(DECIMAL(18, 6), doc=u'实际经营性现金现金流入量')
bill_sale = Column(DECIMAL(18, 6), doc=u'开票销售率')
sale_add = Column(DECIMAL(18, 6), doc=u'销售增长率')
sale_fin = Column(DECIMAL(18, 6), doc=u'销售融资比')
add_taxes = Column(DECIMAL(18, 6), doc=u'新增融资纳税比')
wage_pay = Column(DECIMAL(18, 6), doc=u'工资性支出同比增长')
acc_turn = Column(DECIMAL(18, 6), doc=u'应收账款周转率')
net_asset = Column(DECIMAL(18, 6), doc=u'净资产')
cash_in = Column(DECIMAL(18, 6), doc=u'现金流入量')
gross_ratio = Column(DECIMAL(18, 6), doc=u'')
asset_ratio = Column(DECIMAL(18, 6), doc=u'资产负债率')
from_Date = Column(Date, doc=u'生效日期')
end_Date = Column(Date, doc=u'失效日期')
class ProfitTable(Base):
__tablename__ = 'profit_table'
year = Column(String(4), primary_key=True)
period = Column(String(8), primary_key=True)
stock_cd = Column(String(6), primary_key=True)
data_sour = Column(String(1000), primary_key=True)
biz_income = Column(DECIMAL(24, 2))
biz_cost = Column(DECIMAL(24, 2))
sell_cost = Column(DECIMAL(24, 2))
manage_cost = Column(DECIMAL(24, 2))
explor_cost = Column(DECIMAL(24, 2))
fin_cost = Column(DECIMAL(24, 2))
ast_devalu_loss = Column(DECIMAL(24, 2))
fair_value_chng_net_inc = Column(DECIMAL(24, 2))
inv_prft = Column(DECIMAL(24, 2))
invest_assoc_joint_comp = Column(DECIMAL(24, 2))
operat_prft_oth_subj = Column(DECIMAL(24, 2))
run_prft = Column(DECIMAL(24, 2))
subs_reven = Column(DECIMAL(24, 2))
nonbiz_incom = Column(DECIMAL(24, 2))
nonbiz_cost = Column(DECIMAL(24, 2))
ncurrt_ast_dispos_nloss = Column(DECIMAL(24, 2))
oth_subj_affect_total_prft = Column(DECIMAL(24, 2))
profit_tamt = Column(DECIMAL(24, 2))
income_tax = Column(DECIMAL(24, 2))
oth_subj_affect_net_prft = Column(DECIMAL(24, 2))
net_profit = Column(DECIMAL(24, 2))
nprf_attrib_parent_corp = Column(DECIMAL(24, 2))
less_intr_income = Column(DECIMAL(24, 2))
modifytime = Column(String(19))
class CashFlowTable(Base):
__tablename__ = 'cash_flow_table'
year = Column(String(4), primary_key=True)
period = Column(String(8), primary_key=True)
stock_cd = Column(String(6), primary_key=True)
data_sour = Column(String(1000), primary_key=True)
cash_recev_sell_goods = Column(DECIMAL(24, 2))
refund_taxes = Column(DECIMAL(24, 2))
cash_recev_oth_run_biz = Column(DECIMAL(24, 2))
operat_activ_cash_inflows = Column(DECIMAL(24, 2))
cash_paid_buy_goods = Column(DECIMAL(24, 2))
tax_paym = Column(DECIMAL(24, 2))
cash_paid_staff = Column(DECIMAL(24, 2))
cash_paid_oth_run_biz = Column(DECIMAL(24, 2))
operat_activ_cash_outflow = Column(DECIMAL(24, 2))
operat_activ_cash_flow_net = Column(DECIMAL(24, 2))
cash_recev_invests = Column(DECIMAL(24, 2))
cash_recev_invest_intr = Column(DECIMAL(24, 2))
net_cash_recev_disp_fix_ast = Column(DECIMAL(24, 2))
net_cash_recev_oth_biz = Column(DECIMAL(24, 2))
recev_oth_invest_activ_cash = Column(DECIMAL(24, 2))
cash_inflow_invest_activ = Column(DECIMAL(24, 2))
cash_paid_constr_fixed_ast = Column(DECIMAL(24, 2))
inv_payment = Column(DECIMAL(24, 2))
net_cash_acqu_oth_biz_units = Column(DECIMAL(24, 2))
pay_oth_invest_activ_cash = Column(DECIMAL(24, 2))
cash_outflow_invest_activ = Column(DECIMAL(24, 2))
net_cashflow_make_invest_activ = Column(DECIMAL(24, 2))
cash_recev_invest = Column(DECIMAL(24, 2))
cash_recev_debts = Column(DECIMAL(24, 2))
oth_fin_activ_recv_cash = Column(DECIMAL(24, 2))
fina_activ_cash_inflow = Column(DECIMAL(24, 2))
debt_payment = Column(DECIMAL(24, 2))
pay_intr_cash = Column(DECIMAL(24, 2))
cash_payment_rela_fina_activ = Column(DECIMAL(24, 2))
cash_outflow_fina_activ = Column(DECIMAL(24, 2))
ncash_flow_make_fina_activ = Column(DECIMAL(24, 2))
modifytime = Column(String(19))
class Orders(_BaseMixin):
__tablename__ = "orders"
order_id = Column(String(255), nullable=False, unique=True)
price = Column(DECIMAL(13, 5), nullable=False, default=0)
class Product(BaseModel):
__tablename__ = "product"
product_name = Column(VARCHAR(255), index=True, nullable=False)
sku_name = Column(VARCHAR(64), index=True, nullable=False)
standard_price = Column(DECIMAL(scale=2), default=0.0)
class ValidCredit(Base):
__tablename__ = 'ValidCredit'
creditCode = Column(SMALLINT(6), primary_key=True)
displayValue = Column(String(50))
MaxCreditLimit = Column(DECIMAL(10, 2), server_default=text("'0.00'"))
class Brand(Base):
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(255), nullable=False, default='')
list_pic_url = Column(String(255), nullable=False, default='')
simple_desc = Column(String(255), nullable=False, default='')
pic_url = Column(String(255), nullable=False, default='')
sort_order = Column(Integer, nullable=False, default='50')
is_show = Column(Integer, index=True, nullable=False, default='1')
floor_price = Column(DECIMAL(10, 2), nullable=False, default='0.00')
app_list_pic_url = Column(String(255), nullable=False, default='')
is_new = Column(Integer, nullable=False, default='0')
new_pic_url = Column(String(255), nullable=False, default='')
new_sort_order = Column(Integer, nullable=False, default='100')
@classmethod
def get_info(cls, item):
info = {
'id': item.id,
'name': item.name,
'list_pic_url': item.list_pic_url,
'simple_desc': item.simple_desc,
'pic_url': item.pic_url,
'sort_order': item.sort_order,
'is_show': item.is_show,
'floor_price': str(item.floor_price),
'app_list_pic_url': item.app_list_pic_url,
'is_new': item.is_new,
'new_pic_url': item.new_pic_url,
'new_sort_order': item.new_sort_order
}
return info
@classmethod
def get_detail(cls, id):
item = cls.query.filter_by(id=id, delete_time=None).first()
if not item:
return None
info = cls.get_info(item)
return info
@classmethod
def get_all(cls, params=None):
brand = cls.query
if params is not None:
page = params['page'] if 'page' in params else current_page
size = params['size'] if 'size' in params else PAGESIZE
brand = brand.filter_by(delete_time=None).limit(size).offset(
(int(page) - 1) * size).all()
else:
brand = brand.filter_by(delete_time=None).all()
if not brand:
return None
items = []
for item in brand:
info = cls.get_info(item)
items.append(info)
return items
@classmethod
def search_by_name(cls, q, params=None):
brand = cls.query
if params is not None:
page = params['page'] if 'page' in params else current_page
size = params['size'] if 'size' in params else PAGESIZE
brand = brand.filter(cls.name.like('%' + q + '%'),
cls.delete_time == None).limit(size).offset(
(int(page) - 1) * size).all()
else:
brand = brand.filter(cls.name.like('%' + q + '%'),
cls.delete_time == None).all()
if not brand:
return None
items = []
for item in brand:
info = cls.get_info(item)
items.append(info)
return items
@classmethod
def new(cls, form):
brand = cls.query.filter_by(name=form.name.data,
delete_time=None).first()
if brand is not None:
return False
item = cls.create(
name=form.name.data if form.name.data else '',
list_pic_url=form.list_pic_url.data
if form.list_pic_url.data else '',
simple_desc=form.simple_desc.data if form.simple_desc.data else '',
pic_url=form.pic_url.data if form.pic_url.data else '',
sort_order=form.sort_order.data if form.sort_order.data else '50',
is_show=form.is_show.data if form.is_show.data else '1',
floor_price=form.floor_price.data
if form.floor_price.data else '0.00',
app_list_pic_url=form.app_list_pic_url.data
if form.app_list_pic_url.data else '',
is_new=form.is_new.data if form.is_new.data else '0',
new_pic_url=form.new_pic_url.data if form.new_pic_url.data else '',
new_sort_order=form.new_sort_order.data
if form.new_sort_order.data else '100',
commit=True)
info = cls.get_info(item)
return info
@classmethod
def edit(cls, id, form):
brand = cls.query.filter_by(id=id, delete_time=None).first()
if brand is None:
return False
item = brand.update(
id=id,
name=form.name.data if form.name.data else '',
list_pic_url=form.list_pic_url.data
if form.list_pic_url.data else '',
simple_desc=form.simple_desc.data if form.simple_desc.data else '',
pic_url=form.pic_url.data if form.pic_url.data else '',
sort_order=form.sort_order.data if form.sort_order.data else '50',
is_show=form.is_show.data if form.is_show.data else '1',
floor_price=form.floor_price.data
if form.floor_price.data else '0.00',
app_list_pic_url=form.app_list_pic_url.data
if form.app_list_pic_url.data else '',
is_new=form.is_new.data if form.is_new.data else '0',
new_pic_url=form.new_pic_url.data if form.new_pic_url.data else '',
new_sort_order=form.new_sort_order.data
if form.new_sort_order.data else '100',
commit=True)
info = cls.get_info(item)
return info
@classmethod
def remove(cls, id):
brand = cls.query.filter_by(id=id, delete_time=None).first()
if brand is None:
return False
item = brand.delete(commit=True)
return True
# -*- coding: utf-8 -*-
from sqlalchemy import Column, String, create_engine, Integer, Float, DECIMAL, Sequence, MetaData, Table
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# 创建对象的基类:
Base = declarative_base()
# 初始化数据库连接:
engine = create_engine(
'mysql+mysqlconnector://root:123456@localhost:3306/city_DistMSG',
echo=True)
metadata = MetaData(engine)
Base.metadata.drop_all(engine)
user_table = Table('cityDist', metadata, Column('id',
Integer,
primary_key=True),
Column('start_city', String(20)),
Column('end_city', String(20)),
Column('start_lat', DECIMAL(15)),
Column('start_lon', DECIMAL(15)),
Column('end_lat', DECIMAL(15)),
Column('end_lon', DECIMAL(15)),
Column('dist_city', DECIMAL(24)))
metadata.create_all()
class GradePubAllScore(NXBase):
u"""
对公评级总分表
"""
__tablename__ = 'cr_gradepuballscore'
id = Column(BigInteger, doc=u'ID', primary_key=True)
customerno = Column(String(64), doc=u'客户号')
custno = Column(String(64), doc=u'客户内码')
custname = Column(String(512), doc=u'客户姓名')
custtype = Column(String(64), doc=u'公私标志')
loantype = Column(String(64), doc=u'贷款类型')
main_industry = Column(String(64), doc=u'主行业')
detail_industry = Column(String(64), doc=u'子行业')
model_type = Column(String(64), doc=u'模型类型')
comp_num = Column(DECIMAL(18, 2), doc=u'企业职工人数')
comp_num_score = Column(DECIMAL(18, 6), doc=u'企业职工人数得分')
man_age = Column(DECIMAL(18, 2), doc=u'经营者年龄')
man_age_score = Column(DECIMAL(18, 6), doc=u'经营者年龄得分')
man_edu = Column(String(64), doc=u'经营者学历')
man_edu_score = Column(DECIMAL(18, 6), doc=u'经营者学历得分')
comp_age = Column(DECIMAL(18, 6), doc=u'企业经营年限')
comp_age_score = Column(DECIMAL(18, 6), doc=u'企业经营年限得分')
wate_ect_pay = Column(DECIMAL(18, 6), doc=u'水电费支出同比增长率')
wate_ect_pay_score = Column(DECIMAL(18, 6), doc=u'水电费支出同比增长率得分')
taxes_pay = Column(DECIMAL(18, 6), doc=u'增值销项税同比增长率')
taxes_pay_score = Column(DECIMAL(18, 6), doc=u'增值税销项税同比增长率得分')
act_cash_in = Column(DECIMAL(18, 6), doc=u'实际经营性现金现金流入量')
act_cash_in_score = Column(DECIMAL(18, 6), doc=u'实际经营性现金流入量得分')
elect_value = Column(DECIMAL(18, 6), doc=u'用电量')
elect_value_score = Column(DECIMAL(18, 6), doc=u'用电量得分')
bill_sale = Column(DECIMAL(18, 6), doc=u'开票销售率')
bill_sale_score = Column(DECIMAL(18, 6), doc=u'开票销售率得分')
sale_add = Column(DECIMAL(18, 6), doc=u'销售增长率')
sale_add_score = Column(DECIMAL(18, 6), doc=u'销售增长率得分')
sale_fin = Column(DECIMAL(18, 6), doc=u'销售融资比')
sale_fin_score = Column(DECIMAL(18, 6), doc=u'销售融资比得分')
add_taxes = Column(DECIMAL(18, 6), doc=u'新增融资纳税比')
add_taxes_score = Column(DECIMAL(18, 6), doc=u'新增融资纳税比得分')
wage_pay = Column(DECIMAL(18, 6), doc=u'工资性支出同比增长')
wage_pay_score = Column(DECIMAL(18, 6), doc=u'工资性支出同比增长得分')
acc_turn = Column(DECIMAL(18, 6), doc=u'应收账款周转率')
acc_turn_score = Column(DECIMAL(18, 6), doc=u'应收账款周转率得分')
allscore = Column(DECIMAL(18, 6), doc=u'总得分')
level = Column(String(64), doc=u'信用等级')
adj_level = Column(String(64), doc=u'调整评级（不良下调）')
adj_res = Column(String(64), doc=u'调整说明')
now_level = Column(String(64), doc=u'当前等级（查看）')
from_Date = Column(Date, doc=u'生效日期')
end_Date = Column(Date, doc=u'失效日期')
class AsstLiabTable(Base):
__tablename__ = 'asst_liab_table'
year = Column(String(4), primary_key=True)
period = Column(String(8), primary_key=True)
stock_cd = Column(String(6), primary_key=True)
data_sour = Column(String(1000), primary_key=True)
curr_fund = Column(DECIMAL(24, 2))
notes_recev = Column(DECIMAL(24, 2))
txn_fin_ast = Column(DECIMAL(24, 2))
reces = Column(DECIMAL(24, 2))
prepay = Column(DECIMAL(24, 2))
oth_recev = Column(DECIMAL(24, 2))
recev_afflt_account = Column(DECIMAL(24, 2))
recev_intr = Column(DECIMAL(24, 2))
recev_dividn = Column(DECIMAL(24, 2))
inventy = Column(DECIMAL(24, 2))
consum_bio_ast = Column(DECIMAL(24, 2))
oyear_not_current_ast = Column(DECIMAL(24, 2))
other_current_ast = Column(DECIMAL(24, 2))
current_ast_sum = Column(DECIMAL(24, 2))
saleable_fin_ast = Column(DECIMAL(24, 2))
hold_investm_due = Column(DECIMAL(24, 2))
lterm_reces = Column(DECIMAL(24, 2))
lterm_equity_investm = Column(DECIMAL(24, 2))
real_estate_investm = Column(DECIMAL(24, 2))
fixed_ast = Column(DECIMAL(24, 2))
under_constr_proj = Column(DECIMAL(24, 2))
proj_goods = Column(DECIMAL(24, 2))
fixed_ast_clean = Column(DECIMAL(24, 2))
prod_bio_ast = Column(DECIMAL(24, 2))
oil_ast = Column(DECIMAL(24, 2))
intang_ast = Column(DECIMAL(24, 2))
develop_costs = Column(DECIMAL(24, 2))
goodwill = Column(DECIMAL(24, 2))
deferred_ast = Column(DECIMAL(24, 2))
deferred_tax_ast = Column(DECIMAL(24, 2))
oth_non_current_ast = Column(DECIMAL(24, 2))
non_current_ast_sum = Column(DECIMAL(24, 2))
ast_sum = Column(DECIMAL(24, 2))
sterm_liab = Column(DECIMAL(24, 2))
txn_fin_liab = Column(DECIMAL(24, 2))
notes_payable = Column(DECIMAL(24, 2))
accounts_payable = Column(DECIMAL(24, 2))
adv_account = Column(DECIMAL(24, 2))
payroll_payable = Column(DECIMAL(24, 2))
tax_payable = Column(DECIMAL(24, 2))
intr_payable = Column(DECIMAL(24, 2))
dividn_payable = Column(DECIMAL(24, 2))
oth_payable = Column(DECIMAL(24, 2))
due_related_corp = Column(DECIMAL(24, 2))
oyear_not_current_liab = Column(DECIMAL(24, 2))
oth_current_liab = Column(DECIMAL(24, 2))
current_liab_sum = Column(DECIMAL(24, 2))
ltrem_loan = Column(DECIMAL(24, 2))
bonds_payable = Column(DECIMAL(24, 2))
term_payable = Column(DECIMAL(24, 2))
spec_payable = Column(DECIMAL(24, 2))
estim_liab = Column(DECIMAL(24, 2))
deferr_inc_tax_liab = Column(DECIMAL(24, 2))
oth_not_current_liab = Column(DECIMAL(24, 2))
not_current_liab_sum = Column(DECIMAL(24, 2))
liab_sum = Column(DECIMAL(24, 2))
real_reces_cap = Column(DECIMAL(24, 2))
cap_reserve = Column(DECIMAL(24, 2))
earned_surplus = Column(DECIMAL(24, 2))
treas_stock = Column(DECIMAL(24, 2))
undistr_profit = Column(DECIMAL(24, 2))
minority_equity = Column(DECIMAL(24, 2))
fcurr_trans_spreads = Column(DECIMAL(24, 2))
abnorm_run_proj_inc_adjust = Column(DECIMAL(24, 2))
owner_intr_sum = Column(DECIMAL(24, 2))
liab_owner_sum = Column(DECIMAL(24, 2))
modifytime = Column(String(19))
class GradePerDealerScore(NXBase):
u"""
对私工商乐分值表
"""
__tablename__ = 'cr_gradeperdealerscore'
id = Column(BigInteger, doc=u'ID', primary_key=True)
allscore_id = Column(BigInteger, doc=u'')
net_asset = Column(DECIMAL(18, 6), doc=u'净资产')
net_asset_score = Column(DECIMAL(18, 6), doc=u'净资产分值')
compdis_income = Column(DECIMAL(18, 6), doc=u'企业可支配收入')
compdis_income_score = Column(DECIMAL(18, 6), doc=u'企业可支配收入分值')
operate_age = Column(DECIMAL(18, 6), doc=u'企业经营年限')
operate_age_score = Column(DECIMAL(18, 6), doc=u'企业经营年限分值')
ownership = Column(DECIMAL(18, 6), doc=u'经营场地权属')
ownership_score = Column(DECIMAL(18, 6), doc=u'经营场地权属分值')
sale_ratio = Column(DECIMAL(18, 6), doc=u'销售利润率')
sale_ratio_score = Column(DECIMAL(18, 6), doc=u'销售利润率分值')
asset_ratio = Column(DECIMAL(18, 6), doc=u'资产负债率')
asset_ratio_score = Column(DECIMAL(18, 6), doc=u'资产负债率分值')
net_profit = Column(DECIMAL(18, 6), doc=u'年净利润率')
net_profit_score = Column(DECIMAL(18, 6), doc=u'年净利润率分值')
count_score = Column(DECIMAL(18, 6), doc=u'专家模型分值汇总')
from_Date = Column(Date, doc=u'生效日期')
end_Date = Column(Date, doc=u'失效日期')
__abstract__ = True
aging_definition = Table('AGING DEFINITION',
DDIDataWarehouse.metadata,
Column('AGINGCODE', Unicode(100)),
Column('AGINGDESCRIPTION', Unicode(500)),
Column('AGINGID', Integer, nullable=False),
schema="DDIDataWarehouse")
ar_history = Table('ARHISTORY',
DDIDataWarehouse.metadata,
Column('SJPERIOD', Unicode(4)),
Column('CUSTOMERCODE', Unicode(100)),
Column('CUSTOMERDEFAULTBRANCHCODE', Unicode(100)),
Column('AREOMVALUE', DECIMAL(14, 4), nullable=False),
Column('EXTENDEDSALE', DECIMAL(14, 4)),
Column('CUSTOMERID', Integer),
Column('CUSTOMERDEFAULTBRANCHID', Integer),
Column('SJDATE', DateTime),
Column('ARHISTORYID', Integer, primary_key=True),
schema="DDIDataWarehouse")
branch = Table('BRANCH',
DDIDataWarehouse.metadata,
Column('BRANCHCODE', Unicode(100), nullable=False),
Column('BRANCHDESC', Unicode(500)),
Column('DIVISIONCODE', Unicode(100)),
Column('BRANCHID', Integer, primary_key=True),
Column('DIVISIONID', Integer),
Column('BRANCHCODEDESC', Unicode(500)),
class GradePerEmpScore(NXBase):
u"""
对私金领乐工薪乐分值表
"""
__tablename__ = 'cr_gradeperempscore'
id = Column(BigInteger, doc=u'ID', primary_key=True)
allscore_id = Column(BigInteger, doc=u'')
job = Column(String(64), doc=u'职务')
job_score = Column(DECIMAL(18, 6), doc=u'职务分值')
workyear = Column(DECIMAL(18, 2), doc=u'工作年限')
workyear_score = Column(DECIMAL(18, 6), doc=u'工作年限分值')
year_wage = Column(DECIMAL(18, 2), doc=u'年工资收入')
year_wage_score = Column(DECIMAL(18, 6), doc=u'年工资收入分值')
if_social = Column(String(64), doc=u'是否缴纳社保')
if_social_score = Column(DECIMAL(18, 6), doc=u'是否缴纳社保分值')
pro_fund = Column(DECIMAL(22, 4), doc=u'公积金月缴存额')
pro_fund_score = Column(DECIMAL(18, 6), doc=u'公积金月缴存额分值')
pro_year = Column(DECIMAL(18, 4), doc=u'公积金持续年份')
pro_year_score = Column(DECIMAL(18, 6), doc=u'公积金持续年份分值')
exp_income = Column(DECIMAL(18, 6), doc=u'支出收入比')
exp_income_score = Column(DECIMAL(18, 6), doc=u'支出收入比分值')
house_value = Column(DECIMAL(18, 4), doc=u'房产价值')
house_value_score = Column(DECIMAL(18, 6), doc=u'房产价值分值')
count_score = Column(DECIMAL(18, 6), doc=u'专家模型分值汇总')
from_Date = Column(Date, doc=u'生效日期')
end_Date = Column(Date, doc=u'失效日期')
# metadata = MetaData()
conn = engine.connect()
numbers = Table('sort',
metadata,
Column('id', Integer(), primary_key=True),
Column('numbers', Integer()),
Column('created_on', DateTime(), default=datetime.now),
autoload=True)
runtime = Table('runtime',
metadata,
Column('id', Integer(), primary_key=True),
Column('size_of_array', Integer()),
Column('time', DECIMAL(precision=10, scale=5)),
Column('created_on', DateTime(), default=datetime.now),
autoload=True)
metadata.drop_all(bind=conn, checkfirst=True)
metadata.create_all(conn)
tic = timeit.default_timer()
size = 15
random_list = np.random.randint(10, 1000, size)
print(random_list)
ordered_random_list = np.sort(random_list)
print(ordered_random_list)
class GradePerBase(NXBase):
u"""
对私评级基础信息表
"""
__tablename__ = 'cr_gradeperbase'
id = Column(BigInteger, doc=u'ID', primary_key=True)
custno = Column(String(64), doc=u'客户内码')
customerno = Column(String(64), doc=u'客户号')
custname = Column(String(512), doc=u'客户姓名')
custtype = Column(String(64), doc=u'公私标志')
loantype = Column(String(64), doc=u'贷款类型')
certno = Column(String(64), doc=u'身份证号')
age = Column(DECIMAL(18, 2), doc=u'年龄')
sex = Column(String(16), doc=u'性别')
education = Column(String(64), doc=u'最高学历')
marriage = Column(String(64), doc=u'婚姻状况')
workpro = Column(String(64), doc=u'单位性质')
avgdep = Column(DECIMAL(30, 6), doc=u'日均存款')
avgloan = Column(DECIMAL(30, 6), doc=u'日均贷款')
starlevel = Column(String(64), doc=u'客户星级')
starlevel_r = Column(String(64), doc=u'原客户星级')
job = Column(String(64), doc=u'职务')
workyear = Column(DECIMAL(18, 4), doc=u'工作年限')
year_wage = Column(DECIMAL(22, 4), doc=u'年工资收入')
if_social = Column(String(64), doc=u'是否缴纳社保')
pro_fund = Column(DECIMAL(22, 4), doc=u'公积金月缴存额')
pro_year = Column(DECIMAL(18, 4), doc=u'公积金持续年份')
exp_income = Column(DECIMAL(18, 6), doc=u'支出收入比')
house_value = Column(DECIMAL(18, 4), doc=u'房产价值')
breed_income = Column(DECIMAL(18, 4), doc=u'年种养殖收入')
dis_income = Column(DECIMAL(18, 4), doc=u'可支配收入')
net_asset = Column(DECIMAL(18, 4), doc=u'净资产')
compdis_income = Column(DECIMAL(18, 4), doc=u'企业可支配收入')
operate_age = Column(DECIMAL(18, 2), doc=u'企业经营年限')
ownership = Column(String(64), doc=u'经营场地权属')
sale_ratio = Column(DECIMAL(18, 6), doc=u'销售利润率')
asset_ratio = Column(DECIMAL(18, 6), doc=u'资产负债率')
net_profit = Column(DECIMAL(18, 6), doc=u'年净利润率')
from_Date = Column(Date, doc=u'生效日期')
end_Date = Column(Date, doc=u'失效日期')
class asset(Base):
uid = Column(Integer(), primary_key=True, nullable=True)
asset = Column(DECIMAL(19, 4), nullable=True)
class PaymentBase(Base):
__abstract__ = True
amount = Column(DECIMAL(precision=10, scale=2), nullable=False)
patient_id = Column(Text, nullable=False, index=True)
class Transaction(Base):
id1 = Column(Integer(), primary_key=True)
id2 = Column(Integer(), primary_key=True)
num = Column(DECIMAL(19, 4), nullable=True)
create_date = Column(DateTime(), nullable=True)
class CollectionRecords(ORMBase):
"""
归集记录
"""
__tablename__ = 'tb_collection_records'
id = Column(key='id',
type_=Integer(),
primary_key=True,
autoincrement=True,
comment='id 自增')
token_name = Column(key='token_name',
type_=String(20),
nullable=False,
comment='币种名')
from_address = Column(key='from_address',
type_=String(100),
nullable=False,
comment='源地址')
to_address = Column(key='to_address',
type_=String(100),
nullable=False,
comment='目的地址')
amount = Column(key='amount',
type_=DECIMAL(precision=28,
scale=8,
decimal_return_scale=8,
asdecimal=True),
nullable=False,
comment='归集金额')
complete_time = Column(key='complete_time',
type_=DateTime(timezone=True),
nullable=True,
comment='完成时间')
block_height = Column(key='block_height',
type_=BigInteger(),
default=0,
nullable=False,
comment='交易所在区块高度')
block_time = Column(key='block_time',
type_=DateTime(timezone=True),
nullable=True,
comment='区块时间戳')
transaction_status = Column(
key='transaction_status',
type_=String(20),
nullable=False,
comment=
'交易状态, NOTYET:尚未汇出, PENDING:已转出,等待交易为被打包进区块, FAIL :交易失败, SUCCESS : 交易成功 '
)
collection_type = Column(key='collection_type',
type_=String(20),
nullable=False,
comment="归集类型")
tx_hash = Column(key='tx_hash',
type_=String(100),
nullable=False,
comment='交易hash(txid)')
pro_id = Column(Integer(),
ForeignKey(Project.pro_id, ondelete='CASCADE'),
comment='项目方ID')
project = relationship("Project", backref='collection_records_ref_project')
tx_confirmations = Column(key='tx_confirmations',
type_=Integer(),
nullable=False,
default=0,
comment='交易的区块确认数')
def __repr__(self):
return f'CollectionRecords(id:{self.id}, token_name:{self.token_name}, from_address:{self.from_address}, ' \
f'to_address:{self.to_address}, amount:{self.amount}, tx_hash:{self.tx_hash} )'
class Asset_table(Base):
__tablename__ = 'asset'
uid = Column(Integer(), primary_key=True, nullable=True)
asset = Column(DECIMAL(19, 4), nullable=True)
class IndexMarket(Base):
__tablename__ = 'index_daily_price'
__table_args__ = (
Index('id', 'trade_date', 'security_code', unique=True),
)
id = Column(VARCHAR(32), primary_key=True)
security_code = Column(VARCHAR(24), primary_key=True)
trade_date = Column(DATE, primary_key=True)
name = Column(VARCHAR(50))
pre_close = Column(DECIMAL(15, 6))
open = Column(DECIMAL(15, 6))
close = Column(DECIMAL(15, 6))
high = Column(DECIMAL(15, 6))
low = Column(DECIMAL(15, 6))
volume = Column(DECIMAL(20, 2))
money = Column(DECIMAL(18, 3))
deals = Column(DECIMAL(10, 0))
change = Column(DECIMAL(9, 4))
change_pct = Column(DECIMAL(8, 4))
circulating_market_cap = Column(DECIMAL(18, 4))
market_cap = Column(DECIMAL(9, 4))
class Record_tabl(Base):
__tablename__ = 'record'
one_id = Column(Integer(), primary_key=True)
other_id = Column(Integer(), primary_key=True)
deal = Column(DECIMAL(19, 4), nullable=True)
create_date = Column(DateTime(), nullable=True)
class Asset_table(Base):
#用户 id、用户名
__tablename__ = 'q6_asset'
user_id = Column(Integer(), primary_key=True )
asset = Column(DECIMAL(21,3), index=True)
class Plans(Base):
"""
This is the Medicare plans that are paid for don't change
"""
__tablename__ = 'plans'
id = Column(Integer, primary_key=True)
CONTRACT_ID = Column(String)
PLAN_ID = Column(Integer)
SEGMENT_ID = Column(String)
CONTRACT_NAME = Column(String)
PLAN_NAME = Column(String)
FORMULARY_ID = Column(Integer)
PREMIUM = Column(DECIMAL(precision=8, asdecimal=True, scale=2),
nullable=True)
DEDUCTIBLE = Column(DECIMAL(precision=8, asdecimal=True, scale=2),
nullable=True)
ICL = Column(Integer, nullable=True)
MA_REGION_CODE = Column(Integer)
PDP_REGION_CODE = Column(Integer)
STATE = Column(String)
COUNTY_CODE = Column(Integer)
SNP = Column(Integer, nullable=True)
PLAN_SUPPRESSED_YN = Column(Boolean)
GEO_ids = Column(ARRAY(Integer, ForeignKey('geolocate.id')))
@classmethod
def find_by_formulary_id(cls, fid):
"""
:param fid:
:return:
"""
qry = cls.session.query(cls).filter(cls.FORMULARY_ID == fid)
return qry.all()
@classmethod
def find_by_plan_name(cls, name, exact=False, geo=None):
"""
Find similar drugs in the database
:param name: drug name
:return: matches
"""
if not exact:
name = f"%{name.lower()}%"
else:
name = name.lower()
fltr = cls.PLAN_NAME.ilike(name)
if geo:
#select * from plans where 2090 = ANY("GEO_ids");
fltr = and_(fltr, cls.GEO_ids.any(geo))
qry = cls.session.query(cls).filter(fltr)
return qry.all()
@classmethod
def find_in_county(cls, county_code, ma_region, pdp_region, name='*'):
"""
Query plans in a certain county
"""
flter = or_(cls.COUNTY_CODE == county_code,
cls.MA_REGION_CODE == ma_region,
cls.PDP_REGION_CODE == pdp_region)
if not name == '*':
look_for = f"{name.lower()}%"
flter = and_(flter, cls.PLAN_NAME.ilike(look_for))
qry = cls.session.query(Plans.PLAN_NAME).filter(flter).distinct(
cls.PLAN_NAME).all()
results = [r.PLAN_NAME for r in qry]
return results
def __repr__(self):
return "<{}>".format(self.PLAN_NAME)