Parse JSON data from URL to MySQL

Question

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
}

Rob Streeting · Accepted Answer · 2019-09-16 16:06:16Z

1

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"]))

edited Sep 16, 2019 at 16:06

answered Sep 16, 2019 at 11:02

Rob Streeting

1,7253 gold badges18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Arron Over a year ago

Hi Rob, Can you show me what this would actually look like in the code as I'm now getting json_obj is not defined.

Rob Streeting Over a year ago

Hey, I've edited my original answer to add a code example.

Arron Over a year ago

Perfect thanks! If I have more thank once instance of stripe_token how do Ioop through them all. Currently it will just duplicate the first json object many times into a new row.

Rob Streeting Over a year ago

If you have multiple stripe_tokens, how do you envisage the structure of the JSON looking? Currently the JSON you've given will only allow for one stripe_token value.

Arron Over a year ago

Thanks Rob, I'm trying that and getting TypeError: 'int' object is not subscriptable

|

buran · Accepted Answer · 2019-09-16 11:05:41Z

0

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.

answered Sep 16, 2019 at 11:05

buran

14.4k12 gold badges45 silver badges76 bronze badges

2 Comments

Arron Over a year ago

Thanks, but I just get 'json_obj = json.loads(r) NameError: name 'json' is not defined'

buran Over a year ago

why do you need to json.loads(r)? You had json_obj = r.json() in your code - that is convince method of the requests.Response object....

Collectives™ on Stack Overflow

Parse JSON data from URL to MySQL

2 Answers 2

10 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related