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

Question

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'

What error are you getting? What's the problem you're encountering specifically? — Dresden
– Dresden, Commented Apr 6, 2016 at 18:50
Strings usually have to be enclosed in single-quotes in SQL. — John Gordon
– John Gordon, Commented Apr 6, 2016 at 18:50
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'Hoyt' in 'where clause' — Hoyt Felton
– Hoyt Felton, Commented Apr 6, 2016 at 18:59

Carl Groner · Accepted Answer · 2016-04-06 20:04:10Z

2

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.

edited Apr 6, 2016 at 20:04

answered Apr 6, 2016 at 19:09

Carl Groner

4,4191 gold badge22 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Hoyt Felton Over a year ago

Well in the finished project the variables i have will be inputs. I do know this way works, but I am looking for a way to use the variables

Carl Groner Over a year ago

Added another example.

Hoyt Felton Over a year ago

After executing this code with your code i got this error: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line 1

Carl Groner Over a year ago

I updated my my example, When using only on parm you need to make sure that sql_data is a list (notice the the comma at the end of the sql_data = (usr_to_remove,) line.

Hoyt Felton Over a year ago

Thank you so much! That fixed the problem

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related