Insert data into sqlite3 database with API

Question

I'm trying to insert data from a web API into my database (I am using sqlite3 on python 3.7.2) and I can't find any tutorials on how do to so. So far all my code is:

import requests, sqlite3
database = sqlite3.connect("ProjectDatabase.db")
cur = database.cursor()

d = requests.get("http://ergast.com/api/f1/2019/drivers")

I'm aiming to get the names and driver numbers of each driver and insert them all into a table called Drivers. (I'm also using more APIs with more tables, but if I figure out how to do one then the rest should be fine.) I assume I have to do something like

cur.execute('''INSERT INTO Drivers VALUES(?,?), (driverName, driverNumber)
''')

but I'm struggling to figure out how to insert the data straight into the table from the website. Does anyone know how to do this? Thanks

Is it necessary to parse the XML output? This particular API supports JSON response. — Luke
– Luke, Commented Mar 20, 2019 at 10:34
Try to just get the names from the website without putting them in a database first. — 576i
– 576i, Commented Mar 20, 2019 at 10:36
You correctly assume. The normal way has 3 steps: 1/ get the data 2/ parse the data 3/ insert the relevant parts into the database. — Serge Ballesta
– Serge Ballesta, Commented Mar 20, 2019 at 10:36
I don't quite see where you struggle to be honest. It basically goes as follow: request -> parse -> insert at what point do you struggle? — Vulpex
– Vulpex, Commented Mar 20, 2019 at 10:37
@LukaszSalitra I don't know, I used the 'requests.get()' and that's what it automatically gave me. If JSON is needed then that's fine, I'd just need to figure out how to get the JSON response instead of XML. — NotEvenTheRain
– NotEvenTheRain, Commented Mar 20, 2019 at 10:38

Vulpex · Accepted Answer · 2019-03-20 11:14:48Z

3

As stated in the comment section in the OP, the problem seemed to be how to parse the API point.

d = requests.get("http://ergast.com/api/f1/2019/drivers.json")
d = d.json()
drivers = d["MRData"]["DriverTable"]["Drivers"]

would be the answer to that question to access all drivers provided by that API.

To add the entries to the db you can use the following:

for dr in drivers:
    name = dr["familyName"]
    number = dr["permanentNumber"]
    sql = 'INSERT INTO Drivers (name,number) VALUES(?,?)'
    val = (name,number)
    cur.execute(sql,val)

with this solution you don't have to use specify the index and can directly access the parameter you're interested in.

edited Mar 20, 2019 at 11:14

answered Mar 20, 2019 at 10:49

Vulpex

1,0838 silver badges20 bronze badges

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

9 Comments

Vulpex Over a year ago

I've edited the loop into the answer. You can basically take that answer as posted and put it into your code, shouldn't need too much editing, except ofcourse the db connection and cursor creation.

NotEvenTheRain Over a year ago

Thanks, I've tried to use this but it's throwing up an error saying there's no such column "name" (it doesn't with "driverName" either [same with "number"]) - I tried to specify the columns I'm entering the data into but it still throws up this error.

Vulpex Over a year ago

@Grace have you changed the url and added the drivers = d["MRData"]["DriverTable"]["Drivers"] part? It should work given you've copied the complete answer.

NotEvenTheRain Over a year ago

I have, I've just double checked and it's the same as yours.

Vulpex Over a year ago

@Grace yeah I just saw I've goofed with the sql statement, this way it should work and the way I usually do it. note: in the sql statement I name the columns I want to add, in my test db the column names were name and number.

|

Collectives™ on Stack Overflow

Insert data into sqlite3 database with API

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related