Dynamic raw query (select clause) Django

Django Internals ORM
1

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

   def func(all=True){
   if all:
      query_parameters = {
           'col': '*'
      }
   else:
      query_parameters = {
           'col': 'a,b,c'
      }
    with connections["redshift"].cursor() as cursor:
        cursor.execute(
            "select %(col)s from table limit 2 ;",
            query_parameters,
        )
        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
2

would something like this work?

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

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 ?

4

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:

5

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.

6

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']
fields.append('d')
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;
7

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

8

heh :wink:

9

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

10

have you tried something like this?

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

i.e. w/o the keywords syntax?

11

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.

12

Well, tell us about the hack! :slight_smile:

13

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
        else:
            self.export_col += '*'

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

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

1 Like