How To Format And Build Query Strings In Python Sqlite?

What is the most used way to create a Sqlite query in Python? query = 'insert into events (date, title, col3, col4, int5, int6) values('%s', '%s', '%s', '%s', %s, %s)' % (dat

Solution 1:

You should always use parameter substitution of DB API, to avoid SQL injection, query logging is relatively trivial by subclassing sqlite3.Cursor:

import sqlite3


classMyConnection(sqlite3.Connection):

    defcursor(self):
        returnsuper().cursor(MyCursor)


classMyCursor(sqlite3.Cursor):

    defexecute(self, sql, parameters=''):
        print(f'statement: {sql!r}, parameters: {parameters!r}')
        returnsuper().execute(sql, parameters)


conn = sqlite3.connect(':memory:', timeout=60, factory=MyConnection)
conn.execute('create table if not exists "test" (id integer, value integer)')
conn.execute('insert into test values (?, ?)', (1, 0));
conn.commit()

yields:

statement:'create table if not exists "test" (id integer, value integer)', parameters: ''statement:'insert into test values (?, ?)', parameters: (1, 0)

Solution 2:

To avoid formatting problems and SQL injection attacks, you should always use parameters.

When you want to log the query, you can simply log the parameter list together with the query string. (SQLite has a function to get the expanded query, but Python does not expose it.)

Each parameter markers corresponds to exactly one value. If writing many markers is too tedious for you, let the computer do it:

parms = (1, 2, 3)
markers = ",".join("?" * len(parms))