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