Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Read a remote text file
#1
Hi All,

I have a scenario where i need to read a TEXT file(log.txt) in a remote server(Server 1) and write the read data to a database of another server(Server 2).

I'm using the below code ,But do not know what modification need to do for accessing and reading a remote file and write it to the PostgreSQL database in the destination server.In my local the query is working fine.

Any leads will be of great help.

import psycopg2
import time
import os
import MySQLdb
from utils.config import Configuration as Config
from utils.postgres_helper import get_connection
from utils.utils import get_global_config

start_time = time.perf_counter()

#Magento connection 
try:
    source_host = 'mag'
    conf = get_global_config()
    cnx_msql = MySQLdb.connect(host=conf.get(source_host, 'host'),
                               user=conf.get(source_host, 'user'),
                               passwd=conf.get(source_host, 'password'),
                               port=int(conf.get(source_host, 'port')),
                               db=conf.get(source_host, 'db'))
    print ("Source Connected")
except mysql.connector.Error as e:
   print ("MYSQL: Unable to connect!", e.msg)
   sys.exit(1)

# Postgresql connection
 try:
   cnx_psql = get_connection(get_global_config(), 'pg_dwh')
   print ("DWH Connected")
 except psycopg2.Error as e:
   print('PSQL: Unable to connect!\n{0}').format(e)
   sys.exit(1)


# Cursors initializations
 cur_msql = cnx_msql.cursor()
 cur_psql = cnx_psql.cursor()

except (Exception, psycopg2.Error) as error:
        # Confirm unsuccessful connection and stop program execution.
        print ("Error while fetching data from PostgreSQL", error)
        print("Database connection unsuccessful.")
        quit()        
try:

    #filePath='''/Users/linu/Downloads/log''' 
    filePath='''/deft/log/final/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()

    with open(filePath, 'r') as file:
         for line in file:
           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()
Reply
#2
How do you communicate with the remote machine to get access to the data? HTTP? (S)FTP? Something else?

Also, you shouldn't be building SQL statements by concatenating strings, as that's vulnerable to SQL injection.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Recommended way to read/create PDF file? Winfried 3 2,782 Nov-26-2023, 07:51 AM
Last Post: Pedroski55
  python Read each xlsx file and write it into csv with pipe delimiter mg24 4 1,304 Nov-09-2023, 10:56 AM
Last Post: mg24
  read file txt on my pc to telegram bot api Tupa 0 1,047 Jul-06-2023, 01:52 AM
Last Post: Tupa
  parse/read from file seperated by dots giovanne 5 1,043 Jun-26-2023, 12:26 PM
Last Post: DeaD_EyE
  Formatting a date time string read from a csv file DosAtPython 5 1,160 Jun-19-2023, 02:12 PM
Last Post: DosAtPython
  How do I read and write a binary file in Python? blackears 6 6,003 Jun-06-2023, 06:37 PM
Last Post: rajeshgk
  Read csv file with inconsistent delimiter gracenz 2 1,140 Mar-27-2023, 08:59 PM
Last Post: deanhystad
  Read text file, modify it then write back Pavel_47 5 1,499 Feb-18-2023, 02:49 PM
Last Post: deanhystad
  Correctly read a malformed CSV file data klllmmm 2 1,813 Jan-25-2023, 04:12 PM
Last Post: klllmmm
  How to read csv file update matplotlib column chart regularly SamLiu 2 1,014 Jan-21-2023, 11:33 PM
Last Post: SamLiu

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020