在MariaDB上使用pandas的单个for循环中，插入具有多对多关系的两个表格

Question

我正在尝试批量插入数据到两个具有多对多关系的表中，如果我只在一个表中插入没有问题，但我无法同时在两个表中插入。

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;

这是我尝试的代码

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)

现在，显然我会得到错误：AttributeError: 'Series' object has no attribute 'Categorie' 但是如果我在

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

然后我在第一个插入中没有用到它，会出现与未使用所有字段相关的错误。

我认为一定存在一个简单的解决方案，或者我的数据库结构存在问题。 我尝试了很多不同的方法，但都没有成功，有人可以帮助解决这个问题吗？

谢谢。

Answer 1

如果我理解正确，下面的代码行：

cat = (row.Categorie,)

创建了一个只有一个值的元组。 然后，使用这个语句提供了一个只有一个值的元组：

cursor.execute(sql1, cat)

但实际上你应该提供更多的值。 所以你需要检查这行代码，并提供sql1需要的所有值。

希望对你有帮助。