CODEWITHSUNDEEP
pythonsqlitepython-2.6
Kimbluey
Kimbluey

Reputation: 1339

How do I create a SELECT statement with a list as a parameter?

Code

The following code only works when the list targets_in_sw only has one value in it:

sw_current = cursor.execute("SELECT * from SOFTWARE_")
sw_current = sw_current.fetchall()
for sw_item in sw_current:
    current_software_id = sw_item[0]
    # Create Software XML Element
    sw_element = ET.SubElement(root, "Software")
    # Get all Targets for current sw_element
    targets_in_sw = cursor.execute("SELECT TARGET2 from SOFTWARE_TARGET_ WHERE SOFTWARE1=?", (current_software_id,))
    targets_in_software = targets_in_sw.fetchall()
    targets_in_software = list(chain.from_iterable(targets_in_sw))
    # Get all Target IDs for current sw_element
    current_target_IDs = cursor.execute("SELECT * from TARGET_ WHERE id_=?", targets_in_software)
    current_target_IDs = current_target_IDs.fetchall()
    ## The following line ONLY prints when my list contains one value ##
    print current_target_IDs

Question

How can I change this so that I may select everything from TARGET_ when targets_in_software is a list of multiple values?

Notes:

In case you didn't see the tags, I'm using SQLite3.

Here's an example of the values within targets_in_software while going through the loop:

iteration       targets_in_software
        1       [21]
        2       [28]
        3       [29]
        4       [91]
        5       [92]
        6       [94]
        7       [217]
        8       [218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228]
        9       [251]
       10       [261]
...etc.

Upvotes: 1

Views: 300

Answers (4)

vks
vks

Reputation: 67988

You can create your query this way using IN:

SELECT * from TARGET_ WHERE id IN tuple(targets_in_software)

This will work when targets_in_software has multiple values.

Upvotes: 1

john wamburu
john wamburu

Reputation: 121

Your raw SQL query should look like this:

SELECT val FROM table_name WHERE ref_val in ('val_one','val_two');

So for your case, you should do:

targets_in_sw = cursor.execute("SELECT TARGET2 from SOFTWARE_TARGET_ WHERE SOFTWARE1=(" + ",".join(targets_in_software) + ");")

Please note that this will not protect you against SQL injection.

Upvotes: 2

1.618
1.618

Reputation: 1765

If you pass a tuple as the second parameter of execute, it will try to substitute each element of the tuple for the placeholders in the query string, for example:

stmt = "INSERT INTO mytable (name, id, rank) VALUES (%s, %s, %s)"
cursor.execute(stmt, (a, b, c))

creates a query where each %s is replaced by an element of the tuple. In your case, you want to end up with a query where the entire tuple is together in one place, so you need to convert it to a string:

stmt = "SELECT * from TARGET_ WHERE id_ IN %s"
#convert list of ints to tuple of strings
stmt_param = str(tuple(map(str, targets_in_software)))
cursor.execute(stmt, stmt_param)

I think this should work, although I couldn't test it because I don't have a database to test against. I hope this helps.

Upvotes: 1

Michael Lindemuth
Michael Lindemuth

Reputation: 141

You need to use the SQL IN operator with your ORM. I don't have enough details to know what you're using. Try SELECT TARGET2 from SOFTWARE_TARGET_ WHERE SOFTWARE1 IN ? or SELECT TARGET2 from SOFTWARE_TARGET_ WHERE SOFTWARE1 IN ? with sw_item as your parameter instead of current_software_id.

Upvotes: 1

Related Questions