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:
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.
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.
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')]
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.