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

Question

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

Do not do data concatenation like qs.join('where max=') + max since this is a security hole and will also impact application scalability. Use a bind variable instead. — Christopher Jones
– Christopher Jones, Commented Sep 5, 2018 at 1:07

abc · Accepted Answer · 2018-09-01 16:37:14Z

1

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

answered Sep 1, 2018 at 16:37

abc

12k2 gold badges30 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

masasa · Accepted Answer · 2018-09-01 16:31:00Z

0

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

answered Sep 1, 2018 at 16:31

masasa

2601 silver badge10 bronze badges

2 Comments

user3541631 Over a year ago

based on received arguments/variables I need to join none or up to 3 tables, and have multiple where clauses

masasa Over a year ago

you need to create all the 20 options of outcomes , and join them into 1 table in an inner join , so only the outcome that goes with the data will not be null, and just query that table , but for dynamic queryies , i would use python instead to query the data, it is much easier for more then 2-3 options..

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related