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, (‘webmaster@python.org’, ‘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, (‘webmaster@python.org’,))
    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

How to Change MySQL/MariaDB Data Directory on Ubuntu

By default, the MySQL/MariaDB SQL database servers put their data on the root filesystem. This is not what you want in...
29/12/2020

Install phpMyAdmin 5 on CentOS 8

phpMyAdmin is used to manage MySQL/MariaDB databases graphically from the web browser. phpMyAdmin 5 is the latest version...
29/12/2020

MariaDB Replication on RHEL 8/ CentOS 8

Disasters happen, and when they do, precious data can go with the wind, never again to be recovered, and when recovered,...
29/12/2020
Bài Viết

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

mua Proxy riêng ở đâu, và nó đem lại lợi ích gì cho người sử dụng
22/11/2022

Hướng dẫn sử dụng Proxy Helper Fakeip khi thuê proxy
21/11/2022

PROXY NUÔI TÀI KHOẢN FACEBOOK – KINH NGHIỆM FAKE IP – THUÊ PROXY GIÁ RẺ
14/11/2022

Mua Proxy Nuôi Zalo Giá Rẻ Tại Onet.com.vn
14/11/2022

BẢNG GIÁ MUA PROXY VIỆT NAM và PROXY US Onet.com.vn
14/11/2022