CODEWITHSUNDEEP
pythonjson
Arron
Arron

Reputation: 25

Parse JSON data from URL to MySQL

I have this code sending JSON data from a URL to a MySQL database however I get the error 'String indices must be integers'

import urllib.parse
import requests
import pymysql

mydb = pymysql.connect(host='127.0.0.1', user='test', passwd='test', db='test', local_infile=1)

r = requests.get('https://example.com/apidata')

cursor = mydb.cursor()

json_obj = r.json()

for ord in json_obj["0"]:
    print("stripe_token", ord["stripe_token"])

cursor.execute("INSERT INTO apidata (stripe_token) VALUES (%s)", (ord["stripe_token"]))

#close the connection to the database.
mydb.commit()
cursor.close()

My Json data looks like this

{
"0": {
"need_session_refresh": "1",
"signup_email_sent": "1",
"stripe_cc_expires": "0000",
"stripe_cc_masked": "123456",
"stripe_token": "1x734g834",

What am I doing wrong?

Thanks :)

Edit - I'd like to also parse this recursively e.g

{
"0": {
"stripe_token": "756474745",
"need_session_refresh": "1",
},
"1": {
"nstripe_token": "34563456",
"need_session_refresh": "1",
},
"_total": 43054
}

Upvotes: 1

Views: 961

Answers (2)

Rob Streeting
Rob Streeting

Reputation: 1735

The issue is that, when you iterate through a dictionary like this:

for ord in json_obj["0"]:

ord actually takes the values of all of the keys in your dictionary, not the key-value pairs. That means that when you do:

ord["stripe_token"]

ord is the key in the dictionary (i.e. a string) and it's complaining you can't use another string to index into a string.

I think the issue is that you are assuming that the structure inside "0" is a list rather than a dictionary. If you do json_obj["0"]["stripe_token"], you should find that you get the value that you want. You can also remove the loop for ord in json_obj["0"]:, since there's not actually a list inside json_obj["0"].

The code would then look like this:

json_obj = r.json()
ord = json_obj["0"]

print("stripe_token", ord["stripe_token"])

cursor.execute("INSERT INTO apidata (stripe_token) VALUES (%s)", (ord["stripe_token"]))

EDIT: as you've added that there are further keys inside json_values that you need to access, you'll need to loop over these keys to do so. The code would look like this:

for index in json_obj:
    if index != "_total":
        cursor.execute("INSERT INTO apidata (stripe_token) VALUES (%s)", (json_obj[index]["stripe_token"]))

Upvotes: 1

buran
buran

Reputation: 14233

for ord in json_obj["0"]:
    print("stripe_token", ord["stripe_token"])

Here ord would iterate over keys of json_obj["0"]. You don't need loop at all

print(f"stripe_token: {json_obj['0']['stripe_token']}")

You would need loop only if your json_obj has more than one element.

I would advise to properly handle the case when json object does not look like what you expect.

Upvotes: 0

Related Questions