Uncategorized

Set Up MySQL for a Django Application

I wanted to store a variable list of things in a Django Model. One way to do that is to create another Model, another way is to use a JSON field in your current Django Model. Unfortunately, the default SQLite3 database does not support JSON fields, and you will need to set up a MySQL database.

Install MySQL with Homebrew

I am using a Mac, so I used brew to install MySQL

$ brew install mysql

This should eventually spit out something like

==> Installing mysql
==> Downloading https://homebrew.bintray.com/bottles/mysql-5.7.20.high_sierra.bottle.tar.gz
######################################################################## 100.0%
==> Pouring mysql-5.7.20.high_sierra.bottle.tar.gz
==> /usr/local/Cellar/mysql/5.7.20/bin/mysqld --initialize-insecure --user=connorjohnson --basedir=/usr/local/Cellar/mysql/5.7.20 --datadir=/usr/local/var/mysql --tmpdir=/tmp
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start

Per the directions, start the service using,

$ brew services start mysql

Create a Database and User

This is part is borrowed from Digital Ocean’s excellent tutorial. Log into the database,

$ mysql -u root

And create a database, a user, and grant that user privileges on that database,

CREATE DATABASE myproject CHARACTER SET UTF8;
CREATE USER myprojectuser@localhost IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON myproject.* TO myprojectuser@localhost;
FLUSH PRIVILEGES;
exit

Install mysqlclient

This might require some debugging. I used this GitHub issue to resolve my problems. I added some environment variables and everything worked out.

export LDFLAGS="-L/usr/local/opt/openssl/lib"
export CPPFLAGS="-I/usr/local/opt/openssl/include"
pip install mysqlclient

Configure Django to use MySQL

Open the settings.py file, usually located in project/project/, and change DATABASES to the following,

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'myproject',
        'USER': 'myprojectuser',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '',
    }
}

Add JSON to Your Models

Change your model to something like the following, note lines 3 and 9.

# models.py
from django.db import models
from django_mysql.models import JSONField

# Create your models here.
class Quiz(models.Model):
    title = models.CharField(max_length=50)
    description = models.TextField()
    questions = JSONField()
    slug = models.SlugField(unique=True)
    class Meta:
        verbose_name_plural = "Quizzes"