How to Connect to a MySQL Database in Python

1. Connect to a MySQL Database With the Default MySQL Connector for Python
2. Connect to a MySQL Database With the pymysql Library in Python
3. Connect to a MySQL Database With the mysqlclient Library in Python

This tutorial will discuss and demonstrate how to connect to a MySQL database in Python.

Connect to a MySQL Database With the Default MySQL Connector for Python

Oracle has provided a connector for Python to connect to its MySQL databases. It is the easiest way to connect to a MySQL database in Python.

We can either manually download and install the connector from the official webpage or install it through the command prompt.

The command to install the MySQL connector for Python is given below.

!pip install mysql-connector-python

After the installation, we need to import this library into our code file. The procedure is shown in the following line.

import mysql.connector as connector

We’ve imported the mysql.connector library and given it the alias connector. We can now refer to this library using the connector alias.

After importing the library, we need to create a connection with the connect() method. This method takes the user, password, host, and the database as input parameters.

• The user parameter specifies the name of the user currently logged in,
• the password parameter specifies the password of that specific user,
• the host parameter specifies the server’s address where the database is being hosted, and
• the database parameter specifies the database name we want to connect.

We need to wrap this line of code inside a try/except block for exception handling. Exception handling is a crucial part of database programming in any programming language.

The code snippet below shows us how we can create a connection with exception handling in Python.

try:
    connection = connector.connect(
        user="root", password="12345", host="127.0.0.1", database="sakila"
    )
except connector.Error as e:
    print("Error: Could not make connection to the MySQL database")
    print(e)

We used connector.Error to report any errors on the runtime while connecting to the database. We need a way to perform CRUD (Create, Read, Update, Delete) operations on the database.

This is done by something called a cursor.

A cursor acts as a pointer used to perform operations on the database and its tables. We need to execute the cursor() inside our connection object to create a cursor.

This is shown in the following code snippet.

cursor = connection.cursor()

Once the cursor is created, we can execute our queries. We must use the execute() function inside the cursor object to perform a particular query.

The method to execute queries with the cursor is shown in the following code snippet.

query = "show databases"
cursor.execute(query)

This doesn’t show any output because the query’s result is stored inside the cursor. We have to loop over the cursor and display each value separately to display the results.

The code snippet below shows us how we can do that.

for i in cursor:
    print(i)

Output:

('information_schema',)
('mysql',)
('performance_schema',)
('sakila',)
('sys',)
('world',)

After executing our queries, we need to close our cursor and connect with the following code.

cursor.close()
connection.close()

Although it is the easiest method to get a MySQL database connected with Python, it has flaws. There are subtle bugs that come with the standard MySQL connector provided by Oracle, and hence this approach is not encouraged.

Connect to a MySQL Database With the pymysql Library in Python

Instead of going the easy way and using Oracle’s default python MySQL connector, we can also use the pymysql library to connect to a MySQL database. It is also straightforward.

There are also no compatibility or interoperability issues in the pymysql library because it is written in pure Python.

The command to install the pymysql library is given below.

!pip install pymysql

After installing, we need to follow the same steps described in the previous section. Even the names of the methods in the pymysql library are the same as in the default Python connector.

The code example below shows us how we can connect to a MySQL database and execute a query with the pymysql library in Python.

import pymysql

connection = pymysql.connect(
    host="localhost", user="root", password="12345", db="sakila"
)

try:
    cursor = connection.cursor()
    query = "show databases"
    cursor.execute(query)
    for i in cursor:
        print(i)
except connector.Error as e:
    print("Error: Could not make connection to the MySQL database")
    print(e)
cursor.close()
connection.close()

Output:

('information_schema',)
('mysql',)
('performance_schema',)
('sakila',)
('sys',)
('world',)

The output is the same as the previous section because we connected to the same database and executed the same query. The only noticeable difference here is in the name of the imported library.

Connect to a MySQL Database With the mysqlclient Library in Python

Another great way of connecting to a MySQL database in Python is the mysqlclient library. To install this library, we need to execute the following command.

!pip install mysqlclient

After installation, we need to apply the same steps mentioned in the first section. The only difference from the previous methods is that the library’s name imported isn’t the same as the name used during installation.

We need to import this mysqlclient in our code to import the MySQLdb library, as shown in our coding example below.

import MySQLdb

connection = MySQLdb.connect(
    host="localhost", user="root", password="12345", db="sakila"
)

try:
    cursor = connection.cursor()
    query = "show databases"
    cursor.execute(query)
    for i in cursor:
        print(i)
except connector.Error as e:
    print("Error: Could not make connection to the MySQL database")
    print(e)
cursor.close()
connection.close()

Output:

('information_schema',)
('mysql',)
('performance_schema',)
('sakila',)
('sys',)
('world',)

The output is still the same as the previous two sections because we connected to a similar database and executed the same query. The only difference here is in the name of the imported library.