Python 3 - Mysql.connector 如何删除用户名 = 变量的行？

Question

我在这个问题上遇到了很多麻烦，并且一直在检查整个堆栈溢出和网络以寻找答案。我正在尝试替换此语句中的变量(“DELETE FROM where USERNAME ="+ [variable])

这是我的代码:

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()

感谢所有帮助。谢谢:)

编辑:这是我遇到的错误 mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'Hoyt' in 'where clause'

Answer 1

您通常不希望像那样通过连接变量来构建 sql 语句，而是使用参数化语句。 %s 是您想要变量数据的占位符(您将其作为第二个参数传递给 execute:

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

cursor.execute(sql_delete, sql_data)

如果您绝对必须为表名和列名使用可变数据(只要完全确定您确切知道该数据是什么)，但继续传递必须作为参数转义的数据:

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

cursor.execute(sql_delete, sql_data)

查看更多示例 here .此外，如果走第二条路线，请确保您了解 potential downsides .