1. Forum
  2. Usenet
  3. COMP.LANG.PYTHON

  • Silly/crazy problem with sqlite

    From Chris Green@21:1/5 to All on Fri Nov 24 20:49:20 2023
    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
    ·

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Rimu Atkinson@21:1/5 to All on Sat Nov 25 10:49:06 2023


    I really can't think of a case
    where the missing comma would make any sense at all.


    That is pretty tricky, yes.

    The comma means it's a tuple. Without the comma, it's just a string with parenthesis around it, which is a string.

    PyDev console: starting.
    Python 3.9.15 (main, Oct 28 2022, 17:28:38) [GCC] on linux
    x = ('%' + "2023-11" + '%')
    x
    '%2023-11%'
    x = ('%' + x + '%',)
    x
    ('%%2023-11%%',)
    x.__class__.__name__
    'tuple'

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Chris Green@21:1/5 to Chris Green on Fri Nov 24 21:10:08 2023
    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
    ·

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Stefan Ram@21:1/5 to Chris Green on Sat Nov 25 09:45:12 2023
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Sibylle Koczian@21:1/5 to All on Sat Nov 25 14:34:37 2023
    Am 24.11.2023 um 22:49 schrieb Rimu Atkinson via Python-list:



    I really can't think of a case
    where the missing comma would make any sense at all.


    That is pretty tricky, yes.

    The comma means it's a tuple. Without the comma, it's just a string with parenthesis around it, which is a string.


    Placeholders for the parameters in an SQL command for
    sqlite3.execute(..) must always be given as dict or sequence. Even if
    it's just one parameter.

    Same thing with other database modules, it's given in PEP 249.

    HTH
    Sibylle

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Chris Green@21:1/5 to Stefan Ram on Sat Nov 25 15:01:26 2023
    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
    ·

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Mats Wichmann@21:1/5 to Chris Green via Python-list on Sat Nov 25 12:32:30 2023
    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}%', ))

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Stefan Ram@21:1/5 to Chris Green on Sat Nov 25 21:12:08 2023
    Chris Green <cl@isbd.net> writes:
    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.

    a = 1, 2, 3
    print( a )
    |(1, 2, 3)

    In the first line, one can see that tuples are written with commas.
    But when a tuple is printed, Python add parentheses. This may have
    led people to believe that parentheses mark tuples.

    Moreover, the empty tuple can be written as "()", i.e., a pair of
    parentheses without a comma. In this special case, the parentheses
    really are related to the tupleness of the expression!

    However, a single expression (and nothing more) in parentheses
    does not evaluated to a tuple (unless the single expression
    already evaluates to a tuple even without the parens).

    Python teachers should take care to explain these points when
    they introduce tuple notation in their classes!

    (And even when you already know all of this, you might sometimes
    forget to type this comma and sometimes might stare at the error
    message and do not immediately see where the problem is located.)

    In mathematics, "(1,2)" also denotes a pair (sometimes written
    <1,2>). The ambiguity between the reading of "(1)" as a term
    with the value of "1" and as a term the value of which is the
    1-tuple containing the value "1" is observed rarely in math
    because mathematics is sometimes less formal than programming
    languages and 1-tuples of values can often be identified
    with the value itself. So, in mathematics one rarely uses
    1-tuples and very rarely needs to write them down.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Thomas Passin@21:1/5 to Rimu Atkinson via Python-list on Sat Nov 25 18:40:58 2023
    On 11/24/2023 4:49 PM, Rimu Atkinson via Python-list wrote:



    I really can't think of a case
    where the missing comma would make any sense at all.


    That is pretty tricky, yes.

    The comma means it's a tuple. Without the comma, it's just a string with parenthesis around it, which is a string.

    PyDev console: starting.
    Python 3.9.15 (main, Oct 28 2022, 17:28:38) [GCC] on linux
    x = ('%' + "2023-11" + '%')
    x
    '%2023-11%'
    x = ('%' +  x + '%',)
    x
    ('%%2023-11%%',)
    x.__class__.__name__
    'tuple'

    To make it very clear in your code so that you are reminded next time
    you want to re-use it, you could write

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

    Probably the param value will actually use a variable instead of the
    hard-coded value in the example. So use an f-string, because it's more
    readable and easier to get right:

    date = ... # Where ever the actual date value comes from
    param = f'%{date}%'
    cr.execute(sql, (param,))

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)