write json into a table

[herobpv]

I need to write a subset of data into a table using pymssql library. I am able to prepare the data I want but stuck on how to write this data into a table.

import json

data = {
  "demographic": [
    {
      "id": 1,
      "country": {
        "code": "AU",
        "name": "Australia"
      },
      "state": {
        "name": "New South Wales"
      },
      "location": {
        "time_zone": {
          "name": "(UTC+10:00) Canberra, Melbourne, Sydney",
          "standard_name": "AUS Eastern Standard Time",
          "symbol": "AUS Eastern Standard Time"
        }
      },
      "address_info": {
        "address_1": "",
        "address_2": "",
        "city": "",
        "zip_code": ""
      }
    },
    {
      "id": 2,
      "country": {
        "code": "AU",
        "name": "Australia"
      },
      "state": {
        "name": "New South Wales"
      },
      "location": {
        "time_zone": {
          "name": "(UTC+10:00) Canberra, Melbourne, Sydney",
          "standard_name": "AUS Eastern Standard Time",
          "symbol": "AUS Eastern Standard Time"
        }
      },
      "address_info": {
        "address_1": "",
        "address_2": "",
        "city": "",
        "zip_code": ""
      }
    },
    {
      "id": 3,
      "country": {
        "code": "US",
        "name": "United States"
      },
      "state": {
        "name": "Illinois"
      },
      "location": {
        "time_zone": {
          "name": "(UTC-06:00) Central Time (US & Canada)",
          "standard_name": "Central Standard Time",
          "symbol": "Central Standard Time"
        }
      },
      "address_info": {
        "address_1": "",
        "address_2": "",
        "city": "",
        "zip_code": "60611"
      }
    }
  ]
}

I need to write this result to a table which has id, country_name, zip

result = [(d["id"], d["country"]["name"], d["address_info"]["zip_code"]) for d in data["demographic"] if d["country"]["code"] == "US"]

using pymssql library in python

buran write Jan-20-2023, 09:31 AM:
Please, use proper tags when post code, traceback, output, etc. This time I have added tags for you.
See BBcode help for more info.

[Larz60+]

It is more standard to use pandas, and from experence I have found this method to be quite fast as well.
I have an example in a tutorial that I wrote here.
Search for: Part 8 '-Create and Load Database. LoadDatabase.py'

[herobpv]

It is more standard to use pandas, and from experence I have found this method to be quite fast as well.
I have an example in a tutorial that I wrote here.
Search for: Part 8 '-Create and Load Database. LoadDatabase.py'

Hi Larz60+ Thank you for your reply. I need to use pymssql library.

The tuple result has below data

[('1', 'USA', '12345'), 
 ('2', '', '23456'), 
 ('3', 'USA', '')]

Code

cursor = conn.cursor()
cmd = """INSERT INTO tbl (id, country_name, zip) VALUES (?, ?, ?)"""
for record in result:
    cursor.execute(cmd, record)
conn.commit()

Error

Error:
Traceback (most recent call last):
  File "src\pymssql\_mssql.pyx", line 2075, in pymssql._mssql._substitute_params
IndexError: tuple index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "C:/test.py", line 10, in <module>
    cur.executemany(sql, center)
  File "src\pymssql\_pymssql.pyx", line 487, in pymssql._pymssql.Cursor.executemany
  File "src\pymssql\_pymssql.pyx", line 461, in pymssql._pymssql.Cursor.execute
  File "src\pymssql\_mssql.pyx", line 1087, in pymssql._mssql.MSSQLConnection.execute_query
  File "src\pymssql\_mssql.pyx", line 1118, in pymssql._mssql.MSSQLConnection.execute_query
  File "src\pymssql\_mssql.pyx", line 1235, in pymssql._mssql.MSSQLConnection.format_and_run_query
  File "src\pymssql\_mssql.pyx", line 1257, in pymssql._mssql.MSSQLConnection.format_sql_command
  File "src\pymssql\_mssql.pyx", line 2077, in pymssql._mssql._substitute_params
ValueError: more placeholders in sql than params available

But, I see the number of elements are same and somehow run into this error.

[snippsat]

Try loop over the list then insert the the values.

cursor = conn.cursor()
for item in lst:
    cursor.execute("INSERT INTO tbl (id, country_name, zip) VALUES (?, ?, ?)", item)
conn.commit()
cursor.close()
conn.close()

[herobpv]

Try loop over the list then insert the the values.

cursor = conn.cursor()
for item in lst:
    cursor.execute("INSERT INTO tbl (id, country_name, zip) VALUES (?, ?, ?)", item)
conn.commit()
cursor.close()
conn.close()

Hi snippsat, Works now. Thanks very much.