[Tutor] tkinter/sqlite3?

[Jim Byrnes]

On 08/26/2016 02:03 AM, Peter Otten wrote:
> 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:

See my reply to Alan as to my guess why the pythoncard script worked.

> 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,))
>

Thanks for showing me the correct way to write that statement.

Regards,  Jim