tabulate-django 0.2.1

An application to pretty print Django QuerySets and Model instances in the shell

Tabulate-Django

Tabulate-Django is a small collection of functions to make working with Django Querysets and Objects in the shell easier.

Tabulate-Django uses the tabulate library for formatting.

Motivation

When using Django, I often need to drop to the shell to retrieve information. In such cases I will usually look at the result of a QuerySet, the default output of which is not particularly useful. I tended to use .values() to extract the fields that I wanted and then pass them into the excellent tabulate library. I would often make the same calls over an over and ended up creating avoid repetition. Over time this function grew in size and capabilities.

In order to make it easy to put into any codebase I am working on, I decided to publish it on PyPI, with the added bonus of making it available for other users

My motivation is primarily my own needs, so I won't commit to adding any features that would not be of use to me, however PRs are welcome.

Licence

This software is licenced under the MIT licence

Installation

The package is available on PyPI so installation is as simple as

pip install tabulate-django

or use the package manager of your choice

This version supports Python 2.7, however compatibility may be removed in a future release (basically when I stop developing anything under 2.7)

Usage

For examples, I am using a simple e-commerce style app called test_app that contains three models, Country, Account and Order.

Account has a foreign key to Country and Order has a foreign key to Account.

Commands are assumed to be running in the Django shell started with python manage.py shell

First, let's get a queryset with all Accounts

from test_app.models import Account
all_accounts = Account.objects.order_by("name")
print(all_accounts)

response:

<QuerySet [<Account: Alice Smith>, <Account: Bob Jones>, <Account: Charlotte Muller>, <Account: Dietrich Schmidt>, <Account: Eva Dupont>, <Account: Francois Michel>]>

