Python smartsql编译插入语句

Question

我想使用sqlbuilder（https://sqlbuilder.readthedocs.io/en/latest/）库为sqlite构建本机查询。下面是我插入数据的代码：

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

此代码不起作用，因为compile为sqlite生成不正确的sql和参数： ('(?, (?, ?))', ['INSERT INTO "temp" ("i", "t") VALUES (%s, %s)', 1, 'text'])，我得到了错误：sqlite3.OperationalError: near "(": syntax error

有趣的是，编译和执行select语句没有问题。你知道吗

更新：

select语句及其工作代码：

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

答案已修订

从sqlite3 API的python doc开始：

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)

返回值insert` `('(?, (?, ?))', ['INSERT INTO "temp" ("i", "t") VALUES (%s, %s)', 1, 'text'])表示sqlbuilder正在尝试接受此建议。剩下的是如何进行字符串插值以将其放入有效的sqlite语法中。结果证明result构造函数的Q参数就可以做到这一点。你知道吗

insert = Q(T.temp,result=Result(compile=compile)).insert({T.temp.t: 'text', T.temp.i: 1})将返回一个“SQL就绪”的元组，即：('INSERT INTO `temp` (`i`, `t`) VALUES (?, ?)', [1, 'text'])。现在您看到“%s”已被替换为“？”。别忘了导入Result。你知道吗