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) 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/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

Installing phpMyAdmin on Debian 10

phpMyAdmin is a web based tool for managing MySQL/MariaDB databases. In this article, I am going to show you how to install...
29/12/2020

How to Use MySQL JSON Data Type

MySQL is a relational database system developed to store data in medium to large computer systems. It uses schema to define...
29/12/2020

MySQL Commands Tutorial

MYSQL COMMANDS FROM THE LINUX TERMINAL MySQL is one of the most popular relational databases utilized in  applications...
28/12/2020
Bài Viết

Bài Viết Mới Cập Nhật

SỰ KHÁC BIỆT GIỮA RESIDENTIAL PROXY VÀ PROXY DATACENTER
17/02/2024

Mua Proxy v6 US Private chạy PRE, Face, Insta, Gmail
07/01/2024

Mua shadowsocks và hướng dẫn sữ dụng trên window
05/01/2024

Tại sao Proxy Socks lại được ưa chuộng hơn Proxy HTTP?
04/01/2024

Mua thuê proxy v4 nuôi zalo chất lượng cao, kinh nghiệm tránh quét tài khoản zalo
02/01/2024