Django model database tasks

Django is equipped to execute database level tasks on Django models that are often done by database tools. A Django project's manage.py command offers several management sub-commands for tasks like backing-up, loading and deleting data from database tables linked to Django models, as well as recreating Django models from database tables & issuing interactive commands to a database.

Backup data: Fixtures, dumpdata, loaddata and inspectdb

The dumpdata and loaddata commands are Django's data back-up and loading tools, similar to native tools included in databases (e.g. MySQL mysqldump, Postgres pg_dump).

Django uses the term fixture to refer to the data structures created and used by the dumpdata and loaddata commands. Because Django fixtures are designed for Django model instance data, their structure is based on formats that better describe this type of data. By default, Django fixtures use the JSON (JavaScript Object Notation) format, but it's also possible to create fixtures in XML and YAML.

The dumpdata command outputs database table data linked to Django models. It accepts a wide array of options, the most common of which are described in the following examples:

manage.py dumpdata > all_model_data.json.- Outputs data for all project models and places it in the all_model_data.json file.
manage.py dumpdata stores --format xml.- Outputs data for all models in the stores app in XML.
manage.py dumpdata about.contact --indent=2.- Outputs data for the Contact model in the about app with two space indentation -- the indentation makes the output more readable.
manage.py dumpdata items.menu --pk=1,2,3 --format yaml.- Outputs the Menu model records with primary keys (i.e. id values) 1,2,3 from the items app in a YAML format.

Note To output YAML you need the PyYAML package (e.g. pip install PyYAML).

The manage.py loaddata command is designed to load fixture files produced by dumpdata. This means invoking loaddata is as simple as executing manage.py loaddata <fixture_file_name>. The loaddata command can accept relative or absolute paths to fixture files, but in addition, it also searches for fixture files in the fixtures folder inside apps. The next section on 'Django model initial data set up' describes the procedure to use fixtures in apps.

The most important variation for the manage.py loaddata command are: it can accept multiple fixture files as arguments (e.g. loaddata <fixture_1>, <fixture_2>, <fixture_3>), which is necessary if fixture files have inter-dependencies; and it can restrict the searching/loading of fixtures to certain apps (e.g. manage.py loaddata menu --app items, searches/loads a fixture file named menu, but only inside the items app, specifically inside its fixtures folder).

The manage.py inspectdb is a reverse-engineering process that outputs Django models generated from database tables. Note the manage.py inspectdb outputs a single stream of model classes, so it requires rearranging the output if the model classes are to be placed in different models.py files.

Delete data: flush, sqlflush and sqlsequencereset

Django also offers the flush and sqlflush commands to delete the contents of database tables linked to Django models. The manage.py flush command triggers the actual deleting process, where as manage.py sqlflush outputs the SQL required to delete all data in Django model database tables (i.e. the logic triggered by flush).

The manage.py sqlsequenereset command outputs the required SQL to reset logic used by database sequences of a given app (e.g. manage.py sqlsequencereset stores to output the SQL necessary to reset the sequences used by models in the stores app) . Sequences are used by databases to give automatically incrementing values to certain Django models fields (e.g. id field) and this command is used to fix issues when sequence values become out of sync.

Interact with data: dbshell

Sometimes the need to connect directly to a database linked to a Django project, can become inevitable in order to perform a complex task or query. The manage.py dbshell command is designed to connect to a Django project's database using the credentials in a Django projects settings.py file, in turn, avoiding the need to type in credentials to access a database.

Depending on the database brand you're using, the dbshell command opens an interactive command line shell to the built-in tool for each database brand. For PostgreSQL to the dpsql environment, for MySQL to the mysql environment, for SQLite to the sqlite3 environment and for Oracle to the sqlplus environment.