Python provides several ways to connect to a MySQL database and process data. This article describes three methods.
Before you can access MySQL databases using Python, you must install one (or more) of the following packages in a virtual environment:
All three of these packages use Python's portable SQL database API. This means that if you switch from one module to another, you can reuse almost all of your existing code (the code sample below demonstrates how to do this).
To set up the Python virtual environment and install a MySQL package, follow these steps:
cd ~ virtualenv -p /usr/bin/python2.7 sqlenv
To activate the virtual environment, type the following command:
Type the command for the package you want to install:
pip install MySQL-python
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
After you install a MySQL package in the virtual environment, you are ready to work with actual databases. The following sample Python code demonstrates how to do this, as well as just how easy it is to switch between the different SQL package implementations.
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 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 MySQLdb…" import MySQLdb myConnection = MySQLdb.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() print "Using mysql.connector…" import mysql.connector myConnection = mysql.connector.connect( host=hostname, user=username, passwd=password, db=database ) doQuery( myConnection ) myConnection.close()
This example creates a series of Connection objects that opens the same database using different MySQL modules. Because all three MySQL modules use the portable SQL database API interface, they are able to use the code in the doQuery() function without any modifications.
When you have a Connection object associated with a database, you can create a Cursor object. The Cursor object enables you to run the execute() method, which in turn enables you to run raw SQL statements (in this case, a SELECT query on a table named employee).