Conditional query with sqlite3 python

Question

This code snippet is part of a telegram bot I am currently developing. I used the PyTelegramBotAPI package and used the @bot.callback_query_handler() to handle callback queries from users. I then created a function that prints out the last entry from a database for that particular user using the following code:

def splitbill(user):
    row = c.execute('SELECT * FROM database WHERE user = (?) ORDER BY datetime DESC LIMIT 1', user).fetchall()
    print(row[0])

This returned and error stating ValueError: parameters are of unsupported type

def splitbill(user):
    row = c.execute('SELECT * FROM database WHERE user = (?) ORDER BY datetime DESC LIMIT 1', (user,)).fetchall()
    print(row[0])

I googled and came upon this solution by using (user,) instead of (user). But I have no idea why it worked. Can someone enlighten me? Thank you!

The second argument needs to be a sequence of some kind (list, tuple, etc) of values to bind to parameters in the query. — Shawn
– Shawn, Commented Jan 21, 2019 at 3:01

kennyvh · Accepted Answer · 2019-01-21 03:08:41Z

2

The reason that (user,) works is because the execute method is expecting a tuple and when you pass (user) without the comma, python interprets it as just user.

You can verify this pretty quickly in a python shell:

>>> a = 'howdy'
>>> tuple = (a, )      # note the comma
>>> not_a_tuple = (a)  # note the lack of comma
>>>
>>> print(tuple)       
('howdy',)
>>> print(not_a_tuple)
howdy
>>>
>>> type(tuple)
<class 'tuple'>
>>> type(not_a_tuple)
<class 'str'>

answered Jan 21, 2019 at 3:08

kennyvh

2,8641 gold badge21 silver badges28 bronze badges

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

Comments

Benton Snyder · Accepted Answer · 2019-01-21 03:00:08Z

0

(user) is interpreted as an expression whereas (user,) is always a tuple.

answered Jan 21, 2019 at 3:00

Benton Snyder

1663 bronze badges

Comments

saifuddin778 · Accepted Answer · 2019-01-21 03:06:08Z

0

In Python world, single-element tuples are declared with , at the end. For example:

>>> p=(1)
>>> print(type(p))
<type 'int'> #--note that this is taken as an int, irrespective of the braces.
>>> q=(1,)
>>> print(type(q))
<type 'tuple'>

The function c.excute probably accepts a tuple of placeholders, which is the reason why your passed in (user) parameter wasn't considered a tuple. Good question though.

answered Jan 21, 2019 at 3:06

saifuddin778

7,33816 gold badges54 silver badges72 bronze badges

Collectives™ on Stack Overflow

Conditional query with sqlite3 python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related