Python Django Build Raw SQL Query From a Given List

Question

I try to get some information from my database backend by performing a raw SQL query. I need to do calculations (multiplying a field with a factor, truncating it) and to convert it in a performant way to JSON. That's the reason why I have chosen to not use the (Django) ORM here.

with connection.cursor() as cursor:
    cursor.execute(
      '''
      SELECT json_build_object(
        'data', array_agg(r)
      )
      FROM (
        SELECT
          time,
          trunc(column1 * 100, 2) as COL1,
          trunc(column2 * 100, 2) as COL2,
          [...]
        FROM measurements
          WHERE device_id=%s
          ORDER BY time DESC
          LIMIT 5
      ) r
      ''',
      [device_id]
  )
  result = cursor.fetchall()

I need to adapt the statement above from the following list:

[
  {'column': 'column1', 'factor': 100, 'description': 'COL1', 'trunc': 2},
  {'column': 'column2', 'factor': 100, 'description': 'COL2', 'trunc': 2},
  [..]
]

Since I am not used to the python syntax yet I am wondering if there is an elegant solution for creating such a statement. I know I could just loop over the list of dicts and append the query but that doesn't feel good/right to me. Any suggestions would be appreciated.

I am thinking about something like this:

['trunc({} * {}, {}) as {}'.format(
  d['column'], d['factor'], d['trunc'], d['description']) for d in l
]

Dmitry Kovriga · Accepted Answer · 2017-11-02 15:25:33Z

If you are using psycopg 2.7 you can use the sql module, which will make everything safe

from psycopg2 import sql

query = sql.SQL('''
  SELECT json_build_object(
    'data', array_agg(r)
  )
  FROM (
    SELECT time, {}
    FROM measurements
      WHERE device_id=%s
      ORDER BY time DESC
      LIMIT 5
  ) r
''')

truncated_rows = sql.SQL(', ').join(
    sql.SQL('trunc({} * {}, {}) as {}').format(
        sql.Identifier(d['column']),
        sql.Literal(d['factor']),
        sql.Literal(d['trunc']),
        sql.Identifier(d['description'])
    )
    for d in l
)

with connection.cursor() as cursor:
    cursor.execute(query.format(truncated_rows), [device_id])

With 2.6 and earlier you will have to fall back to python string format

','.join(
    'trunc({column} * {factor}, {trunc}) as {description}'.format(**data)
    for data in
    [
        {'column': 'column1', 'factor': 100, 'description': 'COL1', 'trunc': 2},
        {'column': 'column2', 'factor': 100, 'description': 'COL2', 'trunc': 2},
    ]
)

Collectives™ on Stack Overflow

Python Django Build Raw SQL Query From a Given List

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related