Read JSON via API and write to SQL database

[TecInfo]

Hello everyone, I'm new to Python and new to the forum.

I'm developing a Python program that accesses an API, this API returns a JSON and this return I have to write to a SQL database.

API access is OK, the problem is writing to the bank.

Here's the source code I'm using.

from optparse import Values
from turtle import st
from urllib import request
import requests
import pyodbc
import json
import textwrap

request = requests.get('http://SRVAPI:8280/api/cdp/v1/getCliente')

addressData = request.json()
jsonString = json.dumps(addressData['items'])

server = 'SRVBanco' 
dataBase = 'DWSimu' 
userName = 'sa' 
passWord = 'senha'

conexao = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+dataBase+';UID='+userName+';PWD='+ passWord)

cursor = conexao.cursor()
valoresInserir = []

for cliente in jsonString:
    clienteId = cliente['clienteId']
    nomeAbrev = cliente['nomeAbrev']
    nome = cliente['nome']

    linhaInserir = (clienteId, nomeAbrev, nome)
    valoresInserir.append(linhaInserir)

comandoSQL = "INSERT INTO cliente (cliente_id, nome_abrev, nome) VALUES (%s, %s, %s)"
cursor.executemany(comandoSQL, valoresInserir)
conexao.commit()

cursor.close()
conexao.close()

Following is the error that is returned.

Traceback (most recent call last):
File "c:\Projetos\api\getCliente.py", line 25, in <module>
clienteId = cliente['clienteId']
TypeError: string indices must be integers

One detail, the customerId field is integer

Translated by Google

[snippsat]

Line 12 is wrong,here you dump to string then can iterate over to get key in line 24.
Use addressData(it's a dictionary) as Requests has build in json decoder(don't need import json).

Try change line 24 to:

for cliente in addressData['items']:

[TecInfo]

Line 12 is wrong,here you dump to string then can iterate over to get key in line 24.
Use addressData(it's a dictionary) as Requests has build in json decoder(don't need import json).

Try change line 24 to:

for cliente in addressData['items']:

Hello, I changed as requested, but it gave the following error.

Traceback (most recent call last):
File "c:\Projetos\api\getCliente.py", line 33, in <module>
cursor.executemany(comandoSQL, valoresInserir)
pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 3 parameters were supplied', 'HY000')

[TecInfo]

Here's a snippet of the JSON

[{"clienteId": 0, "nomeAbrev": "PEF", "nome": "PAGAMENTO EXTRA FORNECEDOR"}]

[buran]

Instead of %s use parameters marker (placeholder) ?

[TecInfo]

Instead of %s use parameters placeholder ?

Perfect!
Thanks a lot for the help.