Tuple problem with parameterized SQLite query

Question

I'm working on a telethon-based telegram chatbot that can query a customer db given a last name in the following format: /search thompson, but am having some trouble using the fill function to keep the queries safe.

I'm getting one of two errors - one is tuple index out of range when accessing query directly, and Incorrect number of bindings supplied. The current statement uses 1, and there are 8 supplied. when I use join (method 2 above). What am I doing incorrectly here?

UPDATE - this is the full error traceback followed by the code:

Traceback (most recent call last):
  File "/Users/.../.../.../.../script.py", line 155, in select
    test_message = create_message_select_query(res)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/.../.../.../.../script.py", line 115, in create_message_select_query
    lname = i[1]
            ~^^^
IndexError: tuple index out of range

And associated code:

# create message listing matches
def create_message_select_query(ans):
    text = ""
    for i in ans:
        id = i[0]
        lname = i[1]
        fname = i[2]
        creation_date = i[3]
        text += "<b>"+ str(id) +"</b> | " + "<b>"+ str(lname) +"</b> | " + "<b>"+ str(fname)+"</b> | " + "<b>"+ str(creation_date)+"</b>\n"
    message = "Information about customers:\n\n"+text
    return message

@client.on(events.NewMessage(pattern="(?i)/search"))

async def select(event):
    try:
        # Get the sender of the message
        sender = await event.get_sender()
        SENDER = sender.id
        
        # Get the text of the user AFTER the /search command
        list_of_words = event.message.text.split(" ")
        # accessing first item
        query = list_of_words[1]
             
        sql = "select (SELECT * from customers where lname = ?)"

        #args = ''.join((query))
        args = query
        
        cursor = conn.execute(sql, (args,))
        
        res = cursor.fetchall() # fetch all the results
        
        # If there is at least 1 row selected, print a message with matches
        # The message is created using the function defined above
        if(res):
            test_message = create_message_select_query(res) 
            await client.send_message(SENDER, test_message, parse_mode='html')
        # Otherwhise, print a default text
        else:
            text = "No matching customers found"
            await client.send_message(SENDER, text, parse_mode='html')

What exactly does would a sample of res look like? The problem happens after you call conn.execute, while iterating over the result. — chepner
– chepner, Commented Mar 2, 2024 at 17:51

John Gordon · Accepted Answer · 2024-03-02 17:12:35Z

1

The second argument to execute() needs to be a sequence (usually a list or tuple) of one or more items.

In your code you are passing a string as the second argument. Strings technically are sequences, but it counts each individual character as a separate item, which is why you get the incorrect number of bindings error.

Pass the second argument as a list of one item:

cursor = conn.execute(sql, [args])

Or as a tuple of one item:

cursor = conn.execute(sql, (args,))

answered Mar 2, 2024 at 17:12

John Gordon

33.8k9 gold badges48 silver badges72 bronze badges

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

8 Comments

Marcatectura Over a year ago

when I pass as a list or tuple of one item, I'm still getting the tuple index out of range error.

John Gordon Over a year ago

@Marcatectura This code does not do any tuple indexing that I can see, so the error must be happening in other code that you have not shown us. Please update the question with the full error traceback message.

John Gordon Over a year ago

@Marcatectura Your query is SELECT EXISTS(...), which returns rows with only a single true/false value. So i[1] is indeed out of range.

Marcatectura Over a year ago

With just a SELECT query, I'm getting a sqlite3.OperationalError: sub-select returns 4 columns - expected 1 with a traceback to the cursor = conn.execute(sql, (args,)) line.

John Gordon Over a year ago

@Marcatectura I don't see how "just a SELECT query" could possibly raise an error about sub-select. Don't make us guess what your query is. Post it.

|

Collectives™ on Stack Overflow

Tuple problem with parameterized SQLite query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related