MySQL Python connector error while inserting-CodePudding

So I am trying to build a database in MySQL using python. I already created the database and created the table I want to use. it's called 'alliances'.

Now this table only has two columns, an ID column and a alliance_name column. The ID's auto increment , and the names I already have them as a list and would like to insert them into the table one by one. but it gives me an error:

ProgrammingError: 1054 (42S22): Unknown column 'alliance' in 'field list'

this is my code :

import mysql.connector

db = mysql.connector.connect(host = "localhost", user = "root", password = "#########", database = 'WANHUI')

mycursor = db.cursor()

# mycursor.execute("CREATE DATABASE WANHUI")  we already added to the db

# mycursor.execute("CREATE TABLE alliances (ID INT PRIMARY KEY AUTO_INCREMENT, alliance_name VARCHAR(100) NOT NULL) ")

mini_list = alliance_list[:30]   # we only need the first 30

for alliance in mini_list: 
    mycursor.execute("INSERT INTO alliances (alliance_name) VALUES (alliance)")
    db.commit()

Why does it say no 'alliance' column if i do specify in the query to insert into the alliance_name and that 'alliance' is the string that I wish to insert ??

EDIT: I already tried this version:

for alliance in mini_list: 
    mycursor.execute("INSERT INTO alliances (alliance_name) VALUES (%s)", (alliance))
    db.commit()

But it gives me this error:

ProgrammingError: Could not process parameters: str(Iron Brotherhood Alliance), it must be of type list, tuple or dict

and even if I do :

mycursor.execute("INSERT INTO alliances (alliance_name) VALUES (%s)", (mini_list))
db.commit()

it would give me this :

ProgrammingError: Not all parameters were used in the SQL statement

CodePudding user response：

Just parametrize your query like this:

for alliance in mini_list: 
    mycursor.execute("INSERT INTO alliances (alliance_name) VALUES (%s)", alliance)
    db.commit()

Then you can go further and make a batch insert with one query, by prebuilding its statement string, instead of executing multiple calls to your database. Let's assume your mini_list is equal to something like this: ["aa", "bb", "cc"].

# You get a string "('aa'), ('bb'), ('cc')" here for `VALUES` part of your SQL query:
alliances = ", ".join(f"('{alliance}')" for alliance in mini_list)


# Now use the string `alliances` to build a query:
mycursor.execute(f"INSERT INTO alliances (alliance_name) VALUES {alliances}")
db.commit()

This will produce SQL: INSERT INTO alliances (alliance_name) VALUES ('aa'), ('bb'), ('cc') and will allow you to insert all alliances at once.

Another way and the easier one to achieve the same result is to use cursor.executemany(), that will produce the same query:

cursor.executemany("INSERT INTO alliances (alliance_name) VALUES (%s)",
                   [(alliance, ) for alliance in mini_list])