In python, changing MySQL query based on function variables

Question

I'd like to be able to add a restriction to the query if user_id != None ... for example:

"AND user_id = 5" 

but I am not sure how to add this into the below function?

Thank you.

def get(id, user_id=None):

    query = """SELECT *
               FROM USERS
               WHERE text LIKE %s AND
                     id = %s
            """
    values = (search_text, id)

    results = DB.get(query, values)

This way I can call:

get(5)
get(5,103524234) (contains user_id restriction)

Answer 1

You could build the SQL query using a list of conditions:

def get(id, user_id=None):
    query = """SELECT *
               FROM USERS
               WHERE 
            """
    values = [search_text, id]
    conditions=[
        'text LIKE %s',
        'id = %s']
    if user_id is not None:
        conditions.append('user_id = %s')
        values.append(user_id)
    query+=' AND '.join(conditions)+' LIMIT 1'
    results = DB.get(query, values)

Answer 2

def get(id, user_id=None):

    query = """SELECT *
               FROM USERS
               WHERE text LIKE %s AND
                     id = %s
            """
    values = [search_text, id]

    if user_id is not None:
        query += ' AND user_id = %s'
        values.append(user_id)

    results = DB.get(query, values)

As you see, the main difference wrt your original code is the small if block in the middle, which enriches query string and values if needed. I also made values a list, rather than a tuple, so it can be enriched with the more natural append rather than with

         values += (user_id,)

which is arguably less readable - however, you can use it if you want to keep values a tuple for some other reasons.

edit: the OP now clarifies in a comment (!) that his original query has an ending LIMIT clause. In this case I would suggest a different approach, such as:

   query_pieces = ["""SELECT *
                     FROM USERS
                     WHERE text LIKE %s AND
                         id = %s
                   """, "LIMIT 5"]
    values = [search_text, id]

    if user_id is not None:
        query_pieces.insert(1, ' AND user_id = %s')
        values.append(user_id)

    query = ' '.join(query_pieces)
    results = DB.get(query, values)

You could do it in other ways, but keeping a list of query pieces in the proper order, enriching it as you go (e.g. by insert), and joining it with some whitespace at the end, is a pretty general and usable approach.

Answer 3

What's wrong with something like:

def get(id, user_id=None):
    query = "SELECT * FROM USERS WHERE text LIKE %s"
    if user_id != None:
        query = query + " AND id = %s"%(user_id)
    :
    :

That syntax may not be perfect, I haven't done Python for a while - I'm just trying to get the basic idea across. This defaults to the None case and only adds the extra restriction if you give a real user ID.