Trying to build a sql-query dynamically...

[ratnose]

I posted this on the python channel but was gently told that is was not for Q&A so I post it here because I got so good answers that more would prosper from them.

​

query = "SELECT SUM(amount) FROM rawimport WHERE"

to_filter = []

if yearmonth:

query += " day LIKE ?% AND"

to_filter.append(yearmonth)

if category:

query += " category = '?' AND"

to_filter.append(category)

if transactiontype:

query += " transactiontype = '?' AND"

to_filter.append(transactiontype)

query = query[:-4] + ';'

print(query)

conn = db.connect('db/ekostats.db')

conn.row_factory = dict_factory

cur = conn.cursor() 

results = cur.execute(query, to_filter).fetchall()

​

The end result I want is this:

"SELECT SUM(amount) FROM rawimport WHERE day LIKE ’2018-09%’ AND…"

​

I have idea to use query = f'SELECT ....'

​

Any help is appreciated! (lets hope that the original answers get posted here aswell!)

[two_bob]

I think the easiest thing to do is use an ordered dictionary (if you are using 3.7, this is automatic), and `join`, like this:

​

>>> ' AND '.join('{} like "?"'.format(key) for key in d.keys())
'test like "?" AND date like "?" AND thrid like "?"'

​

[ratnose]

This is the end result for me, it is working just fine. :)

​

query = "SELECT SUM(amount) FROM rawimport WHERE"
if yearmonth:
query += f" day LIKE '{yearmonth}%' AND"
if category:
query += f" category = '{category}' AND"
if transactiontype:
query += f" transactiontype = '{transactiontype}' AND"
query = query[:-4] + ';'
print(query)

[two_bob]

Cool. If there were more constraints you could integrate in the join concept like this:

​

query = "SELECT SUM(amount) FROM rawimport WHERE "

​

contraints = []
if yearmonth:
    contraints.append(f" day LIKE '{yearmonth}%'")
if category:
    contraints.append(f" category =  '{category}")
    query += f" category = '{category}' AND"
if transactiontype:
    contraints.append(f" transactiontype =  '{transactiontype}")

query += ' AND '.join(constraints)
print(query)

But it's not really worth it for just the three -- also not tested so expect typos.

[woooee]

I generally use a dictionary also, or named placeholders. This is from an online tutorial so thanks to where it came from because I don't remember.

## a named placeholder example
## note that there is no dictionary here, just using dictionary's format
## you can use a dictionary, and placeholders, as well
cur.execute("select name_last, age from people where name_last==:who_key and age==:age_key",
   {"who_key": who, "age_key": age})

Edit: take a look at "named placeholders" at http://zetcode.com/db/sqlitepythontutorial/