Problems while trying to insert API DATA in SQL LITE DATABASE

Question

everyone! So basically I have this problem. I'm trying to make a request to a zipcode API on Brazil, but the data is showed on the JSON format. What I have to do, it's to take this information from the API and put it on a SQLlite database. But, somehow I don't know how to do it. I tried to use this code:

import requests
import sqlite3
import json
print('Identifying the ZIP CODE')
CEPC = input('Please type the zipcode:')
Requisicao = requests.get('https://viacep.com.br/ws/%7B%7D/json/%27.format(CEPC))
print(Requisicao.json())
#Database
con = sqlite3.connect('CEPS.db')
cur = con.cursor()
cur.execute('DROP TABLE IF EXISTS Requisicao')
cur.execute("CREATE TABLE Requisicao (cep int(8), data json)")
for Requisicao1 in Requisicao:
cur.execute("insert into Requisicao values (8, 5)",
          [Requisicao1['cep'], json.dumps(Requisicao1)])
cur.commit()
cur.close()

It's all working when I try to make the request to the API, but when I try to insert the data on the database I just can't. When it come to this code part:

for or Requisicao1 in Requisicao:
cur.execute("insert into Requisicao values (8, 5)",
      [Requisicao1['cep'], json.dumps(Requisicao1)])



It gives me the error: TypeError: byte indices must be integers or slices, not str.

Examples of zipcodes are: 09931080, 05565000.

The outpout to the zipcode example is:

{
  "cep": "05565-000",
  "logradouro": "Avenida General Asdrúbal da Cunha",
  "complemento": "",
  "bairro": "Jardim Arpoador",
  "localidade": "São Paulo",
  "uf": "SP",
  "ibge": "3550308",
  "gia": "1004",
  "ddd": "11",
  "siafi": "7107"
}

This is the output when I try to get the request.

Can you add some sample zipcodes to the question, in particular ones that will return multiple results? — norie
– norie, Commented Aug 14, 2021 at 19:14

snakecharmerb · Accepted Answer · 2021-08-15 07:44:27Z

1

There are a few issues with the code in the question:

the url is invalid: it seems the path has been before formatting; however this is presumably a transcription mistake, otherwise it would cause an error immediately
the code loops over the response (Requisicao). This is operating on the raw request, which is not useful here. Perhaps, the intention is to handle the API returning multiple zip codes? As we only have examples we don't need to loop.
in the query statement, values (8, 5) would cause 8 and 5 to be inserted in each row. It should be values (?, ?) to insert the values that you provide (this is know as parameter substitution).
the connection should be committed, not the cursor.

Based on the above comments, the insertion code would look like this:

Requisicao1 = Requisicao.json()
cur.execute("insert into Requisicao values (?, ?)",
            [Requisicao1['cep'], json.dumps(Requisicao1)])
con.commit()
cur.close()

answered Aug 15, 2021 at 7:44

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

3 Comments

Rui Barbosa Over a year ago

Could you please check my new edit? The code is working, but I need to insert more columns

snakecharmerb Over a year ago

@Rui3144 No - you can't change your question once it's been answered. Ask a new question. And roll back your edit to the question.

Rui Barbosa Over a year ago

Oh, I'm Sorry I didn't know that

norie · Accepted Answer · 2021-08-15 07:46:27Z

1

The URL you are using doesn't appear to match the one here so I've replaced it in the following code.

I've also removed the loop as neither example zipcode returns multiple records - if there are zipcodes that do please add one.

Note the data to insert needs to be a tuple, not a list.

Also, for multiple records you would use executemany and a list of tuples.

import requests
import sqlite3
import json

CEPC = input("Please type the zipcode:")

print("Identifying the ZIP CODE")

Requisicao = requests.get(f"https://viacep.com.br/ws/{CEPC}/json")

if Requisicao.status_code == 200:
    data = Requisicao.json()
    # Database
    con = sqlite3.connect("CEPS.db")
    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS Requisicao")
    cur.execute("CREATE TABLE Requisicao (cep int(8), data json)")

    cur.execute("insert into Requisicao values (?, ?)", (data["cep"], json.dumps(data)))
    con.commit()
    con.close()
else:
    print(f"Request failed with status code {Requisicao.status_code} ")

edited Aug 15, 2021 at 7:46

answered Aug 14, 2021 at 19:27

norie

9,9372 gold badges14 silver badges19 bronze badges

2 Comments

Rui Barbosa Over a year ago

your code it's also working, could you please me help me out again? Please check my edit

Rui Barbosa Over a year ago

The code it's no inserting values to multiple columns, I had to change the code

Collectives™ on Stack Overflow

Problems while trying to insert API DATA in SQL LITE DATABASE

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related