How to format and build query strings in Python Sqlite?

Question

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)' % (date, title, col3, col4, int5, int6)
print query
c.execute(query)

Problem: it won't work for example if title contains a quote ".

```
query = 'insert into events (date, title, col3, col4, int5, int6) 
             values(?, ?, ?, ?, ?, ?)'
c.execute(query, (date, title, col3, col4, int5, int6))
```
Problem: in solution 1., we could display/print the query (to log it); here in solution 2. we can't log the query string anymore because the "replace" of each ? by a variable is done during the execute.
Another cleaner way to do it? Can we avoid to repeat ?, ?, ?, ..., ? and have one single values(?) and still have it replaced by all the parameters in the tuple?

@georgexsh yes and also avoiding quotes and weird char problems — Basj
– Basj, Commented Mar 2, 2018 at 12:20

georgexsh · Accepted Answer · 2018-03-03 04:30:43Z

4

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

import sqlite3


class MyConnection(sqlite3.Connection):

    def cursor(self):
        return super().cursor(MyCursor)


class MyCursor(sqlite3.Cursor):

    def execute(self, sql, parameters=''):
        print(f'statement: {sql!r}, parameters: {parameters!r}')
        return super().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)

edited Mar 3, 2018 at 4:30

answered Mar 2, 2018 at 20:08

georgexsh

16.8k3 gold badges45 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CL. · Accepted Answer · 2018-03-02 18:31:14Z

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

answered Mar 2, 2018 at 18:31

CL.

182k18 gold badges241 silver badges282 bronze badges

Collectives™ on Stack Overflow

How to format and build query strings in Python Sqlite?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related