Getting Started with MariaDB using Docker, Python and Flask
Tags: mariadb, docker, python, flask
Published: 2020-04-25 19:38:58

Since being [forked from MySQL](https://en.wikipedia.org/wiki/MariaDB) back in 2009, [MariaDB](http://www.mariadb.com/) 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](https://mariadb.org/) 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](https://mariadb.com/) 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](http://www.docker.com/) and [Python](https://www.python.org/) (with the help of [Flask](https://flask.palletsprojects.com/en/1.1.x/)), 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](https://mariadb.com/products/skysql/docs/clients/)
* [Docker](https://www.docker.com/products/docker-desktop)
* [Python](https://www.python.org/downloads/) (v. 3+)
* [MariaDB Connector/C](https://mariadb.com/products/skysql/docs/clients/mariadb-connector-c-for-skysql-services/) (v. 3.1.5+)

# Using a MariaDB Docker Container

To pull the [MariaDB Server](https://mariadb.com/docs/) [image](https://hub.docker.com/r/mariadb/server) and spin up a container simply open a terminal window and run the following.

```bash
$ 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](https://mariadb.com/products/skysql/docs/clients/). 

> 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.

```bash
$ 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!

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ei68xshd01ovhu8i8364.png)

Next, create a new database.

```sql
CREATE DATABASE demo;
```

Then create a new table.

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

Finally, insert a couple records.

```sql
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](https://mariadb.com/docs/develop/connector-python/)! Connector/Python enables Python programs to access MariaDB databases using an API that is compliant with the Python DB API 2.0 (PEP-249). 

> For more information on the announcement of MariaDB Connector/Python check out my blog post [here](https://mariadb.com/resources/blog/mariadb-connector-python-beta-now-available/)!

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](https://mariadb.com/products/skysql/docs/clients/mariadb-connector-c-for-skysql-services/) (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](https://docs.python.org/3/library/venv.html#venv-def) is done by executing the following command:

```bash
$ pyvenv venv
```

> Tip: pyvenv is only available in Python 3.4 or later. For older versions please use the [virtualenv](https://virtualenv.pypa.io/en/latest/) 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:

```bash
$ . venv/bin/activate activate
```

# Create a Flask application

[Flask](https://flask.palletsprojects.com/en/1.1.x/) is a micro web framework written in Python. It is classified as a [microframework](https://en.wikipedia.org/wiki/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.

```bash
$ 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:

```python
# 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.

```bash
$ python3 api.py
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/qxxypatppkftj1unc69r.png)

By default, the app will start running on port 5000. You can test the results by requesting data from [http://localhost:5000](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.

```bash
$ pip3 install mariadb
```

Open _api.py_, and add code to import the MariaDB and [JSON](https://docs.python.org/3/library/json.html) packages directly under the Flask package import.

```python
import json
import mariadb
```

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

```python
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.

```python
# 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:

```python
# 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.

```bash
$ python3 api.py
```

Then test the _peoples_ endpoint. This can be done through a variety of techniques (e.g. directly within a browser, [Postman](https://www.postman.com/), etc.). 

For instance, considering using a [curl](https://curl.haxx.se/) command:

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

Which yields the following JSON response payload:

```json
[{"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.