Connecting to MariaDB with PyMySQL

29/12/2020
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) [email protected]:/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/PyMySQL0.9.3py2.py3noneany.whl (47kB)
|████████████████████████████████| 51kB 644kB/s
Installing collected packages: pymysql
Successfully installed pymysql0.9.3

Next, install Python MySQL connector with the following command:

pip install mysqlconnectorpython

Output:

Collecting mysqlconnectorpython
Downloading https://files.pythonhosted.org/packages/80/58/6a7943792a9b0c627c569ebcead
62bfd4bfc5fdb99241594f198e82f42fb/mysql_connector_python8.0.16cp27cp27mu
manylinux1_x86_64.whl (13.1MB)|████████████████████████████████| 13.1MB 1.8MB/s
Collecting protobuf>=3.0.0 (from mysqlconnectorpython)
Downloading https://files.pythonhosted.org/packages/b2/a8/ad407cd2a56a052d92f602e164
a9e16bede22079252af0db3838f375b6a8/protobuf3.8.0cp27cp27mumanylinux1_x86_64.whl
 (1.2MB)|████████████████████████████████| 1.2MB 1.6MB/s
Collecting six>=1.9 (from protobuf>=3.0.0->mysqlconnectorpython)
Downloading https://files.pythonhosted.org/packages/73/fb/00a976f728d0d1fecfe898238
ce23f502a721c0ac0ecfedb80e0d88c64e9/six1.12.0py2.py3noneany.whl
Requirement already satisfied: setuptools in ./sqlenv/lib/python2.7/sitepackages
(from protobuf>=3.0.0->mysqlconnectorpython) (41.0.1)
Installing collected packages: six, protobuf, mysqlconnectorpython
Successfully installed mysqlconnectorpython8.0.16 protobuf3.8.0 six1.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.

ONET IDC thành lập vào năm 2012, là công ty chuyên nghiệp tại Việt Nam trong lĩnh vực cung cấp dịch vụ Hosting, VPS, máy chủ vật lý, dịch vụ Firewall Anti DDoS, SSL… Với 10 năm xây dựng và phát triển, ứng dụng nhiều công nghệ hiện đại, ONET IDC đã giúp hàng ngàn khách hàng tin tưởng lựa chọn, mang lại sự ổn định tuyệt đối cho website của khách hàng để thúc đẩy việc kinh doanh đạt được hiệu quả và thành công.
Bài viết liên quan

MySQL GUI Tools for Ubuntu 18.04

In this lesson, we will look at some most popular GUI tools to access MySQL database on Ubuntu 18.04. These vary in application...

Best MySQL Clients for Ubuntu

MySQL is very popular and one of the most widely used relational database management systems. Written in C and C++, it...

Install phpMyAdmin on Ubuntu 18.04

phpMyAdmin is a very useful web-based database management tool to manage MySQL or MariaDB databases. It is a free tool...