0

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?

Improve this question
1
  • 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 docs.python.org/2/library/json.html Commented Apr 1, 2015 at 1:07

1 Answer 1

Reset to default
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).

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

4 Comments

its compiling, but the data is not being imported, the mysql table is still blank
@sandy have you tried to call db.commit() after the execute() call?
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 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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.