def work(conp,**krg):
info=krg['info']
para={
"zfcg_bdate":None,"gcjs_bdate":None
}
if info is not None:
info=json.loads(info)
para.update(info)
zfcg_bdate=para['zfcg_bdate']
gcjs_bdate=para['gcjs_bdate']
quyu=conp[4]
schemas=db_query(""" SELECT nspname FROM pg_namespace """,dbtype="postgresql",conp=conp)['nspname'].values.tolist()
if quyu not in schemas: db_command("""create schema if not exists %s """%quyu,dbtype="postgresql",conp=conp)
tables=db_query("""SELECT tablename FROM pg_tables where schemaname='%s' """%quyu,dbtype="postgresql",conp=conp)['tablename'].values.tolist()
if 't_gg' not in tables:tag=None
else:tag='cdc'
for st in data:
if tag is None:
jytype=st[1]
if jytype=='zfcg':
bdate=st[4] if zfcg_bdate is None else zfcg_bdate
else:
bdate=st[4] if gcjs_bdate is None else gcjs_bdate
print(bdate)
else:
bdate=datetime.strftime(datetime.now()+timedelta(days=-2),'%Y-%m-%d')
src_update_dates(st[0],st[1],conp,bdate)
est_func
print("t_gg_update")
t_gg_update(conp,st[2],st[3])
# conp=['postgres','since2015','192.168.4.201','zlsys','zlsys_yunnan_dalizhou']
# work(conp,info=None)
def insert_tb(tbname,diqu,conp):
data=ext_tb(tbname)
ggtype=data["ggtype"]
jytype=data["jytype"]
#info=data["info"]
ggtype="'%s'"%ggtype if ggtype is not None else "NULL"
jytype="'%s'"%jytype if jytype is not None else "NULL"
schema=conp[4]
sql2="""
insert into %s.gg
select distinct on (name,href,ggstart_time ) name,href,ggstart_time,%s::text as ggtype,%s::text as jytype,
'%s'::text diqu, info from %s.%s
where (name,href,ggstart_time) not in(select name,href,ggstart_time from %s.gg)
"""%(schema,ggtype,jytype,diqu,schema,tbname,schema)
db_command(sql2,dbtype="postgresql",conp=conp)
def create_schemas():
conp = get_conp1('guangxi')
arr = [
"baise",
"beihai",
"chongzuo",
"fangchenggang",
"guangxi",
"guigang",
"guilin",
"hechi",
"hezhou",
"laibin",
"liuzhou",
"nanning",
"qinzhou",
"wuzhou",
]
for diqu in arr:
sql = "create schema if not exists %s" % diqu
db_command(sql, dbtype="postgresql", conp=conp)
def src_cdc1(shi, jytype, conp, date):
user, passwd, host, db, schema = conp
update(shi, jytype, date, conp)
print("增量下载到数据库")
tbs = db_query(
"""select tablename from pg_tables where schemaname='%s' and tablename ~'gg_cdc|html_cdc' order by tablename """
% schema,
dbtype="postgresql",
conp=conp)['tablename'].values.tolist()
print(tbs)
n = int(len(tbs) / 2)
for i in range(n):
j = i + 1
tbname = 't_gg_src_%d_cdc' % j
gg_tbname, html_tbname = tbs[2 * i], tbs[2 * i + 1]
sql = """drop table if exists %s.%s ;
select a.*,b.page into %s.%s from %s.%s as a , %s.%s as b where a.gg_file=b.guid """ % (
schema, tbname, schema, tbname, schema, gg_tbname, schema,
html_tbname)
db_command(sql, dbtype="postgresql", conp=conp)
sql = "drop table if exists %s.%s;drop table if exists %s.%s;" % (
schema, gg_tbname, schema, html_tbname)
db_command(sql, dbtype="postgresql", conp=conp)
def create_schemas():
conp = get_conp('public')
arr = ['guyuan','ningxia','yinchuan']
for diqu in arr:
sql = "create schema if not exists %s" % diqu
db_command(sql, dbtype="postgresql", conp=conp)
def est_func(conp):
sql = r"""
create or replace function ggtype_tran(ggtype text ) returns text
as $$
data={"pbjieguo":"评标结果公告",
"zhaobiao":"招标公告",
"fuhe_jieguo":"复核结果公告",
"pbjieguo_biangeng":"评标结果变更",
"zhongbiao":"中标公告",
"zhongbiao_biangeng":"中标结果变更公告",
"dayi":"答疑公告",
"liubiao":"流标公告",
"yichang":"异常公告",
"biangeng":"变更公告"
}
if ggtype in data.keys():return data[ggtype]
else:return None
$$ language plpython3u ;
--drop function merge_info(xmjl text, bm_endtime text, tb_endtime text, bzj_time text, kb_time text, pb_time text, db_time text, zhongbiao_hxr text, zhongbiaojia numeric, kzj numeric,bd_guid text);
create or replace function merge_info( xmjl text ,bm_endtime text,tb_endtime text ,bzj_time text
,kb_time text ,pb_time text ,db_time text ,zhongbiao_hxr text ,zhongbiaojia text ,kzj text,bd_guid text ,bd_bh text,bd_name text,zbr text ,zbdl text,xmjl_dj text ,xmjl_zsbh text
) returns text
as $$
import json
import re
import time
def exttime(tstr):
a=re.findall('[1][0-9]{12}',tstr)
if a!=[]:
t=a[0]
t=int(int(a[0])/1000)
val=time.localtime(t)
dt = time.strftime('%Y-%m-%d %H:%M:%S', val)
return dt
a=re.findall('([0-9]{4})[\-\\/]([0-9]{1,2})[\-\\/]([0-9]{1,2})',tstr)
if a!=[]:
t=a[0]
t=t[0]+'-'+(t[1] if len(t[1])==2 else '0%s'%t[1])+'-'+(t[2] if len(t[2])==2 else '0%s'%t[2])
return t
return None
def extprice(price):
if price is None:return None
CN_NUM = {
'〇' : 0, '一' : 1, '二' : 2, '三' : 3, '四' : 4, '五' : 5, '六' : 6, '七' : 7, '八' : 8, '九' : 9, '零' : 0,
'壹' : 1, '贰' : 2, '叁' : 3, '肆' : 4, '伍' : 5, '陆' : 6, '柒' : 7, '捌' : 8, '玖' : 9, '貮' : 2, '两' : 2,
}
CN_UNIT = {
'十' : 10,
'拾' : 10,
'百' : 100,
'佰' : 100,
'千' : 1000,
'仟' : 1000,
'万' : 10000,
'萬' : 10000,
'亿' : 100000000,
'億' : 100000000,
'兆' : 1000000000000,
}
def chinese_to_arabic(cn:str) -> int:
unit = 0 # current
ldig = [] # digest
for cndig in reversed(cn):
if cndig in CN_UNIT:
unit = CN_UNIT.get(cndig)
if unit == 10000 or unit == 100000000:
ldig.append(unit)
unit = 1
else:
dig = CN_NUM.get(cndig)
if unit:
dig *= unit
unit = 0
ldig.append(dig)
if unit == 10:
ldig.append(10)
val, tmp = 0, 0
for x in reversed(ldig):
if x == 10000 or x == 100000000:
val += tmp * x
tmp = 0
else:
tmp += x
val += tmp
return val
a=re.findall('[四〇伍叁零二八三壹六柒貮一捌九五两贰肆玖七陆億兆佰亿万萬十拾仟千百]{3,}',price)
if a!=[]:
result=chinese_to_arabic(a[0])
return result
a=re.findall('([1-9][0-9\.]{0,}[0-9]|0\.[0-9]+)[^%]',price)
if a!=[]:
result=a[0]
if result.count('.')>1: result='.'.join(result.split('.')[:2])
if '万' in price:
result=float(result)
result=result*10000
if '亿' in price:
result=float(result)
result=result*100000000
return result
return None
data={}
if xmjl is not None :data['xmjl']=xmjl
if bm_endtime is not None:data['bm_endtime']=exttime(bm_endtime)
if tb_endtime is not None :data['tb_endtime']=exttime(tb_endtime)
if bzj_time is not None:data['bzj_time']=exttime(bzj_time)
if kb_time is not None:data['kb_time']=exttime(kb_time)
if db_time is not None:data['db_time']=exttime(db_time)
if zhongbiao_hxr is not None:data['zhongbiao_hxr']=zhongbiao_hxr
if bd_guid is not None:data['bd_guid']=bd_guid
if bd_name is not None:data['bd_name']=bd_name
if bd_bh is not None:data['bd_bh']=bd_bh
if zbr is not None:data['zbr']=zbr
if zbdl is not None:data['zbdl']=zbdl
if xmjl_dj is not None:data['xmjl_dj']=xmjl_dj
if xmjl_zsbh is not None:data['xmjl_zsbh']=xmjl_zsbh
if zhongbiaojia is not None:data['zhongbiaojia']=extprice(str(zhongbiaojia ))
if kzj is not None:data['kzj']=extprice(str(kzj ))
data=json.dumps(data,ensure_ascii=False)
return data
$$ language plpython3u ;
create or replace function exttime(tstr text ) returns text
as $$
import time
import re
if tstr is None:return None
a=re.findall('[1][0-9]{12}',tstr)
if a!=[]:
t=a[0]
t=int(int(a[0])/1000)
val=time.localtime(t)
dt = time.strftime('%Y-%m-%d %H:%M:%S', val)
return dt
a=re.findall('([0-9]{4})[\-\\/]([0-9]{1,2})[\-\\/]([0-9]{1,2}) ([0-9]{1,2}:[0-9]{1,2}:[0-9]{1,2})',tstr)
if a!=[]:
t=a[0]
t=t[0]+'-'+(t[1] if len(t[1])==2 else '0%s'%t[1])+'-'+(t[2] if len(t[2])==2 else '0%s'%t[2])+' '+t[3]
return t
return None
$$ language plpython3u;
"""
db_command(sql, dbtype="postgresql", conp=conp)
def create_schemas():
conp = get_conp('public')
arr = ['shanxi']
for diqu in arr:
sql = "create schema if not exists %s" % diqu
db_command(sql, dbtype="postgresql", conp=conp)
def create_schemas():
conp = get_conp1('qinghai')
arr = ['qinghai', 'xining']
for diqu in arr:
sql = "create schema if not exists %s" % diqu
db_command(sql, dbtype="postgresql", conp=conp)
def create_schemas():
conp = get_conp('public')
arr = ['baoshan','chuxiong','dali','lijiang','lincang','puer','tengchong','wenshan','yunnan','yuxi','zhaotong','kunming']
for diqu in arr:
sql = "create schema if not exists %s" % diqu
db_command(sql, dbtype="postgresql", conp=conp)
def drop_zlsys(quyu,conp):
user,password,ip,db,schema=conp
sql="alter table %s.gg drop partition for('%s')"%(schema,quyu)
db_command(sql,dbtype='postgresql',conp=conp)
def create_schemas():
conp = get_conp('public')
arr = ["daqing", "hegang", "heilongjiang", 'qiqihaer', 'yichun']
for diqu in arr:
sql = "create schema if not exists %s" % diqu
db_command(sql, dbtype="postgresql", conp=conp)
def create_schemas():
conp = get_conp1('chongqing')
arr = ["yongchuan"]
for diqu in arr:
sql = "create schema if not exists %s" % diqu
db_command(sql, dbtype="postgresql", conp=conp)
def copy(self,tb_src,tb_dst,coltext=None,**krg):
#para={"distri":None,"pxf_partition":None,'external_only':False,"exclude_col":None,"rename":{},"drop":False}
bg=time.time()
#"&PARTITION_BY=year:int&RANGE=2011:2013&INTERVAL=1"
para={"distri":None,"pxf_partition":None,'external_only':False,"exclude_col":None,"rename":{},"drop":False}
para.update(krg)
user,passwd,host,db,schema=self.conp_src
src_schema,src_name=tb_src.split('.')
dst_schema,dst_name=tb_dst.split('.')
#删除可能存在的外部表
sql="select tablename from pg_tables where schemaname='%s' and tablename='%s_tmp' "%(dst_schema,dst_name)
df=db_query(sql,dbtype="postgresql",conp=self.conp_dst)
if not df.empty:
sql="drop external table %s.%s_tmp"%(dst_schema,dst_name)
print(sql)
db_command(sql,dbtype="postgresql",conp=self.conp_dst)
coltext=self.get_coltext(tb_src,coltext)
col_type=[[re.split('\s+',w,1)[0].strip(),re.split('\s+',w,1)[1].strip() ] for w in re.split(",(?!\s*[0-9\)])",coltext)]
data1=[]#外部表
data2=[]#目的地表
data3=[]
arr=[] #insert 处的内容
brr=[] #insert 处的select 内容
exclude_col=[]
rename=para['rename']
if para['exclude_col'] is not None:
exclude_col=[ w.strip() for w in para['exclude_col'].split(',') ]
for w in col_type:
if w[1] in ['json','tsvector','jsonb']:
data1.append([w[0],'text'])
elif w[1].startswith('_'):
data1.append([w[0],'text'])
else:
data1.append(w)
if w[0] not in exclude_col:
data2.append(w)
arr.append(w[0])
if w[0] in rename.keys():
brr.append(rename[w[0]])
else:
if w[1] in ['json','jsonb','tsvector']:
brr.append('%s::%s %s'%(w[0],w[1],w[0]))
elif w[1].startswith('_'):
brr.append("%s::%s %s"%(w[0],w[1],w[0]))
else:
brr.append(w[0])
coltext_external=','.join([ ' '.join(w1) for w1 in data1])
coltext_dsttb=','.join([ ' '.join(w1) for w1 in data2])
#dst里创建外部表
partition_by="" if para['pxf_partition'] is None else para['pxf_partition']
sql="""
create external table %s.%s_tmp(%s)
LOCATION ('pxf://%s.%s?PROFILE=JDBC&JDBC_DRIVER=org.postgresql.Driver&DB_URL=jdbc:postgresql://%s/%s&USER=%s&PASS=%s%s')
FORMAT 'CUSTOM' (FORMATTER='pxfwritable_import');
"""%(dst_schema,dst_name,coltext_external,src_schema,src_name,host,db,user,passwd,partition_by)
print(sql)
db_command(sql,dbtype="postgresql",conp=self.conp_dst)
##只生成外部表
if para['external_only']:
print("只生成外部表")
return
#select into 生成表
if para['drop']:
sql="drop table if exists %s.%s"%(dst_schema,dst_name)
print(sql)
db_command(sql,dbtype="postgresql",conp=self.conp_dst)
else:
sql="truncate %s.%s"%(dst_schema,dst_name)
print(sql)
db_command(sql,dbtype="postgresql",conp=self.conp_dst)
colnames=','.join(arr)
colnames1=','.join(brr)
if para['distri'] is None:
sql="""create table if not exists %s (%s ) """%(tb_dst,coltext_dsttb)
else:
sql="""create table if not exists %s (%s ) distributed by(%s) """%(tb_dst,coltext_dsttb,para['distri'])
print(sql)
db_command(sql,dbtype="postgresql",conp=self.conp_dst)
sql="""insert into %s(%s) select %s from %s_tmp """%(tb_dst,colnames,colnames1,tb_dst)
print(sql)
db_command(sql,dbtype="postgresql",conp=self.conp_dst)
#删除外部表
sql="select tablename from pg_tables where schemaname='%s' and tablename='%s_tmp' "%(dst_schema,dst_name)
df=db_query(sql,dbtype="postgresql",conp=self.conp_dst)
if not df.empty:
sql="drop external table %s.%s_tmp"%(dst_schema,dst_name)
print(sql)
db_command(sql,dbtype="postgresql",conp=self.conp_dst)
ed=time.time()
cost=int(ed-bg)
print("total--cost %d s"%cost)
def command(sql):
path1=join(dirname(__file__),"cfg_db")
db_command(sql,dbtype="sqlite",conp=path1)
def create_schemas():
conp = get_conp1('hubei')
arr = ["dangyang", "enshi", "lichuan", "yidu"]
for diqu in arr:
sql = "create schema if not exists %s" % diqu
db_command(sql, dbtype="postgresql", conp=conp)
def create_schemas():
conp = get_conp('public')
arr = ['xizang', 'lasa', 'rikaze']
for diqu in arr:
sql = "create schema if not exists %s" % diqu
db_command(sql, dbtype="postgresql", conp=conp)
def create_schemas():
conp = get_conp1('xinjiang')
arr = ["xinjiang", "beitun", "atushen", "wulumuqi"]
for diqu in arr:
sql = "create schema if not exists %s" % diqu
db_command(sql, dbtype="postgresql", conp=conp)
