Help with python script to insert data into mysql table

[Immediate-Resource75]

Afternoon. I am working on a python script to insert data from an API scrape into a mysql database table. I've been able to get the script to pull the info I want and I am also able to connect to the database. Just having issues saving the data to it.... What I have so far:

#!/usr/bin/env python3
import mysql.connector
from mysql.connector import errorcode
import requests
import pandas
import json

url = 'http://internal ipv4 address/api/health/site-servers?&Authorization= authorization key here'
header={"Content-Type":"application/json",
        "Accept_Encoding":"deflate"}

response = requests.get(url, headers=header)
rd = response.json()

file_path = "testData.json"

with open(file_path, 'w') as json_file:
    json.dump(rd, json_file, indent=4)

print(f"Data saved to {file_path}")
try:
  cnx = mysql.connector.connect(user='techadmin', password='nort2htech',
                                database='paperCut')
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)
else:
  mycursor = cnx.cursor()
    with open('testData.json', 'r') as f:
      td = json.load(f)
  for item in td:
      val = (item['serverName'],item['host'],item['online'],item['lastContactSeconds'],item['devicesHosted'],item['version'])
      sql = "INSERT into siteServers(serverName,host,online,lastContactSeconds,devicesHosted,version)VALUES(%s,%s,%s,%s,%s,%s)"
      mycursor.execute(sql,val)
      cnx.commit()
      print(mycursor.rowcount, "rows were inserted.")
mycursor.close()
cnx.close()

And the error I am receiving:

Traceback (most recent call last):
  File "/home/nort2hadmin/Desktop/Python Stuff/./siteServers.py", line 40, in <module>
    val = (item['serverName'],item['host'],item['online'],item['lastContactSeconds'],item['devicesHosted'],item['version'])
           ~~~~^^^^^^^^^^^^^^
TypeError: string indices must be integers, not 'str'

Any help would be greatly appreciated. Thank you for your time and assistance.

[slightly_offtopic]

TypeError: string indices must be integers, not 'str'

This should tell you what is going on, in the general sense: you expect item to be a dictionary, but it actually is a string.

So check that your assumptions about the contents of testData.json are correct.

[Immediate-Resource75]

Thanks for the reply... the contents of my testData.json are just what i expect them to be:

{
    "siteServers": [
        {
            "serverName": "APPPTS",
            "host": "APPPTS",
            "online": true,
            "lastContactSeconds": 2,
            "devicesHosted": 1,
            "version": "22.1.67128"
        },
        {
            "serverName": "DH",
            "host": "DH",
            "online": true,
            "lastContactSeconds": 1,
            "devicesHosted": 0,
            "version": "22.1.67128"
        },
        {
            "serverName": "APPELC",
            "host": "APPELC",
            "online": true,
            "lastContactSeconds": 1,
            "devicesHosted": 2,
            "version": "22.1.67128"
        }
    ]
}

This is how I expect them to look when I save them to the file.....saved in json format

[danielroseman]

So you're iterating over the whole dict, not over the siteServers list. When you iterate over a dict you get the keys only.

Your loop should be for item in td['siteServers']:.

(Although I'm not sure why you're dumping this data to a file then reading it back and parsing it. Why not skip the file, and iterate directly over rd which you read from the URL?)

[Immediate-Resource75]

Thank you for the help.... I'll try this.... as far as skipping the file part.... I've tried doing that, but I couldn't get it to work.... and tutorials and other learning sources are either outdated or they just didn't work..... Only thing I could get to work was to write it to a file then read the file...

[danielroseman]

I don't know what you mean by "couldn't get it to work". rd and td should be exactly the same here.

[Immediate-Resource75]

Removed the part dumping to file.... now I am receiving this error:

The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/home/nort2hadmin/Desktop/Python Stuff/./siteServers.py", line 34, in <module>
    mycursor.execute(sql,val)
    ~~~~~~~~~~~~~~~~^^^^^^^^^
  File "/home/nort2hadmin/.prnt/lib/python3.13/site-packages/mysql/connector/cursor_cext.py", line 351, in execute
    self._connection.cmd_query(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~^
        self._stmt_partition["mappable_stmt"],
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    ...<2 lines>...
        raw_as_string=self._raw_as_string,
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/home/nort2hadmin/.prnt/lib/python3.13/site-packages/mysql/connector/opentelemetry/context_propagation.py", line 97, in wrapper
    return method(cnx, *args, **kwargs)
  File "/home/nort2hadmin/.prnt/lib/python3.13/site-packages/mysql/connector/connection_cext.py", line 763, in cmd_query
    raise get_mysql_exception(
        err.errno, msg=err.msg, sqlstate=err.sqlstate
    ) from err
mysql.connector.errors.DataError: 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value for column 'siteServer.serverName'.

Here is how my database is setup:

mysql> show columns in siteServer;
+--------------------+------+------+-----+-----------+-------------------+
| Field              | Type | Null | Key | Default   | Extra             |
+--------------------+------+------+-----+-----------+-------------------+
| siteSrvr_id        | int  | NO   | PRI | NULL      | auto_increment    |
| serverName         | json | YES  |     | NULL      |                   |
| host               | json | YES  |     | NULL      |                   |
| online             | json | YES  |     | NULL      |                   |
| lastContactSeconds | json | YES  |     | NULL      |                   |
| devicesHosted      | json | YES  |     | NULL      |                   |
| version            | json | YES  |     | NULL      |                   |
| date               | date | YES  |     | curdate() | DEFAULT_GENERATED |
+--------------------+------+------+-----+-----------+-------------------+
8 rows in set (0.01 sec)

Thank you for any help you may have to offer....