70 lines
2.2 KiB
Python
70 lines
2.2 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-.
|
|
|
|
from sqlalchemy import create_engine, text
|
|
from sqlalchemy.engine import URL
|
|
from datetime import datetime as dt
|
|
import pandas as pd
|
|
import geopandas as gpd
|
|
|
|
|
|
# Parametres bdd
|
|
user = 'cen_admin'
|
|
pwd = '#CEN38@venir'
|
|
adr = '91.134.194.221'
|
|
port = '5432'
|
|
base = 'azalee'
|
|
|
|
url = URL.create('postgresql+psycopg2',
|
|
username=user,
|
|
password=pwd,
|
|
host=adr,
|
|
database=base,
|
|
)
|
|
con = create_engine(url)
|
|
|
|
v_rpg2021_ilots_anonymes_isere = """
|
|
DROP VIEW IF EXISTS ref_territoire.v_rpg2021_ilots_anonymes_isere;
|
|
CREATE OR REPLACE VIEW ref_territoire.v_rpg2021_ilots_anonymes_isere
|
|
AS SELECT --s.id,
|
|
s.*
|
|
FROM ref_territoire.rpg2021_ilots_anonymes_reg s,
|
|
ref_territoire.dept_isere reg
|
|
WHERE st_intersects(s.geom, reg.geom);
|
|
"""
|
|
grant_rpg2021_ilots = """
|
|
ALTER TABLE ref_territoire.v_rpg2021_ilots_anonymes_isere OWNER TO cen_admin;
|
|
GRANT ALL ON TABLE ref_territoire.v_rpg2021_ilots_anonymes_isere TO cen_admin;
|
|
GRANT ALL ON TABLE ref_territoire.v_rpg2021_ilots_anonymes_isere TO grp_admin;
|
|
GRANT SELECT ON TABLE ref_territoire.v_rpg2021_ilots_anonymes_isere TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_rpg2021_ilots_anonymes_isere)
|
|
cnx.execute(grant_rpg2021_ilots)
|
|
|
|
|
|
v_rpg2021_parcelles_graphiques_isere = (v_rpg2021_ilots_anonymes_isere
|
|
.replace('v_rpg2021_ilots_anonymes_isere','v_rpg2021_parcelles_graphiques_isere')
|
|
.replace('rpg2021_ilots_anonymes_reg','rpg2021_parcelles_graphiques_reg'))
|
|
grant = grant_rpg2021_ilots.replace('v_rpg2021_ilots_anonymes_isere','v_rpg2021_parcelles_graphiques_isere')
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_rpg2021_parcelles_graphiques_isere)
|
|
cnx.execute(grant)
|
|
|
|
|
|
v_rpg2020_ilots_anonymes_isere = v_rpg2021_ilots_anonymes_isere.replace('2021','2020')
|
|
grant = grant_rpg2021_ilots.replace('2021','2020')
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_rpg2020_ilots_anonymes_isere)
|
|
cnx.execute(grant)
|
|
|
|
|
|
v_rpg2020_parcelles_graphiques_isere = v_rpg2021_ilots_anonymes_isere.replace('v_rpg2021_ilots_anonymes_isere','v_rpg2020_parcelles_graphiques_isere')
|
|
grant = grant_rpg2021_ilots.replace('v_rpg2021_ilots_anonymes_isere','v_rpg2020_parcelles_graphiques_isere')
|
|
with con.begin() as cnx:
|
|
cnx.execute(v_rpg2020_parcelles_graphiques_isere)
|
|
cnx.execute(grant)
|
|
|
|
|
|
|