CODEWITHSUNDEEP
pythonmysql
Hoyt Felton
Hoyt Felton

Reputation: 43

Python 3 - Mysql.connector How do i delete a row where username = variable?

I have been having a lot of trouble with this issue and have been checking all over stack overflow and the web for an answer. I am trying to substitute a variable in this statement ("DELETE FROM where USERNAME =" + [variable])

Here is my code:

import mysql.connector as mysql

global usr_to_rmv
usr_to_rmv = 'Hoyt'

global from_table
from_table = "accounts"

global from_db
from_db = "users"

global from_column
from_column = "username"

cnx = mysql.connect(user = 'root', password = 'mag1c1234',
                      host = 'localhost',
                      database = from_db)

cursor = cnx.cursor()

# Uncomment to reset new data to 1
cursor.execute("ALTER TABLE accounts AUTO_INCREMENT = 1")

removeuser = ("DELETE FROM" + " " + from_table + " " + "WHERE" + " " + from_column + "=" + usr_to_rmv);

cursor.execute(removeuser)

query_1 = ("SELECT * FROM accounts");
cursor.execute(query_1)

for row in cursor.fetchall():
  print(row)

cnx.commit()
cursor.close()                 
cnx.close()

All help appreciated. Thanks :)

Edit: This the error i am encountering mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'Hoyt' in 'where clause'

Upvotes: 1

Views: 3931

Answers (1)

Carl Groner
Carl Groner

Reputation: 4359

You generally don't want to build your sql statement by concatenating variables like that, instead use parameratized statements. The %s is a placeholder where you want your variable data (which you pass as a second param to execute:

sql_delete = "DELETE FROM accounts WHERE username = %s"
sql_data = (usr_to_remove,)

cursor.execute(sql_delete, sql_data)

If you absolutely must use variable data for the table and column name you can (just be completely sure you know exactly what that data is), but continue to pass the data that must be escaped as a parameter:

sql_delete = "DELETE FROM " + from_table + " WHERE " + from_column + " = %s"
sql_data = (usr_to_remove,)

cursor.execute(sql_delete, sql_data)

See more examples here. Also, if going the second route make sure you understand the potential downsides.

Upvotes: 2

Related Questions