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)
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.
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.
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)
