47 lines
1.1 KiB
Python
47 lines
1.1 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_perso = """
|
|
DROP VIEW IF EXISTS personnes.v_personne CASCADE;
|
|
CREATE OR REPLACE VIEW personnes.v_personne
|
|
AS
|
|
SELECT
|
|
a.id,
|
|
CASE WHEN btrim(lower(concat(a.nom, ' ', a.prenom))) = lower(COALESCE(b.abbrev, b.nom))
|
|
then btrim(concat(a.nom, ' ', a.prenom))
|
|
else string_agg(btrim(concat(a.nom, ' ', a.prenom,' (',COALESCE(b.abbrev, b.nom),')')),';')
|
|
end AS auteur
|
|
FROM personnes.personne a
|
|
JOIN personnes.organisme b ON a.id_organisme = b.id
|
|
GROUP BY a.id,b.abbrev, b.nom;
|
|
"""
|
|
grant = """
|
|
GRANT ALL ON TABLE personnes.v_personne TO grp_admin;
|
|
GRANT SELECT ON TABLE personnes.v_personne TO grp_consult;
|
|
"""
|
|
with con.begin() as cnx:
|
|
cnx.execute(text(v_perso))
|
|
cnx.execute(grant)
|