Files
airflow/dags/jh/price/pqsql_connect.py
2025-04-23 17:54:22 +08:00

100 lines
5.5 KiB
Python

import pandas as pd
import os
from pgsql_client import PgsqlDb
os.environ['NLS_LANG'] = 'SAMPLIFIED CHINESE_CHINA.UTF8'
pgdb = PgsqlDb('warehouse')
pgdb_new = PgsqlDb('fangjia')
def get_community_base_price(cityid,yearmonth):
"""
查询提供建行小区数据
:param cr:
:return:
"""
sql = f"select gujia_admndiv_nm,gujia_bds_id,urbn_nm,comm_nm,gujiabds_clc_yrmo_val,gujia_comm_basic_prc,gujia_bds_clmtd from price_stat.avm_community_base_price where urbn_id='{cityid}' and gujiabds_clc_yrmo_val={yearmonth}"
data_df = pgdb.read(sql)
data_df = data_df.rename(columns={'gujia_admndiv_nm':'district','gujia_bds_id': '_id','urbn_nm':'city','comm_nm':'name','gujiabds_clc_yrmo_val':'yearmonth','gujia_comm_basic_prc':'price','gujia_bds_clmtd':'type'})
return data_df
def get_community_base_price_new(cityid,yearmonth):
"""
查询提供建行小区数据
:param cr:
:return:
"""
sql = f"select gujia_bds_id,urbn_nm,comm_nm,gujiabds_clc_yrmo_val,gujia_comm_basic_prc,gujia_bds_clmtd from price_stat.avm_community_base_price where urbn_id='{cityid}' and gujiabds_clc_yrmo_val={yearmonth}"
data_df = pgdb_new.read(sql)
data_df = data_df.rename(columns={'gujia_bds_id': '_id','urbn_nm':'city_copy','comm_nm':'name_copy','gujiabds_clc_yrmo_val':'yearmonth','gujia_comm_basic_prc':'price_new','gujia_bds_clmtd':'type_new'})
return data_df
def get_district_polyline(cityid=None,cr=None):
"""
查询提供建行小区数据
:param cr:
:return:
"""
if cityid:
sql = f"select * from bigdata_district_polyline_gd where cityid='{cityid}'"
else:
sql = f"select distinct city,distname from bigdata_district_polyline_gd"
queryse = cr.execute(sql)
columns = [i[0].lower() for i in queryse.description]
rs = cr.fetchall()
data_df = pd.DataFrame(rs,columns=columns)
return data_df
def get_community_ccb(cityid):
sql = f"select gujia_comm_id,gujia_admndiv_nm,comm_nm from base.bigdata_community where urbn_id='{cityid}' and gujia_bds_bds_st=0"
data_df = pgdb.read(sql)
data_df = data_df.rename(columns={'gujia_comm_id': '_id','gujia_admndiv_nm':'region','comm_nm':'name'})
return data_df
def get_bak_community(cityid,cr):
"""
查询小区数据
:param cr:
:return:
"""
sql = f"select a.*,b.longitude,b.latitude,c.distname from bigdata_community_bak a left join (select cityid, objectid, LONGITUDE, LATITUDE from bigdata_position_bak where objecttype = '5' and positiontype = '1') b on a.cityid = b.cityid and a.commid = b.objectid left join bigdata_district_bak c on a.cityid=c.cityid and a.distid=c.distid where a.cityid = '{cityid}'"
queryse = cr.execute(sql)
columns = [i[0].lower() for i in queryse.description]
rs = cr.fetchall()
data_df = pd.DataFrame(rs,columns=columns)
return data_df
def avm_price_copy(cityid,yearmonth):
sql = f"insert into price_stat.avm_community_base_price_gj_h_copy select * from price_stat.avm_community_base_price where urbn_id='{cityid}' and gujiabds_clc_yrmo_val={yearmonth}"
pgdb.sql_execute(sql)
def get_sample(cityid):
sql = f"select urbn_id,gujia_comm_id,comm_nm,gujia_bds_unitprc,guiabconstructionarea from base.bigdata_forsale_gj_h where urbn_id='{cityid}' and gujia_hs_src_dtsrc in ('1','4') and anc_tm >= TO_DATE('2023-04-01','YYYY-MM-DD')"
data_df = pgdb.read(sql)
return data_df
def get_deal(cityid):
sql = f"select urbn_id,gujia_comm_id,comm_nm,guiabconstructionarea,gujia_bds_trdtckt_prc from base.bigdata_deal where urbn_id='{cityid}' and txn_dt >= TO_DATE('2024-03-01','YYYY-MM-DD') and txn_dt < TO_DATE('2024-04-01','YYYY-MM-DD')"
data_df = pgdb.read(sql)
data_df = data_df.rename(columns={'urbn_id': 'cityid','gujia_comm_id': '_id','comm_nm': 'name', 'guiabconstructionarea': 'area','gujia_bds_trdtckt_prc': 'deal_price'})
return data_df
def get_deal_data(cityid):
sql = f"select * from base.bigdata_deal where urbn_id='{cityid}' and txn_dt >= TO_DATE('2024-03-01','YYYY-MM-DD') and txn_dt < TO_DATE('2024-04-01','YYYY-MM-DD')"
data_df = pgdb.read(sql)
return data_df
def get_region_price(cityid,yearmonth):
sql = f"select urbn_id,gujia_admndiv_id,gxda_admndiv_nm,gujia_bds_urbn_avprc from price_stat.avm_district_price_gj_h where urbn_id='{cityid}' and gujiabds_clc_yrmo_val={yearmonth}"
data_df = pgdb.read(sql)
data_df = data_df.rename(columns={'urbn_id': 'cityid','gujia_admndiv_id': 'district_id','gxda_admndiv_nm': 'district_name', 'gujia_bds_urbn_avprc': 'district_price'})
return data_df
def get_evaluation_third_price(cityid,yearmonth,type='第三方'):
sql = f"select urbn_id,gujia_comm_id,gujiabds_to_mkprc_mny from price_stat.avm_evaluation_item_gj_h where urbn_id='{cityid}' and gujiabds_clc_yrmo_val={yearmonth} and gujia_data_tp='{type}' "
data_df = pgdb.read(sql)
data_df = data_df.rename(columns={'urbn_id': 'cityid','gujiabds_to_mkprc_mny': 'third_price','gujia_comm_id':'_id'})
return data_df
def get_third_price(cityid,yearmonth):
sql = f"select urbn_id,gujia_bds_id,comm_nm,gujia_dtsr_dsc,gujia_bds_comm_avprc from base.bigdata_third_party_price where urbn_id='{cityid}' and gujiabds_avprc_dt_val={yearmonth} and gujia_dtsr_dsc in ('链家','安居客') "
data_df = pgdb.read(sql)
data_df = data_df.rename(columns={'urbn_id': 'cityid','gujia_bds_id': '_id','gujia_dtsr_dsc': 'source','gujia_bds_comm_avprc': 'price'})
return data_df
if __name__ == '__main__':
city='常州'
yearmonth=202302
get_community_base_price(city=city,yearmonth=yearmonth)