Importing JSON into mysql using python

Question

Im trying to import this into mysql, I have the json output:

 {u'age': u'27',
  u'foulsCommitted': u'36',
  u'foulsSuffered': u'19',
  u'goalAssists': u'0',
  u'no': u'18',
  u'pla': u'Jaume Costa',
  u'pos': u'D',
  u'redCards': u'1',
  u'shotsOnTarget': u'2',
  u'starts': u'19',
  u'subIns': u'1',
  u'totalGoals': u'0',
  u'totalShots': u'7',
  u'yellowCards': u'7'},
 {u'age': u'28',
  u'foulsCommitted': u'0',
  u'foulsSuffered': u'0',
  u'goalAssists': u'0',
  u'no': u'3',
  u'pla': u'Bojan Jokic',
  u'pos': u'D',
  u'redCards': u'0',
  u'shotsOnTarget': u'0',
  u'starts': u'0',
  u'subIns': u'0',
  u'totalGoals': u'0',
  u'totalShots': u'0',
  u'yellowCards': u'0'},
 {u'age': u'18',
  u'foulsCommitted': u'4',
  u'foulsSuffered': u'2',
  u'goalAssists': u'0',
  u'no': u'28',
  u'pla': u'Adri\xe1n Mar\xedn ',
  u'pos': u'D',
  u'redCards': u'0',
  u'shotsOnTarget': u'0',
  u'starts': u'4',
  u'subIns': u'0',
  u'totalGoals': u'0',
  u'totalShots': u'1',
  u'yellowCards': u'1'},
 {u'age': u'24',
  u'foulsCommitted': u'10',
  u'foulsSuffered': u'10',
  u'goalAssists': u'0',
  u'no': u'5',
  u'pla': u'Mateo Musacchio',
  u'pos': u'D',
  u'redCards': u'0',
  u'shotsOnTarget': u'4',
  u'starts': u'10',
  u'subIns': u'1',
  u'totalGoals': u'3',
  u'totalShots': u'5',
  u'yellowCards': u'1'},
 {u'age': u'24',
  u'foulsCommitted': u'19',
  u'foulsSuffered': u'9',
  u'goalAssists': u'1',
  u'no': u'2',
  u'pla': u'Mario Gaspar ',
  u'pos': u'D',
  u'redCards': u'0',
  u'shotsOnTarget': u'8',
  u'starts': u'22',
  u'subIns': u'2',
  u'totalGoals': u'3',
  u'totalShots': u'24',
  u'yellowCards': u'2'},
 {u'age': u'27',
  u'foulsCommitted': u'15',
  u'foulsSuffered': u'9',
  u'goalAssists': u'2',
  u'no': u'15',
  u'pla': u'Victor Ruiz',
  u'pos': u'D',
  u'redCards': u'0',
  u'shotsOnTarget': u'0',
  u'starts': u'17',
  u'subIns': u'1',
  u'totalGoals': u'0',
  u'totalShots': u'3',
  u'yellowCards': u'3'},
 {u'age': u'30',
  u'foulsCommitted': u'11',
  u'foulsSuffered': u'9',
  u'goalAssists': u'0',
  u'no': u'22',
  u'pla': u'Antonio Rukavina',
  u'pos': u'D',
  u'redCards': u'0',
  u'shotsOnTarget': u'0',
  u'starts': u'11',
  u'subIns': u'3',
  u'totalGoals': u'0',
  u'totalShots': u'3',
  u'yellowCards': u'3'},
 {u'age': u'24',
  u'foulsCommitted': u'48',
  u'foulsSuffered': u'18',
  u'goalAssists': u'9',
  u'no': u'17',
  u'pla': u'Denis Cheryshev',
  u'pos': u'M',
  u'redCards': u'0',
  u'shotsOnTarget': u'20',
  u'starts': u'17',
  u'subIns': u'7',
  u'totalGoals': u'4',
  u'totalShots': u'59',
  u'yellowCards': u'6'},
 {u'age': u'24',
  u'foulsCommitted': u'8',
  u'foulsSuffered': u'12',
  u'goalAssists': u'1',
  u'no': u'6',
  u'pla': u'Jonathan Dos Santos',
  u'pos': u'M',
  u'redCards': u'0',
  u'shotsOnTarget': u'5',
  u'starts': u'12',
  u'subIns': u'10',
  u'totalGoals': u'2',
  u'totalShots': u'10',
  u'yellowCards': u'4'},
 {u'age': u'20',
  u'foulsCommitted': u'24',
  u'foulsSuffered': u'13',
  u'goalAssists': u'4',
  u'no': u'19',
  u'pla': u'Mois\xe9s G\xf3mez',
  u'pos': u'M',
  u'redCards': u'0',
  u'shotsOnTarget': u'11',
  u'starts': u'16',
  u'subIns': u'8',
  u'totalGoals': u'4',
  u'totalShots': u'26',
  u'yellowCards': u'5'},
 {u'age': u'18',
  u'foulsCommitted': u'0',
  u'foulsSuffered': u'0',
  u'goalAssists': u'0',
  u'no': u'27',
  u'pla': u'Nahuel Leiva',
  u'pos': u'M',
  u'redCards': u'0',
  u'shotsOnTarget': u'0',
  u'starts': u'0',
  u'subIns': u'0',
  u'totalGoals': u'0',
  u'totalShots': u'0',
  u'yellowCards': u'0'},
 {u'age': u'30',
  u'foulsCommitted': u'32',
  u'foulsSuffered': u'25',
  u'goalAssists': u'1',
  u'no': u'21',
  u'pla': u'Bruno',
  u'pos': u'M',
  u'redCards': u'0',
  u'shotsOnTarget': u'7',
  u'starts': u'20',
  u'subIns': u'1',
  u'totalGoals': u'2',
  u'totalShots': u'18',
  u'yellowCards': u'3'},
 {u'age': u'23',
  u'foulsCommitted': u'3',
  u'foulsSuffered': u'5',
  u'goalAssists': u'0',
  u'no': u'26',
  u'pla': u'Sergio Marcos',
  u'pos': u'M',
  u'redCards': u'0',
  u'shotsOnTarget': u'2',
  u'starts': u'5',
  u'subIns': u'0',
  u'totalGoals': u'0',
  u'totalShots': u'3',
  u'yellowCards': u'0'},
 {u'age': u'27',
  u'foulsCommitted': u'26',
  u'foulsSuffered': u'18',
  u'goalAssists': u'3',
  u'no': u'4',
  u'pla': u'Tom\xe1s Pina',
  u'pos': u'M',
  u'redCards': u'1',
  u'shotsOnTarget': u'1',
  u'starts': u'10',
  u'subIns': u'5',
  u'totalGoals': u'0',
  u'totalShots': u'7',
  u'yellowCards': u'3'},
 {u'age': u'23',
  u'foulsCommitted': u'34',
  u'foulsSuffered': u'20',
  u'goalAssists': u'1',
  u'no': u'14',
  u'pla': u'Manuel Trigueros',
  u'pos': u'M',
  u'redCards': u'0',
  u'shotsOnTarget': u'6',
  u'starts': u'18',
  u'subIns': u'5',
  u'totalGoals': u'1',
  u'totalShots': u'22',
  u'yellowCards': u'6'},
 {u'age': u'22',
  u'foulsCommitted': u'5',
  u'foulsSuffered': u'14',
  u'goalAssists': u'0',
  u'no': u'10',
  u'pla': u'Joel Campbell',
  u'pos': u'F',
  u'redCards': u'0',
  u'shotsOnTarget': u'3',
  u'starts': u'7',
  u'subIns': u'0',
  u'totalGoals': u'0',
  u'totalShots': u'9',
  u'yellowCards': u'1'},
 {u'age': u'25',
  u'foulsCommitted': u'18',
  u'foulsSuffered': u'23',
  u'goalAssists': u'3',
  u'no': u'9',
  u'pla': u'Giovani Dos Santos',
  u'pos': u'F',
  u'redCards': u'0',
  u'shotsOnTarget': u'8',
  u'starts': u'13',
  u'subIns': u'8',
  u'totalGoals': u'1',
  u'totalShots': u'35',
  u'yellowCards': u'2'},
 {u'age': u'22',
  u'foulsCommitted': u'15',
  u'foulsSuffered': u'15',
  u'goalAssists': u'3',
  u'no': u'23',
  u'pla': u'Gerard Moreno',
  u'pos': u'F',
  u'redCards': u'0',
  u'shotsOnTarget': u'15',
  u'starts': u'11',
  u'subIns': u'5',
  u'totalGoals': u'5',
  u'totalShots': u'26',
  u'yellowCards': u'2'},
 {u'age': u'31',
  u'foulsCommitted': u'28',
  u'foulsSuffered': u'31',
  u'goalAssists': u'1',
  u'no': u'8',
  u'pla': u'Ikechukwu Uche',
  u'pos': u'F',
  u'redCards': u'0',
  u'shotsOnTarget': u'15',
  u'starts': u'17',
  u'subIns': u'4',
  u'totalGoals': u'5',
  u'totalShots': u'37',
  u'yellowCards': u'2'}]

        import json
        testFile = open("output.json")
        data = json.load(testFile)

