I'm trying to build a sqlite query programmatically by building a string that I pass to db.execute.
query = "SELECT * FROM places WHERE postal_code LIKE :q"
result = db.execute(query, q=q)
This works fine, but now I want to do this by iterating over a list of words. Something like...
query = "SELECT * FROM places WHERE"
words = ['Cambridge','MA']
for word in words:
query = query + "postal_code LIKE {word} OR place_name LIKE {word} OR"
I don't know how many words there will be, so I need to build my parameters as I go.
This page says I can input my parameters like this example:
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
But when I run this, which is the simplest version of the syntax I can write, I get an error:
t = ('Cambridge',)
result = db.execute("SELECT * FROM places WHERE postal_code LIKE ?", t)
result = db.execute("SELECT * FROM places WHERE postal_code LIKE ?", t) TypeError: execute() takes 2 positional arguments but 3 were given
I've tried as many variations as I can think of, like placing (?) instead of ? or
t = ('Cambridge')
result = db.execute('SELECT * FROM places WHERE postal_code LIKE ?', t)
I've found reference to the issue here but the accepted answer is 'turns out I needed to use a colon', i.e. my working example:
query = "SELECT * FROM places WHERE postal_code LIKE :q"
result = db.execute(query, q=q)
I've tried something like the following, but I don't see this approach succeeding as I'm mixing variables with strings:
query = "SELECT * FROM places WHERE postal_code LIKE :q"
params = 'q=q'
result = db.execute(query, params)
How do I work around this?