100 Days of Learning: Day 10 & 11 – Connecting to MariaDB from Python and OpenFaaS

Photo by benjamin lehman on Unsplash

Here is my Log book.

Git repository for the functions.

Adding MariaDB connector as a dependency

Continuing on from yesterday where I had a mocked API in place, the next phase is to add storage for the lego database.

I will be using SQLAlchemy as the ORM layer and this will connect to the MariaDB instance I have setup in AWS RDS. In other to connect to MariaDB I will need to use mysql-connector MariaDB connector

Develop and test locally

Instead of doing trial and error development, I want to develop and test the Python code locally on my machine first and once I know it works it can be transferred to the OpenFaaS code.

$ cd legodb/legodb
$ ls
handler.py        requirements.txt

# Install and activate a virtual environment
$ python3 -m venv ./venv
$ source venv/bin/activate
(venv)$

(venv)$ python3 -m pip install --upgrade pip

Installing mariadb-connector-c and mariadb (connector)

(venv)$ brew install mariadb-connector-c
(venv)$ pip install mariadb

Collecting mariadb
  Using cached mariadb-1.0.6.tar.gz (67 kB)
Using legacy 'setup.py install' for mariadb, since package 'wheel' is not installed.
Installing collected packages: mariadb
    Running setup.py install for mariadb ... done
Successfully installed mariadb-1.0.6

The MariaDB connector for Python requires the MariaDB connector for C to be installed first. This is going to be a learning curve later to see how I get the faas-cli + template + faasd to install these dependencies (including C ones on basically an Ubuntu image)

Make a test connection

First I am testing to see if the connector is working and that I can connect to AWS database.

(venv)$ python
>>> import mariadb
>>> connection = mariadb.connect(user="admin", \
password="DA_PASSWORD", \
host="xyzabc.rds.amazonaws.com", \
port=3306,database="openfaasdb")
>>> cursor = connection.cursor()
>>> cursor.execute("SELECT Description FROM openfaasdb.legosets")
>>> for description in cursor:
...     print(description)
...
('Pirates of Barracuda Bay',)
('Medieval Blacksmith',)
>>> exit() # This is a bit like the "how do you quit vim?"

Nice! Ok next test is to add it to the handler.py and see if we can get the dependencies to install correctly on faasd.

Configure function and dependencies

# Update the requirments.txt to include the packages we have installed locally
(venv)$ pip freeze > requirements.txt
(venv)$ cat requirements.txt
mariadb==1.0.6

I am 100% sure that running faas-cli up will explode because we need a way to also specify that mariadb-connector-c be installed. If faas-cli doesn’t explode then faasd surely will.

$ faas-cli up -f legodb.yml
...
#22 [stage-1 14/18] COPY function/requirements.txt	.
#22 sha256:333b1ca8a3738bc696305e1b571339f0dd690a63f9211340f23111e9be70c1ed
#22 DONE 0.2s

#23 [stage-1 15/18] RUN pip install --user -r requirements.txt
#23 sha256:bc0ee19013bf0423e61eb77d7d2f6e9ae7fc90b25b9557e7d451250819bb3c0d
#23 1.848 Collecting mariadb==1.0.6
#23 2.017   Downloading mariadb-1.0.6.tar.gz (67 kB)
#23 2.286     ERROR: Command errored out with exit status 1:
...
#23 2.286     OSError: mariadb_config not found.
#23 2.286
#23 2.286     Please make sure, that MariaDB Connector/C is installed on your system.
#

Ok that confirms at least that during faas-cli build it will explode.

Down the rabbit hole

My suspicion earlier was spot on that it was going to be a learning curve to figure out how to get other non-python dependencies working.

I read a number of docs and tutorials and even came across a github issue for OpenFaaS that described you can pass values to the template’s Dockerfile from you yaml file. But sometimes you will read a lot of things and the brain has not yet made all the required connections.

Below I will describe the process I went through to figure this out.

# 1st attempt, I edited template.yml
build_options:
  - name: dev
    packages:
      ... 
      - mariadb-connector-c
# Failed in the same way about not being able to find mariadb_config

# 2nd attempt, I edited legodb.yml
functions:
  legodb:
    ...
    build_args:
      # ADDITIONAL_PACKAGE is used in the Dockerfile
      ADDITIONAL_PACKAGE: mariadb-connector-c
