RawQuery: (easier) raw queries in Django

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")
cursor.fetchall()

(
  (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")

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.

rq.run("UPDATE my_table SET name = %s WHERE id = %s", ["Douglas", 1])

1

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