使用python读取远程文本文件

Question

我有一个场景，我需要读取远程服务器中的文本文件，并将读取的数据写入另一台服务器。

我使用下面的代码，但不知道访问和读取远程文件需要做什么修改，并将其写入目标server.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。如果您正在处理大量数据，那么可以使用大容量加载选项。