Using Python variables within sqlite3 statement

Question

I would like to use python variable within a sql request.

ss_length = 15

I tried this :

cursor = db.execute("SELECT SUBSTR(timestamp, 1, %s), count(SUBSTR(timestamp, 1, %s)) FROM tweets GROUP BY SUBSTR(timestamp, 1, %s) ORDER BY count(SUBSTR(timestamp, 1, %s))" % ss_length, ss_length, ss_length, ss_length)

and this

cursor = db.execute("SELECT SUBSTR(timestamp, 1, ?), count(SUBSTR(timestamp, 1, ?)) FROM tweets GROUP BY SUBSTR(timestamp, 1, ?) ORDER BY count(SUBSTR(timestamp, 1, ?))", ss_length, ss_length, ss_length, ss_length)

No one of the two worked for me.

What's wrong with them ?

Bryan Oakley · Accepted Answer · 2014-05-30 14:39:31Z

2

I used this and it worked, thank you all for your responses:

cursor = db.execute("""SELECT SUBSTR(timestamp, 1, ?), count(SUBSTR(timestamp, 1, ?)) 
                       FROM tweets 
                       GROUP BY SUBSTR(timestamp, 1, ?) 
                       ORDER BY count(SUBSTR(timestamp, 1, ?))
                    """, (ss_length, ss_length, ss_length, ss_length))

edited May 30, 2014 at 14:39

Bryan Oakley

389k53 gold badges582 silver badges739 bronze badges

answered May 30, 2014 at 13:39

4m1nh4j1

4,47616 gold badges68 silver badges108 bronze badges

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

3 Comments

4m1nh4j1 Over a year ago

I am not sure why I am obliged to add the last "," after ss_length. Without it, the query don't work.

4m1nh4j1 Over a year ago

Yes, it worked without the last comma, but first time I removed it it haven't worked for me.. weird. I'll correct my answer.

Bryan Oakley Over a year ago

In my experience, embedded SQL queries are much easier to manage when split across multiple lines. I took the liberty of reformatting the example, which I think makes it easier to understand.

Collectives™ on Stack Overflow

Using Python variables within sqlite3 statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related