Ler JSON via API e gravar em banco SQL

[TecInfo]

Olá pessoal, eu sou novo em Python e novo aqui no fórum.

Eu estou desenvolvendo um programa em Python que acessa uma API, essa API retorna um JSON e esse retorno eu tenho que gravar em um banco de dados SQL.

O acesso a API está OK, o problema é gravar no banco.

Segue o código fonte que estou usando.

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()

 

Segue o erro que é retornado.

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

 

Um detalhe, o campo clienteId é inteiro

[Thanael]

Olá @TecInfo tudo bem?

Se eu fosse realizar isso utilizaria o pandas, para tratar os dados e inserir no banco, segue um exemplo de como ficaria o código.

# Importar as libs
import requests
import pyodbc
import json
import pandas as pd # pip install pandas

# Conexão com o banco
server = 'SRVBanco' 
dataBase = 'DWSimu' 
userName = 'sa' 
passWord = 'senha'

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

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

# Transformar em DataFrame
dados = json.loads(request.content)
df = pd.DataFrame(dados['items'])

# Transformar o clienteId em inteiro - pode ser que ao criar o dataframe já venha como inteiro
df['clienteId'] = df['clienteId'].astype(int)

#Realizar a inserção no banco de dados
df.to_sql('cliente',conexao,index=False,if_exists='append')

Esta seria a maneira com a qual eu realizaria este processo. 

[Thanael]

Em relação ao seu código ao meu ver, pode ser que seu problema esteja no for cliente in jsonString: e na criação do comando SQL estar fora do seu LOOP, tenta ajustar para executar cada SQL de INSERT dentro do FOR, desta forma

for cliente in jsonString:
    clienteId = cliente['clienteId']
    nomeAbrev = cliente['nomeAbrev']
    nome = cliente['nome']
    
    comandoSQL = f"INSERT INTO cliente (cliente_id, nome_abrev, nome) VALUES ({clienteId}, {nomeAbrev}, {nome})"
    cursor.execute(comandoSQL)
    
conexao.commit()

Acredito que desta forma possa funcionar também, tenta realizar alguma das maneiras e me informa se funcionou, caso não funcione, procuramos outra solução. 

[TecInfo]

Boa tarde,

@thanael

Usando o pandas deu os seguintes erros.

C:/Python310/python.exe c:/Projetos/api/getClienteV2.py
C:\Python310\lib\site-packages\pandas\io\sql.py:761: UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
  warnings.warn(
Traceback (most recent call last):
  File "C:\Python310\lib\site-packages\pandas\io\sql.py", line 2020, in execute
    cur.execute(*args, **kwargs)
pyodbc.ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "c:\Projetos\api\getClienteV2.py", line 26, in <module>
    df.to_sql('cliente',conexao,index=False,if_exists='append')
  File "C:\Python310\lib\site-packages\pandas\core\generic.py", line 2951, in to_sql
    return sql.to_sql(
  File "C:\Python310\lib\site-packages\pandas\io\sql.py", line 697, in to_sql
    return pandas_sql.to_sql(
  File "C:\Python310\lib\site-packages\pandas\io\sql.py", line 2189, in to_sql
    table.create()
  File "C:\Python310\lib\site-packages\pandas\io\sql.py", line 831, in create
    if self.exists():
  File "C:\Python310\lib\site-packages\pandas\io\sql.py", line 815, in exists
    return self.pd_sql.has_table(self.name, self.schema)
  File "C:\Python310\lib\site-packages\pandas\io\sql.py", line 2197, in has_table
    return len(self.execute(query, [name]).fetchall()) > 0
  File "C:\Python310\lib\site-packages\pandas\io\sql.py", line 2032, in execute
    raise ex from exc
pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

[TecInfo]

Boa tarde,

@Thanael, usando a segunda opção, gerou o erro abaixo.

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

[Thanael]

Tenta ajustar para

 clienteId = int(cliente['clienteId'])

[TecInfo]

Não deu certo.

for cliente in jsonString:
    clienteId = int(cliente['clienteId'])
    nomeAbrev = cliente['nomeAbrev']
    nome = cliente['nome']
    
    comandoSQL = f"INSERT INTO cliente (cliente_id, nome_abrev, nome) VALUES ({clienteId}, {nomeAbrev}, {nome})"
    cursor.execute(comandoSQL)
    
conexao.commit()

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

[TecInfo]

Segue um trecho do JSON.

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

[Thanael]

Então sabemos que o erro esta no Tipo do dado, tenta fazer o seguinte.

for cliente in jsonString:
    clienteId = int(cliente['clienteId'])
    nomeAbrev = cliente['nomeAbrev']
    nome = cliente['nome']
    
    comandoSQL = f"INSERT INTO cliente (cliente_id, nome_abrev, nome) VALUES ({clienteId}, '{nomeAbrev}', '{nome}')"
    cursor.execute(comandoSQL)
    
conexao.commit()

Ao ajustar o comandoSQL, faz com que ele force adicionar as aspas (' item ') de modo com que não ocorra erro na hora da inserção no banco.

[TecInfo]

@Thanael, resolvi da seguinte forma.

valoresInserir = []

for clientes in cliente['items']:
    valoresInserir.append((clientes['clienteId'], clientes['nomeAbrev'], clientes['nome']))
 
comandoSQL = "INSERT INTO cliente (cliente_id, nome_abrev, nome) VALUES (?, ?, ?)"
cursor.executemany(comandoSQL, valoresInserir)

Muito obrigado pela ajuda.