sqlite3.OperationalError: near " ": syntax error

Question

Hi all right with you? I hope so, so I'm developing a commercial application, however this is giving an error in sql where it speaks: sqlite3.OperationalError: near "José": syntax error as if there was an error in the blank space of the phrase

cursor.execute(f"""update funcionarios
 set nome = {nome}, senha = {senha}, telefone = {telefone},
 endereco = {endereco}, anotacoes = {anotacao}
 where cpf = {pesq}
 """)

Name "ErlonJunior" without spaces works, but "José Street" with space returns this error:

(IMAGE) Name "ErlonJunior" without spaces works, but "José Street" with space returns this error

Error sqlite3.OperationalError: near "José": syntax error:

(IMAGE) Error sqlite3.OperationalError: near "José": syntax error

• Lukasz Szozda: Is senha = {senha} string interpolation in Python? If yes then you will get something like UPDATE tab SET col = Rua Jose obvious error because you really want UPDATE tab set col = 'Rua Jose'. I suggest to use parametrized query to avoid possible SQL Injection attacks.
• Erlon Dantas: @LukaszSzozda The parameter is as string, is giving error is in the spacing of the string, I already checked the type

Answer 1

Don't format your SQL string by hand (in your case with f-strings), it's error prone. You could use parameterized queries in SQLite3:

import sqlite3
with sqlite3.connect(":memory:") as con:
 cur = con.cursor()
 cur.execute('CREATE TABLE funcionarios (id integer PRIMARY KEY, nome text)')
 cur.execute('INSERT INTO funcionarios (nome) VALUES (:nome)',
 {'nome': 'Rua Jose'})
 cur.execute('UPDATE funcionarios SET nome = :nome WHERE id = 1',
 {'nome': 'Santa Maria'})
 cur.execute('SELECT * FROM funcionarios')
 print(cur.fetchall())

This prints:

[(1, 'Santa Maria')]

• Erlon Dantas: The problem is when I update, formatted in several ways, and continues the error, the problem is that in the update it is not accepting "white space" between the strings
• Andrej Kesely: @ErlonDantas You can use named parameters in any query. I updated my answer to use UPDATE command with named parameter.
• Erlon Dantas: It worked, oh SQL god @Andrej Kesely Dude, thank you very much, I tried a lot to solve this, thank you very much