1

I know this seems really basic, but whatever answers I can find simply don't work for me.

import sqlite3

conn = sqlite3.connect("mydb.db")


cursor = conn.cursor()


name = "John Doe"
cursor.execute("SELECT * FROM users WHERE username = ?", name)
items = cursor.fetchall()
print(items)


conn.close()

I'm trying to get a list of all users with the username 'John Doe' (which I know there is one of), but the previous code gives the following error:

Traceback (most recent call last):
  File ".\sqlitetest.py", line 10, in <module>
    cursor.execute("SELECT * FROM users WHERE username = ?", name)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 8 supplied.
ilikeapples1234
  • 49
  • 4
  • 1
    You should pass a _tuple_ of values to bind. `"John Doe"` is being interpreted as a sequence of 8 values (each a single-character string). – jonrsharpe Jun 02 '21 at 08:09

2 Answers2

3

The variables should be passed in a tuple, even if only 1 variable is passed i.e (name,)

cursor.execute("SELECT * FROM users WHERE username = ?", (name,))
AmineBTG
  • 597
  • 3
  • 13
  • I see. I tried this before, but I didn't add the trailing comma. Would you happen to know why either of these are required (passing the variables in a tuple, and the trailing comma needed for it to be recognized as a tuple)? – ilikeapples1234 Jun 02 '21 at 08:27
  • 1
    Passing the variable in a tuple is needed as the method expects an iterable (usually more than 1 variable are passed). The trailling coma is needed when you put only 1 item in a tuple so it can be considered as a tuple. If you do not put a trailling coma, Python will just consider it as a normal expression put inside parenthesis such as (3 + 5) or (a + b) – AmineBTG Jun 02 '21 at 09:07
  • 1
    Thanks, that explains it well. – ilikeapples1234 Jun 02 '21 at 09:08
1

usually i use sql server for databases, i pass variable data like

query = f"SELECT * FROM users WHERE username='{var_name}'"

or query = "SELECT * FROM users WHERE username='{}'".format(var_name)

Nanasaheb Yadav
  • 23
  • 6
  • This is, in effect, what I'm doing here. – ilikeapples1234 Jun 02 '21 at 08:30
  • While using string formatting may work, it relies on the programmer remembering to quote the value(s) correctly, and carries the risk of SQL injection attacks if used with untrusted data. It's much better to use the tools provided by the DB-API connector. – snakecharmerb Jun 02 '21 at 09:25