PYnative

Python Programming

Home » Python » Databases » Python MySQL Database Connection using MySQL Connector

Python MySQL Database Connection using MySQL Connector

Updated on:  | 43 Comments

In this lesson, you will learn how to connect the MySQL database in Python using the ‘MySQL Connector Python‘ module. This Python MySQL tutorial demonstrates how to develop and integrate Python applications with a MySQL database server.

In Python, We can use the following modules to communicate with MySQL.

  • MySQL Connector Python
  • PyMySQL
  • MySQLDB
  • MySqlClient
  • OurSQL

Note: Above all interfaces or modules are adhere to Python Database API Specification v2.0 (PEP 249) that means the syntax, method, and way of access the database is the same in all.

PEP 249 is designed to encourage and maintain similarity between the Python modules that are used to access databases. By doing this, above all modules are following rules defined in Python Database API Specification v2.0 (PEP 249).

You can choose any of the above modules as per your requirements. The way of accessing the MySQL database remains the same. I recommend you to use any of the following two modules:-

  1. MySQL Connector Python
  2. PyMySQL

Note: This tutorial focuses on the MySQL Connector Python module. All examples are created using MySQL Connector Python.

Advantages and benefits of MySQL Connector Python: –

  • MySQL Connector Python is written in pure Python, and it is self-sufficient to execute database queries through Python.
  • It is an official Oracle-supported driver to work with MySQL and Python.
  • It is Python 3 compatible, actively maintained.

Table of contents

How to connect MySQL database in Python

Let’s see how to connect the MySQL database in Python using the ‘MySQL Connector Python’ module.

Arguments required to connect

You need to know the following detail of the MySQL server to perform the connection from Python.

ArgumentDescription
UsernameThe username that you use to work with MySQL Server. The default username for the MySQL database is a root.
PasswordPassword is given by the user at the time of installing the MySQL server. If you are using root then you won’t need the password.
Host nameThe server name or Ip address on which MySQL is running. if you are running on localhost, then you can use localhost or its IP 127.0.0.0
Database nameThe name of the database to which you want to connect and perform the operations.

How to Connect to MySQL Database in Python

  1. Install MySQL connector module

    Use the pip command to install MySQL connector Python.
    pip install mysql-connector-python

  2. Import MySQL connector module

    Import using a import mysql.connector statement so you can use this module’s methods to communicate with the MySQL database.

  3. Use the connect() method

    Use the connect() method of the MySQL Connector class with the required arguments to connect MySQL. It would return a MySQLConnection object if the connection established successfully

  4. Use the cursor() method

    Use the cursor() method of a MySQLConnection object to create a cursor object to perform various SQL operations.

  5. Use the execute() method

    The execute() methods run the SQL query and return the result.

  6. Extract result using fetchall()

    Use cursor.fetchall() or fetchone() or fetchmany() to read query result.

  7. Close cursor and connection objects

    use cursor.clsoe() and connection.clsoe() method to close open connections after your work completes