Блог

Как заменить . с помощью _ в моем файле json и отправьте результирующий файл json в таблицу DB.

Пробовал приведенный ниже код, но не работает:

 import pandas as pd
import json
import sys
import os
import psycopg2
import glob
import traceback
import csv

user= sys.argv[2]
password = sys.argv[3]
host = sys.argv[4]
port = sys.argv[5]
db = sys.argv[6]
documenttype = sys.argv[7]
schema_name =  sys.argv[8]

from sqlalchemy import create_engine

engine = create_engine('postgresql psycopg2://' user ':' password '@' host ':' port '/' db)
print("Database is connected")

data = sys.argv[1]
output_path = sys.argv[9]
for fileName in os.listdir(data):
    file = open(data fileName, "r",encoding='utf-8')
    contents = file.read()
    if '.' in contents:        
        with open(output_path fileName,'w',encoding='utf-8') as output:
            rmSplc=contents.replace('.','_')
            output.write(rmSplc)
            print(". replaced with _ in file")


df = pd.read_json(rmSplc)

df['RecordsNew'] = df['Records'].astype('|S80')

df_1 = pd.json_normalize(df['Records'])
df_1.columns = map(str.lower, df_1.columns)
table_name =documenttype.lower()

             
df_1.to_sql(table_name,schema=schema_name,con=engine, if_exists = 'append',index=False)
  

Но получаю ошибку ниже:

 Database is connected
Traceback (most recent call last):
  File "/xdata/dev/ariba/python/JSON_PYTHON_RESPONSE.py", line 36, in <module>
    for fileName in os.listdir(data):
OSError: [Errno 36] File name too long: '{"Records":[{"ExceptionType":{"ExceptionTypeId":"UnderTaxVariance-SAP"},"InvoiceLineNumber":2,"ExceptionStatus":"Unreconciled","InvoiceSubmissionMethod":"PaperInvoice","AccountingDate":{"Day":"2020-02-19T00:00:00Z"},"OrderID":null,"InvoiceId":"IRC2347-R337-154","InvoiceWithExceptionCount":1,"Requester":{"SourceSystem":"SSP-sap-CHILD1","UserId":"","PasswordAdapter":""},"Supplier":{"SourceSystem":"SSP-sap-CHILD1","SupplierId":"sid111","SupplierLocationId":"sid111_loc1"},"InvoiceNumber":"C2347-R337","InvoiceDate":{"Day":"2020-02-20T00:00:00Z"},"IRStatus":"Reconciling","POId":null,"ReconciledBy":{"SourceSystem":"SSP-sap-CHILD1","UserId":"","PasswordAdapter":""},"LoadUpdateTime":"2020-02-19T19:27:38Z","ProcurementUnit":{"SourceSystem":"","UniqueName":""},"LoadCreateTime":"2020-02-19T19:27:38Z","InvoiceSourceDocument":"NoOrderInformation","POLineNumber":null,"SourceSystem":{"SourceSystemId":"SSP-sap-CHILD1"}}],"PageToken":"TlhXQ0FXVDJHMUV3Qg"}'
InvoiceExceptionsFactis created
ProjectFactSystemView
ProjectFact
  

Пример Json:

 {"Records":[{"CommodityId":"3470","SourceSystem":"SSP-generic-CHILD4","CommodityName":"ANCHOR BOLTS - BILL OF MATERIALS","CommodityType":""},
{"CommodityId":"468657","SourceSystem":"SSP-generic-CHILD4","CommodityName":"COOLING INSERT","CommodityType":""},
{"CommodityId":"836519","SourceSystem":"SSP-generic-CHILD4","CommodityName":"DIAPHRAGM 2ND STAGE PGT25","CommodityType":""},
{"CommodityId":"807525","SourceSystem":"SSP-generic-CHILD4","CommodityName":"MOBILE NOZZLE MACHINING 2ST MS5002C(S2N)","CommodityType":""},
{"CommodityId":"DEFAULT","SourceSystem":"SSP-sap-BH100","CommodityName":"DEFAULT ERP Commodity Code","CommodityType":""},
{"CommodityId":"0074","SourceSystem":"SSP-sap-BH100","CommodityName":"Parts for equip,mach","CommodityType":""},
{"CommodityId":"B14","SourceSystem":"SSP-sap-BH100","CommodityName":"Glycols amp; Glycol Eth","CommodityType":""},
{"CommodityId":"CSU","SourceSystem":"SSP-sap-BH100","CommodityName":"SPRINGS","CommodityType":""}],"PageToken":"TlhXQ0FVcTlzNE8rQQ"}
  

Если я удалю фрагмент кода для замены. с _ он работает нормально.
Данные считываются, таблица создается, и данные также передаются в таблицу.
Но хотите заменить . в _, а затем запишите его в таблицу.
Предложения по решению этой проблемы окажут большую помощь.

если замена — единственная проблема в вашем коде, попробуйте это:

 # -*- coding: utf-8 -*-
import os
input_path='E:/CD034591Q_001log5.json'
output_path='E:/CD034591Q_001log5.json' 

file = open(input_path, "r",encoding='utf-8')
contents = file.read()
if '' in contents:        
    with open(output_path,'w',encoding='utf-8') as output:
        rmSplc=contents.replace('','')
        output.write(rmSplc)