CODEWITHSUNDEEP
pythonsqlstringpython-3.x
user3541631
user3541631

Reputation: 4008

Joining strings using logic conditions(for sql queries)

I want to create an SQL query by concatenating strings but with conditions.

qs_str= 'Select *, from items ...'

The conditions:

if active:
            qs_str = ' '.join([qs_str, f'WHERE active=true'])
if is_parent:
          qs_str = ' '.join([qs_str, f'WHERE parent=true'])
if limit:
            qs_str = ' '.join([qs_str, f'WHERE LIMIT={limit}'])
.....

There is not really a fixed limit on how many conditions will be.

The issue is the WHERE clause, should be added only once(or not if there is no condition) and how to add AND, to know if a condition is already in place.

Because there is no theoretically limit for the number of conditions I can't using combinatorics ifs like(if is_active and is_parent) is not a solution.

Upvotes: 0

Views: 31

Answers (1)

Odysseas
Odysseas

Reputation: 1060

You can collect the conditions in a list, and then only append the "where" clause if the list is non-empty.

Use ' and '.join(conditions) to easily handle adding "and" exactly as needed.

def create_item_query(active=True, is_parent=True, limit=10):
    qs_str= 'select * from items'

    conditions = []

    if active:
        conditions.append('active=true')
    if is_parent:
        conditions.append('parent=true')
    if limit:
        conditions.append('LIMIT={limit:d}'.format(limit=limit))


    if conditions:
        qs_str += 'where ' + ' and '.join(conditions)

    return qs_str

print(create_item_query(True, True, 10))
print(create_item_query(True, False, None))
print(create_item_query(False, False, None))

Edit: I should point out that, as Patrick Artner mentioned in his comment, that it's not actually a good practice to build SQL queries as a string, due to the danger of SQL injection. The precise way on how to create your queries would then depend on the exact SQL library that you're using (a popular tool is SQLAlchemy).

Upvotes: 1

Related Questions