使用python读取远程文本文件

Question

我有一个场景，需要在远程服务器中读取文本文件，然后将读取的数据写入另一个服务器。你知道吗

我使用下面的代码，但不知道需要做什么修改来访问和读取远程文件并将其写入目标服务器.In我的本地计算机查询工作正常。你知道吗

我的源服务器是MySQL，目标服务器是PostgreSQL。你知道吗

任何线索都会有很大帮助。你知道吗

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()

Answer 1

所以您正在尝试从Mysql（Magento）服务器读取数据并将其写入Postgresql服务器，是吗？您需要对Mysql服务器运行SQL查询，对返回数据的模式和类型执行任何所需的转换，然后对Postgresql执行每个记录的插入，而不是读取文本文件。如果您正在处理大量的数据，那么就有批量加载选项可供探索。你知道吗