CODEWITHSUNDEEP
pythonstringpython-3.x
user3541631
user3541631

Reputation: 4008

Creating a dynamic sql query using variables and in some cases their values (with joins)

I need to create a dynamic SQL query that changes based on different arguments/variables existence and value:

So, I have something like this:

def create_query(user=None, max=None, list=None)

qs = 'SELECT N.status'

if user: 
    qs = qs.join('U.email')

qs = join('from notes as N)'

if user: 
  qs = 'LEFT JOIN users AS U on N.user_id=U.id'

if max:
  qs = qs.join('where max=') + max

if list:

  qs = qs.join('where fame_values in list')

....

I have around 20 variables that can change the query, so I'm looking for something more pythonic(like list comprehensions with conditions) but for strings

Upvotes: 0

Views: 1461

Answers (2)

abc
abc

Reputation: 11929

You may create a function which create the query according to the arguments you pass to it.
Consider the following example:

def create_query(user, max_value = None):
    query = 'SELECT N.status'+ (' U.email' if user else ' ') + ' from notes as N' + (' LEFT JOIN users AS U on N.user_id=U.id' if user else ' ') + (' where max={}'.format(max_value) if max_value else " ") 
    return query

query = create_query(user=True,max_value=10)
# SELECT N.status U.email from notes as N LEFT JOIN users AS U on N.user_id=U.id where max=10
query = create_query(user=False)
# SELECT N.status  from notes as N 
query = create_query(user=True)
# SELECT N.status U.email from notes as N LEFT JOIN users AS U on N.user_id=U.id

Upvotes: 1

masasa
masasa

Reputation: 260

if you are working with python , i suggest to build the query inside your code , and connects to the database with cx_Oracle (or other modules, if you have a different database)

if you really want to do it in pure sql, what you need to do is to preform a master join of everything , and use case to show the desired data you wish.

case when x then y
when b then c
else null end case1,
case2 when x then t
when g then c
else null end case2
from a join b join c join...

some examples for that exactly you wish to do will be helpful..

Upvotes: 0

Related Questions