Providing initial data for models

It’s sometimes useful to pre-populate your database with hard-coded data when you’re first setting up an app. There’s a couple of ways you can have Django automatically create this data: you can provide initial data via fixtures, or you can provide initial data as SQL.

In general, using a fixture is a cleaner method since it’s database-agnostic, but initial SQL is also quite a bit more flexible.

Providing initial data with fixtures

A fixture is a collection of data that Django knows how to import into a database. The most straightforward way of creating a fixture if you’ve already got some data is to use the manage.py dumpdata command. Or, you can write fixtures by hand; fixtures can be written as JSON, XML or YAML (with PyYAML installed) documents. The serialization documentation has more details about each of these supported serialization formats.

As an example, though, here’s what a fixture for a simple Person model might look like in JSON:

[
  {
    "model": "myapp.person",
    "pk": 1,
    "fields": {
      "first_name": "John",
      "last_name": "Lennon"
    }
  },
  {
    "model": "myapp.person",
    "pk": 2,
    "fields": {
      "first_name": "Paul",
      "last_name": "McCartney"
    }
  }
]

And here’s that same fixture as YAML:

- model: myapp.person
  pk: 1
  fields:
    first_name: John
    last_name: Lennon
- model: myapp.person
  pk: 2
  fields:
    first_name: Paul
    last_name: McCartney

You’ll store this data in a fixtures directory inside your app.

Loading data is easy: just call manage.py loaddata <fixturename>, where <fixturename> is the name of the fixture file you’ve created. Each time you run loaddata, the data will be read from the fixture and re-loaded into the database. Note this means that if you change one of the rows created by a fixture and then run loaddata again, you’ll wipe out any changes you’ve made.

Automatically loading initial data fixtures

Deprecated since version 1.7: If an application uses migrations, there is no automatic loading of fixtures. Since migrations will be required for applications in Django 1.9, this behavior is considered deprecated. If you want to load initial data for an app, consider doing it in a data migration.

If you create a fixture named initial_data.[xml/yaml/json], that fixture will be loaded every time you run migrate. This is extremely convenient, but be careful: remember that the data will be refreshed every time you run migrate. So don’t use initial_data for data you’ll want to edit.

Where Django finds fixture files

By default, Django looks in the fixtures directory inside each app for fixtures. You can set the FIXTURE_DIRS setting to a list of additional directories where Django should look.

When running manage.py loaddata, you can also specify a path to a fixture file, which overrides searching the usual directories.

See also

Fixtures are also used by the testing framework to help set up a consistent test environment.

Providing initial SQL data

Deprecated since version 1.7: If an application uses migrations, there is no loading of initial SQL data (including backend-specific SQL data). Since migrations will be required for applications in Django 1.9, this behavior is considered deprecated. If you want to use initial SQL for an app, consider doing it in a data migration.

Django provides a hook for passing the database arbitrary SQL that’s executed just after the CREATE TABLE statements when you run migrate. You can use this hook to populate default records, or you could also create SQL functions, views, triggers, etc.

The hook is simple: Django just looks for a file called sql/<modelname>.sql, in your app directory, where <modelname> is the model’s name in lowercase.

So, if you had a Person model in an app called myapp, you could add arbitrary SQL to the file sql/person.sql inside your myapp directory. Here’s an example of what the file might contain:

INSERT INTO myapp_person (first_name, last_name) VALUES ('John', 'Lennon');
INSERT INTO myapp_person (first_name, last_name) VALUES ('Paul', 'McCartney');

Each SQL file, if given, is expected to contain valid SQL statements which will insert the desired data (e.g., properly-formatted INSERT statements separated by semicolons).

The SQL files are read by the sqlcustom and sqlall commands in manage.py. Refer to the manage.py documentation for more information.

Note that if you have multiple SQL data files, there’s no guarantee of the order in which they’re executed. The only thing you can assume is that, by the time your custom data files are executed, all the database tables already will have been created.

Initial SQL data and testing

This technique cannot be used to provide initial data for testing purposes. Django’s test framework flushes the contents of the test database after each test; as a result, any data added using the custom SQL hook will be lost.

If you require data for a test case, you should add it using either a test fixture, or programmatically add it during the setUp() of your test case.

Database-backend-specific SQL data

There’s also a hook for backend-specific SQL data. For example, you can have separate initial-data files for PostgreSQL and SQLite. For each app, Django looks for a file called <app_label>/sql/<modelname>.<backend>.sql, where <app_label> is your app directory, <modelname> is the model’s name in lowercase and <backend> is the last part of the module name provided for the ENGINE in your settings file (e.g., if you have defined a database with an ENGINE value of django.db.backends.sqlite3, Django will look for <app_label>/sql/<modelname>.sqlite3.sql).

Backend-specific SQL data is executed before non-backend-specific SQL data. For example, if your app contains the files sql/person.sql and sql/person.sqlite3.sql and you’re installing the app on SQLite, Django will execute the contents of sql/person.sqlite3.sql first, then sql/person.sql.