How to Connect to MySQL Database from Python With Example

From a Python program, you can connect to MySQL database to access the tables and manipulate your data.

For this, you should use one of the Python MySQL Libraries. While there are few of these libraries available, the most popular and stable is mysql-connector-python library.

The mysql-connector-python library uses APIs that are complaint with the Python Database API Specification v2.0 (PEP 249). Also, this library by itself is written in python program and does not have any other external dependencies, which makes it easier to maintain.

This tutorial explains how to install mysql-connector-python library. We’ve also provided a sample python program that shows how to use the API to connect to a MySQL database and retrieve data from tables.

1. Install MySQL connector

Mysql-connector-python library is available for all flavors of Linux including CentOS, Debian, Ubuntu, RedHat. It is also available for Windows.

You can download the mysql-connector-python from this download page.

In the following example, I’m using wget command to directly download the RPM file to install it on CentOS 7.

# wget http://cdn.mysql.com//Downloads/Connector-Python/mysql-connector-python-2.1.3-1.el7.x86_64.rpm

Next, use rpm command to install the mysql-connector-python library as shown below. This does not have any other additional package dependencies. All you need is just this package.

# rpm -ivh mysql-connector-python-2.1.3-1.el7.x86_64.rpm 
Preparing...         ############# [100%]
Updating / installing...
   1:mysql-connector-python-2.1.3-1.el################# [100%]

2. Verify Python MySQL Library is Installed

In the following example, this server has Python 2.7.5 version installed.

# python -V
Python 2.7.5

If you are completely new to Python, try this Python Hello World Example Program.

Next, use get_python_lib function to get the directory location where all the Python libraries will be installed. Before you can use the get_python_lib function, you should also import that function as shown below.

# python
>>> from distutils.sysconfig import get_python_lib
>>> print get_python_lib() 
/usr/lib/python2.7/site-packages

As we see from the above output, on this particular system python libraries are installed under /usr/lib/python2.7/site-packages directory. Verify whether we got the mysql_connector_python is available under that directory as shown below.

# ls -l /usr/lib/python2.7/site-packages | grep -i mysql
drwxr-xr-x.  3 root root     78 Jun  9 08:14 mysql
-rw-r--r--.  1 root root   1499 Sep 10  2015 mysql_connector_python-2.1.3-py2.7.egg-info

When you are executing any command from the python command line, don’t give a space in front of the command. If you have any extra space in front of “print”, it will display the “IndentationError: unexpected indent” error message.

>>>  print get_python_lib() 
  File "<stdin>", line 1
    print get_python_lib() 
    ^
IndentationError: unexpected indent

Also, if you try to executing the get_python_lib without importing it, you’ll see the following error message.

>>> print get_python_lib() 
NameError: name 'get_python_lib' is not defined

3. Validate Python MySQL Library Load

Next, let us make sure the mysql library that we installed is valid, and we are able to import it without any issue. If the following import mysql.connector doesn’t return any error message, then it means that python is now able access the mysql connector library and import it without any issues. From this point on-awards, you can start using any MySQL python library commands and get it going.

# python
>>> import mysql.connector
>>> exit();

If you get the following error, then something is wrong with the Python MySQL library installation.

>>> import mysql.connector
ImportError: No module named mysql.connector

4. Connect to MySQL Database from Python Program

Next, let us write a quick small sample python program that will connect to a specific MySQL database using an user name and password.

In the following example, this python program will try to connect to a MySQL database called “thegeekstuff” that is running on the localhost (127.0.0.1), with the given username and password.

Mysql.connector.connect is a mysql-python library function that will create this connection as shown below. This function will return a connection object, which we are storing in the “conn” variable. Before we can call any of the mysql library functions, we should first import the library using “import mysql.connector” as shown in the example below.

Later we can use this “conn” variable to call other MySQL Python library functions to manipulate table data. Finally, we are using conn.close(), which will simply close the MySQL connection that was opened.

# cat con-test.py
import mysql.connector

conn = mysql.connector.connect(
         user='root',
         password='Dev$SQL2Pwd',
         host='127.0.0.1',
         database='thegeekstuff')

conn.close()

If you are trying to connect to a MySQL database that is running on a different server, then change the ip-address in the host parameter accordingly.

If you execute the above sample python program as shown below, if it doesn’t return any error message, then it means that the program was able to successfully make the connection to given MySQL database using the given username and password.

# python con-test.py
#

If there is an error while making the connection, it will throw an error messages as shown below. In the following example, it gives “Access denied” error message, which means that the given username and password combination is invalid.

# python con-test.py
Traceback (most recent call last):
  File "my.py", line 5, in <module>
    database='test')
  File "/usr/lib/python2.7/site-packages/mysql/connector/__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 95, in __init__
    self.connect(**kwargs)
  File "/usr/lib/python2.7/site-packages/mysql/connector/abstracts.py", line 719, in connect
    self._open_connection()
  File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 210, in _open_connection
    self._ssl)
  File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 144, in _do_auth
    self._auth_switch_request(username, password)
  File "/usr/lib/python2.7/site-packages/mysql/connector/connection.py", line 177, in _auth_switch_request
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

5. Retrieve MySQL Table Records From Python Script

The following example expands the above python program. In this python script example, it will connect to “thegeekstuff” MySQL database, and retrieve data from employee table, and display it.

# cat employee.py 
import mysql.connector

conn = mysql.connector.connect(
         user='root',
         password='Dev$SQL2Pwd',
         host='127.0.0.1',
         database='thegeekstuff')

cur = conn.cursor()

query = ("SELECT * FROM employee")

cur.execute(query)

for (id, name, dept, salary) in cur:
  print("{}, {}, {}, {}".format(id, name,dept,salary))

cur.close()
conn.close()

In the above example python program:

• import mysql.connector – Imports the mysql connector python library.
• conn = mysql.connector.connect – Connects to a specific MySQL datbase using the given db name, username, password and host. This returns a connection object, which is stored in the “conn” variable.
• cur = conn.cursor() – Using the connection object that we just created, we are creating a new cursor to retrieve and manipulate our query. The cursor object will be stored in the “cur” variable.
• query = (“SELECT … ) – Here we are definting our MySQL query. In this example, we are simply selecting all records from employee table. If you want to use different variation of select command, this will be helpful: 25 Essential MySQL Select Command Examples
• cur.execute(query) – Using the “cur” cursor variable that we created above, we are executing the given query. Since the cursor is already tied to a specific connection, it knows exactly where to execute the SQL query and get the output.
• for (…) in cur – This command just loops through the cursor and gets all the values in the rows and stores them in the given parameter. Since this table has 4 columns, there are 4 variables defined inside the for loop. I.e id, name, dept and salary.
• print(“…”.format()) – This print command is getting called inside the for loop, which simply prints the value of the given variables, which are the column values retrieved for a particular row on the employee table.
• cur.close and conn.close – Finally, after the for loop execution is over, we are simply closing the cursor and the connection that we created earlier.

The following is the output of the above sample python program, which connects to a MySQL database and retrieve rows from the given table.

# python employee.py
100, Thomas, Sales, 5000
200, Jason, Technology, 5500
300, Mayla, Technology, 7000
400, Nisha, Marketing, 9500
500, Randy, Technology, 6000