91 lines
2.2 KiB
Python
91 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
|
|
from pycen import update_to_sql
|
|
import pandas as pd
|
|
import geopandas as gpd
|
|
from sqlalchemy.sql.expression import column
|
|
|
|
|
|
nom_numerateur = 'GEIER'
|
|
pnom_numerator = 'Colas'
|
|
today = dt.now().date().isoformat()
|
|
# Parametres bdd
|
|
user = 'cen_admin'
|
|
pwd = '#CEN38@venir'
|
|
adr = '91.134.194.221'
|
|
port = '5432'
|
|
base = 'sicen2'
|
|
|
|
url = URL.create('postgresql+psycopg2',
|
|
username=user,
|
|
password=pwd,
|
|
host=adr,
|
|
database=base,
|
|
)
|
|
con = create_engine(url)
|
|
|
|
# FONCTION
|
|
def get_numerator(nom,pnom,id_structure=1):
|
|
nom_numerateur = nom.upper()
|
|
pnom_numerator = pnom[0].upper() + pnom[1:]
|
|
query_get_numerator = """
|
|
SELECT * FROM md.personne
|
|
WHERE nom = '%s'
|
|
AND prenom = '%s'
|
|
AND id_structure = %i
|
|
""" % (nom_numerateur,pnom_numerator,id_structure)
|
|
numerateur = pd.read_sql_query(
|
|
sql = query_get_numerator,
|
|
con = con
|
|
)
|
|
return numerateur
|
|
|
|
# SELECT OBSERVATION
|
|
sql = 'SELECT * FROM saisie.saisie_observation'
|
|
saisie = gpd.read_postgis(
|
|
sql,
|
|
con,
|
|
'geometrie')
|
|
saisie.sort_values('id_obs', inplace=True)
|
|
|
|
# SELECT COMMUNE
|
|
sql = 'SELECT * FROM ign_bd_topo.commune'
|
|
com = gpd.read_postgis(
|
|
sql,
|
|
con,
|
|
'geometrie')
|
|
|
|
|
|
# JOINTURE spatiale
|
|
df = gpd.sjoin(saisie[['id_obs','code_insee','geometrie']],com[['code_insee','geometrie']],op='within')
|
|
df.sort_values('id_obs', inplace=True)
|
|
tmp = df[~df.code_insee_left.eq(df.code_insee_right)].copy()
|
|
tmp.rename(columns={'code_insee_right':'code_insee'}, inplace=True)
|
|
# UPDATE SET new code_insee
|
|
update_to_sql(
|
|
tmp[['id_obs','code_insee']],
|
|
con=con,
|
|
table_name='saisie_observation',
|
|
schema_name='saisie',
|
|
key_name='id_obs',
|
|
geom_col='geometrie'
|
|
)
|
|
|
|
|
|
# UPDATE utilisateur OF saisie.suivi_saisie_observation
|
|
numerateur = get_numerator(nom_numerateur,pnom_numerator)
|
|
update_structure_name = """
|
|
UPDATE saisie.suivi_saisie_observation a
|
|
SET utilisateur = REPLACE(utilisateur, 'inconnu', '{email_numerator}')
|
|
WHERE date_operation > '{today}'
|
|
;
|
|
""".format(
|
|
email_numerator=numerateur.email.item(),
|
|
today = today
|
|
)
|
|
with con.begin() as cnx:
|
|
cnx.execute(update_structure_name) |