MySQL Python connector error while inserting

Question

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

Duplicate of [MySQL parameterized queries](https://stackoverflow.com/questions/775296/mysql-parameterized-queries) — esqew, Aug 04 '22 at 10:18

Eugene Naydenov · Accepted Answer · 2022-08-05T11:54:29.687

Just parameterize 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])

But what if I want to insert one by one? why will it not work? — Armonia, Aug 04 '22 at 10:56
Inserting one by one is also acceptable, but makes more calls to the database, so if you already have a collection of items to insert, it's better to insert a batch using one query to the database to reduce the load on it. — Eugene Naydenov, Aug 04 '22 at 14:27

MySQL Python connector error while inserting

1 Answers1