Jon Combe logo

Django RawQuery: (easier) raw queries in Django

July 20203 years, 7 months ago

Django's ORM is excellent: use it. But very occasionally there are times when raw database queries can be preferable, or even necessary.

Django RawQuery is a tiny class I put together which makes performing raw queries just a little bit easier. Rather than having to perform a query and then manipulate the returned data to suit your use case, you can now choose one of the four provided methods to get data back in a format which may work for you straight out of the box.

A raw query in Django looks like this:

cursor.execute("SELECT id, name, class FROM my_table ORDER BY id")
  (1, "Thomas", "G4R"),
  (2, "Charlotte", "G1S")

This works of course, but you need to think about the output it gives you, i.e. the order the values are returned in. SELECT * becomes a little troublesome too, particularly if your schema changes over time.

Django RawQuery provides four methods which give you a little more control over the data structure returned back to you, and one to perform UPDATE, INSERT or any other command that isn't a SELECT. It saves you some typing at the very least.

Fetching multiple records

Outputs a list of dicts (or an empty list if no records are found).

rq.multiple_rows("SELECT * FROM my_table ORDER BY id")
  { "id": 1, "name": "Thomas", "class": "G4R" },
  { "id": 2, "name": "Charlotte", "class": "G1S" }

Fetching a single record.

Outputs a single dict (or None if no record is found).

rq.single_row("SELECT * FROM my_table WHERE id = %s", [1])
{ "id": 1, "name": "Thomas", "class": "G4R" }

Fetching multiple values

Outputs a list of values (or an empty list if no records are found).

rq.multiple_values("SELECT name FROM my_table ORDER BY id")
["Thomas", "Charlotte"]

Fetching a single value

Outputs a single value (or None if no record is found).

rq.single_value("SELECT COUNT(*) FROM my_table")

Non-SELECT statements

Finally, to UPDATE, INSERT, or any other non-SELECT statement, you can perform the run command. This outputs an integer of the number of rows affected by your query.

INPUT"UPDATE my_table SET name = %s WHERE id = %s", ["Douglas", 1])

Thanks for reading. You can find installation and usage docs in the GitHub repo. The package is available on PyPI. Happy querying!