TheCodeBuzz

Today in this article, we will use Python MySQL Connectors- Connect To A MySQL Database and perform efficient CRUD operations on MySQL database.

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

• Using pymysql – Please visit this article for more details.
• Using MySQL connectors- we will discuss this approach in this article,

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

Installing mysql.connector

python3 -m pip install mysql-connector-python

----- Installing 'mysql-connector-python' -----
Collecting mysql-connector-python
  Downloading mysql_connector_python-8.0.31-cp37-cp37m-win_amd64.whl (7.9 MB)
     ---------------------------------------- 7.9/7.9 MB 5.2 MB/s eta 0:00:00
Collecting protobuf<=3.20.1,>=3.11.0
  Downloading protobuf-3.20.1-cp37-cp37m-win_amd64.whl (905 kB)
     -------------------------------------- 905.1/905.1 kB 5.7 MB/s eta 0:00:00
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.31 protobuf-3.20.1
----- Successfully installed 'mysql-connector-python' -----

mysql-connector-python is MySQL driver written in Python which does not depend on MySQL libraries and implements the DB API v2.0 specification (PEP-249).

Step 1: Adding import statements

Adding import statement to python,

import mysql.connector

Step 2: Connect to the database

Connect to the database using mysql.connector connect method as below.

Below in the example connect() constructor creates a connection to the MySQL server and returns a MySQLConnection object.

The following example shows how to connect to the MySQL server:

connection =  mysql.connector.connect(host='localhost',
                             user='user',
                             password='passwd',
                             database='db')

except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Please provide valid DB credentials")
  elif err.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(err)

Step 3: Read from the database table

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.