In this post we will use the MySQL 5.7.7 labs release which has support for JSON documents stored in a special data type. We will be using Connector/Python and show how to get going with updating documents and getting data out.

Required read

If you wonder what this is all about, please check the following great reads from the MySQL Server team:

Please also consider that MySQL 5.7 introduce lots of new features, changes and also deprecates/removes functionality. Read up the “What is new in MySQL 5.7”-section of the manual to know more.

Installing MySQL 5.7.7 Labs

If work on Oracle Linux or Red Hat Enterprise 6, you are lucky: a binary is provided. So go ahead and download the file mysql-5.7.7-labs-json-linux-el6-x86_64.tar.gz.

For the rest of, we have to compile and follow the instructions from the manual. If too long for you, here are the few steps to get you going using the downloaded tar ball mysql-5.7.7-labs-json.tar.gz:

$ tar xzf mysql-5.7.7-labs-json.tar.gz
$ cd mysql-5.7.7-labs/build
$ cmake .. -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.7.7-labs
$ make
$ make install

Make sure that:

you can write to the path you set for the CMAKE_INSTALL_PREFIX,
and you have your operating system’s development packages/applications installed.

Starting MySQL 5.7

Here are the instructions to get you going with a MySQL Server 5.7 configured so the root user has no password. For testing, this is sufficient. Note, I do everything using OS X, but instructions should be OS agnostic unless otherwise noted. (Windows users should adapt a bit.)

Configuration file

We will be using data directory in the MySQL installation itself. Bad practice, but remember: this is testing and it’s a lab release. So lets make sure it’s self contained.

Save the following to /opt/mysql-5.7.7-labs/my.cnf:

[mysqld]
basedir = /opt/mysql-5.7.7-labs/
datadir = /opt/mysql-5.7.7-labs/data/
socket = mysql.sock
log_error = mysql_labs.err
port = 3357      # Or use default 3306 if no MySQL server is running

Initialize and Start

Initializing the MySQL data directory changed a bit, and MySQL 5.7 does not use the mysql_install_db any longer:

$ cd /opt/mysql-5.7.7-labs
$ ./bin/mysqld --defaults-file=./my.cnf --initialize-insecure

To start the MySQL Server, use mysqld_safe:

$ cd /opt/mysql-5.7.7-labs
$ ./bin/mysqld_safe --defaults-file=./my.cnf &

At this point, if MySQL does not start, check the error log. In our example it is located in the data directory:

$ less /opt/mysql-5.7.7-labs/data/mysql_labs.err

Look for Aborting.

Install MySQL Connector/Python

We are a Python shop. We do it Python.

Of course you know how to install Connector/Python, but here’s the pip one liner (yeah, I know.. annoying extra command line argument):

$ pip install mysql-connector-python --allow-external mysql-connector-python

MySQL JSON data type and Python

You could store your JSON data (or documents) in a TEXT field. That’s fine, but that’s so 2014. In 2015 and later you can take advantage of the new MySQL data type called JSON.

CREATE TABLE `blogs` (
  `doc` json DEFAULT NULL,
  `updated` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Inserting data

Lets do JSON in Python

import json
import mysql.connector

cnx = mysql.connector.connect(user="root", port=3357, database="blog")
cur = cnx.cursor()

new_blog = {
    'page_id': 'c262672a1e9e',
    'title': 'MySQL 5.7 with JSON and Connector/Python',
}

insert = "INSERT INTO blogs (doc) VALUES (%s)"
cur.execute(insert, (json.dumps(new_blog),))
cnx.commit()

Selecting data

select = (
    "SELECT JSN_EXTRACT(doc, '$.page_id'), "
    "JSN_EXTRACT(doc, '$.title') "
    "FROM blogs "
    "WHERE JSN_CONTAINS(doc, JSN_OBJECT('page_id', %s))"
)

cur.execute(select, ('c262672a1e9e',))

for row in cur:
    print("%s | %s" % (row[0], row[1]))

The result is:

"c262672a1e9e" | "MySQL 5.7 with JSON and Connector/Python"

Conclusion

I hope the above will get going with MySQL 5.7.7 labs release. There are two things though. There is the problem with committing the transaction explicitly using cnx.commit(). Need to check that. And there is the issue with the extra quotes in the result. But I guess we’ll get that figured out as well.