Python MySQL – Connect to a MySQL Database in Python

Summary: in this tutorial, you will learn how to connect to MySQL databases from Python using MySQL Connector/Python API.

This tutorial picks up where the Getting Started with MySQL Python Connector tutorial left off.

Setting up a sample database

First, download the following pub database, uncompress the zip file, and copy it to a directory such as C:\temp\pub.sql :

Download the Pub Sample Database

Second, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to a MySQL server using the mysql client tool:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Enter the password for the root user.

Enter password: ********Code language: SQL (Structured Query Language) (sql)

Third, execute the SQL statements in the pub.sql file using the SOURCE command:

SOURCE c:\temp\pub.sqlCode language: SQL (Structured Query Language) (sql)

The command will create a new database called pub with some tables.

Fourth, switch the current database to pub:

USE pub;Code language: PHP (php)

Fifth, display all tables from the pub database using the SHOW TABLES command:

SHOW TABLES;Code language: SQL (Structured Query Language) (sql)

The output will be:

+---------------+
| Tables_in_pub |
+---------------+
| authors       |
| book_author   |
| books         |
+---------------+
3 rows in set (0.01 sec)Code language: JavaScript (javascript)

Finally, exit the mysql client tool:

exitCode language: PHP (php)

Connecting to the MySQL database

Create the following connect.py file in the project directory and add the following code to the file:

import mysql.connector

# Initialize a variable to hold the database connection
conn = None

try:
    # Attempt to establish a connection to the MySQL database
    conn = mysql.connector.connect(host='localhost', 
                                   port=3306,
                                   database='pub',
                                   user='<user>',
                                   password='<password>')
    
    # Check if the connection is successfully established
    if conn.is_connected():
        print('Connected to MySQL database')

except mysql.connector.Error as e:
    # Print an error message if a connection error occurs
    print(e)

finally:
    # Close the database connection in the 'finally' block to ensure it happens
    if conn is not None and conn.is_connected():
        conn.close()
Code language: Python (python)

How it works:

First, import the mysql.connector module:

import mysql.connectorCode language: JavaScript (javascript)

Second, initialize a conn variable to None:

conn = None

Third, use the connect() method to connect to the MySQL server:

conn = mysql.connector.connect(host='localhost', 
                                port=3306,
                                database='pub',
                                user='<root>',
                                password='<password>')Code language: JavaScript (javascript)

The connect() method accepts host, port, database name, user, and password. You should change the connection details to yours.

Note that you need to replace the user/password with the actual user/password.

Fourth, check if the connection is created and display a message if the connection is created successfully:

if conn.is_connected():
    print('Connected to MySQL database')Code language: PHP (php)

If an error occurs during the connection process, display an error in the except block.

print(e)Code language: PHP (php)

Finally, close the database connection in the finally block:

if conn is not None and conn.is_connected():
        conn.close()Code language: CSS (css)

Executing the connect.py module

To execute the connect.py module, you follow these steps:

First, open the Command Prompt on Windows or a Terminal on Unix-like systems.

Second, go to the project directory.

cd pub

Third, activate the virtual environment:

/venv/scripts/activate 

Third, execute the connect.py module:

python connect.pyCode language: CSS (css)

Output:

Connected to MySQL database

The output indicates that we have connected to the MySQL server successfully.

In this example, we’ve embedded the database configuration, including the password, directly into the code. However, it is not considered a best practice.

To address this issue, let’s enhance the code by incorporating a separate database configuration file.

Using a configuration file

First, create a database configuration file named app.ini in the project directory and define a section called mysql with the following parameters:

[mysql]
host = localhost
port = 3306
database = pub
user = <user>
password = <password>Code language: HTML, XML (xml)

Second, create a new file config.py in the project directory that reads the app.ini configuration file and returns a dictionary that contains the database connection:

from configparser import ConfigParser

def read_config(filename='app.ini', section='mysql'):    
    # Create a ConfigParser object to handle INI file parsing
    config = ConfigParser()
    
    # Read the specified INI configuration file
    config.read(filename)

    # Initialize an empty dictionary to store configuration data
    data = {}

    # Check if the specified section exists in the INI file
    if config.has_section(section):
        # Retrieve all key-value pairs within the specified section
        items = config.items(section)

        # Populate the data dictionary with the key-value pairs
        for item in items:
            data[item[0]] = item[1]
    else:
        # Raise an exception if the specified section is not found
        raise Exception(f'{section} section not found in the {filename} file')

    # Return the populated data dictionary
    return data

if __name__ == '__main__':
    # Read the configuration from the default section ('mysql') in the 'app.ini' file
    config = read_config()

    # Display the obtained configuration
    print(config)
Code language: Python (python)

Third, modify the connect.py module that uses the config.py module to connect to the MySQL database:

from mysql.connector import MySQLConnection, Error
from config import read_config


def connect(config):
    """ Connect to MySQL database """
    conn = None
    try:
        print('Connecting to MySQL database...')
        conn = MySQLConnection(**config)

        if conn.is_connected():
            print('Connection is established.')
        else:
            print('Connection is failed.')
    except Error as error:
        print(error)
    finally:
        if conn is not None and conn.is_connected():
            conn.close()
            print('Connection is closed.')


if __name__ == '__main__':
    config = read_config()
    connect(config)Code language: Python (python)

Finally, execute the connect.py module:

python connect.pyCode language: CSS (css)

Note that you need to activate the virtual environment before executing this command.

Summary

• Use the connect() function or MySQLConnection class to connect to the MySQL Server.