[Tutor] tkinter/sqlite3?

[Peter Otten]

Jim Byrnes wrote:

> I am working with Python 3.4.3 on Ubuntu 14.04.
> 
> I am learning tkinter so I decided to rewrite a program I had written in
> pythoncard in tkinter. I found that a sqlite3 SELECT statement that
> works in pythoncard throws an error in tkinter and am wondering why?
> 
> # Fill the accounts listbox from the passwords database
> def fill_accounts_lb(category):
>    conn = sqlite3Connect()
>    cur = conn.cursor()
>    #cur.execute('''SELECT Account FROM pwds WHERE Category=? ORDER BY
> Account COLLATE NOCASE''', category) (1)
>    cur.execute('''SELECT Account FROM pwds WHERE Category='%s' ORDER BY
> Account COLLATE NOCASE'''
>      % category)
>    result = [row[0] for row in cur.fetchall()]
>    clearListbox()
>    for account in result:
>      lb_accounts.insert(END, account)
>    conn.close()
> 
> (1)
> Exception in Tkinter callback
> Traceback (most recent call last):
>    File "/usr/lib/python3.4/tkinter/__init__.py", line 1536, in __call__
>      return self.func(*args)
>    File "tk_pwds.py", line 22, in rbCall
>      fill_accounts_lb('WebSites')
>    File "tk_pwds.py", line 56, in fill_accounts_lb
>      cur.execute('''SELECT Account FROM pwds WHERE Category=? ORDER BY
> Account COLLATE NOCASE''', category)
> sqlite3.ProgrammingError: Incorrect number of bindings supplied. The
> current statement uses 1, and there are 8 supplied.
> 
> I cut the working statement from pythoncard and pasted it into tkinter
> and am curious why it works in the pythoncard version and not the
> tkinter version. I'm not sure where it is coming up with the 8 bindings
> it said are supplied?

category is probably a string with eight characters. As cursor.execute() 
expects a sequence as its second argument it misinterprets this string as 8 
distinct arguments. If the string were of length one your code would 
(accidentally) work; this might have been the case in your other script.
The correct fix is to put even a single value into a list or tuple:

cur.execute('''SELECT Account FROM pwds WHERE Category=? ORDER BY
Account COLLATE NOCASE''', [category])

If you choose the tuple remember that a one-tuple requires a trailing comma:

cur.execute('''SELECT Account FROM pwds WHERE Category=? ORDER BY
Account COLLATE NOCASE''', (category,))