如何修复python中字符串与变量串联时的换行

Question

我正试图用python生成一条SQL语句。请检查以下脚本：

import re
json_file_object = open("sample_json_paths.txt", "r")
sql = list()
for sample_text in json_file_object:
# sample_text = "$.testABM.test.test.test.test.test.testReference"
sql.append("SELECTn")
sql.append("            CONVERT(NVARCHAR(32), HashBytes('MD5', concat(rt.id,'_', @now)), 2) AS docidn")

#Append parentnodeid row
remove_dollar_sign = sample_text.replace("$.","")
json_string_list = remove_dollar_sign.split(".")
nodeid = json_string_list.pop(-1)
parent_node_id = ".".join(json_string_list)
sql.append("            ,'" + parent_node_id + "' " + "AS parentnodeidn")
#Append nodeid row
sql.append("            ,'" + nodeid + "' " + "AS nodeidn")
sql.append("            ,testABM_layer.RequestHeader AS RequestHeadern")
sql.append("            ,final_layer.[key] AS final_layer_keyn")
sql.append("            ,final_layer.[value] AS Ofinal_layer_valuen")
sql.append("FROM        dbo.test_dataset_backup rt")
sql.append("""
OUTER APPLY OPENJSON ( rt.test) AS layer_root
OUTER APPLY OPENJSON ( layer_root.value ) 
WITH    (
[RequestHeader] NVARCHAR(MAX) AS json,
[test] NVARCHAR(MAX) AS json
) AS testABM_layern""")
#append OUTER APPLY with json sub path
remove_leading_json = sample_text.replace(".testABM.test","")
json_string_list = remove_leading_json.split(".")
json_string_list.pop(-1)
json_sub_path = ".".join(json_string_list)
sql.append("            OUTER APPLY OPENJSON ( testABM_layer.test, " + "'" + json_sub_path + "'" + ") AS final_layern")
sql.append("            WHERE final_layer.[key] = '" + nodeid + "'n")
sql.append("UNION ALLn")
# print(json_sub_path)
sql_output = "".join(sql)
f = open("sql_statements.txt", "a")
f.write(sql_output)
f.close()
print(sql_output)

由于敏感信息导致的Delete和由于敏感信息而导致的Delete处出现换行-在UNION ALL之前问题不会出现在最后一个循环中。您也可以检查输入文件中的示例文本，如下所示：

SELECT
CONVERT(NVARCHAR(32), HashBytes('MD5', concat(rt.id,'_', @now)), 2) AS docid
,'testABM.test.test.test' AS parentnodeid
,'testReference
' AS nodeid
,testABM_layer.RequestHeader AS RequestHeader
,final_layer.[key] AS final_layer_key
,final_layer.[value] AS Ofinal_layer_value
FROM        dbo.test_dataset_backup rt
OUTER APPLY OPENJSON ( rt.test) AS layer_root
OUTER APPLY OPENJSON ( layer_root.value ) 
WITH    (
[RequestHeader] NVARCHAR(MAX) AS json,
[test] NVARCHAR(MAX) AS json
) AS testABM_layer
OUTER APPLY OPENJSON ( testABM_layer.test, '$.test.test') AS final_layer
WHERE final_layer.[key] = 'testReference
'
UNION ALL

如何解决此问题？

感谢

Answer 1

在文件内容上迭代时，sample_text在末尾包含换行符，例如'$.SyncCustomerRequestABM.SyncCustomerRequest.Customers.Customer.OriginalSystemReferencen'

问题是nodeid通过拆分行并获取拆分的最后一个元素。

您可以通过在每次迭代开始时剥离sample_text来解决问题：

sample_text = sample_text.strip()

它之所以适用于最后一行，是因为它在文件中不包含换行符。

---

这将帮助您使用现有的代码，但我也强烈建议您寻找更好的方法来生成这些字符串：

• 数据库库允许将参数传递给SQL查询，例如psycopghttps://www.psycopg.org/docs/usage.html#passing-sql查询的参数
• 使用f-string避免样板代码https://realpython.com/python-f-strings/