Dynamic raw query (select clause) Django

I’m trying to execute a raw query in Django where I dynamically want to pick column names.

   def func(all=True){
   if all:
      query_parameters = {
           'col': '*'
      query_parameters = {
           'col': 'a,b,c'
    with connections["redshift"].cursor() as cursor:
            "select %(col)s from table limit 2 ;",
        val = dictfetchall(cursor)
        return val

Django is executing it like.

select "*" from table limit 2;

so the output is just like select "*"


and in the else case it is executed like

select "a,b,c" from table limit 2;

so the output is a,b,c

How can I run the command so that Django run it like

select a , b , c from table limit 2

so that the output is

a b c 
1 2 3 
4 5 6 

would something like this work?

fields = ['a','b','c']
objects = Model.objects.only(*fields).filter()[:2]

This is just a simple version of the actual problem. I cant use ORM for this particular issue, I have to use raw query. Is this not possible with raw query ?

fair enough, how more complex is it?
We can’t be right/wrong and/or give good/bad ideas if we do not have more details than you gave :slight_smile:

Actually, it’s a legacy system and the query contains 5-10+ strange combinations of joins. We surely will re-write everything in ORM at a later stage of the project. But for the initial presentation, we are reusing the queries.

ah ok. Fair enough indeed.

Well what I proposed allows you to use the ORM, all the while dynamically building the list of fields/columns you want to fetch from the database:

fields = ['a','b','c']
field.insert(0, 'z')
limit = 2
objects = Model.objects.only(*fields).filter()[:limit]

this will result in something similar to the following query being generated and sent over the wire:

SELECT z, a , b , c, d FROM objects LIMIT 2;

Thanks for this, I know the ORM way. Just wanted to know if it’s possible with raw query.

heh :wink:

:rofl: :sweat_smile: Now I think its not possible with raw.

have you tried something like this?

cols = ['a, b']
cursor.execute("SELECT %s FROM table", [','.join(cols)])

i.e. w/o the keywords syntax?

Thanks, I tried but didn’t work out. But just after the last reply. I just got an idea and the hack worked. It’s working now.

Well, tell us about the hack! :slight_smile:

Prepared query step by step

Input data (Columns I need)

self.export_col = “a,b,c”

def calc_col(self):
        if self.exp == 'T':
            select_col = ""
            self.export_col = self.export_col.split(',')
            for col in self.export_col:
                select_col += col + ","
            select_col = select_col[:-1]
            self.export_col = select_col
            self.export_col += '*'

def prepare_query(self):
        query += " SELECT "
        query += self.export_col
        query += """ from table limit 2;"""

I see… bet you can’t wait to migrate this to something newer! :slight_smile:

1 Like