MySQL Tutorial: Connecting Python to MySQL

By /
Connecting MySQL To Python

Python is a general-purpose high-level programing language. It is used in many areas such as server-side web development, data science, computational mathematics, software development, and so on. Python can also be used in database applications. In this tutorial, we’ll learn to use python to connect to the MySQL database.

Setting Up Python and MySQL

Let’s begin setting up both Python and MySQL on our desktop.

If you don’t have MySQL installed on your desktop then you can go to the below link, and download and install MySQL on your desktop or laptop.
https://www.mysql.com/downloads/

To install python on your desktop, you can refer to the below link.
https://www.python.org/downloads/

Install Python connector

We’ll now install the Python connector to ensure that the MySQL connection is possible.

Step 1: Check if your system recognizes the pip command

The pip command is a package manager developed in python and is widely used. It comes with Python installation itself, so generally, there is no need to install it exclusively.

We’ll be using the pip command to install the python connector on our desktop. But before that let’s make sure that our desktop accepts the pip command. To do that open the command prompt and use the command mentioned below:

pip --version
Code language: PowerShell (powershell)
checking pip installed or not

In case an error is returned then make sure that you have python installed on your desktop and the path is set in the path variables.

Step 2: Install MySQL python connector

The next step is to install the MySQL python connector, connectors act as a middleware between two different types of applications so that they could communicate. MySQL and python connectors are made so that python and MySQL could communicate. To install the python-MySQL connector, we’ll fire the command on the command prompt.

pip install mysql-connector-python

OR

pip3 install mysql-connector-python

Connecting Python to MySQL

Till now we have established our setup for MySQL database connectivity to python. Now, we’ll be establishing the connection to the database. For establishing a connection between MySQL and python you can follow the given steps mentioned below:

  1. Import MySQL connector
  2. Create connection
  3. Write a query

As we are just establishing the connection between MySQL and Python and there is no application or program-specific coding, we’ll simply be using a notepad and a command prompt.

Import the MySQL connector

We have already installed the MySQL connector for python using the pip command but whenever we are going to write a python code to connect to MySQL we need to import the MySQL connector in our python code. To do that, first, you need to make a file with an extension of .py which refers to a python file. If you’ve already created a python file then you can use the same file and write the below code on the top:

import mysql.connectorCode language: Python (python)

Create connection

After importing the MySQL connector we need to establish the connection, and for establishing the connection we’ll be needing three arguments:

  1. Host: Host is the hostname of your MySQL, generally we use “localhost” as the host name because it’s a default setup. In case you need to check the name of your host, you can open MySQL Workbench and check the hostname or can use the command prompt.
  2. Password: When we do the installation of MySQL, we get an option to create a password for the MySQL server, use the same password here.
  3. User: You must know your username using which you access your database, in most cases we use root as the username.

Let’s create a connection to MySQL using python:

conn = mysql.connector.connect(host = 'localhost', password = 'root123', user = 'root')Code language: JavaScript (javascript)

Write the Query

After importing the MySQL connection and creating a connection the last step is to fire a query to the database. In our case, as we are just creating a connection to MySQL, therefore, we are just going to print if the connection is set up or not.

if conn.is_connected:
print("connection established")Code language: Python (python)

Check the connection

After writing the code for database connectivity, we’ll run the code. Let’s see the code below:

import mysql.connector

conn = mysql.connector.connect(host = 'localhost', passowrd = 'root123', user = 'root')

if conn.is_connected:
print("connection established")Code language: Python (python)

To check whether the connection is established or not we’ll open the command prompt and type the below code.

python filename.pyCode language: PowerShell (powershell)
checking python connection with mysql

Conclusion

In this tutorial, we learned about connecting MySQL to python using MySQL connector by following some simple steps.

Resources:
https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html