CODEWITHSUNDEEP
pythonmysql
Kartik Anand
Kartik Anand

Reputation: 4609

MySQL query for SELECT statement if WHERE may/may not be empty

I'm using Python to access MySQL database using MySQLdb. I want to get all rows from a particular table "global"

The table global has the following columns: 

regno  
make  
state

The user can enter regno, make and state values to get only that particular rows, if he doesn't enter then all rows should come as output

I've tried the following code:

import MySQLdb as db
from config.py import *

con = db.connect(server, user, pwd, database)
cur = con.cursor()

while(1):
    print "-------Central Database-------"
    print "Select : "
    print "1. Balance Sheet\n2. Central Sheet"

    choice = raw_input()

    if choice == '3':
        break

    elif choice == '2':

        regno = raw_input('Input Registration number (Blank for all) : ')
        state = raw_input('Input state in/out (Blank for all) : ')
        make = raw_input('Input make of the vehicle (Blank for all) : ')

        if regno == '' and state == '' and make == '':
            cur.execute("select * from global")

        elif regno != '' and state != '' and make != '':
            cur.execute("select * from global where regno=%s and state=%s and make=%s",(regno, state, make))
        ...

As you can see this would lead to a lot all if-elif statements, is there any way in which I can straightforwardly use a MySQL query such as

select * from global where regno='' OR regno=%s

Upvotes: 0

Views: 298

Answers (1)

ignaZ
ignaZ

Reputation: 530

You can just add all the seperate condition clauses into a list, and then join together the list of conditions; like this:

regno = raw_input('Input Registration number (Blank for all) : ')
state = raw_input('Input state in/out (Blank for all) : ')
make = raw_input('Input make of the vehicle (Blank for all) : ')

conditions = []
args = []

if regno:
    conditions.append("regno=%s")
    args.append(regno)

if state:
    conditions.append("state=%s")
    args.append(make)

if make:
    conditions.append("make=%s")
    args.append(make)

if conditions:
    cur.execute("select * from global where " + " and ".join(conditions), args)
else
    cur.execute("select * from global")

The join function builds a string out of a list by putting a separator string between the list elements, e.g. " and ".join(["foo", "bar"] becomes foo and bar.

Upvotes: 1

Related Questions