Error Building SQLite Query Programmatically: TypeError: execute() takes 2 positional arguments but 3 were given

Question

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?

Answer 1

1. There is no ? in cs50 execute. If you send arguments in addition to the query, they are bind variables. The execute from page is not the execute used in cs50.
2. This execute() takes 2 positional arguments but 3 were given is the error given when the query argument has no bound variables, but there are additional arguments sent.
3. This is single-quote/double-quote/back-tick/parenthesis hell.

I tried this in 2015 pset8 and gave up (too much typing). And now that you know I'm a quitter, I'll give you advice. "Begin with the end in mind" (one of the 7 Habits). Write the query you want to execute. For example:

query = db.execute("SELECT * from places where (postal_code like 'Cambridge%' OR place_name like 'Cambridge%') OR (postal_code like 'MA%' OR place_name like 'MA%')")

Then back into it. Which " are significant? Which ' are significant? And then there's %. Maybe python format will help?

Methinks you'll have to create the query without bound variables.

Answer 2

As DinoCoderSaurus states above, the execute method that accepts '?' as parameters detailed here is different to the CS50 module method.

I've successfully implemented the standard library sqlite3 module to perform this database operation. This allows me to programatically add my parameters to a list, one for every '?' added to my query, then make a tuple from this to pass to the execute method. I've kept the CS50 module for everything else to avoid rewriting the prebuilt database operations.

import sqlite3

conn = sqlite3.connect('mashup.db')
dbsql = conn.cursor()

query = 'SELECT * FROM places WHERE place_name LIKE ?'
t = ('Cambridge',) # the extra comma makes this a tuple rather than a string in parenthesis.

result = dbsql.execute(query, t)

data = result.fetchall()

result.close()

Turns out there's no check50 for pset8, but I've only run into issues with check50 when I included modules I've had to install as per the aforementioned post and Dino's comment above. So I don't think there's any danger using a different module here.

Thanks for your assistance