How to connect to MySQL using Python

Python provides several ways to connect to a MySQL database and process data. This article describes three methods.

Connecting to MySQL using Python

Before you can access MySQL databases using Python, you must install one (or more) of the following packages in a virtual environment:

• mysqlclient: This package contains the MySQLdb module. It is written in C, and is one of the most commonly used Python packages for MySQL.
• mysql-connector-python: This package contains the mysql.connector module. It is written entirely in Python.
• PyMySQL: This package contains the pymysql module. It is written entirely in Python.

Setting up the Python virtual environment and installing a MySQL package

To set up the Python virtual environment and install a MySQL package, follow these steps:

1. Log in to your account using SSH.
2. To create a virtual environment, type the following commands:

   cd ~
   virtualenv sqlenv

3. To activate the virtual environment, type the following command:

   source sqlenv/bin/activate

4. To update pip in the virtual environment, type the following command:

   pip install -U pip

5. Type the command for the package you want to install:

   • To install the mysqlclient package, type the following command:

     pip install mysqlclient

   • To install the mysql-connector-python package, type the following command:

     pip install mysql-connector-python

   • To install the pymysql package, type the following command:

     pip install pymysql

Code sample

You are now ready to work with actual databases after installing a MySQL package in the virtual environment. The sample Python code below shows how to achieve this, as well as how simple it is to swap between SQL package implementations. Python 2.7 and Python 3.x are supported by the sample code.

In your own code, replace username with the MySQL database username, password with the database user's password, and dbname with the database name:

#!/usr/bin/python

from __future__ import print_function

hostname = 'localhost'
username = 'username'
password = 'password'
database = 'dbname'

# Simple routine to run a query on a database and print the results:
def doQuery( conn ) :
    cur = conn.cursor()

    cur.execute( "SELECT fname, lname FROM employee" )

    for firstname, lastname in cur.fetchall() :
        print( firstname, lastname )


print( "Using mysqlclient (MySQLdb):" )
import MySQLdb
myConnection = MySQLdb.connect( host=hostname, user=username, passwd=password, db=database )
doQuery( myConnection )
myConnection.close()

print( "Using mysql.connector:" )
import mysql.connector
myConnection = mysql.connector.connect( host=hostname, user=username, passwd=password, db=database )
doQuery( myConnection )
myConnection.close()

print( "Using pymysql:" )
import pymysql
myConnection = pymysql.connect( host=hostname, user=username, passwd=password, db=database )
doQuery( myConnection )
myConnection.close()

This example builds a series of Connection objects that use different MySQL modules to open the same database. Because all three MySQL modules use the portable SQL database API interface, the code in the doQuery() function can be used without change.

A Cursor object can be created when a Connection object is linked with a database. The Cursor object allows you to call the execute() function, which allows you to execute raw SQL commands (in this case, a SELECT query on the employee table).