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)