How to parse and store json data downloaded from api url into SQLite DB table using python

Question

I'm in trouble with python (let me say I'm a beginner...) I was try to parse the following json data (part of data) downloaded by an api URL...

{
"sha": "cffff88d9f69932845ea770b09bfdfbdd3c23ed9",
"node_id": "MDY6Q29tbWl0NjUyNzU5NTM6Y2ZmZmY4OGQ5ZjY5OTMyODQ1ZWE3NzBiMDliZmRmYmRkMzJmZDAzYQ==",
"commit": {
  "author": {
    "name": "Anton",
    "email": "[email protected]",
    "date": "2018-09-18T08:46:12Z"
  },
  "committer": {
    "name": "Anton",
    "email": "[email protected]",
    "date": "2018-09-18T08:46:12Z"
  },
  "message": "Release 2.0.0",
  "author": {
  "login": "Tony",
  }
}

I have used requests library to retreive data from api but when I try to store data into columns python goes in error. I used a for loop to store data...

api_url = requests.get('https://.......)

data_json = api_url.json()

sqlite_file = 'users.db'
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()
c.execute("DROP TABLE IF EXISTS users")
c.execute("CREATE TABLE IF NOT EXISTS users (sha TEXT, date TEXT, author 
TEXT, message TEXT, is_external INTEGER)")

for item in data_json:
    sha = item['sha']
    date = item['commit']['author']['date']
    author = item['author']['login']
    message = item['commit']['message']

 c.execute("INSERT INTO users (sha,date,author,message) VALUES (?,?,?,?)", sha, date, author, message)


 conn.commit()
 conn.close()

The error is the following:

c.execute("INSERT INTO users (sha,date,author,message) VALUES (?,?,?,?)", sha,date,author,message) TypeError: function takes at most 2 arguments (5 given)

I'm stuck... And thanks for your help!

This question isn't really to do with JSON. See how to create a minimal reproducible example. And <scottish accent>Welcome to Stack Overflow</scottish accent> — Open AI - Opting Out
– Open AI - Opting Out, Commented Oct 22, 2018 at 22:08

Mike Scotty · Accepted Answer · 2018-10-22 21:57:53Z

1

You need to provide the parameters as one argument, therefore make it an iterable - e.g. a list or a tuple.

c.execute("INSERT INTO users (sha,date,author,message) VALUES (?,?,?,?)", [sha, date, author, message])

- or -

c.execute("INSERT INTO users (sha,date,author,message) VALUES (?,?,?,?)", (sha, date, author, message))

answered Oct 22, 2018 at 21:57

Mike Scotty

10.8k5 gold badges41 silver badges52 bronze badges

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

Collectives™ on Stack Overflow

How to parse and store json data downloaded from api url into SQLite DB table using python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related