CODEWITHSUNDEEP
pythondatabasesqlite
T. Douglass
T. Douglass

Reputation: 38

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

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!

Upvotes: 1

Views: 4771

Answers (3)

T. Douglass
T. Douglass

Reputation: 38

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!

Upvotes: 0

roganjosh
roganjosh

Reputation: 13185

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.

Upvotes: 2

VD26
VD26

Reputation: 138

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

Upvotes: 0

Related Questions