Set up a database for a Django project

Django in its 'out-of-the-box' state is set up to communicate with SQLite -- a lightweight relational database included with the Python distribution. So by default, Django automatically creates a SQLite database for your project.

In addition to SQLite, Django officially supports (i.e. included in Django itself) three other popular relational databases that include: PostgreSQL, MySQL and Oracle. And unofficially (i.e. with third party packages) Django supports connectivity to other relational databases that include: SAP (Sybase) SQL Anywhere, IBM DB2 and Firebird, as well as the ADO (ActiveX Data Objects) and ODBC (Open Database Connectivity) interfaces, the last two of which are standard for connecting to Microsoft SQL Server and the latter is supported by most relational database brands.

The Django configuration to connect to a database is done inside the settting.py file of a Django project in the DATABASES variable.

If you open the settings.py file of a Django project you'll notice the DATABASES variable has a default Python dictionary with the values illustrated in listing 1-14.

Listing 1-14. Default Django DATABASES dictionary

# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
import os

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}
Tip Use SQLite if you want the quickest database setup.

A database setup by itself can be time consuming. If you want the quickest setup to enable Django with a database leave the previous configuration as is. SQLite doesn't require additional credentials or Python packages to establish a Django database connection. Just be aware a SQLite database is a flat file and Django creates the SQLite database based on the NAME variable value. In the case of listing 1-14, under a Django project's BASE_DIR and as a flat file named db.sqlite3.

Note If you use SQLite, you can skip to the last step in this section 'Test Django database connection and build Django base tables'.

The Django DATABASES variable defines key-value pairs. Each key represents a database reference name and the value is a Python dictionary with the database connection parameters. In listing 1-14 you can observe the default database reference. The default reference name is used to indicate that any database related operation declared in a Django project be executed against this connection. This means that unless otherwise specified, all database CRUD (Create-Read-Update-Delete) operations are done against the database defined with the default key.

The database connection parameters for the default database in this case are the keys ENGINE and NAME, which represent a database engine (i.e. brand) and the name of the database instance, respectively.

The most important parameter of a Django database connection is the ENGINE value. The Django application logic associated with a database is platform neutral, which means that you always write database CRUD operations in the same way irrespective of the database selection. Nevertheless, there are minor differences between CRUD operations done against different databases which need to be taken into account.

Django takes care of this issue by supporting different backends or engines. Therefore, depending on the database brand you plan to use for a Django application, the ENGINE value has to be one of the values illustrated in table 1-2.

Table 1-2. Django ENGINE value for different databases

Database Django ENGINE value Required package
MySQL django.db.backends.mysql None, it's included with Django
Oracle django.db.backends.oracle None, it's included with Django
PostgreSQL django.db.backends.postgresql_psycopg2 None, it's included with Django
SQLite django.db.backends.sqlite3 None, it's included with Django
SAP (Sybase) SQL Anywhere sqlany_django pip install sqlany-django
IBM DB2 ibm_db_django pip install ibm_db_django
Firebird firebird pip install django-firebird
ADO -- Microsoft SQL Server sqlserver_ado pip install django-mssql
ODBC -- Microsoft SQL Server,
Azure SQL Database or other ODBC compatible database
sql_server.pyodbc *pip install django-pyodbc-azure
OR
pip install django-pyodbc
*ODBC is supported through the django-pyodbc-azure package which is newer and created from the older django-pyodbc package.

The Django database connection parameter NAME is used to identify a database instance, and its value convention can vary depending on the database brand. For example, for SQLite NAME indicates the location of a flat file, whereas for MySQL it indicates the logical name of an instance.

The full set of Django database connection parameters is described in table 1-3.

Table 1-3. Django database connection parameters based on database brand

