Select column in values, in python

Question

I have a code like the following:

   sql_command='''SELECT Grade From GRADES where Student_Number='''+str(id) +''' AND Course_ID IN'''+(list)
   grades=execute_sql(sql_command,sql_cursor)

where the list is the a list containing elements in the course_id column satisfying a condition (specific course id),

I want to say, select the only grade where the course_id column is the course_id in my list. How do I do that?

score 0 · Accepted Answer · answered Aug 03 '14 at 17:03

0

You want to join all elements in the list into one string, e.g.:

sql_command ='''SELECT Grade From GRADES where Student_Number='''+str(id) 
sql_command+=''' AND Course_ID IN ('''+ ','.join(list) + ''')'''

answered Aug 03 '14 at 17:03

ebo

2,717
1
27
22

the list contains tuples, not str.... is it the same thing to do or ?@EricBouwers – ryan Aug 03 '14 at 17:47
That was not clear to me from the question. Assuming that one of the tuple values contains the course id you are interested in you can create a new list with only those values using the answer to [this question](http://stackoverflow.com/questions/7876272/select-value-from-list-of-tuples-where-condition) – ebo Aug 04 '14 at 07:28

score 0 · Answer 2 · edited May 23 '17 at 12:25

0

A couple of things:

Assuming ID is coming from a user's input, this script is going to have being vulnerable to a SQL injection attack. See here for an example of using parameters, which are safer.
The solution to your specific question involves imploding your list:

format_strings = ','.join(['%s'] * len(list))

Source: imploding a list for use in a python MySQLDB IN clause

edited May 23 '17 at 12:25

Community

1
1

answered Aug 03 '14 at 17:04

JasonS

199
9

score 0 · Answer 3 · edited Aug 03 '14 at 17:35

0

Here is how it can be done using sqlite3

cursor.execute(
    "SELECT Grade From GRADES where Student_Number= ? AND Course_ID IN (?)",
    (id, ','.join(map(str, list)))
)

Please don't use %s as its not recommended. Check sqlite3 documentation for more.

edited Aug 03 '14 at 17:35

Stephan

2,863
1
17
14

answered Aug 03 '14 at 17:14

Neerav Kumar

84
5

the list contains tuple,not str, is it the same thing to write?@Stephan – ryan Aug 03 '14 at 17:50

Select column in values, in python

3 Answers3