MySQL Python连接器更新未更新

Question

我在Python中使用MySQL，但我的更新函数没有更新行，我无法理解消息错误

def atualizaCartelaTabela(campo,valor,jogador):
    try:
        connection = mysql.connector.connect(host='localhost',
                                     user='root',
                                     password='root',
                                     database='modular')
        cursor = connection.cursor() 
        sql = """UPDATE Cartela 
                SET %s = %s
                WHERE PONTOS = %s"""
        atualiza = (campo,valor,jogador)
        cursor.execute(sql,atualiza)
    except Error as e:
        print("Erro ao atualizar campos da tabela Cartela ->", e)
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

def atualizaCartelaTabela(campo,valor,jogador):
    try:
        connection = mysql.connector.connect(host='localhost',
                                     user='root',
                                     password='root',
                                     database='modular')
        cursor = connection.cursor() 
        sql = """UPDATE Cartela 
                SET %s = %s
                WHERE PONTOS = %s"""
        atualiza = (campo,valor,jogador)
        cursor.execute(sql,atualiza)
    except Error as e:
        print("Erro ao atualizar campos da tabela Cartela ->", e)
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

atualizaCartelaTabela('Um',2,'Jogador 1')

我得到的错误是：

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 ''Um' = 2 WHERE PONTOS = 'Jogador 1'' at line 2

这是用于创建表（工作）的函数：

def criaTabelaCartela():
    try:
        connection = mysql.connector.connect(host='localhost',
                                     user='root',
                                     password='root',
                                     database='modular')
        cursor = connection.cursor()  
        cursor.execute("CREATE TABLE Cartela (PONTOS VARCHAR(10), Um int(2), Dois int(1), Tres int(1), Quatro int(1), Cinco int(1), Seis int(1), Full int(1), SequenciaBaixa int(1), Trinca int(1), Quadra int(1), SequenciaAlta int(1), Yahtzee int(1), PontuaçãoFinal int(1))")
        connection.commit()
    except Error as e:
        print("Erro ao criar tabela Cartela ->", e)
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()

Answer 1

我猜int - 2是这里的问题。 试试这个：

将您的SQL-query设置为：

sql = """
    UPDATE Cartela 
    SET %s = CAST(%s AS int)
    WHERE PONTOS = %s
"""

在调用方法的地方atualizaCartelaTabela将其设置为：

atualizaCartelaTabela('Um','2','Jogador 1')

Answer 2

查看以下3条语句，了解导致错误的原因：

sql = """UPDATE Cartela 
        SET %s = %s
        WHERE PONTOS = %s"""
atualiza = (campo,valor,jogador)
cursor.execute(sql,atualiza)

在更新查询中放入SET X = "y"时，列名X无法参数化。只有值

因此campo的列名应该直接在SQL字符串中替换，而不是在execute期间替换，但请注意这是不安全的。将第一个参数更改为{}表示形式，并将值保留为%s，以便它们是execute()的参数：

sql = """UPDATE Cartela 
        SET {} = %s
        WHERE PONTOS = %s""".format(campo)  # unsafe
atualiza = valor, jogador
cursor.execute(sql, atualiza)

---

作为安全检查，请确保campo的值是表中一列的名称，但PONTOS或任何导入键列除外。最好检查它是否位于您希望允许更新的列的白名单中