63 lines
1.3 KiB
Python
63 lines
1.3 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-
|
|
|
|
from sqlalchemy import create_engine
|
|
from sqlalchemy.engine import URL
|
|
from datetime import datetime as dt
|
|
import pandas as pd
|
|
import pycen
|
|
|
|
|
|
|
|
# Parametres bdd
|
|
user = 'cgeier'
|
|
pwd = 'adm1n*bdCen'
|
|
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)
|
|
|
|
sch = 'saisie'
|
|
tab = 'saisie_observation'
|
|
ids = 'id_obs'
|
|
col_updt = 'phenologie'
|
|
|
|
sql = """
|
|
SELECT
|
|
{ids},
|
|
{col}
|
|
FROM {sch}.{tab}
|
|
WHERE {col} = 'Inderterminé'
|
|
""".format(sch=sch,tab=tab,ids=ids,col=col_updt)
|
|
df = pd.read_sql_query(sql,con)
|
|
df[col_updt] = 'Indéterminé'
|
|
pycen.update_to_sql(df,con,tab,sch,ids,geom_col=None)
|
|
|
|
|
|
tab_suivi = 'suivi_saisie_observation'
|
|
ids_suivi = ['operation','date_operation','id_obs']
|
|
col_updtSuivi = 'utilisateur'
|
|
date = dt.today().date().isoformat()
|
|
sql = """
|
|
SELECT
|
|
{ids},
|
|
{col}
|
|
FROM {sch}.{tab}
|
|
WHERE {col} = 'inconnu'
|
|
AND date_operation > '{date}'
|
|
""".format(sch=sch,tab=tab_suivi,ids=','.join(ids_suivi),col=col_updtSuivi, date=date)
|
|
df = pd.read_sql_query(sql,con)
|
|
df[col_updtSuivi] = 'colas.geier@cen-isere.org'
|
|
pycen.update_to_sql(
|
|
df,
|
|
con,
|
|
table_name=tab_suivi,
|
|
schema_name=sch,
|
|
key_name = ids_suivi,
|
|
geom_col=None) |