Importing data into PostgreSQL from remote text file using ftplib class I need to read a remote file using FTP and process it and insert the processed data to a PostgreSQL database in another server.

The below code is working fine, but i want some advice to modify few things.


In this line i don't want to write the actual details, I want that to
be hidden from my script. How can i do that?


ftp = FTP('server.com','user_name','password')


The script downloads the file to my local system and process it as
it is a local file. Is there any way to read and process it without
downloading it to the source server (there might be permission issues
also)?
If there is no way rather than downloading it to the source, should I
specify a path where this file would be downloaded
rather than the way I have written it?
Is there anything which i can do to improve this code?


Any guidance will be of great help.

import psycopg2
import time
import os
#import MySQLdb
#import paramiko
from ftplib import FTP
#from utils.config import Configuration as Config
#from utils.utils import get_global_config

start_time = time.perf_counter()

cnx_psql = psycopg2.connect(host="localhost", database="postgres", user="postgres",
                        password="postgres", port="5432")

# Cursors initializations
cur_psql = cnx_psql.cursor()

def getFile(ftp, filename):
    try:
        local_filename = os.path.join(r"/Users/linu/Downloads/", filename)
        lf = open(local_filename, "wb")
        ftp.retrbinary("RETR " + filename ,lf.write)
        print("file copied")
    except:
        print ("Error")
try:

    filePath='''/Users/linu/Downloads/log''' 
    #filePath='''/cmd/log/stk/log.txt''' 
    table='staging.stock_dump'

    SQL="""DROP TABLE IF EXISTS """+  table + """;CREATE TABLE IF NOT EXISTS """+ table + """
    (created_date TEXT, product_sku TEXT, previous_stock TEXT, current_stock TEXT );"""

    cur_psql.execute(SQL)
    cnx_psql.commit()


    ftp = FTP('server.com','user_name','password')
    print("FTP connection succesful")
    data = []
    ftp.cwd('/stockitem/')
    getFile(ftp,'log')

    read_file = open(filePath, "r")
    my_file_data = read_file.readlines()

    for line in my_file_data:
        if 'Stock:' in line:
            fields=line.split(" ")
            date_part1=fields[0]
            date_part2=fields[1][:-1]
            sku=fields[3]
            prev_stock=fields[5]
            current_stock=fields[7]
            if prev_stock.strip()==current_stock.strip():
                continue
            else:
               cur_psql.execute("insert into " + table+"(created_date, product_sku, previous_stock , current_stock)" + " select CAST('" + date_part1+ " "+ date_part2 + "' AS TEXT)" +", CAST('"+sku+"' AS TEXT),CAST('" + prev_stock +"' AS TEXT),CAST('" +current_stock  + "' AS TEXT);")
    cnx_psql.commit()       
    cur_psql.close()
    cnx_psql.close()
    print("Data loaded to DWH from text file")
    print("Data porting took %s seconds to finish---" % (time.perf_counter() - start_time))          
except (Exception, psycopg2.Error) as error:
        print ("Error while fetching data from PostgreSQL", error)
        print("Error adding  information.")
        quit()
finally:
    ftp.close()
In this line i don't want to write the actual details,i want that to be hidden from my script.How can i do that? ftp = FTP('server.com','user_name','password')
  


You may want to declare constants, either at the beginning of your file or in a Credentials class that would be used as an enum:

SERVER = 'server.com'
USER_NAME = 'Linu'
PASSWORD = 'CR.SEisGreat'

ftp = FTP(SERVER, USER_NAME, PASSWORD)


You may also want to encrypt your password, with base64 or something alike. Storing passwords in files isn't a good idea anyway, so if you're using this file as a script you're going to call via bash or something alike, I would use a placeholder like argparse that would prompt your password when calling the script: 

parser = argparse.ArgumentParser(description='Reading a file from a distant FTP server.')
parser.add_argument('--passwd', dest='password', type=str,
                   help='password required for FTP connection')



  
  The script downloads the file to my local system and process it as it
  is a local file, Is there anyway too read and process it without
  downloading it to the source server(there might be permission issues
  also)?
  


I don't know much about FTP manipulation with Python so I'm not sure there is a way to read a file without having to download it.


  
  If there is no way rather than downloading it to the source, shall I
  able to mention specific path where this file would be downloaded
  rather than the way i have written?
  


Again, you may place the path into a class constant (or in a separate class):

FILE_PATH='''/path/to/file/''' 



  
  Is there anything which i can do to improve this code?
  


Imports

import psycopg2
import time
import os


I would edit this to import only the parts you need, e.g. 

from psycopg2 import connect, sql 
from time import perf_counter


There is no need to import the whole module if you only use a couple of functions, so whenever you can, I'd suggest you only import what you will actually use. 



Raising exceptions

try:
    # ...
except:
    print ("Error")


I would raise an exception here, since it's a undesired behaviour:

 try:
    # ...
except Exception as e:
    print ("Error: {}".format(e))


You may want to do the same with your psycopg and FTP errors, I see you catch them but don't read their value. It may provide a more helpful message than the prints, which just indicate you encountered an error but don't tell more about what actually happened.

SQL placeholders

SQL="""DROP TABLE IF EXISTS """+  table + """;CREATE TABLE IF NOT EXISTS """+ table + """
(created_date TEXT, product_sku TEXT, previous_stock TEXT, current_stock TEXT );"""


Should you want to make your code more generic, you could transform the above mentioned statement into:

sql.SQL="""DROP TABLE IF EXISTS {table_name} ; CREATE TABLE IF NOT EXISTS {table_name}
 (created_date TEXT, product_sku TEXT, previous_stock TEXT, 
 current_stock TEXT );""".format(
    table_name = sql.Literal(table)
)


That way you may reuse the query on other tables. You may do the same on the table attributes, i.e. 

keys = ['created_date', 'product_sku', 'previous_stock', 'current_stock']
sql.SQL("""CREATE TABLE IF NOT EXISTS {} ({});""").format(
    sql.Identifier(table),
    sql.SQL('{} TEXT,').join(map(sql.Identifier, keys))
)


It alleviates the query and makes it more generic and reusable. By the way, I don't think you need to add IF NOT EXISTS after CREATE since you already deleted the table in the first part of your query.