Python- Connect to a MySQL Database with examples

Today in this article, we will see an example with Python-Connect to MySQL Database.

We will see different techniques for connecting to MySQL using Python.

• Using pymysql – discussed here
• Using MySQL connectors – Please see this article for more details.

However, this article will focus on how to use PyMySQL which is MySQL client library.

Installing PyMySQL using CLI

python3 -m pip install PyMySQL

----- Installing 'pip install pymysql' -----
Collecting pip
  Using cached pip-22.3-py3-none-any.whl (2.1 MB)
Collecting install
  Using cached install-1.3.5-py3-none-any.whl (3.2 kB)
Collecting pymysql
  Using cached PyMySQL-1.0.2-py3-none-any.whl (43 kB)
Installing collected packages: pip, install, pymysql
  Attempting uninstall: pip
    Found existing installation: pip 20.1.1
    Uninstalling pip-20.1.1:
      Successfully uninstalled pip-20.1.1
Successfully installed install-1.3.5 pip-22.3 pymysql-1.0.2
----- Successfully installed 'pip install pymysql' -----

To use “sha256_password” or “caching_sha2_password” for authenticate,

python3 -m pip install PyMySQL[rsa]

Installing PyMySQL using Requirments.txt

Please visit below article,

• How to install Python PyMySQL with RSA Keys

Prerequisites requirements

• Python – one of the following:
  • CPython >= 3.6
  • Latest PyPy 3

• MySQL Server – one of the following:
  • MySQL >= 5.6
  • MariaDB >= 10.0

Step 1: Adding import statements

Adding import statement to Python,

import pymysql.cursors

Step 2: Connect to the database

Connect to the database,

connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             database='db',
                             cursorclass=pymysql.cursors.DictCursor)

If you are using RSA based connect then kindly check refer article for more details, Python RSA Key pair Encryption and Decryption with examples

Step 3: Read from the database

Let’s perform read operations on the MySQL Database,

  with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `College`, `Name` FROM `Employee` WHERE `ID`=%s"
        cursor.execute(sql)
        result = cursor.fetchone()
        print(result)

Step 4: Read all tables from MySQL database using Python

Read All tables from MYSQL

try:
    with connection.cursor() as cursor:
        # Read tables 
        sql = "SHOW TABLES"
        cursor.execute(sql)
        result = cursor.fetchone()
        print(result)

finally:
    connection.close()

Example:

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.