Python Forum
Read JSON via API and write to SQL database - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Read JSON via API and write to SQL database (/thread-37920.html)

Read JSON via API and write to SQL database - TecInfo - Aug-09-2022

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

RE: Read JSON via API and write to SQL database - snippsat - Aug-09-2022

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']:


RE: Read JSON via API and write to SQL database - TecInfo - Aug-09-2022

(Aug-09-2022, 03:12 PM)snippsat Wrote: 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')

RE: Read JSON via API and write to SQL database - TecInfo - Aug-09-2022

Here's a snippet of the JSON

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

RE: Read JSON via API and write to SQL database - buran - Aug-09-2022

Instead of %s use parameters marker (placeholder) ?

RE: Read JSON via API and write to SQL database - TecInfo - Aug-09-2022

(Aug-09-2022, 04:31 PM)buran Wrote: Instead of %s use parameters placeholder ?

Perfect!
Thanks a lot for the help.