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
– DeepSpace, Commented Mar 1, 2018 at 12:05
@DeepSpace I would assume OP wants different columns each time. — Adi219
– Adi219, Commented Mar 1, 2018 at 12:07
@DeepSpace my column names start with @. '@var1' is a column name — Andreuccio
– Andreuccio, Commented Mar 1, 2018 at 12:08

Adi219 · Accepted Answer · 2018-03-01 12:12:47Z

4

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.

answered Mar 1, 2018 at 12:12

Adi219

4,8362 gold badges22 silver badges44 bronze badges

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

Comments

unutbu · Accepted Answer · 2018-03-01 12:26:27Z

2

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.

edited Mar 1, 2018 at 12:26

answered Mar 1, 2018 at 12:19

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Collectives™ on Stack Overflow

Pandas read_sql_query using multiple AND statements

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related