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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

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

1	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 2 3

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

Code

1 2 3 4 5

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.

1 2 3 4 5 6

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.

1 2 3 4 5 6

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.