Connecting to MariaDB (especially in Python)

The Password File

First, set up your .my.cnf file:
Here is an example:

password='your user db password here'

This file allows you to connect to the DB more easily. With it, all you have to type (assuming your db is on the same machine you’re running commands from): “mysql db1”. Without it, you would have to type: “mysql -u mica db1 -p” and then type your password at the prompt every time you want to connect. (Note that you should never type your password in plaintext on the command line using “-p=mypassword” or “–password=mypassword”, because other processes on the computer can see what you typed in and steal your password. Use the .my.cnf file or use the “-p|–password” without an argument and type it in at the prompt where your password is hidden from view.)

Connecting to MariaDB in Python

Original webpage:
MySQL-python client:

If you have anaconda (see my post Jupyter Notebook Introduction and Basic Installation for installing anaconda):

# this creates an environment called "mariadb" that installs a library called "mysqlclient" using the channel "bioconda".
conda create -n mariadb -c bioconda mysqlclient

Otherwise, create a virtualenv by following this tutorial:

# note that "~" is synonymous with your home folder, e.g. /Users/mica in mac or /home/mica in linux.
# on windows replace "~" with "YOUR_HOME_FOLDER", wherever your home folder is.

# create the environment.
virtualenv ~/python3

# activate the environment
source ~/python3/bin/activate

# confirm that the environment is active.
which pip
# should return something like ~/python3/bin/pip

# follow the pip command as mentioned by the link mentioned above:
pip install MySQL-python

Finally, some python code to confirm that the installation is working:

Additional Notes

This demonstrates proper use of query parameters. It properly formats the data types for the query (e.g. dates/integers/strings/etc.), and is also the proper use in production code for preventing injection attacks:

    'select * from companies where SNUM=%(snum)s',
        'snum': 'S1',

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.