ProjetoInsert_python

import pymysql.cursors
import time
import datetime

#definir energia que está sendo inserida
# energia eolica = 18
# energia solar = 5
# Bioenergia = 12

tipoEnergia = 18
idPais = 10

connection = pymysql.connect(host='localhost',
                             user='root',
                             port=3306,
                             password='admin',
                             db='testeDB',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

ts = time.time()
st = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
with open('links.txt') as f:
    content = f.read().splitlines()
print (content)
try:
    with connection.cursor() as cursor:
        for word in content:

            print("################### INICIO FOR WORD IN CONTENT ############## \n")
            nome = "{\"pt_BR\":\"" + word.split(',')[0] + "\"}"
            nome.replace("(", "").replace(")", "")
            portifolio = "{\"pt_BR\":\"" + '' + "\"}"


            site = word.split(',')[1]
            busca = nome

            #debug
            #print (" nome " , nome , "portifolio", portifolio, "site= ",site, "busca",busca)
            #busca se já existe na tabela provider

            nomebusca = word.split( ',')[0]
            sql = "select * from provider where name like %s and status = 'A'"
            cursor.execute(sql,("%" + nomebusca + "%",))
            print("PALAVRA SENDO BUSCADA = ", nomebusca)
            print("->>>> select : ", sql)


            row = cursor.fetchone()
            print ("resultadoCursor\n\n",row)

            if (row is None ): # se não existe inserir ela toda.

                print("\t \nNÃO EXISTE NA BUSCA E O VALOR DA ROW É :: ",row)
                row = cursor.fetchone()
                sqlMaxIdBefore = "select max(id) from provider"
                cursor.execute(sqlMaxIdBefore)
                row = cursor.fetchone()
                idProvider = row['max(id)']
                idProvider = idProvider + 1
                print(idProvider)

                #st = tempo
                sql = repr(
                    "INSERT INTO `provider` ( `id`,`name`, `email`, `link`, `portfolio`, `search`, `status`, `created_at`, `updated_at`) VALUES('{}','{}','','{}' ,'{}','{}','A','{}',NULL);").replace(
                    "\"", "").format(idProvider,nome, site, portifolio, busca, st)
                cursor.execute(sql)
                connection.commit()
                #print
                print (repr(
                    "INSERT INTO `provider` ( `id`,`name`, `email`, `link`, `portfolio`, `search`, `status`, `created_at`, `updated_at`) VALUES('{}','{}','','{}' ,'{}','{}','A','{}',NULL);").replace(
                    "\"", "").format(idProvider,nome, site, portifolio, busca, st) )
                print ("\n")
                #cria endereco

                sqlMaxIdBefore = "select max(id) from address"
                cursor.execute(sqlMaxIdBefore)
                row = cursor.fetchone()

                idAddress = row['max(id)']
                idAddress = idAddress + 1

                cidade = ""
                vizinhaca = ""
                postal_code = ""
                address = ""
                number = ""
                phone = ""

                sql = "insert into address (id, country_id,city,neighborhood,postal_code,address,number,phone) " \
                      "values(%s, %s, %s, %s, %s, %s, %s, %s)"
                cursor.execute(sql,(idAddress,idPais,cidade,vizinhaca,postal_code,address,number,phone) )
                connection.commit()
                print(sql,"VALUES,",idAddress,idPais,cidade,vizinhaca,postal_code,address,number,phone,"\n\n")

                #Liga id ao endereco

                sql = "insert into provider_address(provider_id,address_id) values (%s,%s)"
                cursor.execute(sql,(idProvider,idAddress))
                connection.commit()

                print("provider address\n",sql,"\nValues = ",idProvider,idAddress)

                #liga provedor a energia
                #energia está definida lá em cima


                sql = "insert into provider_energy_type( provider_id, energy_type_id) values(%s,%s)"
                cursor.execute(sql,(idProvider,tipoEnergia))
                connection.commit()

                print(sql,"\n Values : ",idProvider,tipoEnergia)
            # já existe o link.
            else:
                print ("EXISTE NA BUSCA  \n " )
                idEncontrado = row['id']

                sql = "select * from provider_energy_type where provider_id = %s and energy_type_id = %s"
                cursor.execute(sql, (idEncontrado,tipoEnergia))
                row = cursor.fetchone()

                print ("select* from provider energy type....\n",row)

                if (row is None):
                    sql = "insert into provider_energy_type (provider_id,energy_type_id) values (%s,%s)"
                    cursor.execute(sql,(idEncontrado,tipoEnergia))
                    connection.commit()
                    print("inseriu energia")
                else:
                    print("nao inseriu energia")
                #para inserir, verifico se já não existe o registro

                sql = "select * from provider_address join address on address.id = provider_address.address_id where provider_id = %s and address.country_id = %s"
                cursor.execute(sql,(idEncontrado,idPais))
                row2 = cursor.fetchone()

                if (row2 is None):
                    # cria endereco

                    sqlMaxIdBefore = "select max(id) from address"
                    cursor.execute(sqlMaxIdBefore)
                    row = cursor.fetchone()

                    idAddress = row['max(id)']
                    idAddress = idAddress + 1

                    cidade = ""
                    vizinhaca = ""
                    postal_code = ""
                    address = ""
                    number = ""
                    phone = ""

                    sql = "insert into address (id, country_id,city,neighborhood,postal_code,address,number,phone) " \
                          "values(%s, %s, %s, %s, %s, %s, %s, %s)"
                    cursor.execute(sql, (idAddress, idPais, cidade, vizinhaca, postal_code, address, number, phone))
                    connection.commit()
                    print(sql, "VALUES,", idAddress, idPais, cidade, vizinhaca, postal_code, address, number, phone,
                          "\n\n")

                    # Liga id ao endereco

                    sql = "insert into provider_address(provider_id,address_id) values (%s,%s)"
                    cursor.execute(sql, (idEncontrado, idAddress))
                    connection.commit()

                    print("provider address\n", sql, "\nValues = ", idEncontrado, idAddress)
                else:
                    print ("nao inseriu pais")


        print("################### FIM FOR WORD IN CONTENT ############## \n\n")
finally:
    connection.close()