Mysql – Insert in two tables with many to many relation in a single for loop with pandas on mariaDB

I’m triyng do bulk insert data into two tables with a many to many relationship, no issue if I insert on a single table but I’m unable to insert in both at once.

CREATE TABLE IF NOT EXISTS `mydbv3`.`PRODOTTI` (
  `idPRODOTTI` INT(11) NOT NULL AUTO_INCREMENT,
  `PROD_ATTIVO` TINYINT(4) NULL DEFAULT 1,
  `EAN13` VARCHAR(45) NOT NULL,
  `prod_nome` VARCHAR(300) NOT NULL,
  `Prezzo` DECIMAL(15,2) NULL DEFAULT NULL,
  `Costo` DECIMAL(15,2) NOT NULL,
  `PRODOTTI_marca` VARCHAR(45) NULL DEFAULT NULL,
  `Quantita` DECIMAL(10,0) NOT NULL DEFAULT 0,
  `PRODOTTI_descrizione` TEXT NULL DEFAULT NULL,
  `Data_ins` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
  `Data_update` TIMESTAMP NULL DEFAULT NULL,
  `CheckProd` TIMESTAMP NULL DEFAULT NULL,
  `Fornitori_Ordini_idOrdini` INT(11) NULL DEFAULT NULL,
  `Fornitori_idFornitori` INT(11) NOT NULL,
  `CAT_IVA_idCAT_IVA` INT(11) NOT NULL,
  PRIMARY KEY (`idPRODOTTI`),
  UNIQUE INDEX `idPRODOTTI_UNIQUE` (`idPRODOTTI` ASC) VISIBLE,
  INDEX `fk_PRODOTTI_Fornitori1_idx` (`Fornitori_idFornitori` ASC) VISIBLE,
  INDEX `fk_PRODOTTI_CAT_IVA1_idx` (`CAT_IVA_idCAT_IVA` ASC) VISIBLE,
  CONSTRAINT `fk_PRODOTTI_CAT_IVA1`
    FOREIGN KEY (`CAT_IVA_idCAT_IVA`)
    REFERENCES `mydbv3`.`CAT_IVA` (`idCAT_IVA`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_PRODOTTI_Fornitori1`
    FOREIGN KEY (`Fornitori_idFornitori`)
    REFERENCES `mydbv3`.`Fornitori` (`idFornitori`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 145908
DEFAULT CHARACTER SET = utf8;

CREATE TABLE IF NOT EXISTS `mydbv3`.`CATEGORIE` (
  `idCATEGORIE` INT(11) NOT NULL AUTO_INCREMENT,
  `Nome_Categoria` VARCHAR(45) NOT NULL,
  `Categoria_Padre` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idCATEGORIE`))
ENGINE = InnoDB
AUTO_INCREMENT = 31
DEFAULT CHARACTER SET = utf8;

here is the code that I’ve tried

import pandas as pd
import mysql.connector as msql
from mysql.connector import Error

empdata = pd.read_csv('static/files/prod_ridotto3.csv', index_col=False, delimiter=';', on_bad_lines='skip', usecols=["Attivo (0/1)","EAN13","Nome","Categorie","Prezzo","IVAID","Costo","Fornitore","Quantità","Data Ordine"])
#print(empdata.head())
#cat = series_one = pd.Series(empdata.Age)
#EANDATA = pd.read_csv('static/files/prod_ridotto3.csv', delimiter=';', on_bad_lines='skip', usecols=["Categorie"])
#print (EANDATA)
#print(EANDATA.head())


try:
    conn = msql.connect(host='192.168.1.2', database='mydbv3', user='root', password='password')
    try:
        if conn.is_connected():
            cursor = conn.cursor()
            cursor.execute("select database();")
            record = cursor.fetchone()
            #print (record)
            print("You're connected to database: ", record)
            #loop through the data frame
            for i,row in empdata.iterrows():
                sql = "INSERT INTO PRODOTTI (PROD_ATTIVO,EAN13,prod_nome,Prezzo,CAT_IVA_idCAT_IVA,Costo,Fornitori_idFornitori,Quantita,Data_ins) 
                VALUES (%s,%s,%s,%s,(select idCAT_IVA from CAT_IVA where CAT_IVA_code = %s),%s, 
                (select idFornitori from Fornitori where FORNITORI_Nome = %s),%s,%s)"
                #print (type(row))
                #print(row)
                #print(tuple(row))
                cat = (row.Categorie,)
                #print("Type CAT",type(cat))
                #print("CAT=",cat)
                #print (type(tuple(cat)))
                sql1 = "INSERT INTO PRODOTTI_has_CATEGORIE (PRODOTTI_idPRODOTTI,CATEGORIE_idCATEGORIE) VALUES ((SELECT LAST_INSERT_ID()),(select CATEGORIE.idCATEGORIE from CATEGORIE where Nome_Categoria = %s))"
                cursor = conn.cursor()
                cursor.execute("select database();")
                record = cursor.fetchone()
                print (record)
                #print(sql)
                #print(tuple(row))
                #print(row)
                cursor.execute(sql, tuple(row))
                cursor.execute(sql1, cat)
                print("Product inserted",i)
                conn.commit()
    except Error as e:
        print("Error while inserting in DB", e)
except Error as e:
    print("Error while connecting to MySQL", e)

now, obviousolly I get the error: AttributeError: 'Series' object has no attribute 'Categorie'
but if I insert Caregorie in

usecols=["Attivo (0/1)","EAN13","Nome","Categorie","Prezzo","IVAID","Costo","Fornitore","Quantità","Data Ordine"])

thenb I have no use for it in the first insert, and get an error related to the fact that not all field are used.

I suppose must exist an easy solution or there is some problem in my DB structure.
I’ve tried a lot of different approach, but without succes, cabn somebody hel on this issue?

Thank you.