MariaDB 上のパンダを使用して、単一の for ループで多対多の関係を持つ 2 つのテーブルを挿入する
<p>我正在尝试批量插入数据到两个具有多对多关系的表中，如果我只在一个表中插入没有问题，但我无法同时在两个表中插入。</p>
<pre class="brush:php;toolbar:false;">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;</pre>
<p>这是我尝试的代码</p>
<pre class="brush:php;toolbar:false;">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)</pre>
<p>现在，显然我会得到错误：<code>AttributeError: 'Series' object has no attribute 'Categorie'</code>
但是如果我在</p>
<pre class="brush:php;toolbar:false;">usecols=["Attivo (0/1)","EAN13","Nome","Categorie","Prezzo","IVAID","Costo","Fornitore","Quantità","Data Ordine"])</pre>
<p>然后我在第一个插入中没有用到它，会出现与未使用所有字段相关的错误。</p>
<p>我认为一定存在一个简单的解决方案，或者我的数据库结构存在问题。
我尝试了很多不同的方法，但都没有成功，有人可以帮助解决这个问题吗？</p>
<p>谢谢。</p>
