connect to MYSQL using Python EC Instance

Managing MySQL Database using Python Script – EC2 Instance – Ubuntu

Use Case:

In this know-how/tutorial, we shall discuss various ways and walk you through how to connect to a MySQL database using a python script on an EC2 Instance running Ubuntu distribution.

The skill level is elementary and all you need is an EC2 instance that is configured correctly for MySQL operations.

The example covered is pretty basic but it is complete, complemented with all the steps necessary with some useful long info as well.

The first attempt was made on installing Python2,7 and associated dependencies.

We can advise already that support and dependencies for Python2.7 are fairly and largely deprecated, as will be partly demonstrated in this tutorial.

We strongly advise using Python3 for any future project and associated dependencies.

The complete example has been built and executed on an EC2 Instance running Ubuntu distribution with preinstalled MySQL server.

 

Correct EC2 security configuration for MySQL Project:

 

EC2 Security Config for MySQL-Ports-Network-Security

 

 

If you have followed our mySQL installation for Ubuntu, then you have met the minimum requirement for the tutorial. You have a ubuntu server with a running MySQL server.  Before going ahead we can do certain checks

Note – We have used the entire session root execution context by using sudo -i  command which may or may not be suitable for your application.

Check MySQL version

root@ip-172-31-26-246:~# mysql -Version
mysql Ver 8.0.29-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
root@ip-172-31-26-246:~#

Install Python2  and Python3

root@ip-172-31-26-246:~# sudo apt update
root@ip-172-31-26-246:~# apt install python2 
root@ip-172-31-26-246:~# apt install python3 

Unpacking python2 (2.7.17-2ubuntu4) ...
Setting up libpython2.7-stdlib:amd64 (2.7.18-1~20.04.3) ...
Setting up python2.7 (2.7.18-1~20.04.3) ...
Setting up libpython2-stdlib:amd64 (2.7.17-2ubuntu4) ...

Check python Version

Notice the uppercase -V command. Also, not the system’s default python call for us is executing Python2.7 and NOT Python3.

See also  Save Money on EC2 Instances using Spot Instances -  EC2InstanceHelper

Python3 is called explicitly by using the correct Python3 call keyword.  Of course Calling, Python2 will call Python2 which is the system default.

Your system config may be different so be mindful of the version of python called.

Both Python versions can stay harmoniously on EC2 Instance without any issues, so long that this fact is understood well by the end user.

 

root@ip-172-31-26-246:~# python -V
Python 2.7.18


root@ip-172-31-26-246:~# python2 -V
Python 2.7.18


root@ip-172-31-26-246:~# python3 -V
Python 3.8.10

Installing MYSQL  Python2 support  Packages

we have installed python MySQL support using the said method but others can be used if available. Without this support package, one will not be able to operate and manipulate a database in Python.

 

root@ip-172-31-26-246:~# sudo add-apt-repository 'deb http://archive.ubuntu.com/ubuntu bionic main'

root@ip-172-31-26-246:~# sudo apt update

root@ip-172-31-26-246:~# sudo apt install -y python-mysqldb
Unpacking python-mysqldb (1.3.10-1build1) ...
Setting up libmysqlclient20:amd64 (5.7.21-1ubuntu1) ...
Setting up python-is-python2 (2.7.17-4) ...
Setting up python-mysqldb (1.3.10-1build1) ...

Issues with Python2 script connecting to a MySQL database  on Ubuntu:

As we have discussed already to some extent in the introduction of the article,  results with Python2 were not very successful.

We were not able to successfully implement and run the example python code that connects the sample database.

The main issue seems to be the deprecated support for Python2 support modules and the lack of support and community content surrounding the technical hurdles.

 

Configuring Python3 MySQL support modules on Ubuntu distribution:

 

If not already installed, get pip3 installed on your system, followed by  MySQL Connector Module for Python3.

root@ip-172-31-26-246:~# apt install python3-pip

root@ip-172-31-26-246:~# pip3 install mysql-connector-python



Reboot is recommended after installation.

See also  EC2 Cost Saver - Save Money using Reserved Instances - EC2InstanceHelper

Configure a test scenario: Log into MySQL to create and add a new test Database :

We have not set up our MySQL password for the root user hence no password switch ( -p  ) is needed.

root@ip-172-31-26-246:~# mysql -u root -p
----
---
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> CREATE DATABASE testdb ;
mysql> SHOW DATABASES ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.00 sec)

mysql>
Once the database is created, select the newly created database

 

mysql> USE testdb ;
Database changed
mysql>

Create a test table in the Database and insert some elements

CREATE TABLE table1 (x INT, y INT , str1 VARCHAR(20), str2 VARCHAR(20));
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| table1 |
+------------------+
1 row in set (0.00 sec)


  • Initialize testdb with some values

mysql> INSERT INTO table1 (x,y,str1, str2) VALUES(10001,2000,"monkey","Zebra");
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO table1 (x,y,str1, str2) VALUES(10021,2002,"monkey","Zebra"); 
Query OK, 1 row affected (0.01 sec)
  • Print the Database data

mysql> SELECT * from table1 ;
+-------+------+--------+-------+
| x | y | str1 | str2 |
+-------+------+--------+-------+
| 10001 | 2000 | monkey | Zebra |
| 10021 | 2002 | monkey | Zebra |
+-------+------+--------+-------+
2 rows in set (0.00 sec)

 

Create a Test user and Assign the Test Database to it:

 

CREATE USER 'user'@'localhost' IDENTIFIED BY 'testpw';

GRANT ALL PRIVILEGES ON testdb.* TO 'user'@'localhost';

Python3 script connecting MySQL test database

Copy the code below and create a test script in any location of choice using the editor of your preference.

import mysql.connector

testdb = mysql.connector.connect(
host="localhost",
user="user",
password = "testpw",
database="testdb",
port="3306"
)

# create a Cursor object for control
cur = testdb.cursor()

cur.execute("SELECT * FROM table1")

result = cur.fetchall()

print (result)

testdb.close()

Run the code using python3

root@ip-172-31-26-246:~# python3 mysql1.py
[(10001, 2000, 'monkey', 'Zebra'), (10021, 2002, 'monkey', 'Zebra')]

 

See also  Apache Server Configuration Guide for EC2 Instance - EC2InstanceHelper

Conclusion:

A method of connecting to a MySQL database using python script has been discussed. The example used here is very basic but does demonstrate the principle.

This  Stackoverflow post has been the inspiration for this article, however, we were unable to replicate the results in this example and had to migrate to the python3 application.

There is some other content that has been looked upon on StackOverflow and other web resources. We do apologize we are not able to quote and infer all the said resources here.

 

 

Leave a Comment

Your email address will not be published.