我是 Python 新手。我需要处理 JSON 文件的目录并将数据保存到 SQL Server 表中。

JSON 文件每天都会自动下载并保存到目录中。

我在网站上找到了一个解释某些部分的脚本，但我不确定如何根据我的要求修改它并处理 JSON 文件中的所有数据。

我已复制此消息的 JSON 结构。是否可以指导和帮助我完成此操作，以便我可以在 SQL Server 上运行它？

DROP TABLE IF EXISTS Dim_Countries;

CREATE TABLE Dim_Countries (Country varchar(250))

INSERT INTO Dim_Countries
    EXEC sp_execute_external_script 
            @language = N'Python',@script=N'
import pandas as pd
import json

path = "C:\\Files\\import_data\\"
files = os.listdir(path)

jvalues = set()
for file in files:
   data = json.load(open(path + file,''r'')) 
   for val in data:
      if val["AdClicks"] > 0:
         jvalues.add(val["Country"])

dataset = pd.DataFrame(data=list(jvalues))
Outputdataset=dataset
'

SELECT * FROM Dim_Countries

JSON

{
  "totalRecords": 4,"success": true,"data": [
    {
      "PAYPERIOD": "16 Dec - 15 Jan (2021)","START_DATE": "2020-12-15T22:00:00.000Z","END_DATE": "2021-01-14T22:00:00.000Z","PC1": 5215146,"DESC_PC1": "NRM","PC2": 0,"DESC_PC2": "","PC3": 8971,"DESC_PC3": "OT","PC4": 0,"DESC_PC4": "","PC5": 836225,"DESC_PC5": "NS 1","PC6": 0,"DESC_PC6": "","PC7": 0,"DESC_PC7": "","PC8": 0,"DESC_PC8": ""
    },{
      "PAYPERIOD": "16 Feb - 15 Mar (2021)","START_DATE": "2021-02-15T22:00:00.000Z","END_DATE": "2021-03-14T22:00:00.000Z","PC1": 3428072,"PC3": 5774,"PC5": 741570,{
      "PAYPERIOD": "16 Jan - 15 Feb (2021)","START_DATE": "2021-01-15T22:00:00.000Z","END_DATE": "2021-02-14T22:00:00.000Z","PC1": 4411773,"PC3": 13747,"PC5": 731791,{
      "PAYPERIOD": "16 Mar - 15 Apr (2021)","START_DATE": "2021-03-15T22:00:00.000Z","END_DATE": "2021-04-14T22:00:00.000Z","PC1": 3479095,"PC3": 40606,"PC5": 773000,"DESC_PC8": ""
    }
  ]
}