Getting Started with MariaDB using Docker, Python and Flask

Since being forked from MySQL back in 2009, MariaDB has become one of the most popular databases of choice for developers over the past decade. While many technologists have likely gravitated to it as a solution due to its open source roots and that it's rooted in the relational database world, that really only begins to scratch the surface of what MariaDB has to offer.

It's no secret that over the years, MariaDB has added many features and functionality that have set it apart from other database solutions, but what developers may not know is that there are two groups actively contributing to the ever advancing code base; MariaDB Foundation and MariaDB Corporation.

• MariaDB Foundation is the custodian of the MariaDB community code and guardian of the MariaDB community and the open source principles that have propelled it to being one of the most popular databases in the world.
• MariaDB Corporation contributes to the community codebase as well, but also provides superior quality, enterprise grade products that thrusts MariaDB into the forefront of database vendors. MariaDB Corporation even offers columnar and HTAP based solutions, but I digress.

With that in mind, I've written this short walkthrough to provide a launchpad for you to get started using MariaDB with Docker and Python (with the help of Flask), within a matter of minutes, so you can check things out for yourself.

Requirements

Before jumping into code, you're going to need to make sure you have a few things on your machine:

• MariaDB client
• Docker
• Python (v. 3+)
• MariaDB Connector/C (v. 3.1.5+)

Using a MariaDB Docker Container

To pull the MariaDB Server image and spin up a container simply open a terminal window and run the following:

$ docker run -p 3306:3306 -d --name mariadb -eMARIADB_ROOT_PASSWORD=Password123! mariadb/server:10.4 

The previous command will spin up a MariaDB Server container that you can connect to and communicate with using the MariaDB client.

While you can certainly use a variety of other SQL clients, for the sake of keeping things simple and uniform, I've only included samples using the official MariaDB client.

Connect to your MariaDB instance by executing the following command in a terminal window.

$ mariadb --host 127.0.0.1 -P 3306 --user root -pPassword123!

You should see something like the following, which means you've successfully connected to the MariaDB instance!

Next, create a new database.

CREATE DATABASE demo;

Then create a new table.

CREATE TABLE demo.people (name VARCHAR(50));

Finally, insert a couple records.

INSERT INTO demo.people VALUES ('rob'), ('tracy'), ('sam'), ('duke');

Getting Started with MariaDB Connector/Python

Now that you've downloaded, installed, and stood up a MariaDB database (using Docker), you're ready to put it to use within a new Python app.

Recently, MariaDB announced the availability of MariaDB Connector/Python beta! Connector/Python enables Python programs to access MariaDB databases using an API that is compliant with the Python DB API 2.0 (PEP-249).

MariaDB developers chose to optimize performance of the new connector by writing it in C which allowed them the ability to leverage the MariaDB Connector/C client library for client-server communication.

To use the new MariaDB Python connector, you'll also need to install MariaDB Connector/C (v3.1.5+).

Set up and activate a virtual environment

A virtual environment is a directory tree which contains Python executable files and other files which indicate that it is a virtual environment. Basically, it's the backbone for running your Python Flask app.

Creation of virtual environments is done by executing the following command:

$ pyvenv venv

Tip: pyvenv is only available in Python 3.4 or later. For older versions please use the virtualenv tool.

Before you can start installing or using packages in your virtual environment, you’ll need to activate it. Activating a virtual environment will put the virtual environment-specific python and pip executables into your shell’s PATH.

Activate the virtual environment using the following command:

$ . venv/bin/activate activate

Create a Flask application

Flask is a micro web framework written in Python. It is classified as a microframework because it does not require particular tools or libraries.

TL;DR (if I am even allowed to do that for two sentences), it's a great, lightweight framework to use for an API.

First, install Flask.

$ pip3 install flask

Then create a new file called api.py that will be used to create a new Flask app, and add the following code:

# import necessary packages
import flask

# create the flask app
app = flask.Flask(__name__)
app.config["DEBUG"] = True

@app.route('/', methods=['GET'])
def index():
   return 'Success!'

# run the app
app.run()

What you have now is the foundation for an extremely simple API. You can test it out by running the app and testing the endpoint.

To create an instance, it has to be give a name. Using (__name__) ensures that it can be started as an application or imported as a module. The use of the route() decorator lets our flask app know which URL should trigger the corresponding method.

You're ready to run the app.

$ python3 api.py

By default, the app will start running on port 5000. You can test the results by requesting data from http://localhost:5000.

Integrate with MariaDB

Once you've created a simple Flask application, you're ready to add code to integrate with MariaDB.

Start by installing the MariaDB Python connector.

$ pip3 install mariadb

Open api.py, and add code to import the MariaDB and JSON packages directly under the Flask package import.

import json
import mariadb

Next, add a configuration object with the connection information for your running MariaDB instance.

config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': 'Password123!',
    'database': 'demo'
}

The config object will be used within a new route method block to connect to MariaDB.

# route to return all people
@app.route('/api/people', methods=['GET'])
def index():
   # connection for MariaDB
   conn = mariadb.connect(**config)
   # create a connection cursor
   cur = conn.cursor()
   # execute a SQL statement
   cur.execute("select * from people")

   # serialize results into JSON
   row_headers=[x[0] for x in cur.description]
   rv = cur.fetchall()
   json_data=[]
   for result in rv:
        json_data.append(dict(zip(row_headers,result)))

   # return the results!
   return json.dumps(json_data)

Bringing it all together, your api.py content should look like the following:

# import the necessary packages
import flask
import json
import mariadb

app = flask.Flask(__name__)
app.config["DEBUG"] = True

# configuration used to connect to MariaDB
config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': 'Password123!',
    'database': 'demo'
}

# route to return all people
@app.route('/api/people', methods=['GET'])
def index():
   # connection for MariaDB
   conn = mariadb.connect(**config)
   # create a connection cursor
   cur = conn.cursor()
   # execute a SQL statement
   cur.execute("select * from people")

   # serialize results into JSON
   row_headers=[x[0] for x in cur.description]
   rv = cur.fetchall()
   json_data=[]
   for result in rv:
        json_data.append(dict(zip(row_headers,result)))

   # return the results!
   return json.dumps(json_data)

app.run()

Testing it out

Start the app.

$ python3 api.py

Then test the people endpoint. This can be done through a variety of techniques (e.g. directly within a browser, Postman, etc.).

For instance, considering using a curl command:

$ curl http://localhost:5000/api/people

Which yields the following JSON response payload:

[{"name":"rob"},{"name":"tracy"},{"name":"duke"},{"name":"sam"}]

Just the beginning

Hopefully this short walkthrough has helped you get started using MariaDB with Docker, Python and Flask. And, yea, this was a very simple example, but it only gets more exciting from here!

I highly recommend that you check out all of what MariaDB has to offer and how you can use a truly innovative database to create modern applications.