Category Archives: MySQL

Administrate Project – Technical Project Manager

The client, a highly respected training company, was using an in-house developed system to manage the courses, bookings, delegates, and sales and marketing processes. When the business outgrew the system they asked me to evaluate an online solution (Administrate).

After presenting my in-depth analysis, the Board of Directors decided to proceed, and hired me as the Technical Project Manager.

I created Python scripts to clean the old database and restructure the data for the new system. Under my guidance, the internal developer integrated the client’s website with Administrate. Administrate staff and myself provided the training.

Overall the transition went very smoothly. The only snag was with some of the more advanced marketing lists (used for email marketing campaigns). I had already expressed my concerns to Administrate that the existing system wouldn’t be able to handle these queries, but was reassured by them that it would. Fortunately, when my concerns were confirmed, Administrate agreed to make the necessary changes. They were very good about it and did this without any protest, although this may have been different without my careful preparations. Administrate spent a good few developer months creating a completely new reporting system, in consultation with me.

Meticulous preparations meant a smooth transition and saved the client months of bespoke work.

Django Migrations

Django has a great system for maintaining your database structure, for keeping your RDMS (typically MySQL, PostgreSQL or SQLite) in line with your Django-defined Models, and within your version control system.

I recently hit a snag with a live client site, which already has 100s of registered users, significant numbers in a small market. Something to handle with extreme caution. Time to dive a bit deeper into Django’s migrations.

Note: migrations were introduced in Django 1.7

Models

Instead of working directly on the database, Django expects you to define the database structure in Python code. This powers many of Django’s features. For instance, the admin system gives  administrators an easy way to maintain the data, and forms can be quickly created.

Once you’ve defined the models and configured the database connection (username, password, etc), Django creates the table structures for you.

Generate the initial database structure by running
>> python manage.py makemigrations

This generates the ‘migration’ path (see below) from an empty database to the initial database

Then apply this to the actual database
>> python manage.py migrate

If you’ve used Django 1.6 or earlier, note that syncdb has been deprecated and replaced by migrate

Migrations

But what happens when you change a table, e.g. when you delete or create a field? And how do you track the changes on your development machine, and make sure the same changes are applied to your test and live systems?

Every time you run
>> python manage.py makemigrations
Django compares the last known Model structure (by running through all past migrations) to the current Model structure and generates a new migrations file, one for each Django app with a changed Model structure. These files are stored in <app>/migrations and are numbered (starting with 0001).

Running
>> python manage.py migrate
applies the most recent migrations. If the database is empty, then Django will start from 0001_initial.py. Otherwise, it checks the migrations table to see, for each app, which migration is the latest one which has been applied to the database, and then runs any later migrations

Migrations and version control

Because the migration files are ordinary Python (text) files, stored in an app’s sub directory, your version control system will manage them for you. When you update the test or live system with the latest code changes, the latest migrations will also be copied across.

Just make sure to run
>> python manage.py migrate
after updating the source code through your version control system

 What went wrong

Normally Django’s migration system works beautifully, making sure the models and database structure stay in sync, between each other and across the development, test and live systems.

Due to some complicated reasons involving tight deadlines and budgets, holidays, and more, something went wrong with the migrations.

The client requested some extra database fields. I added the fields to the model and ran makemigrations.

First nothing happened. Just to be sure I ran
>> python manage.py makemigrations <app name>
and Django told me it had created migration 0001_initial, instead of an incremental change. Checking the database, no changes were made.

Diving into git, I could see a series of past migration files. I could probably roll back the changes to them (i.e. which deleted them), but decided to try something a bit simpler first.

 And how I fixed it

I deleted all existing migrations, from <app name>/migrations and commented out the new database fields in the model.

Running
>> python manage.py makemigrations
created a few 0001_initial migration file, which matches the original model and the current database structure

I removed the comments, to re-instate the new database fields, and re-ran
>> python manage.py makemigrations
to create a migrations file which adds the new fields to the database

Finally
>> python manage.py migrate
applied the changes to the database, adding the new fields

So far so good

Using PHPMyAdmin I can now see the new database fields in the MySQL database

However, I have yet to apply the changes to the live system. I’ll do this once the changes are made and tested. But first I’ll check the revision history on the server and I’ll backup the database, just in case

Django with MySQL

After PostgreSQL, MySQL is probably the most popular DBMS (database management system) used with Django. Here is how I set it up

  1. Install MySQL, etc
    1. Use the Software Manager to install mysql-server (write down the root password you entered here) and phpmyadmin
  2. In your browser, go to http://localhost/phpmyadmin. Enter “root” and the password you wrote down
    1. Note, on my LinuxMint 17 installation I got an error “The mcrypt extension is missing. Please check your PHP configuration”. I used the instructions at http://stackoverflow.com/questions/22721630/the-mcrypt-extension-is-missing-please-check-your-php-configuration to fix this. The commands I used were
      sudo apt-get install mcrypt php5-mcrypt
      php5enmod mcrypt
      sudo service apache2 restart
      The first line may not have been necessary. I made the beginner’s mistake of not using “sudo” at the start of the first line, so apache didn’t actually reboot, until I got this right
  3. Create a database and a user for Django (still in phpmyadmin in your browser)
    1. Second line from the top, click on “Databases”
      1. Create database, enter the name (e.g. “HelloWorldDjango”)
      2. For collation, https://docs.djangoproject.com/en/dev/ref/databases/  suggests using utf8_general_ci, but read the note about case sensitivity
      3. Click on Create
    2. Second line from the top, click on “Users”
      1. Click on “Add user”
      2. Enter the user name – e.g. “DjangoUser” – and write it down
      3. Set the host to “localhost”
      4. Enter a password, or click on the “Generate” button to get a random (and fairly secure) password  and write it down (or copy it into an editor  – you’ll need it soon)
      5. Leave the rest as it is, and click on “Go”
    3. Against your new user, click on the “Edit Privileges” link
      1. Scroll down to the “Database-specific privileges” block, next to “Add privileges on the following database:” click on the “Use text field:” dropdown, and select the new database name (e.g. “HelloWorldDjango”)
      2. At the top of the first box, next to “Database-specific privileges”, click on “Check All”
      3. Click on the “Go” button
    4. Now tell Django to use the database
      1. Open <dev root>Projects/HelloWorldDjango/HelloWorldDjango/settings.py
      2. Replace the 6 lines which start with “DATABASES = {” with:
        DATABASES = {
        ‘default’: {
        ‘ENGINE’: ‘django.db.backends.mysql’,
        ‘NAME’: ‘HelloWorldDjango’,
        ‘USER’: ‘DjangoUser’,
        ‘PASSWORD’: ‘(your password)’,
        ‘HOST’: ‘localhost’, # Or an IP Address that your DB is hosted on
        ‘PORT’: ‘3306’,
        }
        }
    5. Install python-mysql – so Python/Django can access the MySQL database
      1. sudo apt-get install libmysqlclient-dev
      2. sudo apt-get install python-dev
      3. Switch to the virtual env
        source <dev root>/envs/DjangoTest/bin/activate
      4. pip install MySQL-python
    6. And test that it is all working correctly
      1. Still in the virtual env (see previous step)
      2. cd <dev root>Projects/HelloWorldDjango
      3. python manage.py shell
      4. >>> from django.db import connection
      5. >>> cursor = connection.cursor()

Congratulations. You can now create some Django models to start using the database