Discovering django on Debian – part 2: configuring django databases

MySQL (Photo credit: Wikipedia)

In the previous Django tutorial, we left when a website run locally. Now, we will fill it with content. As always with python, we do not like to repeat ourselves. So if our website has several pages with common information elements, we will not repeat them. Instead, we will store all information elements in a database. This tutorial will show you how we setup a local mysql database associated with the website.

sudo apt-get install mysql-server
pip install MySQL-python
ps aux |grep mysql

During the install, you will have to setup the root password for mysql. We will create a database for my_site, and grant the rights on this databese to a new mysql user corresponding to the django application.

mysql -u root -p

I recommend the following mysql tutorial, if you are not familiar with basic SQL operations.

In the mysql prompt, we use the following commands (pick the password that you like): 

create database my_site_db;
CREATE USER 'djangoDbUser'@'localhost' IDENTIFIED BY 'some_pass';
grant all privileges on my_site_db.* to djangoDbUser;
exit;

If you want to modify mysql configuration:

vim /etc/mysql/my.cnf 

For instance, you can activate the logs, it will help us if we mess up the configuration

# * Logging and Replication 
# 
# Both location gets rotated by the cronjob. 
# Be aware that this log type is a performance killer. 
# As of 5.1 you can enable the log at runtime! 
general_log_file        = /var/log/mysql/mysql.log 
general_log             = 1 

Now, we need to edit django project’s settings, in settings.py, to reflect the database configuration.

geany my_site/*.py &

In this file we configure the database in accordance to our mysql configuration:

DATABASES = {
    'default': {
        #'ENGINE': 'django.db.backends.', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
        'ENGINE': 'django.db.backends.mysql', # USE MYSQL
        'NAME': 'my_site_db',                      # Or path to database file if using sqlite3.
        'USER': 'djangoDbUser',                      # Not used with sqlite3.
        'PASSWORD': 'some_pass',                  # Not used with sqlite3.
        'HOST': '',                      # Set to empty string for localhost. Not used with sqlite3.
        'PORT': '',                      # Set to empty string for default. Not used with sqlite3.
    }
} 

Now, we install mysql-python and its dependencies, and check if Django manages to access the database:

sudo apt-get install libmysqlclient-dev python-setuptools
sudo apt-get build-dep python-mysqldb
pip install mysql-python
python manage.py syncdb 

At this stage, if you followed my steps, you should see something like this:

(virtualenv_1)root@Laptop:/home/alan/virtualenv_1/djangoProj_1/mysite# python manage.py syncdb
Creating tables ...
Creating table auth_permission
Creating table auth_group_permissions
Creating table auth_group
Creating table auth_user_user_permissions
Creating table auth_user_groups
Creating table auth_user
Creating table django_content_type
Creating table django_session
Creating table django_site

You just installed Django's auth system, which means you don't have any superusers defined.
Would you like to create one now? (yes/no): yes
Username (leave blank to use 'root'): 
E-mail address: [email protected]
Password: 
Password (again): 
Superuser created successfully.
Installing custom SQL ...
Installing indexes ...
Installed 0 object(s) from 0 fixture(s)

To check that everything went fine, we can list the tables in the database that we created, by logging to mysql with the user and the password that we configured previously (djangoDbUser and some_pass):

mysql -u djangoDbUser -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_site_db         |
+--------------------+
2 rows in set (0.00 sec)

mysql> show tables in my_site_db
    -> ;
+----------------------------+
| Tables_in_my_site_db       |
+----------------------------+
| auth_group                 |
| auth_group_permissions     |
| auth_permission            |
| auth_user                  |
| auth_user_groups           |
| auth_user_user_permissions |
| django_content_type        |
| django_session             |
| django_site                |
+---------------------

REMEMBER TO USE REAL PASSWORD AND NOT CRAPPY ONE AS IN THIS EXAMPLE, IF YOUR APPLICATION IS TO RUN IN THE WILD INSTEAD OF LOCALHOST.

That’s it! In the next part of the tutorial, we will build our website and add content.