looking for some solutions? You are welcome.

SOLVED: How to query data base on whether the condition exists in mysql?

Frank AK:

I want to query my table with different condition, but it depends on the client side input, for example:

SELECT * FROM t_user WHERE f_username like 'frank' and f_country = "%UK%".

Since I wrote my server in Python, so my query will be:

sql = """SELECT * FROM t_user WHERE f_username like '%s' and f_country = "%%s%"."""
cur.execute(sql, ('frank', 'UK'))

But, sometime, the user don't want to filter by country, so they might only get the keyword. so my query should be like:

SELECT * FROM t_user WHERE f_username like 'frank'

Also, my python will be like:

sql = """SELECT * FROM t_user WHERE f_username like '%s'"""
    cur.execute(sql, ('frank',))

and then, I can check the params, decide which sql I should use it, but In my actual project, it has many filter require, that would may me build many many condition check, something like:

if not country_id and not city_id and not keyword:
    condition = """
        WHERE f_username=%s
    """
elif country_id and not city_id and not keyword:
    condition = """
        WHERE f_username=%s and f_country=%s
    """
elif not country_id and city_id and not keyword:
    condition = """
        WHERE f_username=%s and f_city=%s
    """
elif not country_id and not city_id and keyword:
    condition = """
        WHERE f_username=%s and f_activity_title like (%%s%)
    """
elif country_id and city_id and not keyword:
    condition = """
        WHERE f_username=%s and f_country=%s and f_city=%s

Anyway, it works. but I want it more Pythonic, like :

WHERE f_username=(%s or '*')  and f_country=(%s or '*' and f_city=(%s or '*)

I know it not really correct sql syntax, but can you help me find a good one?



Posted in S.E.F
via StackOverflow & StackExchange Atomic Web Robots
Share:

No comments:

Recent