Python smartsql compile insert statement

Question

I want to use sqlbuilder (https://sqlbuilder.readthedocs.io/en/latest/) library for building native queries to sqlite. There is my code for inserting data:

import sqlite3
from sqlbuilder.smartsql import Q, T
from sqlbuilder.smartsql.dialects.sqlite import compile


if __name__ == '__main__':
    connection = sqlite3.connect(':memory:')

    with connection:
        connection.execute('CREATE TABLE temp (t TEXT, i INTEGER)')

        insert = compile(Q(T.temp).insert({T.temp.t: 'text', T.temp.i: 1}))
        sql, params = insert

        connection.execute(
            sql, params
        )

    connection.close()

This code does not work, because compile produces incorrect sql and params for sqlite: ('(?, (?, ?))', ['INSERT INTO "temp" ("i", "t") VALUES (%s, %s)', 1, 'text']), and I got the error: sqlite3.OperationalError: near "(": syntax error

Interesting, there is not problems with compiling and executing select statements.

UPDATE:

Code for select statements and it's work:

import sqlite3
from sqlbuilder.smartsql import Q, T
from sqlbuilder.smartsql.dialects.sqlite import compile

if __name__ == '__main__':
    connection = sqlite3.connect(':memory:')

    with connection:
        connection.execute('CREATE TABLE temp (t TEXT, i INTEGER)')

        select = compile(Q(T.temp).fields('*'))
        print(select)  # ('SELECT * FROM `temp`', [])
        sql, params = select

        connection.execute(
            sql, params
        )

    connection.close()

Answer 1

Answer emended

From the python doc for sqlite3 APIs:

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see https://xkcd.com/327/ for humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.) For example:

# Never do this -- insecure!
symbol = 'RHAT' c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',) c.execute('SELECT * FROM stocks WHERE symbol=?', t)

The returned value of insert` `('(?, (?, ?))', ['INSERT INTO "temp" ("i", "t") VALUES (%s, %s)', 1, 'text']) indicates sqlbuilder is trying to take this advice. What remains is to how to do the string interpolation to put it into valid sqlite syntax. Turns out the result argument to the Q constructor will do just that.

insert = Q(T.temp,result=Result(compile=compile)).insert({T.temp.t: 'text', T.temp.i: 1}) will return a tuple that is "SQL ready", ie: ('INSERT INTO `temp` (`i`, `t`) VALUES (?, ?)', [1, 'text']). Now you see the '%s' have been replaced by '?'. Don't forget to import Result.