Connect to MySQL Database using Python

Are you starting in the web development world? If so.. one of the best recommended languages to start programming web applications is Python. And on this tutorial we will show you how to connect to MySQL database using Python.

One of the most basic things when you develop web based applications is to connect your web app to a database to interact with all the information you store inside. And the popular MySQL database server is one of the popular choices when you have to store data inside a database.

How can I connect to MySQL database using Python ?

There are many ways to connect to MySQL database using Python. After testing several ways when I ran some python – mysql tests, I decided to write this tutorial to show you how to connect to MySQL using Python with MySQL Connector, which is one of the easiest ways to connect to MySQL from Python.

mysql-connector is a Python module used to connect with MySQL protocol. It is compatible on all operating systems as it works out of the box without any dependencies. However, to avoid any extra complications, you can install this package just in case

For Ubuntu/Debian

apt-get install python-mysql.connector

For CentOS/RHEL

dnf install mysql-connector-python

Procedure to connect to MySQL database using Python

This is the procedure you’ll have to use to connect to MySQL using Python programming language:

1. Import MySQL connector
2. Define your MySQL connection details
3. Close the connection

Code example:

#!/usr/bin/python

import mysql.connector

cnx = mysql.connector.connect(user='testuser', password='testpass',
                              host='127.0.0.1',
                              database='test')

cnx.close()

Let’s make some changes to this tiny python script. This is a quick example of how to connect to mysql using a simple python script that shows your MySQL version.

This is the procedure:

1. Open the connection.
2. Open a cursor.
3. Write your SQL query.
4. Execute your SQL query.
5. Show the result using print.
6. Close the cursor.
7. Close your connection.

Screenshot of the python mysql connection:

Code on text format:

#!/usr/bin/python

import mysql.connector

cnx = mysql.connector.connect(user='testuser', password='testpass', host='127.0.0.1',database='test')

cursor = cnx.cursor()

cursor.execute("SELECT VERSION()")

data = cursor.fetchone()

print "Database version : %s " % data

cursor.close()

cnx.close()

This is the expected output on my server:

[[email protected]:~]python mysql-connect.py 
Database version : 5.6.35 
[[email protected]:~]