Python MySQL Database Connection

In this article, you will be able to understand how to connect to a MySQL database through Python.

We generally can use many methods or modules to connect to the database using Python. Here, the actual meaning of the statement " connecting to the database using python " is to process all the queries within the databases in Python by using the MySQL server. Though we have many ways to implement the connection between the Programming language ( Python ) and Structured query language ( SQL ), i.e., MySQL, we majorly use one method. Using MySQL connector, we can implement any operation that joins queries and programs.  

What is MySQL?

The most used Database management system is MySQL. SQL stands for Structured Query Language. This is almost similar to all programming languages that we generally have. But the major difference between Programming languages and Structured Query languages is Structured Query languages run with queries, whereas Programming languages run with programs and codes. One of the servers that can implement SQL is MySQL. MySQL is one of the best Database management systems because it is extremely easy to install free of cost, it is easy to use, it runs with great speed, and it is highly secured. MySQL is available for all operating systems (OS), which includes Windows, Mac OS, Linux, etc.

MySQL connector:

It is one of the most used methods to connect databases to a Programming language like Python. We mostly use this due to many reasons. Let us address all the reasons below. In java, we have JDBC drivers, i.e., Java Data Base Connectivity drivers, to develop a bridge. But in Python, we do not have any such inbuilt drivers. So, to create a connection between the database and the Programming language Python, we need to install external drivers that support the language's environment and requirement, i.e., Python.  

Why do we prefer MySQL connector rather than any other available methods?

MySQL connector is easy to install, and simple codes can be generated to perform any action. It is written purely in Python, so any type of command written in python is acceptable in terms of queries. It is Oracle's official driver to handle MySQL and Python. Older versions of python can also be linked up with this driver.

Steps required to connect to MySQL database through Python:

1. Download and install MySQL connector driver or module
2. Import MySQL connector to Python
3. Making use of inbuilt methods/functions for the respective operations
4. Close the connection to the database

Download and install MySQL connector driver or module

In this step, we are supposed to install the MySQL connector driver to initiate the connectivity to the database. Every access should be permitted to the system by the admin to perform this action. The path should be clearly taken to avoid errors. The command described below allows you to install the MySQL connector.

# installing the MySQL connector driver
pip install mysql-connector-python

Import MySQL connector to Python

This must be done after the successful installation of the MySQL connector to import all the data that supports our connectivity from the driver. All the methods of the modules can be used to communicate with the MySQL server. The statement described below imports all the members from MySQL to python.

# importing all members from mysql connector to python
import mysql.connector

Making use of inbuilt methods/functions for the respective operations

There are many functions that should be addressed for connecting to the database. Majorly used methods are listed below

1. connect()
2. execute()
3. cursor()

Connect method or connect() :

After the installation of the required drivers and importing them, the user's credentials are needed to be given, including passwords, so that the admin of the system can grant permission for the connectivity. For performing this, we use the connect().

Syntax of connect():

data_base_name = mysql.connector.connect(
    user = “ name of the current admin user “
    password = “ password of your current admin user “
    host = “ localhost “
)

Example program that determines connect():

# importing mysql connector after installation
import mysql.connector
# the database “ data_base “ is being created within the current user MySQL server and waiting for the admin’s access 
data_base = mysql.connector.connect(
    user = " scott "
    password = " tiger "
    host = " localhost "
)
# printing the path of the connectivity of database with python
print(data_base)

The above program helps you to connect to the database that the user mentions. As soon as the database is connected, it shows the exact path that it is actually stored in and accessed.

Cursor method or cursor():

The method cursor or cursor() behaves as a mediator between the database, MYSQL server, and computer communicating language. It connects the database to Python. The cursor is an instance of MySQLcursor class. To create a cursor, we call the object by using the cursor().

Syntax of connect():

# creating of cursor using cursor()
cursor = connection.cursor()

The cursor behaves like an object. It is created by using the " cursor() ", and here, the word " connection " indicates the creation of connection with the help of the cursor method.

Example program that determines cursor():

# importing mysql connector after installation
import mysql.connector
# the database “ data_base “ is being created within the current user MySQL server and waiting for the admin’s access 
data_base = mysql.connector.connect(
    user = " scott "
    password = " tiger "
    host = " localhost "
)
# creating cursor or creating an instance of MySQLcursor class
cursor = connection.cursor()

A successful connection will be created after the execution of the program in MySQL. It means that your system has given access to the server.

Execute method or execute():

This method is called by the instance of MySQLcursor, i.e., cursor object, to recognize the query that is given as an input in the form of a string. To be more precise, the execute method converts the given query into the form of a string and then considers it as a query. To run queries, we need to use the execute method or execute().

Syntax of execute():

# to execute the queries given after being converted into   # string
   cursor.execute(database_query)

Example program that determines execute():

# importing mysql connector after installation
import mysql.connector
# the database “ data_base “ is being created within the current user MySQL server and waiting for the admin’s access 
data_base = mysql.connector.connect(
    user = " scott "
    password = " tiger "
    host = " localhost "
)
# creating cursor or creating an instance of MySQLcursor class
cursor = connection.cursor()
# creating a database query 
database_query = "CREATE DATABASE new_data_base " 


# creating cursor or creating an instance of MySQLcursor class
cursor = connection.cursor()


# linking the statement “ database_query “ to the cursor to identify that particular statement as a query
cursor.execute(database_query)

The query given within the variable “ database_query “ will be initially considered to be a string and then taken as an actual query.” CREATE DATABASE new_data_base " is the given query. So, as soon as the execute method is involved, the query will be executed, and a new database with the name " new_data_base “ will be created.

Close the connection to the database

After all the operations have been processed, the database cannot be left open, and the connection cannot remain the same. If this happens, unauthorized access may occur within the database, and sometimes this might lead to the destruction of the entire system. To avoid such problems, we need to close the connectivity immediately after the desired work has prospered.

Syntax for using close() on cursor:

# closing the cursor
cursor.close()

Syntax for using close() to close the connection:

# closing the connection or disabling the connection
connection.close()

Example program that determines close():

# importing mysql connector after installation
import mysql.connector
# the database “ data_base “ is being created within the current user MySQL server and waiting for the admin’s access 
data_base = mysql.connector.connect(
    user = " scott "
    password = " tiger "
    host = " localhost "
)
# creating cursor or creating an instance of MySQLcursor class
cursor = connection.cursor()


print("Creating the database:")
# creating a database query 
database_query = " CREATE DATABASE Hello(id int, name varchar(20), age int, location varchar(20) " 
print("Database of name Hello is created!!")


# creating cursor or creating an instance of MySQLcursor class
cursor = connection.cursor()


# linking the statement “ database_query “ to the cursor to identify that particular statement as a query
cursor.execute(database_query)




# closing the cursor
cursor.close()
# closing the connection or disabling the connection
connection.close()

Output:

Creating the database: 
Database of name Hello is created!!

To make things clear, we have just discussed the overview of the Database connection. The connectivity between SQL and Python is a complex procedure that involves loads of exceptions. To manage all such types of exceptions that get raised during the execution of the above-discussed programs, we use Exception handling keywords, i.e., " try ", " catch ", " throw ", " throws " and " finally " throughout the program.