Skip to content

MariaDB

Python

In the example below, we show how to set up a connection to a MariaDB data source using Python. We demonstrate how you can read data from a MariaDB table to a Pandas DataFrame, and vice versa. To do this, we need Python 3.6 or newer (how to install Python). In a virtual environment, you should install:

We recommend using a requirements.txt file. In this file, you can add:

mysqlclient  # tested with version 2.0.3
SQLAlchemy  # tested with version 1.4.21
pandas  # tested with version 1.3.0
python-dotenv  # tested with version 0.18.0

Read from MariaDB table

In the following example code, we first set up a connection to MariaDB using SQLAlchemy. With pandas, we read a table from MariaDB to a Pandas DataFrame.

import os
from sqlalchemy import create_engine
import pandas as pd

# More about dotenv in the section dotenv
from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

engine = create_engine(
    "mariadb+mysqldb://{user}:{password}@{host}:{port}/{database}".format(
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        host=os.getenv("DB_HOST"),
        port=os.getenv("DB_PORT"),
        database=os.getenv("DB_DATABASE"),
    )
)

df = pd.read_sql_table(table_name, engine)

Info

For Python, there are many packages that you can use to set up a connection to MariaDB. The example we show here is to demonstrate a method you could use with Pandas and how you could apply it in AskAnna. The AskAnna platform is flexible. For example, if you don't use Pandas you could setup the connection directly using mysqlclient. With Askanna you could use other packages to set up a connection as well.

Project variables

To run the above example as a job in AskAnna, you should add project variables. On the project page, go to the tab variables. Here you can create new variables. To run the above example, you should add variables with names:

  1. DB_HOST
  2. DB_PORT
  3. DB_USER
  4. DB_PASSWORD
  5. DB_DATABASE

Warning

Make sure that at least the variable DB_PASSWORD is set to masked. You don't want to expose this value.

dotenv

In the above example, we used:

from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

These two lines make it a lot easier to develop your Python code locally, while you can also run the same code in AskAnna. When you add project variables, these variables will become available as environment variables in the run environment. Locally, you can add a file .env and when you run the Python code locally, the environment variables are loaded from this file. Read more about this on the project page python-dotenv.

To run the above example, you need a .env file with:

DB_HOST={host}
DB_PORT={port}
DB_USER={user}
DB_PASSWORD={password}
DB_DATABASE={database}

Write to MariaDB table

We reuse almost to full setup as with reading the data from MariaDB. Instead of reading data, we first create a Pandas DataFrame and then write the data to MariaDB.

import os
from sqlalchemy import create_engine
import pandas as pd

# More about dotenv in the section dotenv
from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

engine = create_engine(
    "mariadb+mysqldb://{user}:{password}@{host}:{port}/{database}".format(
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        host=os.getenv("DB_HOST"),
        port=os.getenv("DB_PORT"),
        database=os.getenv("DB_DATABASE"),
    )
)

df = pd.DataFrame({'example' : ['value 1', 'value 2', 'value 3']})
df.to_sql(table_name, engine)