Pandas read_sql_query using multiple AND statements

Question

I am trying to put together a SQL query in python pandas. I have attempted different methods, but always getting the following error: Incorrect number of bindings supplied. The current statement uses 6, and there are 3 supplied.

My code is as follows. What am I doing wrong?

conn = sqlite3.connect(my_db)
df = pd.read_sql_query(
    con = conn,
    sql = """SELECT * FROM {table}
        WHERE @var1 = (?)
        AND @var2 = (?)
        AND @var3 = (?)
        ;""".format(table=table),
    params= (value1, value2, value3),
    )

Why are you using `@var1` instead of the actual column name? — DeepSpace, Mar 01 '18 at 12:05
@DeepSpace I would assume OP wants different columns each time. — Adi219, Mar 01 '18 at 12:07
@DeepSpace my column names start with @. '@var1' is a column name — Andreuccio, Mar 01 '18 at 12:08

score 4 · Answer 1 · answered Mar 01 '18 at 12:12

As you've said, @var1, @var2 and @var3 are all column names.

However, SQL interprets the @ symbol as a parameter for a value which you will supply later in your code.

So, your SQL code expects 6 values because of the three (?)s and three @vars. But you're only supplying 3 values (for the (?)s), meaning that the said error is occurring.

I would recommend naming your columns something without '@' so that there is less chance for errors.

See this question for further clarification.

unutbu · Accepted Answer · 2018-03-01T12:26:27.290

sqlite interprets the @ symbol, like ?, as a parameter placeholder (Search for "Parameters"). If @var1 is the name of a column, then it must escaped by surrounding it with backticks:

df = pd.read_sql_query(
    con = conn,
    sql = """SELECT * FROM {table}
        WHERE `@var1` = (?)
        AND `@var2` = (?)
        AND `@var3` = (?)""".format(table=table),
    params= (value1, value2, value3), )

I agree with @AdiC, though -- it would be more convenient to rename your columns so that they do not use characters with special meaning.

Pandas read_sql_query using multiple AND statements

2 Answers2