how to form this mysql select query using python-2.7

Question

I am using Python-2.7 and mysql database and wish to retrieve data from table student using select statement by passing either 2 or any one of 2 parameters as below-

SELECT * FROM student_details where student_city='some_City' or student_stream='Science' or both

parameters will be passed to that function which operated the SELECT statement.

Guidance / Help in any form to make me understand how can I code the where clause part. I don't want to make separate queries.

Apologies if I am asking the question incorrectly or repeating the question. Thanks in advance :)

@BrendanAbel - `code` mysql.connector.connect(user='abc',password='XXX',host = 'localhost',port = 'xyz', database='organisations',buffered=True). — Pallavi Joshi, Jan 22 '16 at 09:26
@Strawberry - I am not sure whether I have understood your question. But all I want is to accept whichever parameter is passed and use it accordingly in WHERE clause. So while calling the actual function one can send only **student_city** or only **student_stream** or can send both the parameters — Pallavi Joshi, Jan 22 '16 at 09:30
Ok, so you want to build the query using only passed parameters. Implode passed parameters with 'and'. I'm sure there are lots of python tutorials on this. — Strawberry, Jan 22 '16 at 10:01

score 2 · Answer 1 · answered Feb 02 '16 at 13:20

I tried and finally this works for me-

 try:
        query = ("""SELECT * FROM student_details  WHERE """)
        if city is not '0' and Stream == '0':
              complete_query = query+("""student_city = %s""")

        elif Stream is not '0' and city == '0':
            complete_query = query+("""student_stream = %s""")

        else:
            complete_query = query+("""student_city = %s AND student_stream = %s""")

        if city is not '0' and Stream == '0':
            s_execute = self.cur2.execute(complete_query,(city,))

        elif Stream is not '0' and city == '0':
            s_execute = self.cur2.execute(complete_query,(Stream,))

        else:
            s_execute = self.cur2.execute(complete_query,(city),(Stream))
 except MySQLdb.Error as error:
                                print(error)

It was some what similar to above suggestion. Thanks all for guiding.

score 0 · Answer 2 · answered Jan 22 '16 at 09:03

0

use a python wrapper around MySQL and use execute() to run whatever you want.For details follow this link

http://www.tutorialspoint.com/python/python_database_access.htm

answered Jan 22 '16 at 09:03

armak

560
5
13

I know that I am asking how to phrase the WHERE clause which juggles between 2 parameters out of which one may or may not be optional. – Pallavi Joshi Jan 22 '16 at 09:36
execute method uses raw_query just pass your query as a string. – armak Jan 22 '16 at 09:45

score 0 · Answer 3 · edited May 23 '17 at 10:28

0

Check this thread on parameterized query:

sql search query with multiple optional search parameters

Hope this answers your query :)

edited May 23 '17 at 10:28

Community

1
1

answered Jan 22 '16 at 11:00

Nirav Bajaj

1
1

how to form this mysql select query using python-2.7

3 Answers3