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()

DinoCoderSaurus · Accepted Answer · 2018-09-11T11:21:18.363

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.

edited Sep 11 '18 at 11:21

answered Sep 10 '18 at 18:12

DinoCoderSaurus

6,110
2
10
15

You are not right about `compile` and instance of `Q`, please see the sqlbuilder doc. And yes, `compile` returns a tuple with sql and array of params, In question I provided this tuple for compiling insert statement, and there is the problem - sql in this tuple is not correct for sqlite, but I dont know why. I make an update for question and provides compiling and executing select statement - and compiling select statement is work – Alexey Nikitin Sep 11 '18 at 04:26
Sorry about that! Thank you for pointing out the inaccuracies in the answer. I have edited the answer with correction. – DinoCoderSaurus Sep 11 '18 at 11:22
Thanks! But main question is why need to use `result`? And I tried using `result` for select statements - not working (`sql, params = Q(T.temp, result=Result(compile=compile)).fields('*')` raises `NotImplementedError`) – Alexey Nikitin Sep 11 '18 at 18:11
The reason to use `result` on INSERT is because `compile` does not give proper INSERT syntax without it. The SELECT does not work with `sql, params =`. It does work with `select =` [some var name]. I do not know why it gives `NotImplementedError`. – DinoCoderSaurus Sep 11 '18 at 19:38
Ok, Thanks for solving my problem! – Alexey Nikitin Sep 12 '18 at 03:10

Python smartsql compile insert statement

1 Answers1