77 lines
2.0 KiB
Python
77 lines
2.0 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-
|
|
|
|
from sqlalchemy import dialects
|
|
from pycen import con_gn
|
|
import pandas as pd
|
|
import json
|
|
import ast
|
|
import os
|
|
|
|
DIR = '/home/colas/Documents/9_PROJETS/6_GEONATURE'
|
|
FILE = 'IMPORTS/OBSERVATEURS/Table Observateurs Platière.csv'
|
|
|
|
|
|
def to_organismes(df):
|
|
sch = 'utilisateurs'
|
|
tab = 'bib_organismes'
|
|
org = pd.read_sql_table(tab,con_gn,sch)
|
|
df = df[~df.nom_organisme.isin(org.nom_organisme)]
|
|
df.to_sql(
|
|
tab,con_gn,sch,
|
|
if_exists='append',
|
|
index=False)
|
|
|
|
|
|
def get_organismes(LIST):
|
|
sch = 'utilisateurs'
|
|
tab = 'bib_organismes'
|
|
formlist = LIST.replace({"'":"''"},regex=True)
|
|
formlist = str(tuple(formlist)).replace('"',"'")
|
|
sql = '''
|
|
SELECT id_organisme,nom_organisme FROM {sch}.{tab}
|
|
WHERE nom_organisme IN {n_org}
|
|
;'''.format(sch=sch,tab=tab,n_org=formlist)
|
|
|
|
return pd.read_sql_query(sql, con_gn)
|
|
|
|
|
|
def to_roles(df):
|
|
sch = 'utilisateurs'
|
|
tab = 't_roles'
|
|
dtypes = {}
|
|
if 'champs_addi' in df.columns:
|
|
dtypes = {**dtypes,'champs_addi':dialects.postgresql.JSONB}
|
|
|
|
df.to_sql(
|
|
tab,con_gn,sch,
|
|
if_exists='append',
|
|
index=False,
|
|
dtype=dtypes)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
|
|
df = pd.read_csv(os.path.join(DIR,FILE))
|
|
df = df[df.rqe!='à ne pas importer']
|
|
obs = df[['organisme_long']]\
|
|
.drop_duplicates()\
|
|
.rename(columns={'organisme_long':'nom_organisme'})
|
|
to_organismes(obs)
|
|
|
|
org = get_organismes(obs.nom_organisme)
|
|
dic_org = dict(zip(org.nom_organisme,org.id_organisme))
|
|
df.organisme_long.replace(dic_org,inplace=True)
|
|
|
|
role = df[['nom','prenom','organisme_long','statut','poste']]\
|
|
.rename(columns={
|
|
'nom':'nom_role',
|
|
'prenom':'prenom_role',
|
|
'organisme_long':'id_organisme',
|
|
})
|
|
role['champs_addi'] = json.loads(
|
|
role[['statut','poste']].to_json(orient="records",force_ascii=False)
|
|
)
|
|
role.drop(columns=['statut','poste'],inplace=True)
|
|
to_roles(role)
|