CODEWITHSUNDEEP
pythonsqlite
Slfar
Slfar

Reputation: 21

selecting from a list in SQL and python

I need help with the following scenario:

in my database file I have a table who looks something like this

https://i.sstatic.net/dCMwJ.png

I want to get a list of all the IDs that have suppliers_id=(2,3) (list of ids)

the problem is that I don't know which values are in the group (I'm getting the list in the function header from other SELECT function)

We were taught that we can use IN option that should get me the result I want but I cant getting it to work

my code (doesn't work for now):

    def update(self, suppliers_id):
        c = self._conn.cursor()
        c.execute("""SELECT id FROM Vaccines WHERE supplier_id IN (?)""", [suppliers_id])

I'm getting sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type. error

Thanks!

Upvotes: 2

Views: 114

Answers (3)

IoaTzimas
IoaTzimas

Reputation: 10624

You pass a tuple inside existing parenthesis in your sql query, this results to IN ((some items)) which is not readable from sql. Try this instead (removed parenthesis and also change suppliers_id to tuple in case that it is a list):

def update(self, suppliers_id):
    c = self._conn.cursor()
    c.execute("""SELECT id FROM Vaccines WHERE supplier_id IN (?)""", [','.join(str(i) for i in suppliers_id)])

Upvotes: 0

Ahmed
Ahmed

Reputation: 924

If you want to select multiple values from table, you've to add it as a tuple without assigning it to a new memory address .

By assigning your variable as a list, you're assigning it to a new memory address which is insufficient way of using Parameters Substitution.

Code Syntax

def update(self, suppliers_id):
    c = self._conn.cursor()
    c.execute("""SELECT id FROM Vaccines WHERE supplier_id IN (?)""", supplier_id, )   # by adding only comma, you're accessing the tuple, iteratively.

Upvotes: 1

Vedant Vasishtha
Vedant Vasishtha

Reputation: 395

Two Options:

Either remove parenthesis from your sql query. For e.g.

def update(self, suppliers_id):
    c = self._conn.cursor()
    c.execute("""SELECT id FROM Vaccines WHERE supplier_id IN ?""", tuple(suppliers_id))

Or use format. For e.g.

def update(self, suppliers_id):
    c = self._conn.cursor()
    c.execute("""SELECT id FROM Vaccines WHERE supplier_id IN {}""".format(tuple(suppliers_id)))

Upvotes: 0

Related Questions