Connecting to MariaDB with PyMySQL

PyMySQL is a free and open source project released under the MIT License. PyMySQL is a MySQL library that can be used to interact with the database. You can connect and manage your MySQL database from Python using PyMySQL. PyMySQL supports MySQL/MariaDB version greater than 5.5. PyMySQL is a drop-in replacement for MySQLdb that implements the Python Database API v2.0.

In this tutorial, we will learn how to connect MariaDB with PyMySQL on Ubuntu 18.04 server.

Requirements

A server running Ubuntu 18.04.
A root password is set up on your server

Getting Started

Before starting, it is recommended to update your repository and packages with the latest version. You can do it with the following command:

apt-get update -y
apt-get upgrade -y

Once the server is updated, restart it to apply all the changes.

Install Required Packages

First, you will need to install MariaDB server and some required packages in order to install PyMySQL. You can install all of them with the following command:

apt-get install mariadb-server git build-essential python-babel zlib1g-dev
libffi-dev libssl-dev libxslt-dev python-dev python-virtualenv -y

Once all the packages are installed, start MariaDB service and enable it to start on boot time with the following command:

systemctl start mariadb
systemctl enable mariadb

Create Database

Next, you will need to create a database, database user and table on your server.

First, log in to MariaDB shell with the following command:

mysql –u root –p

Enter your root password when prompt then create a database and user with the following command:

MariaDB [(none)]> create database testdb;
MariaDB [(none)]> grant all privileges on testdb.* to ‘testuser’@‘localhost’ identified by
‘password’;

Next, flush the database with the following command:

MariaDB [(none)]> flush privileges;

Next, change the database to testdb and create table with the folowing information:

MariaDB [(none)]> use testdb;

MariaDB [testdb]> CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) COLLATE utf8_bin NOT NULL,`password` varchar(255) COLLATE
utf8_bin NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
AUTO_INCREMENT=1 ;

Next, exit from the MariaDB shell with the following command:

MariaDB [testdb]> exit;

Setup Python Virtual Environment

Next, you will need to setup the Python virtual environment on your system.

To create a virtual environment, run the following command:

cd /opt/
virtualenv –p /usr/bin/python2.7 sqlenv

Output:

Running virtualenv with interpreter /usr/bin/python2.7
New python executable in /opt/sqlenv/bin/python2.7
Also creating executable in /opt/sqlenv/bin/python
Installing setuptools, pkg_resources, pip, wheel…done.

Next, activate the virtual environment with the following command:

source sqlenv/bin/activate
(sqlenv) root@ubuntu1804:/opt#

Install and Test PyMySQL

Next, install PyMySQL library under virtual environment with the following command:

pip install pymysql

Output:

Collecting pymysql
Downloading https://files.pythonhosted.org/packages/ed/39/15045ae46f2a123019aa968dfcba0396
c161c20f855f11dea6796bcaae95/PyMySQL–0.9.3–py2.py3–none–any.whl (47kB)
|████████████████████████████████| 51kB 644kB/s
Installing collected packages: pymysql
Successfully installed pymysql–0.9.3

Next, install Python MySQL connector with the following command:

pip install mysql–connector–python

Output:

Collecting mysql–connector–python
Downloading https://files.pythonhosted.org/packages/80/58/6a7943792a9b0c627c569ebcead
62bfd4bfc5fdb99241594f198e82f42fb/mysql_connector_python–8.0.16–cp27–cp27mu–
manylinux1_x86_64.whl (13.1MB)|████████████████████████████████| 13.1MB 1.8MB/s
Collecting protobuf>=3.0.0 (from mysql–connector–python)
Downloading https://files.pythonhosted.org/packages/b2/a8/ad407cd2a56a052d92f602e164
a9e16bede22079252af0db3838f375b6a8/protobuf–3.8.0–cp27–cp27mu–manylinux1_x86_64.whl
(1.2MB)|████████████████████████████████| 1.2MB 1.6MB/s
Collecting six>=1.9 (from protobuf>=3.0.0->mysql–connector–python)
Downloading https://files.pythonhosted.org/packages/73/fb/00a976f728d0d1fecfe898238
ce23f502a721c0ac0ecfedb80e0d88c64e9/six–1.12.0–py2.py3–none–any.whl
Requirement already satisfied: setuptools in ./sqlenv/lib/python2.7/site–packages
(from protobuf>=3.0.0->mysql–connector–python) (41.0.1)
Installing collected packages: six, protobuf, mysql–connector–python
Successfully installed mysql–connector–python–8.0.16 protobuf–3.8.0 six–1.12.0

Next, create a sample Python code to connect MySQL database.

nano /opt/sqlenv/connectmysql.py

Add the following lines:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host=‘localhost’,
user=‘testuser’,
password=‘password’,
db=‘testdb’,
charset=‘utf8mb4’,
cursorclass=pymysql.cursors.DictCursor)

try:
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, (‘[email protected]’, ‘very-secret’))

# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()

with connection.cursor() as cursor:
# Read a single record
sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
cursor.execute(sql, (‘[email protected]’,))
result = cursor.fetchone()
print(result)
finally:
connection.close()

Save and close the file. Then, run the Python code with the following command:

python sqlenv/connectmysql.py

Once the connection established successfully, you should see the following output:

{u‘password’: u‘very-secret’, u‘id’: 1}

Congratulations! you have successfully install PyMySQL on your server and connect it to the MariaDB database.