Jul-17-2019, 07:16 AM
(This post was last modified: Jul-17-2019, 07:16 AM by Sandy7771989.)
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.
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()