从数组向MySQL关联表插入数据时触发参数未用尽错误求助

尝试从数组向新建的Spelltrap表及已有的Cards表插入游戏王卡牌数据，通过外键关联两张表，但执行时触发错误：

mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

自查数据列表、循环逻辑均未发现问题，此前类似逻辑可正常插入数据。以下是代码及报错信息：

代码 #

Yugiohspelltraps = [("1st Movement Solo", "SPELL"), #list of data for card table
               ("A Wingbeat of Giant Dragon", "SPELL"),
               ("Polymerization", "SPELL"),
               ("Xyz Revenge", "SPELL"),
               ("Zombie World", "SPELL"),
               ("A Hero Emerges", "TRAP"),
               ("Blackbird Close", "TRAP")]

Yghcardsspelltrapdetails = [("Normal Spell", "If you control no monsters: Special Summon 1 Level 4 or lower Melodious monster from your hand or Deck. You can only activate 1 1st Movement Solo per turn. You cannot Special Summon monsters during the turn you activate this card, except Melodious monsters."), #list of data for spell trap table
                   ("Normal Spell", "Return 1 Level 5 or higher Dragon-Type monster you control to the hand, and if you do, destroy all Spell and Trap Cards on the field."),
                   ("Normal Spell", "Fusion Summon 1 Fusion Monster from your Extra Deck, using monsters from your hand or field as Fusion Material."),
                   ("Normal Spell", "If your opponent controls a face-up Xyz Monster that has Xyz Material: Target 1 Xyz monster in your Graveyard; Special Summon it, then detach 1 Xyz Material from a monster your opponent controls and attach it to that monster as an Xyz Material. You can only activate 1 Xyz Revenge per turn."),
                   ("Field Spell", "All monsters on the field and in the GYs become Zombie monsters. Neither player can Tribute Summon monsters, except Zombie monsters."),
                   ("Normal trap", "When an opponent's monster declares an attack: Your opponent chooses 1 random card from your hand, then if it is a monster that can be Special Summoned, Special Summon it. Otherwise, send it to the GY."),
                   ("Counter Trap", "When a monster your opponent controls activates its effect: You can send 1 face-up Blackwing monster you control to the GY; negate the activation, and if you do, destroy it, then, you can Special Summon 1 Black-Winged Dragon from your Extra Deck. If you control a Blackwing Synchro Monster or Black-Winged Dragon, you can activate this card from your hand.")]

Q3 = "INSERT INTO Cards (Cardname, Cardtype) VALUES (%s, %s)"#Query for inserting vaules into Cardname and Cardtype columns

Q11 = """CREATE TABLE Spelltrap (SpelltrapID int PRIMARY KEY, FOREIGN KEY(SpelltrapID) REFERENCES Cards(cardID), 
                    speedspell VARCHAR(50), 
                    stflavourtext VARCHAR(7000))""" #Creates the Card details table

Q12 = "INSERT INTO Spelltrap (speedspell, stflavourtext) VALUES (%s, %s)"#Query for inserting vaules into columns of spelltrap table

for x, Yugiohspelltrap in enumerate(Yugiohspelltraps):
    mycursor.execute(Q3, Yugiohspelltrap)
    last_id = mycursor.lastrowid
    mycursor.execute(Q12, (last_id,) + Yghcardsspelltrapdetails[x]) #loops through array of data and inserts them into columns from in both table linked correctly by primary and foregin key.

报错信息 #

mycursor.execute(Q12, (last_id,) + Yghcardsspelltrapdetails[x]) #loops through array of data and inserts them into columns from in both table linked correctly by primary and foregin key.
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement

错误原因 #

Spelltrap表中SpelltrapID是主键兼外键，关联Cards.cardID，但插入语句Q12只指定了speedspell和stflavourtext两个字段，却传入了3个参数（last_id + 列表中的两个元素），SQL语句仅用到2个参数，导致参数数量不匹配触发错误。同时SpelltrapID作为主键必须赋值，无法省略。

修改步骤 #

1. 更新插入语句Q12，加入SpelltrapID字段，匹配参数数量：

Q12 = "INSERT INTO Spelltrap (SpelltrapID, speedspell, stflavourtext) VALUES (%s, %s, %s)"

2. 循环逻辑无需改动，(last_id,) + Yghcardsspelltrapdetails[x]生成的3个参数正好对应SQL中的3个占位符。

修改后的循环代码：

for x, Yugiohspelltrap in enumerate(Yugiohspelltraps):
    mycursor.execute(Q3, Yugiohspelltrap)
    last_id = mycursor.lastrowid
    mycursor.execute(Q12, (last_id,) + Yghcardsspelltrapdetails[x])

额外注意点 #

• 确保Cards表的cardID是自增主键（AUTO_INCREMENT），否则lastrowid无法正确获取刚插入的ID值。
• SpelltrapID未设置自增，手动传入last_id符合外键关联要求，保证两张表数据的关联性。

内容的提问来源于stack exchange，提问作者Jedidiah Boateng