#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Tue Dec 17 11:17:15 2019

@author: rousseau
"""
##Importation des librairies

from sqlalchemy.sql import text
from sqlalchemy import create_engine
import configparser
import os
import pandas as pd

#Mise en place des coordonnées de la base

config = configparser.ConfigParser()
config.read_file(open(os.path.expanduser("~/datalab.cnf")))

TBL = "automate"
CNF = "pgBDD"

##Configuration de connexion avec PostgreSQL

pgSQLengine = create_engine("postgresql://%s:%s@%s/%s" % (config[CNF]['user'], config[CNF]['password'], config[CNF]['host'], "BDD_Franck"))
print(pgSQLengine)

##Création d'une dataframe à partir d'un fichier CSV

URL = '/home/rousseau/Projet5/CSV/automate.csv'

df_collect= pd.DataFrame()
df_collect = pd.DataFrame(columns=['autoid', 'auto_site','auto_adresse','auto_postal','auto_loc','auto_insee','auto_type','auto_lat','auto_long'])
df_collect = pd.read_csv(URL, encoding = 'utf8', sep=',')


###Verifications 1. Traitée les doublons

duplicateRowsDF = df_collect[df_collect.duplicated(['autoid', 'auto_site','auto_adresse','auto_postal','auto_loc','auto_insee','auto_type','auto_lat','auto_long'])]
len(duplicateRowsDF.index)

## Drop duplicate for UNIQUE

#df_collect.drop_duplicates(subset='id_auto', keep='first', inplace=True) 


##Création des tables et implémentations dans PostgreSQL

pgSQLengine.execute("""CREATE TABLE IF NOT EXISTS "automate"(
    id_auto INTEGER GENERATED BY DEFAULT AS IDENTITY,    
    autoid integer NOT NULL,
    auto_site character(50) NOT NULL,
    auto_adresse character(50) NULL,
    auto_postal integer NOT NULL,
    auto_loc character(50) NOT NULL,
    auto_insee character(6) NOT NULL,
    auto_type  character(50) NOT NULL,
    auto_dab character(10) NULL,
    auto_lat decimal NOT NULL,
    auto_long decimal NOT NULL
);""")

statement = text("""
INSERT INTO "automate" (id_auto,autoid,auto_site,auto_adresse,auto_postal,auto_loc,auto_insee,auto_type,auto_lat,auto_long)
VALUES (:id_auto,:autoid,:auto_site,:auto_adresse,:auto_postal,:auto_loc,:auto_insee,:auto_type,:auto_lat,:auto_long)""")

df_collect.to_sql('automate', pgSQLengine, if_exists='append', index=True)






