Python

Python MySQL Connection

Hello in this tutorial, we will understand how to connect to the MySQL server via python programming.

1. Introduction

To connect with MySQL in python programming we have the following modules that are commonly available and known to the developer world as all these modules adhere to the python database api specification

  • MySQL Connector Python – It is a preferred choice in the developer’s community as it is capable of executing database queries through python. It is python3 compatible and actively maintained
  • pyMySQL
  • MySQLDB
  • MySqlClient

1.1 Setting up Python

If someone needs to go through the Python installation on Windows, please watch this link. You can download the Python from this link.

1.2 Setting up MySQL Connector Python

Once the python is successfully installed on your system you can install the MySQL Connector Python using a simple pip command. You can fire the below command from the command prompt and it will successfully download the module from pypi.org and install it.

Installation command

pip install mysql-connector-python

1.3 Setting up MySQL server container

To start with the tutorial, I am hoping that you have the MySQL up and running in your localhost environment. For easy setup, I have the server up and running on the docker environment. You can execute the below command to get the container running on docker in minutes.

Docker commands

docker run --name mysql -e MYSQL_ROOT_PASSWORD=password123 -e MYSQL_DATABASE=user -p 3306:3306 -d mysql

If everything goes well the container would be started successfully as shown in Fig. 1. You can use the docker ps -a command to confirm that the container is started successfully. For further information on docker basics, you can navigate to this tutorial.

Python MySQL - container
Fig. 1: MySQL container on Docker

2. Python MySQL Connection

Before going any deeper in the practical let me walk you through a simple architecture diagram where it shows that wherein the mysql connector python module fits in the picture.

Python MySQL - connection
Fig. 2: Python MySQL connection

For the MySQL connector python module to work we will supply the following attributes to the mysql from the python application i.e.

  • user – Identity user to work with the mysql database. Default mysql user is root
  • password – Credential of the identity user
  • host – Address of the database server. If running on localhost then you can either use localhost or 127.0.0.1
  • port – The port number. Default mysql port is 3306
  • database – Database to which you want to connect. It can be left blank

3. Code Example

Let us dive in with the programming stuff now.

3.1 Create a configuration file

Add the following code to the environment file wherein we will specify the connection and database details. You are free to change these details as per your configuration setup.

local.env

[DB]
username = root
password = password123
host = localhost
port = 3306
database = user

3.2 Reading the configuration

Add the following code to the python script which will read the configuration file created above and return the config object to be used later while connecting to the MySQL server. The script will import the configparser module for reading the configuration file. Remember to give the correct path where the local.env is created.

readdbconfig.py

import configparser
def read_db_params():
    # reading the env file
    config = configparser.ConfigParser()
    config.read('config/local.env')
    return config

3.3 Connecting to the database

Add the following code to the python script which will connect to the MySQL server with the help of the mysql.connector module.

connecttomysqldb.py

# python mysql

import mysql.connector
from mysql.connector import Error

from readdbconfig import *


def connect():
    try:
        # method will read the env file and return the config object
        params = read_db_params()

        # connect to database
        # reading the database parameters from the config object
        conn = mysql.connector.connect(
            host=params.get('DB', 'host'),
            database=params.get('DB', 'database'),
            user=params.get('DB', 'username'),
            password=params.get('DB', 'password'),
            port=params.get('DB', 'port')
        )

        return conn
    except(Exception, Error) as error:
        print(error)

3.4 Printing MySQL server version

Add the following code to the python script which will connect to the MySQL server with the help of the mysql.connector module and print the MySQL version.

printmysqldbversion.py

# python mysql

from connecttomysqldb import *


def print_version(conn):
    # creating a cursor to perform database operations
    cursor = conn.cursor()

    try:
        # execute the sql query
        cursor.execute('SELECT version();')

        # fetch result
        record = cursor.fetchone()
        print('MySQL version = {}'.format(record))
    except(Exception, Error) as error:
        print(error)
    finally:
        if conn is not None:
            cursor.close()
            conn.close()
            print('\nConnection closed')


# driver code
if __name__ == '__main__':
    # ops method
    print_version(connect())

4. Demo logs

The printmysqldbversion.py script will connect to the MySQL server to fetch the version details and show it on the IDE console. If not the exception message will be shown.

Demo logs

MySQL version = ('8.0.23',)

Connection closed

That is all for this tutorial and I hope the article served you with whatever you were looking for. Happy Learning and do not forget to share!

5. Summary

In this tutorial, we learned:

  • Introduction to MySQL connector python module
  • Sample program to connect to the MySQL server and print its version

You can download the source code of this tutorial from the Downloads section.

6. Download the Project

This was a python programming tutorial to connect to the MySQL server with the help of the MySQL connector python module.

Download
You can download the full source code of this example here: Python MySQL Connection

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button