Django connection parameter Default value Notes
ATOMIC_REQUESTS False Enforces (or not) a transaction for each view request. By default set to False, because opening a transaction for every view has additional overhead. The impact on performance depends on the query patterns of an application and on how well a database handles locking.
AUTOCOMMIT True By default set to True, because otherwise it would require explicit transactions to perform commits.
CONN_MAX_AGE 0 The lifetime of a database connection in seconds. By default 0 which closes the database connection at the end of each request. Use None for unlimited persistent connections.
ENGINE ''(Empty string) The database backend to use. See Table 1-2 for value options.
HOST ''(Empty string) Defines a database host, where an empty string means localhost.For MySQL: If this value starts with a forward slash ('/'), MySQL will connect via a Unix socket to the specified socket (e.g."HOST": '/var/run/mysql'). If this value doesn't start with a forward slash, then this value is assumed to be the host.For PostgreSQL: By default(''), the connection to the database is done through UNIX domain sockets ('local' lines in pg_hba.conf). If the UNIX domain socket is not in the standard location, use the same value of unix_socket_directory from postgresql.conf. If you want to connect through TCP sockets, set HOST to 'localhost' or '127.0.0.1' ('host' lines in pg_hba.conf). On Windows, you should always define HOST, as UNIX domain sockets are not available.
NAME ''(Empty string) The name of the database to use. For SQLite, it's the full path to the database file. When specifying the path, always use forward slashes, even on Windows (e.g. C:/www/STORE/db.sqlite3).
OPTIONS {} (Empty dictionary) Extra parameters to use when connecting to the database. Available parameters vary depending on database brand, consult the Django database engine package's documentation. See Table 1-2 for a list of Django database engine packages.
PASSWORD '' (Empty string) The password to use when connecting to the database. Not used with SQLite.
PORT '' (Empty string) The port to use when connecting to the database. An empty string means the default port. Not used with SQLite.
TEST {} (Empty dictionary) Defines database connection parameters -- like the ones described in this table -- for use in a Django project's test database.
TIME_ZONE None (Empty) Specifies the time zone for date times stored in a database. A rarely used option to streamline usage of database records that store times in a local timezone rather than UTC.
USER '' (Empty string) The username to use when connecting to the database. Not used with SQLite.

Install Python database packages

Besides configuring Django to connect to a database, you'll also need to install the necessary Python packages to communicate with your database brand -- the only exception to this is SQLite which is included in the Python distribution.

Each database relies on different packages, but the installation process is straightforward with the pip package manager. If you don't have the pip executable on your system, see the previous section in this chapter 'Install Django' in the 'Install pip' sub-section.

The Python packages for each database supported in Django in its 'out-of-the-box' state are enumerated in table 1-4. In addition, table 1-4 also includes the pip command to install each package.

Table 1-4 Python packages for different databases

Database

Python package

pip installation syntax

PostgreSQL

psycopg2

pip install psycopg2

MySQL

mysql-python

pip install PyMySQL
AND
pip install mysqlclient

Oracle

cx_Oracle

pip install cx_Oracle

SQLite

Included with the Python distribution

N/A

All other databases described in table 1-2

Automatically downloaded by pip, when installing django database specific package described in table 1-2

N/A

Database development libraries

If you receive an error trying to install one of the Python database packages in table 1-4, ensure the database development libraries are installed on your system. Database development libraries are necessary to build software that connects to a database.

Database development libraries are not related to Python or Django, so you'll need to consult the database vendor or operating system documentation (e.g. On a Debian Linux or Ubuntu Linux system you can install the MySQL development libraries with the following apt-get task: apt-get install libmysqlclient-dev).

Test Django database connection and build Django base tables

Once you update the Django settings.py file with database credentials, you can test it to see if the Django application can communicate with the database. There are several tasks you'll do throughout a Django project that will communicate with the database, but one of the most common tasks you can do right now to test a database connection is migrate the project's data structures to the database.

The Django database migration process ensures all Django project logic associated with a database is reflected in the database itself (e.g. the database has the necessary tables expected by a Django project). When you start a Django project, there are a series of migrations Django requires that create tables to keep track of administrators and sessions. This is always the first migration process a Django project runs against a database. So to test the Django database connection, lets run this first migration on the database to create this set of base tables.

To run a migration on a Django project against a database use the manage.py script in a project's BASE_DIR with the migrate argument (e.g. python manage.py migrate). The first time you execute this command the output should be similar to listing 1-15.

Listing 1-15. Run first Django migrate operation to create base database tables

[user@coffeehouse ~]$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying sessions.0001_initial... OK

As illustrated in listing 1-15, if the connection to the database is successful, Django applies a series of migrations that create database tables to manage users, groups, permissions and sessions for a project. For the moment, don't worry too much about how these Django migrations work or where they are located -- I'll provide details later -- just be aware these migrations are needed by Django to provide some basic functionality.

Tip Connect directly to the database. If you receive an error trying to connect to the database or migrating the Django project to create the initial set of database tables, try to connect directly to the database using the same Django parameters. On many occasions a typo in the Django variables NAME, USER, PASSWORD, HOST or PORT can cause the process to fail or inclusively the credentials aren't even valid to connect directly to the database.