Tuesday, April 7, 2015

Creating a Python Flask CRUD API with a MySQL DB

Now that we're a bit more familiar with REST APIs, we're going to be creating a CRUD[1] application using Flask. We're actually only focusing on Create and Read, but you should be able to figure out how to implement the Update and Delete, yourself.

Prerequisites: This is a direct follow up to Connecting a Python Script to a MySQL Database. Carry out the MySQL setup steps. This guide was written for Mac OS X users.

Preparing Our MySQL Database

Make sure to execute these steps before you go any further. Once you have MySQL running, we'll need to create and populate our database:

# ssh into the vm
cd vagrant-ansible-mysql-python
vagrant ssh

# create a user with priveleges to access the db
mysql -uroot

# create and populate db

USE test;
CREATE TABLE `test`.`name` (`id` INT NOT NULL AUTO_INCREMENT, `firstname` VARCHAR(45) NULL, `lastname` VARCHAR(45) NULL, PRIMARY KEY (`id`));
INSERT INTO `test`.`name`(`firstname`,`lastname`) VALUES("Solid","Snake"),("Hal","Emmerich"),("Meryl","Silverburgh");

Creating Our Python Script

Prepare the Environment

# create the directory and activate virtualenv
mkdir python-mysql && cd python-mysql
virtualenv venv
source venv/bin/activate

# install dependencies
pip install mysql-python flask

Creating Our Python App

We're only going to write a nameserver.py file, this time. Make sure to run the Flask service with python nameserver.py before you send any requests. Also, you're going to be learning a bit about Flask's sugar by reading through the code. You'll also notice that we'll be opening and closing the connection to the database before and after each request. This is especially important because MySQL closes long standing connections[2].

Making Requests

Run the following commands in the terminal:

# request names
curl -i -X GET http://localhost:5000/names

# insert a new name
curl -i -H "Content-Type: application/json" -X POST -d '{"firstname": "FirstName", "lastname":"LastName"}' http://localhost:5000/add

# request names again to see what you just added
curl -i -X GET http://localhost:5000/names



  1. Excellent post thanks a bunch for posting it

  2. This comment has been removed by the author.

    1. I had a internanl server error it was because I didnt create a database named test.

  3. I am a newbee !!

    My python script is as follows:

    @app.route("/names", methods=['GET', 'POST'])
    def names():
    result = query_db("SELECT firstname,lastname FROM test.name")
    data = json.dumps(result)
    resp = Response(data, status=200, mimetype='application/json')
    return resp

    Results I get from my browser ( is:
    [{"lastname": "Snake", "firstname": "Solid"}, {"lastname": "Emmerich", "firstname": "Hal"}, {"lastname": "Silverburgh", "firstname": "Meryl"}, {"lastname": "LastName", "firstname": "FirstName"}, {"lastname": "lastname2", "firstname": "FirstName"}, {"lastname": "lastname3", "firstname": "FirstName"}]

    How do I get it to list line by line.

  4. Try this @Seng to print the json dump line by line:

    data = json.dumps(result, sort_keys=True,
    indent=4, separators=(',', ': '))

  5. Try this @Seng:

    data = json.dumps(result, sort_keys=True,
    indent=4, separators=(',', ': '))