# Failed in the same way

# 3rd attempt, I found a StackOverflow post about that you might also need mariadb-dev
    ADDITIONAL_PACKAGE: mariadb-dev mariadb-connector-c
# Ok we are getting somewhere, the error is now about not being able to find gcc

# 4th attempt
    ADDITIONAL_PACKAGE: gcc mariadb-dev mariadb-connector-c
# Now the error is about gcc not being able to find limits.h header file

Clearly I need help here. So I joined the OpenFaaS Slack and asked for help on this. Also I called it quits at this stage for the night.

Next day I got a reply from Richard Gee saying that the build_options would be a better place for this and pointed me to the docs.

Ironically my 1st attempt was on the right path, but the missing bit was that I assumed that the packages from the template.yml was being used during faas-cli build. Turns out you have to pass --build-option dev to install the listed packages.

# template.yml
language: python3-http
fprocess: python index.py
build_options:
  - name: dev
    packages: 
      # ... Added the following 2 packages
      - mariadb-dev
      - mariadb-connector-c

$ faas-cli up --build-option dev -f legodb.yml
# This worked!

Getting the list of Lego sets from MariaDB

First we need to start using secrets and environment variables to pass the database details to the OpenFaaS function.

version: 1.0
provider:
  name: openfaas
  gateway: http://192.168.64.4:9999
functions:
  legodb:
    lang: python3-http
    handler: ./legodb
    image: andrejacobs42/legodb:latest
    environment:
      database-name: openfaasdb
      database-port: 3306
    secrets:
      - database-host
      - database-user
      - database-password

Supply the secrets to the faasd instance.

# Copy the hostname and then create secret
$ pbpaste | faas-cli secret create database-host
Creating secret: database-host
Created: 200 OK

# Copy the username and then create secret
$ pbpaste | faas-cli secret create database-user

# Copy the password and then create secret
$ pbpaste | faas-cli secret create database-password

I will be using MariaDB directly to query the database.

import os
import json
import mariadb

# GET /legosets : Returns the list of lego sets
# POST /legoset : Add a new lego set to the database

def handle(event, context):
    response = {'statusCode': 400}

    if event.method == 'GET':
        if event.path == '/legosets':
            legosets = get_list_of_legosets()
            response = {'statusCode': 200, 
                'body': legosets,
                'headers': {'Content-Type': 'application/json'}
                }
    elif event.method == 'POST':
        if event.path == '/legoset':
            response = add_new_legoset(event.body)
   
    return response

def load_secret(name):
    filepath = os.path.join('/var/openfaas/secrets/', name)
    with open(filepath) as f:
        secret = f.read()
    return secret

def database_connection():
    host = load_secret('database-host')
    user = load_secret('database-user')
    password = load_secret('database-password')
    database_name = os.environ.get('database-name')
    database_port = int(os.environ.get('database-port', '3306'))

    try:
        connection = mariadb.connect(
            user=user,
            password=password,
            host=host,
            port=database_port,
            database=database_name
        )
        return connection
    except mariadb.Error as error:
        print(f"Error: {error}")
        return None

def get_list_of_legosets():
    try:
        connection = database_connection()
        cursor = connection.cursor()
        cursor.execute("SELECT LegoID, Description FROM legosets")
        result = []
        for legoID, description in cursor:
            result.append({ "legoID": legoID, "description": description})
        return {"sets": result}
    except mariadb.Error as error:
        print(f"Error{error}")
        return {"error": f"{error}"}

def add_new_legoset(body):
    response = None
    try:
        inputJSON = json.loads(body.decode('utf8').replace("'", '"'))
        response = {
            'statusCode': 200,
            'body': {'received': inputJSON},
            'headers': {'Content-Type': 'application/json'}
        }
    except ValueError:
        response = {
            'statusCode': 400,
            'body': {'reason': 'Invalid JSON'},
            'headers': {'Content-Type': 'application/json'}
         }
    return response

Build and test

$ faas-cli up --build-option dev -f legodb.yml
$ curl -s http://192.168.64.4:9999/function/legodb/legosets | jq
{
  "sets": [
    {
      "description": "Pirates of Barracuda Bay",
      "legoID": 21322
    },
    {
      "description": "Medieval Blacksmith",
      "legoID": 21325
    }
  ]
}

Next actions

Tomorrow I will start exploring the use of SQLAlchemy.