Insert data to json file for mysql db

json_to_mysql.py

import mysql.connector
import json
from pprint import pprint

print('Antes Mysql')

cnx = mysql.connector.connect(user='root', password='7qt5wzs6',
                              host='mypolitician02',
                              database='meupolitico')


cursor = cnx.cursor()

print('Antes abrir arquivo')
with open('/opt/ceap/Ano-2016.json', encoding='latin-1') as data_file:
 print('depois withopen')
    #data = json.load(data_file)
    #data = json.JSONDecoder(data)
    #print(data)
    #data = data["DESPESA"]
    #data = exemple
    #pprint(data)
#i=0    
#for key in data["DESPESA"]:
#    result = key
#    i=i+1
#    print(i)

 print('json.load')
 try:
  jsonToPython = json.load(data_file)
 except:
  print('Error: ', sys.exc_info()[0])

pprint('Leu o arquivo')

txNomeParlamentar = ""
vlrLiquido = ""
nuCarteiraParlamentar = ""
numMes = ""
nuLegislatura = ""
numEspecificacaoSubCota = "" 
codLegislatura = "" 
nuDeputadoId = ""
ideDocumento = "" 
ideCadastro = "" 
txtTrecho = "" 
txtDescricao = "" 
sgUF = "" 
txtDescricaoEspecificacao = "" 
numAno = ""
txtNumero = ""
numRessarcimento = ""
indTipoDocumento = ""
txtCNPJCPF = ""
numSubCota = ""
vlrGlosa = ""
txtFornecedor = ""
sgPartido = ""
vlrDocumento = ""
numLote = ""
vlrRestituicao = ""
datEmissao = ""
numParcela = ""
txtPassageiro = ""

#################################################
#exibindo item com value e key
i=0
for item in jsonToPython['DESPESA']:
  i=i+1
  print(i)
    
  for key,value in item.items():
    #pprint("--key--["+key+"]"+"--value--["+value+"]")
    if key == "txNomeParlamentar":
        if value is None:
            value = ""
        txNomeParlamentar = value
    elif key == "vlrLiquido":
        if value is None:
            value = 0
        value = value.replace(',','.')
        vlrLiquido = float(value)
    elif key == "nuCarteiraParlamentar":
        if value is None:
            value = 0
        nuCarteiraParlamentar = int(value)
    elif key == "numMes":
        if value is None:
            value = 0
        numMes = int(value)
    elif key == "nuLegislatura":
        if value is None:
            value = 0
        nuLegislatura = int(value)
    elif key == "numEspecificacaoSubCota":
        if value is None:
            value = 0
        numEspecificacaoSubCota = int(value) 
    elif key == "codLegislatura":
        if value is None:
            value = 0
        codLegislatura = int(value)  
    elif key == "nuDeputadoId":
        if value is None:
            value = 0
        nuDeputadoId = int(value)  
    elif key == "ideDocumento":
        if value is None:
            value = 0
        if value == "":
            value = 0
        ideDocumento = int(value)    
    elif key == "ideCadastro":
        if value is None:
            value = 0
        if value == "":
            value = 0
        print(value)
        ideCadastro = int(value)    
    elif key == "txtTrecho":
        if value is None:
            value = ""
        txtTrecho = value    
    elif key == "txtDescricao":
        if value is None:
            value = ""
        txtDescricao = value    
    elif key == "sgUF":
        if value is None:
            value = ""
        sgUF = value    
    elif key == "txtDescricaoEspecificacao":
        if value is None:
            value = ""
        txtDescricaoEspecificacao = value    
    elif key == "numAno":
        if value is None:
            value = 0
        numAno = int(value)    
    elif key == "txtNumero":
        if value is None:
            value = ""
        txtNumero = value    
    elif key == "numRessarcimento":
        if value is None:
            value = 0
        numRessarcimento = int(value)    
    elif key == "indTipoDocumento":
        if value is None:
            value = 0
        indTipoDocumento = int(value)    
    elif key == "txtCNPJCPF":
        if value is None:
            value = ""
        txtCNPJCPF = value  
    elif key == "numSubCota":
        if value is None:
            value = 0
        numSubCota = int(value)     
    elif key == "vlrGlosa":
        if value is None:
            value = 0
        value = value.replace(',','.')
        vlrGlosa = float(value)     
    elif key == "txtFornecedor":
        if value is None:
            value = ""
        txtFornecedor = value     
    elif key == "sgPartido":
        if value is None:
            value = ""
        sgPartido = value     
    elif key == "vlrDocumento":
        if value is None:
            value = 0
        value = value.replace(',','.')    
        vlrDocumento = value     
    elif key == "numLote":
        if value is None:
            value = 0
        numLote = int(value)     
    elif key == "vlrRestituicao":
        if value is None:
            value = 0
        value = value.replace(',','.')    
        vlrRestituicao = value     
    elif key == "datEmissao":
        if value is None:
            value = ""
        datEmissao = value 
    elif key == "numParcela":
        if value is None:
            value = 0
        numParcela = int(value)      
    elif key == "txtPassageiro":
        if value is None:
            value = ""
        txtPassageiro = value 
        
  params = [txNomeParlamentar, vlrLiquido, nuCarteiraParlamentar, numMes, nuLegislatura, numEspecificacaoSubCota, codLegislatura,nuDeputadoId, ideDocumento, ideCadastro, txtTrecho, txtDescricao, sgUF, txtDescricaoEspecificacao, numAno, txtNumero, numRessarcimento, indTipoDocumento, txtCNPJCPF, numSubCota, vlrGlosa, txtFornecedor, sgPartido, vlrDocumento, numLote, vlrRestituicao, datEmissao, numParcela, txtPassageiro]

  #pprint(params)

  query = "INSERT INTO meupolitico.despesas (txNomeParlamentar, vlrLiquido, nuCarteiraParlamentar, numMes, nuLegislatura, numEspecificacaoSubCota, codLegislatura, nuDeputadoId, ideDocumento, ideCadastro, txtTrecho, txtDescricao, sgUF, txtDescricaoEspecificacao, numAno, txtNumero, numRessarcimento, indTipoDocumento, txtCNPJCPF, numSubCota, vlrGlosa, txtFornecedor, sgPartido, vlrDocumento, numLote, vlrRestituicao, datEmissao, numParcela, txtPassageiro) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

  cursor.execute(query, params)
  cnx.commit()
  
  #pprint(query)
  
  #pprint("----------------------------------------")
  #pprint("-----------------query------------------")
  #pprint("----------------------------------------")
  
  txNomeParlamentar = ""
  vlrLiquido = ""
  nuCarteiraParlamentar = ""
  numMes = ""
  nuLegislatura = ""
  numEspecificacaoSubCota = "" 
  codLegislatura = "" 
  nuDeputadoId = ""
  ideDocumento = "" 
  ideCadastro = "" 
  txtTrecho = "" 
  txtDescricao = "" 
  sgUF = "" 
  txtDescricaoEspecificacao = "" 
  numAno = ""
  txtNumero = ""
  numRessarcimento = ""
  indTipoDocumento = ""
  txtCNPJCPF = ""
  numSubCota = ""
  vlrGlosa = ""
  txtFornecedor = ""
  sgPartido = ""
  vlrDocumento = ""
  numLote = ""
  vlrRestituicao = ""
  datEmissao = ""
  numParcela = ""
  txtPassageiro = ""
     
cursor.close()

cnx.close()