How to query multiple parameters in SQLite3 using the "WHERE" and "AND" operators

Question

I'm currently working on code to analyze trading cards, which I'm storing in an SQLite database. One of the functions which queries the database for specific cards is listed below:

    def colors_search(conn,deck_color,card_ID):
        """
        Query all rows in the colors table
        :param conn: the Connection object
        :return:
        """
        color = (deck_color,)
        test = (color,card_ID)
        sql = ''' SELECT Number
                  FROM Colors
                  WHERE Color=?
                  AND Card_ID=?'''

        cur = conn.cursor()
        cur.execute(sql,test)

        number = cur.fetchall()

        return number

When I try and run this function, I keep getting the following error:

sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

This is referring to the line

cur.execute(sql,test)

I feel that the error is coming from how I'm trying to query two variables, but I'm not sure. If this is the problem, how would I format the 'sql' variable to take in two parameters? The deck_color and card_ID variables are a string and integer, respectively, and will vary throughout my program, so I can't hard code either of those in.

Alternately, if this isn't the issue, how can I reformat this to run correctly? Thanks!

No, it's from test which you need to print. I suspect color is a tuple from a previous query — roganjosh
– roganjosh, Commented Mar 30, 2019 at 20:39
Wait, you actually make it a tuple. Get rid of color = (deck_color,). It serves no purpose. That's why the query fails and making it a tuple doesn't, to my knowledge, do anything other than cause this error. Your query already protects against SQL injection. — roganjosh
– roganjosh, Commented Mar 30, 2019 at 20:42

roganjosh · Accepted Answer · 2019-03-30 20:55:39Z

2

There is nothing wrong with your query. The issue is here:

color = (deck_color,)

There is no need to create this tuple. Instead:

def colors_search(conn,deck_color,card_ID):
        """
        Query all rows in the colors table
        :param conn: the Connection object
        :return:
        """
        test = (deck_color,card_ID)
        sql = ''' SELECT Number
                  FROM Colors
                  WHERE Color=?
                  AND Card_ID=?'''

        cur = conn.cursor()
        cur.execute(sql,test)

        number = cur.fetchall()

        return number

Since you're using ?, the values are already escaped to prevent SQL injection. No need to make your own tuples.

answered Mar 30, 2019 at 20:55

roganjosh

13.3k4 gold badges33 silver badges53 bronze badges

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

Comments

VD26 · Accepted Answer · 2019-03-30 20:52:15Z

0

Can you try using cur.execute(sql,(deck_color,card_ID)), or cur.execute(sql,[deck_color,card_ID])

answered Mar 30, 2019 at 20:52

VD26

1382 silver badges12 bronze badges

Comments

T. Douglass · Accepted Answer · 2019-03-30 20:57:34Z

As an update:

I found that the error arose because of the input, not the function itself. The previous function, which lead into it, looked like so:

    def cards_search(conn,cardname):
        """
        Query all rows in the tasks table
        :param conn: the Connection object
        :return:
        """
        name = (cardname,)
        sql = ''' SELECT Card_ID
                  FROM Cards
                  WHERE Name=?'''

        cur = conn.cursor()
        cur.execute(sql,name)

        card_ID = cur.fetchall()

        return card_ID

This resulted in card_ID being a list, which thus means that it was not the data type I previously thought. I fixed this by changing the return statement to look like so:

    return card_ID[0]

My new function, which is now working, is pictured below:

    def colors_search(conn,deck_color,card_ID):
        """
        Query all rows in the colors table
        :param conn: the Connection object
        :return:
        """
        test = (deck_color,card_ID)
        sql = ''' SELECT Number
                  FROM Colors
                  WHERE Color=?
                  AND Card_ID=?'''

        cur = conn.cursor()
        cur.execute(sql,test)

        number = cur.fetchall()

        return number[0]

Thank you for those who contributed!

Collectives™ on Stack Overflow

How to query multiple parameters in SQLite3 using the "WHERE" and "AND" operators

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related