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:

INPUT
cursor.execute("SELECT id, name, class FROM my_table ORDER BY id")
cursor.fetchall()
OUTPUT
(
  (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).

INPUT
rq.multiple_rows("SELECT * FROM my_table ORDER BY id")
OUTPUT
[
  { "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).

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

Fetching multiple values

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

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

Fetching a single value

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

INPUT
rq.single_value("SELECT COUNT(*) FROM my_table")
OUTPUT
2

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
rq.run("UPDATE my_table SET name = %s WHERE id = %s", ["Douglas", 1])
OUTPUT
1

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