# -*- coding: utf-8 -*-
"""
Created on Fri Mar 29 06:01:51 2019

@author: cpace
"""
import csv
import psycopg2
from config import config
 
def connect():
    """ Connect to the PostgreSQL database server """
    conn = None
    try:
        # read connection parameters
        params = config()
 
        # connect to the PostgreSQL server
        #print('Connecting to the PostgreSQL database...')
        conn = psycopg2.connect(**params)
 
        # create a cursor
        cur = conn.cursor()
        
 # execute a statement
        count = 0
        with open(file) as f:
            reader = csv.reader(f)
            for string in reader:
                if len(string)>1:
                    if count > 0:
                        for m in range(len(string)):
                            if string[m]=='':
                                string[m]=0
                        sql = 'update "Soil Sampling" set \
                                om = %s, cec = %s, ph = %s, bph = %s, p = %s, k = %s, bs_k = %s, bs_mg = %s, bs_ca = %s, \
                                bs_h = %s, rec_lime = %s, rec_p2o5 = %s, rec_k2o = %s, rec_mg = %s where gid = %s;'
                        
                        
#                        sql = 'update "Soil Sampling" set \
#                                om = ' + string[9] + ', cec = ' + string[10] + ', ph = ' + string[11] + ', \
#                                bph = ' + string[12] + ', p = ' + string[15] + ', k = ' + string[18] + ', \
#                                bs_k = ' + string[47] + ', bs_mg = ' + string[48] + ', bs_ca = ' + string[49] + ', \
#                                bs_h = ' + string[51] + ', rec_lime = ' + string[60] + ', rec_p2o5 = ' + string[62] + ', \
#                                rec_k2o = ' + string[63] + ', rec_mg = ' + string[65] + ' where gid = ' + string[5] + ';'
#                        print(sql)
                        cur.execute(sql, (string[9],string[10],string[11],string[12], \
                                          string[15],string[18],string[47],string[48], \
                                          string[49],string[51],string[60],string[62], \
                                          string[63],string[65],string[5]))
                        conn.commit()
                    count += 1
                    
 
  
     # close the communication with the PostgreSQL
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print('Error: ' + str(error))
    finally:
        if conn is not None:
            conn.close()
            print('Database connection closed.')
 


path = 'C:/Users/corey/OneDrive/Pace Family Farms - Shared/Soil Sample Reports'
filename = 8
filename = str(filename) + '.csv'
print(filename)
file = path + '/' + filename

connect()