Mailing List Archive: Silly/crazy problem with sqlite

Silly/crazy problem with sqlite

Nov 24, 2023, 12:49 PM

Post #1 of 4 (100 views)

This is driving me crazy, I'm running this code:-

#!/usr/bin/env python3
#
#
# Show the electric fence history, default to last 24 hours
#
import sqlite3
import datetime
import sys

today = datetime.datetime.now()
today = str(today)
x = str(today[0:10])
print(x)

fdb = sqlite3.connect("/home/chris/.share/newbourne.db")
cr = fdb.cursor()

sql = "SELECT * FROM fence where datetime LIKE ?"
cr.execute(sql, ('%' + "2023-11" + '%'))
rv = cr.fetchall()
for d in rv:
print(d)
fdb.commit()
fdb.close()

Forget about the 'today =' bits, they no longer do anything.

When I run the above I get:-

chris@esprimo$ fence.py
2023-11-24
Traceback (most recent call last):
File "/home/chris/dev/bin/fence.py", line 19, in <module> cr.execute(sql, ('%' + "2023-11" + '%')) sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 9 supplied.
chris@esprimo$

It's treating the "2023-11" plus % at each end as separate variables to
the binding, this is crazy! I've done similar elsewhere and it works
OK, what on earth am I doing wrong here? It has to be something very
silly but I can't see it at the moment.

--
Chris Green
·
--
https://mail.python.org/mailman/listinfo/python-list

Re: Silly/crazy problem with sqlite [ In reply to ]

python-list at python

Nov 24, 2023, 1:10 PM

Post #2 of 4 (100 views)

Permalink

Chris Green <cl@isbd.net> wrote:
> This is driving me crazy, I'm running this code:-

OK, I've found what's wrong:-

> cr.execute(sql, ('%' + "2023-11" + '%'))

should be:-

cr.execute(sql, ('%' + x + '%',) )

I have to say this seems very non-pythonesque to me, the 'obvious'
default simply doesn't work right, and I really can't think of a case
where the missing comma would make any sense at all.

Maybe I've had too much to eat and drink tonight! :-)

--
Chris Green
·
--
https://mail.python.org/mailman/listinfo/python-list

Re: Silly/crazy problem with sqlite [ In reply to ]

python-list at python

Nov 25, 2023, 7:01 AM

Post #3 of 4 (98 views)

Permalink

Stefan Ram <ram@zedat.fu-berlin.de> wrote:
> Chris Green <cl@isbd.net> writes:
> >I have to say this seems very non-pythonesque to me, the 'obvious'
> >default simply doesn't work right, and I really can't think of a case
> >where the missing comma would make any sense at all.
>
> |6.15 Expression lists
> ...
> |an expression list containing at least one comma yields a tuple.
> ...
> The Python Language Reference, Release 3.13.0a0;
> Guido van Rossum and the Python development team;
> October 10, 2023.
>
I wasn't meaning that it wasn't correct Python, more that doing the
obvious doesn't work which, in Python, it usually does in my
experience.

The error message could be a bit more helpful too, maybe one of those
"... did you mean ....?" ones could point one in the right direction.

--
Chris Green
·
--
https://mail.python.org/mailman/listinfo/python-list

Re: Silly/crazy problem with sqlite [ In reply to ]

python-list at python

Nov 25, 2023, 11:32 AM

Post #4 of 4 (100 views)

Permalink

On 11/24/23 14:10, Chris Green via Python-list wrote:
> Chris Green <cl@isbd.net> wrote:
>> This is driving me crazy, I'm running this code:-
>
> OK, I've found what's wrong:-
>
>> cr.execute(sql, ('%' + "2023-11" + '%'))
>
> should be:-
>
> cr.execute(sql, ('%' + x + '%',) )
>
>
> I have to say this seems very non-pythonesque to me, the 'obvious'
> default simply doesn't work right, and I really can't think of a case
> where the missing comma would make any sense at all.

as noted, the comma makes it a tuple.

this might be a case where rewriting as an f-string makes it just a
little more readable, since the syntax will make it look like there's a
single string followed by a comma - the addition just makes it look less
clear to my eyes:

cr.execute(sql, (f'%2023-11%', ))

cr.execute(sql, (f'%{x}%', ))

--
https://mail.python.org/mailman/listinfo/python-list