Sqlite3 syntax error

Question

Does anyone know why you made that mistake? before using the androidhelper was working, I realized I was returning a different format, so I turned it into a string equal to a common input:

def insert_password( service, username, password ):#Insere um serviço, com usuário e senha
    cursor.execute(f'
        INSERT INTO users (service, username, password)
        VALUES ('{service}', '{username}', '{password}')
    ')
    conn.commit()


while True:

    if op == 'i':# Caso o usuário deseja utilizar a função de inserir um serviço novo
        service = str(droid.dialogGetInput(" Qual é o serviço? "))
        username = str(droid.dialogGetInput(" Qual é o username? "))
        password = str(droid.dialogGetPassword(" Qual é a senha? "))
        insert_password( service, username, password)
        os.system('clear')
        droid.makeToast(' Salvo com successo')
        pausa()

output:

sqlite3.Operationalerror: near "Novoservice" :syntax error

Answer 1

It is not a good practice to "mount" your query string using strings literais formatadas.

The method Cursor.execute() allows the use of placeholders for that purpose, look at this:

Solution #1 (Qmark style):

def insert_password( service, username, password ):
    cursor.execute('INSERT INTO users (service, username, password) '
                   'VALUES ( ?, ?, ? )',
                   (service, username, password))

Solution #2:

def insert_password( service, username, password ):
    cursor.execute('INSERT INTO users (service, username, password) '
                   'VALUES ( :service, :username, :password )',
                   (service, username, password))

Solution #3 (named style):

def insert_password( **kwargs ):
    cursor.execute('INSERT INTO users (service, username, password) '
                   'VALUES ( :service, :username, :password )',
                   kwargs)

# Chamada da Função
insert_password(service='foobar3', username='fulano3', password='12345678')

Functional Example:

import sqlite3

def insert_password_v1( cursor, service, username, password ):
    cursor.execute('INSERT INTO users (service, username, password) '
                   'VALUES ( ?, ?, ? )',
                   (service, username, password))

def insert_password_v2( cursor, service, username, password ):
    cursor.execute('INSERT INTO users (service, username, password) '
                   'VALUES ( :service, :username, :password )',
                   (service, username, password))

def insert_password_v3( cursor, **kwargs ):
    cursor.execute('INSERT INTO users (service, username, password) '
                   'VALUES ( :service, :username, :password )',
                   kwargs)

conn = sqlite3.connect('foobar.db')
cur = conn.cursor()

cur.execute("CREATE TABLE IF NOT EXISTS users ( service TEXT, username TEXT, password TEXT );")

insert_password_v1( cur, 'serviço1', 'fulano1', '12345678' )
insert_password_v2( cur, 'serviço2', 'fulano2', 'aeiou' )
insert_password_v3( cur, service='serviço3', username='fulano3', password='qwerty' )

conn.commit()
conn.close()

Validating:

$ sqlite3 foobar.db 
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .headers ON
sqlite> .mode column
sqlite> SELECT * FROM users;
service     username    password  
----------  ----------  ----------
serviço1   fulano1     12345678  
serviço2   fulano2     aeiou     
serviço3   fulano3     qwerty    
sqlite>