more information could be provided by running print(account.values("name", "email")

<QuerySet [{'name': 'Alice Smith', 'email': '[e-mail]'}, {'name': 'Bob Jones', 'email': '[e-mail]'}, {'name': 'Charlotte Muller', 'email': '[e-mail]'}, {'name': 'Dietrich Schmidt', 'email': '[e-mail]'}, {'name': 'Eva Dupont', 'email': '[e-mail]'}, {'name': 'Francois Michel', 'email': '[e-mail]'}]>

As the number of records and the number of fields gets bigger this becomes hard to read and will eventually be truncated

queryset_table

let's see what Tabulate Django can do. Pass the queryset and an iterable of fields that you wish to extract to the queryset_table

from tabulate_django import queryset_table
queryset_table(all_accounts, ["name", "email"])

╒══════════════════╤════════════════════════════╕
│ Name             │ Email                      │
╞══════════════════╪════════════════════════════╡
│ Alice Smith      │ [e-mail]        │
├──────────────────┼────────────────────────────┤
│ Bob Jones        │ [e-mail]      │
├──────────────────┼────────────────────────────┤
│ Charlotte Muller │ [e-mail]        │
├──────────────────┼────────────────────────────┤
│ Dietrich Schmidt │ [e-mail]        │
├──────────────────┼────────────────────────────┤
│ Eva Dupont       │ [e-mail]         │
├──────────────────┼────────────────────────────┤
│ Francois Michel  │ [e-mail] │
╘══════════════════╧════════════════════════════╛

As you can see, it puts the output in a pretty printed table with automatically named headers

Table Format

You can change the format by setting table_format to any of the formats supported by tabulate

So to user presto format

queryset_table(all_accounts, ["name", "email"], table_format="presto")

 Name             | Email
------------------+----------------------------
 Alice Smith      | [e-mail]
 Bob Jones        | [e-mail]
...

The default is fancy_grid

TSVs

In addition to the output formats provided by tabulate, there is an additional format "tsv" which outputs in a simple tsv format. This is useful for exporting the results for importing into a spreadsheet or other tool. No escaping is performed so if the data has linebreaks or tabs then it will not be correctly imported

Output to variable

The default behaviour of the function is to print the output to the console. If instead you wish for the output to be placed inside a variable, add the parameter print_result=False e.g.

users=queryset_table(all_accounts, ["name", "email"], print_result=False)

Foreign Keys

You can traverse model relationships using __ notation, so to retrieve the country name of each Account you can do

queryset_table(all_accounts, ["name", "email", "country__name"])

would output

╒══════════════════╤════════════════════════════╤════════════════╕
│ Name             │ Email                      │ Country Name   │
╞══════════════════╪════════════════════════════╪════════════════╡
│ Alice Smith      │ [e-mail]        │ United Kingdom │
├──────────────────┼────────────────────────────┼────────────────┤
│ Bob Jones        │ [e-mail]      │ United Kingdom │
├──────────────────┼────────────────────────────┼────────────────┤
│ Charlotte Muller │ [e-mail]        │ Germany        │
├──────────────────┼────────────────────────────┼────────────────┤
│ Dietrich Schmidt │ [e-mail]        │ Germany        │
├──────────────────┼────────────────────────────┼────────────────┤
│ Eva Dupont       │ [e-mail]         │ France         │
├──────────────────┼────────────────────────────┼────────────────┤
│ Francois Michel  │ [e-mail] │ France         │
╘══════════════════╧════════════════════════════╧════════════════╛
...

Changing Table Headings

If you were unhappy with the generated headers (more likely when using foreign keys and more advanced operations) then you can replace a field with a tuple, the first entry containing the field specification and the second the heading that you wish the column to have

e.g.

queryset_table(all_accounts, ["name", "email", ("country__name", "Residence")])

would output

╒══════════════════╤════════════════════════════╤════════════════╕
│ Name             │ Email                      │ Residence      │
╞══════════════════╪════════════════════════════╪════════════════╡
│ Alice Smith      │ [e-mail]        │ United Kingdom │
├──────────────────┼────────────────────────────┼────────────────┤
│ Bob Jones        │ [e-mail]      │ United Kingdom │
├──────────────────┼────────────────────────────┼────────────────┤
...

Aggregation

As well as getting simple fields from tables, it is also possible to use certain aggregation functions to retrieve more information, by using specially formatted field specifications

These are accessed by preceding the field (or table) name with a symbol

Function	Symbol	Notes
Count	#
Min	_
Max	^
Sum	+
Average	~
StringAgg	*	Postgres only - used for strings
ArrayAgg	[]	Postgres only - used for non-strings

e.g

queryset_table(all_accounts, ["name", "email", "#order", "+order__order_total"])

╒══════════════════╤════════════════════════════╤══════════╤══════════════════════╕
│ Name             │ Email                      │   #Order │   +Order Order Total │
╞══════════════════╪════════════════════════════╪══════════╪══════════════════════╡
│ Alice Smith      │ [e-mail]        │        3 │                   45 │
├──────────────────┼────────────────────────────┼──────────┼──────────────────────┤
│ Bob Jones        │ [e-mail]      │        1 │                  250 │
├──────────────────┼────────────────────────────┼──────────┼──────────────────────┤
│ Charlotte Muller │ [e-mail]        │        1 │                   10 │
├──────────────────┼────────────────────────────┼──────────┼──────────────────────┤
│ Dietrich Schmidt │ [e-mail]        │        2 │                  300 │
├──────────────────┼────────────────────────────┼──────────┼──────────────────────┤
│ Eva Dupont       │ [e-mail]         │        1 │                  220 │
├──────────────────┼────────────────────────────┼──────────┼──────────────────────┤
│ Francois Michel  │ [e-mail] │        1 │                  220 │
╘══════════════════╧════════════════════════════╧══════════╧══════════════════════╛

Alternatively you can use custom aggregate expressions, e.g.

from django.db.models import Count, Sum
queryset_table(
            all_accounts,
            [
                "name",
                "email",
                (Count("order"), "Orders"),
                (Sum("order__order_total"), "Order Value"),
            ]
)

would output

╒══════════════════╤════════════════════════════╤══════════╤═══════════════╕
│ Name             │ Email                      │   Orders │   Order Value │
╞══════════════════╪════════════════════════════╪══════════╪═══════════════╡
│ Alice Smith      │ [e-mail]        │        3 │            45 │
├──────────────────┼────────────────────────────┼──────────┼───────────────┤
│ Bob Jones        │ [e-mail]      │        1 │           250 │
├──────────────────┼────────────────────────────┼──────────┼───────────────┤
...

Header aliases are used here as the default representation of the expression is not particularly friendly

Filtering

The queryset can of course be filtered before being passed into the function, however you might wish to only show accounts that have made more than one order. Rather than requiring you to generate the expression yourself you can pass a filter option to the function which is a dictionary which will be passed as kwargs to .filter()

queryset_table(
    all_accounts,
    [
        "name",
        "email",
        ("#order", "Orders"),
        ("+order__order_total", "Order Value"),
    ],
    filter={"_count_order__gt": 1},
)

which would output:

╒══════════════════╤═════════════════════╤══════════╤═══════════════╕
│ Name             │ Email               │   Orders │   Order Value │
╞══════════════════╪═════════════════════╪══════════╪═══════════════╡
│ Alice Smith      │ [e-mail] │        3 │            45 │
├──────────────────┼─────────────────────┼──────────┼───────────────┤
│ Dietrich Schmidt │ [e-mail] │        2 │           300 │
╘══════════════════╧═════════════════════╧══════════╧═══════════════╛

to get the keys to use for generated fields you can pass show_keys=True to the function

queryset_table(
    all_accounts,
    [
        "name",
        "email",
        ("#order", "Orders"),
        ("+order__order_total", "Order Value"),
    ],
    show_keys=True,
)

outputting

╒══════════════════╤════════════════════════════╤═════════════════════════╤═════════════════════════════════════════╕
│ Name (name)      │ Email (email)              │   Orders (_count_order) │   Order Value (_sum_order__order_total) │
╞══════════════════╪════════════════════════════╪═════════════════════════╪═════════════════════════════════════════╡
│ Alice Smith      │ [e-mail]        │                       3 │                                      45 │
├──────────────────┼────────────────────────────┼─────────────────────────┼─────────────────────────────────────────┤
...

Formatted Strings

Sometimes you may want to transform one or more fields into a string, this can be accomplished by using a field specification starting with f| followed by a format string containing the name of one or more fields e.g.

queryset_table(
    all_accounts,
    [
        "name",
        ("f|https://example.com/admin/test_app/account/{id}/change", "Django Admin URL"),
    ],
)

╒══════════════════╤═════════════════════════════════════════════════════╕
│ Name             │ Django Admin URL                                    │
╞══════════════════╪═════════════════════════════════════════════════════╡
│ Alice Smith      │ https://example.com/admin/test_app/account/1/change │
├──────────────────┼─────────────────────────────────────────────────────┤
│ Bob Jones        │ https://example.com/admin/test_app/account/2/change │
├──────────────────┼─────────────────────────────────────────────────────┤
...

Slicing

To slice the resulting queryset (to paginate results for example) you can pass a slice parameter which is a 2-tuple containg start and end values

queryset_table(all_accounts, ["name", "email"], print_result=False, slice=(2, 4))

which would output:

╒══════════════════╤═════════════════════╕
│ Name             │ Email               │
╞══════════════════╪═════════════════════╡
│ Charlotte Muller │ [e-mail] │
├──────────────────┼─────────────────────┤
│ Dietrich Schmidt │ [e-mail] │
╘══════════════════╧═════════════════════╛

Printing Instance

You can also print a single instance of a model by passing the instance to the instance_table function

from ptk_tabulate import instance_table
instance_table(all_accounts[0])

This uses the dictionary representation of the instance, and sorts by key

╒════════════╤═════════════════════╕
│ country_id │ 1                   │
├────────────┼─────────────────────┤
│ email      │ [e-mail] │
├────────────┼─────────────────────┤
│ id         │ 1                   │
├────────────┼─────────────────────┤
│ name       │ Alice Smith         │
╘════════════╧═════════════════════╛

This will automatically hide members that begin with an underscore, to show these members, pass private=True to the function.

print_result and table_format parameters have the same meaning as in queryset_table however the special tsv value for table_format is not available