81 lines
2.1 KiB
Python
81 lines
2.1 KiB
Python
#!/usr/bin/env python3
|
|
# -*- coding: UTF-8 -*-.
|
|
|
|
from sqlalchemy import create_engine #, text
|
|
from sqlalchemy.engine import URL
|
|
from sqlalchemy.types import Date,Time,String
|
|
from shapely.geometry import Point
|
|
import pandas as pd
|
|
import datetime
|
|
|
|
file = '/home/colas/Documents/9_PROJETS/4_SICEN/IMPORT/Tableau Saisie_FauneFlore_AG_30_03_22.xlsx'
|
|
# file = '/home/colas/Documents/9_PROJETS/4_SICEN/LPO/cdnom_idvisionature.csv'
|
|
to_table = 'import_data_cen38'
|
|
# to_table = 'taxons_fauneisere_biolovision_taxref'
|
|
|
|
# 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)
|
|
|
|
df = pd.read_excel(
|
|
file,
|
|
skiprows=[1,2,3,4]
|
|
)
|
|
if 'nom de champ' in df.columns:
|
|
del df['nom de champ']
|
|
# df = pd.read_csv(file,sep=',')
|
|
df.columns = df.columns.str.strip().str.lower()
|
|
df.dropna(subset=['date_obs'],inplace=True)
|
|
df.drop_duplicates(inplace=True)
|
|
|
|
|
|
date_default = datetime.datetime(1899, 12, 30, 0, 0)
|
|
# data_import.loc[data_import['time_start'] == date_default,'time_start'] = datetime.time(0,0)
|
|
# data_import.loc[data_import['time_stop'] == date_default,'time_stop'] = datetime.time(0,0)
|
|
# data_import.loc[data_import['time_start'] == '1899-12-30 00:00:00','time_start'] = '00:00:00'
|
|
# data_import.loc[data_import['time_stop'] == '1899-12-30 00:00:00','time_stop'] = '00:00:00'
|
|
# df.loc[df['horaire'] == date_default,'horaire'] = datetime.time(0,0)
|
|
df.date_obs = df.date_obs.dt.date
|
|
|
|
if to_table == 'import_data_cen38':
|
|
dtype = {
|
|
'date_obs':Date,
|
|
'date_debut_obs':Date,
|
|
'date_fin_obs':Date,
|
|
'heure':Time,
|
|
'strate':String,
|
|
'phenologie':String,
|
|
'cd_nom':String,
|
|
'sexe':String,
|
|
'precision':String}
|
|
else : dtype = None
|
|
|
|
|
|
df.to_sql(
|
|
name=to_table,
|
|
con = con,
|
|
schema='import',
|
|
index=False,
|
|
if_exists='replace',
|
|
method='multi',
|
|
dtype=dtype
|
|
)
|
|
|
|
sql_grant = """
|
|
GRANT ALL ON TABLE import.%s TO grp_admin
|
|
""" % to_table
|
|
|
|
with con.begin() as cnx:
|
|
cnx.execute(sql_grant)
|