Python sqlite3 operational error when string formatting?

Question

I have this small decorator function where I am going to find an unknown number of entries in a table:

def Deco(func):
     func
     conn = sqlite3.connect('/home/User/vocab_database/vocab.db')
     with conn:
         cur = conn.cursor()
         cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
         total = cur.fetchall()
         print "You have %d tables " % len(total)

       ## this line below, is where I wanted to use a formatted string ##
         cur.execute("SELECT * FROM %s") % total[0]
         entries = cur.fetchall()
         print "You have %d entries" % len(entries)

Then I get this error message:

Traceback (most recent call last):
  File "./database_trial.py", line 19, in <module>
    class Vocab:
  File "./database_trial.py", line 25, in Vocab
    @Deco        
  File "./database_trial.py", line 15, in Deco
    cur.execute("SELECT * FROM %s") % total[0]
sqlite3.OperationalError: near "%": syntax error

Does sqlite3 only accept ? operators? Or is there something I'm mucking up?

Another name for using the mod operator on strings. And harder to confuse with the format() method. — Ignacio Vazquez-Abrams
– Ignacio Vazquez-Abrams, Commented Aug 20, 2012 at 2:40

Ignacio Vazquez-Abrams · Accepted Answer · 2012-08-20 02:54:45Z

3

You're trying to replace metadata, so unfortunately a parametrized query won't work. You must use interpolation or the like here, but make sure that the value is sanitized; this is a possible vector for a SQL injection.

cur.execute("SELECT * FROM %s" % (total[0],))

answered Aug 20, 2012 at 2:54

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

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

4 Comments

tijko Over a year ago

cur.execute("SELECT * FROM %s" % (total[0],)) returns error cur.execute("SELECT * FROM %s" % (total[0],)) sqlite3.OperationalError: near ")": syntax error

Ignacio Vazquez-Abrams Over a year ago

Then you have a problem in total[0].

tijko Over a year ago

I just had print out total[0] = (u'Words',) so I would have to use total[0][0]. To be clear though the ? wouldn't work in this case either way? Much Thanks for the help :)

tijko Over a year ago

Any field names, table names, etc ?

BrenBarn · Accepted Answer · 2012-08-20 02:36:25Z

2

In the line cur.execute("SELECT * FROM %s") % total[0], you are applying the % operator to the result of the cur.execute call. I think you want to do the substitution inside the call, e.g., cur.execute("SELECT * FROM ?", (total[0],)).

answered Aug 20, 2012 at 2:36

BrenBarn

253k39 gold badges421 silver badges392 bronze badges

2 Comments

BrenBarn Over a year ago

You omitted the comma. It's (total[0],), not (total[0]).

tijko Over a year ago

cur.execute("SELECT * FROM ?", (total[0],)) sqlite3.OperationalError: near "?": syntax error even with the comma.

Collectives™ on Stack Overflow

Python sqlite3 operational error when string formatting?

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related