Why do I get this syntax error using UPDATE?

Question

I have the following function:

 def Guardar_Modificaciones(self):
    self.usuario.modificar_usuario(self.ID, self.txtNombre.get(), self.txtHA.get(),self.txtIdentificacion.get(),self.txtEdad.get(),self.txtFNacimiento.get(),self.txtEscolaridad.get(),self.txtSS.get(),self.txtEtnia.get(),self.txtContacto.get(),self.txtDireccion.get())
    messagebox.showinfo("Insertar", 'Elemento insertado correctamente')

when I use this function to modify the database values:

ef modificar_usuario(self, ID, Nombre, HA, Identificacion, Edad, FNacimiento, Escolaridad, SS, Etnia, Contacto, Direccion):
    cur = self.cnn.cursor()
    sql='''UPDATE usuarios SET Nombre='{}', HA='{}', Identificacion='{}', Edad='{}', FNacimiento='{}', Escolaridad='{}', SS='{}', Etnia='{}', Contacto='{}',
    Direccion='{}' WHERE ID = {} '''.format(ID, Nombre, HA, Identificacion, Edad, FNacimiento, Escolaridad, SS, Etnia, Contacto, Direccion)
    cur.execute(sql)
    n=cur.rowcount
    self.cnn.commit()
    cur.close()
    return n

it shows me the following error:

raise get_mysql_exception( mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'CARRERA' in 'where clause'

and it is assumed that CARRERA is a data from the Direccion column, not the ID...

See https://stackoverflow.com/a/902417/476 for how to properly interpolate variables into a query. — deceze, Nov 11 '22 at 05:11

Eric Kong · Accepted Answer · 2022-11-11T04:31:12.890

0

i dont know if this the problem but you miss one " ' " in syntax

''"UPDATE usuarios SET Nombre='{}', HA='{}', Identificacion='{}', Edad='{}', FNacimiento='{}', Escolaridad='{}', SS='{}', Etnia='{}', Contacto='{}', Direccion='{}', WHERE ID = '{}' "''

and what the type your id? if int i dont think you have to use '{}' just {} because if you print that its gonna be where ID = '1' not where ID = 1

(edit) i think the problem in your format, you set the ID in Nombre, move the ID to end

sql="UPDATE usuarios SET Nombre='{}', HA='{}', Identificacion='{}', Edad='{}', FNacimiento='{}', Escolaridad='{}', SS='{}', Etnia='{}', Contacto='{}', Direccion='{}', WHERE ID = {}".format(Nombre, HA, Identificacion, Edad, FNacimiento, Escolaridad, SS, Etnia, Contacto, Direccion,ID)

edited Nov 11 '22 at 04:31

answered Nov 11 '22 at 03:20

Eric Kong

91
7

i did removed the ' ', but it did not work. I put three single quotes and one double at the beginning and at the end, like the example you give me, and the error changes, now It move the values one box to the right, and put the data of the viable name in ID and so on , since ID is supposed to be autoincrement... – Adrián Guerao Nov 11 '22 at 04:12
''' "UPDATE usuarios SET Nombre='{}', HA='{}', Identificacion='{}', Edad='{}', FNacimiento='{}', Escolaridad='{}', SS='{}', Etnia='{}', Contacto='{}', Direccion='{}' WHERE ID = {}" ''' – Adrián Guerao Nov 11 '22 at 04:14
version for the right syntax to use near '"UPDATE usuarios SET Nombre='1', HA='OSCAR', Identificacion='2145', Eda' at line 1 and it should be: SET Nombre='OSCAR', HA='2145'... and so on... – Adrián Guerao Nov 11 '22 at 04:18
maybe you wrong when sent the id @AdriánGuerao – Eric Kong Nov 11 '22 at 04:20
@AdriánGuerao check my edit, i think thats the problem – Eric Kong Nov 11 '22 at 04:26
part of the sql code: CREATE TABLE `usuarios` ( `ID` INT(10) NOT NULL AUTO_INCREMENT, `Nombre` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci', `HA` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci', – Adrián Guerao Nov 11 '22 at 04:47
if i remove ID from format, it tells me that there are not enough positional arguments... – Adrián Guerao Nov 11 '22 at 04:49
I solved it thanks to you, thaks for the help!! – Adrián Guerao Nov 11 '22 at 06:19

Why do I get this syntax error using UPDATE?

1 Answers1