current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
    
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 –  James_Parsons 21 hours ago

1 Answer 1

up vote 0 down vote accepted

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).

share|improve this answer
    
its compiling, but the data is not being imported, the mysql table is still blank –  sandy 20 hours ago
    
@sandy have you tried to call db.commit() after the execute() call? –  alecxe 20 hours ago
    
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 19 hours ago
    
@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 19 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.