Connect Python To MySQL Database

Hello Python Programmers. I hope you are doing well, In this article, you will learn everything about how to connect Python with MySQL database with the help of the Python MySQL connector package. As a developer, we have a requirement to connect Python applications with MySQL servers and perform operations in the MySQL database.

This Python MySQL tutorial will demonstrate everything about the connection between Python applications and MySQL database with proper steps by steps.

In this guide, we will see step by step guide to connecting Python applications with MySQL databases.

Throughout this article, we are about to use a popular Python package that is mysql-connector-python. There are also more packages available apart from this package but this is one of the most popular packages from them.

Why do we need to use MySQL Connector for Python?

As Python developers, we are more comfortable writing code in Python programming, and each MySQL Connector for Python programming is written in pure Python code, so it becomes easier for us to use and understand.

We can easily understand the code of the Python MySQL connector and easily interact with the MySQL database through the Python program.
Don’t worry, we will see all the things in later tutorials, then you will get more clarity with that.

How does Python MySQL Connector Work?

Each MySQL connector module for the Python program is used to establish a connection between any Python application and MySQL database and it happens using the Python database API Specification v2.0 ( PEP 249 ). Basically, it is a Python standard API that is used to access and interact with databases. It allows us to write a couple of Python programs that will work with multiple kinds of RDBMS ( Relational database Management System ) instead of writing separate programs for each RDBMS.

As you can see in the below diagram, How mysql-connector-python works.

Credentials that will require to connect

To establish a connection between the Python application and the MySQL database, we will need some credentials which are as follows.

Arguments	Description
username	The username of the MySQL server that you want to use, The default username of the MySQL server is root.
password	Password for username. Password would have been provided during the installation of the MySQL server if no password is provided then you don’t need to use the password.
hostname	Hostname represents the name of the server or IP address on which the MySQL server is running. If your MySQL server is running on your own machine or laptop then you can use localhost as the hostname.
database	Database name indicates the name of MySQL database to which you want to connect from.
port	the port represents the number on which the MySQL server is running. You have to remember, It should be always an integer value. The default port is always 3306.

So these are the parameters or arguments that will initially require to make the connection between Python and any MySQL database.

There are lots of arguments available which could use to connect Python with MySQL Database. You can find all the arguments in the below screenshot.

👉Image source:- Click Here

MySQL Database

MySQL is one of the most popular open-source relational database management systems written in C and C++ programming languages. MySQL database server is a fast, reliable, and easy-to-use database management system. You can use the MySQL database management system for your python applications with a few configurations.

To connect the Python applications to the MySQL database, You should have MySQL installed on your machine or computer system.

You can download MySQL to click here.

Installing MySQL Connector

After downloading and installing the MySQL server on your machine, you should have the MySQL driver. You can use the python-pip command to download and install the MySQL driver.
Use the following command to download the MySQL driver.

pip install mysql-connector-python

After executing the above command, the MySQL driver will be downloaded and installed into your system.

Connect Python to MySQL

After downloading and installing the Python MySQL driver you can connect with the MySQL database, To connect Python with MySQL, you have to import the MySQL connector in your Python file using the import keyword.

Example:

import mysql.connector
print('successful')

If you are getting a ‘successful‘ message after executing the above Python code, That means MySQL driver is successfully installed in your machine.

Now you are able to connect your Python file with the MySQL database. MySQL connector has a constructor called connect(), Which takes some arguments to establish a connection. Remember, You should have some credentials like username, hostname, password, port, and database name to make the connection.

Example: Create a connection using connect() constructor

from mysql import connector
connection = connector.connect(
    host="localhost",
    user="root",
    password="root21",
    database='testing',
    port=3306

)

In fact, It is possible to create connections using the connector.MySQLConnection() class. Like this.

connection = connector.MySQLConnection(
    host="localhost",
    user="root",
    password="root21",
    database='testing',
    port=3306

)

You can implement any one from both ways ( connector.connect() constructor or connector.MySQLConnection() class ). Both have the same functionality but connect() method is mostly used by developers.

Checking connection is active or not

To check whether the connection is successfully established or not, use the is_connected() method. it returns only the boolean value True or False, If the connection is successfully established then it will return True otherwise it will return False.

connection.is_connected()

Creating a Cursor

After creating the connection successfully, The most important thing is to create a cursor because without a cursor we can not perform any SQL operations in the MySQL database table.

To create a cursor, the connection has a function called cursor() that returns the cursor object as you can see here.

cursor_object = connection.cursor()

Now, To execute the SQL query within the MySQL database, we will have to use execute() method of the cursor object.

cursor_object.execute('select * from student')

In the above SQL statement, the student represents the name of the table on which we want to perform operations.

To fetch records from the table fetchall() method is used here.

data = cursor_object.fetchall()
print(data)

Close cursor and connection

After using the cursor and connection, we should close the cursor and connection using the close() method. The cursor object will close using cursor_object.close() and the connection will close using the connection.close() method. Closing the connection is one of the best practices being a developer.

# closing the cursor
cursor_object.close()

# closing the connection
connection.close()

Handling Exception

If you are passing an invalid argument or argument value inside connect() or connection.MySQLConnection() class, then it will raise an exception, That’s why it will be best practice to handle all the exceptions. To exception, use try and except block.
Write all the code that could be caused to become exceptions and handle all those exceptions except Block.

Here I am handling some of the exceptions, you can handle exceptions as per your requirement.

from mysql import connector
from mysql.connector import Error, errorcode
try:
    connection = connector.connect(
        host="localhost",
        user="root",
        password="root21",
        database='testing',
        port=3306

    )

except Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something went wrong, please check your username and password correctly...")

    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database not exist..")

    elif err.errno == errorcode.ER_NO_SUCH_TABLE:
        print("Table name exist..")
    else:
        print(err)

else:
    connection.close()

Define connection arguments in Python Dictionary

As we know, Python has a great data structure called Dictionary, It is one of the best data structures in Python as it allows us to store elements in the form of key-value pairs.

Similarly, we can wrap all the connection arguments inside a Python dictionary and pass them to the connect() constructor or MySQLConnection() class with double asterisks ( ** ).

from mysql import connector

config = {
    "host": "localhost",
    "port": 3306,
    "username": "root",
    "password": "root21",
    "database": "testing"
}

connection = connector.connect(**config)

Important:– To Download all the code Click Here

Related Articles:

• Python MySQL Create Database
• Python MySQL Create Table
• Python MySQL Insert Into Table
• Python MySQL Select record
• Python MySQL MySQL Where Clause
• Python MySQL MySQL Delete
• Python MySQL MySQL Update

Conclusion

In this article, you have learned all about Python applications with the MySQL database. If you want to use the database in your Python application, Then MySQL will be best because MySQL is the most popular database in the world. And you don’t need to worry about how will you connect your Python application with MySQL server because thanks to Python which provide a great Python MySQL connector.

The reason behind being the world’s most popular programming language is, Python has a large number of libraries which makes work easier.

If you like this article, please keep visiting for further Python MySQL operations.

Note:- Python MySQL Connector reference:- Click Here