I tried setting

pla = data["pla"]

I get the following error: "list indices must be integers, not str"

the following is my insert statement:

sql = "INSERT INTO soccer_player (player_name, player_no , player_pos) VALUES ( pla , no , pos)"

What is the issue here?

You are trying access the data as if it was an array. first you have to parse the JSON into a python object, and then access it. Check out https://docs.python.org/2/library/json.html — James Parsons, Apr 01 '15 at 01:07

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

According to the JSON data you've shown, data becomes a list of dictionaries.

If you want to insert all of the items in that list into the MySQL database, you can approach this with executemany():

sql = """
    INSERT INTO 
        soccer_player (player_name, player_no , player_pos) 
    VALUES 
        (%(pla)s, %(no)s, %(pos)s)
"""

cursor.executemany(sql, data)

Note the placeholders I'm using %(pla)s, %(no)s and %(pos)s - this is a special syntax for "keyword-parameterized" queries in so called "Python extended format" (PEP-249).

edited May 23 '17 at 11:43

Community

1
1

answered Apr 01 '15 at 00:44

alecxe

462,703
120
1,088
1,195

its compiling, but the data is not being imported, the mysql table is still blank – sandy Apr 01 '15 at 01:38
@sandy have you tried to call `db.commit()` after the `execute()` call? – alecxe Apr 01 '15 at 01:38
aah thank you so much, I was wondering the json includes goalkeepers and players, goalkeepers have saves while players have goals, how would that work if i want to insert saves and assists into the table please – sandy Apr 01 '15 at 02:47
@sandy it depends on where do you want to store them and your database table schema. Btw, you can also use an ORM to avoid dealing with SQL queries, like `sqlalchemy` or `ponyORM`. – alecxe Apr 01 '15 at 02:48

Importing JSON into mysql using python

1 Answers1