Connecting to MySQL with Python

Scripting Python to connect to a MySQL database is fairly simple, even if you don’t have any much experience with Python.

You will need to have Python and MySQL installed. Once you have Python installed, you will need to import the MySQLdb and sys modules. For MySQL, you will simply need a user that has permissions to connect to the database and perform a select statement.

For this example, I am using Python v2.5, MySQL version 5.5.8., with Mac OS X 10.6.8.

Here are the links to download the files and applications necessary for this example script:
MySQL – http://dev.mysql.com/downloads
Python – www.python.org/getit/
MySQLdb module – http://sourceforge.net/projects/mysql-python/

In this post, I am not going to cover installing Python, MySQL or the Python modules, so you will need to do this on your own. There are plenty of other websites and blogs that can assist you with these installations. You will also need to know how to run a Python script on whatever OS you are using.

Once you have everything installed and tested to make sure each application is working properly, you are ready to run this script. This script will simply connect to the MySQL database and fetch the MySQL server version. You will need to substitute your own host IP address, username, password and database name in the script.

Since I am using a unix-based OS, I can simply open up a text editor (or use the vi editor) to write the script. You do not want to save the script in rich-text format, as the script will most likely fail upon execution. You want to save the script as a plain-text file. You will also want to make sure that the line “#!/usr/bin/python” is on the first line of the script.

– – – – START SCRIPT – – – – (do not include this line in the script)
#!/usr/bin/python
# version.py – Fetch and display the MySQL database server version.

# import the MySQLdb and sys modules
import MySQLdb
import sys

# open a database connection
# be sure to change the host IP address, username, password and database name to match your own
connection = MySQLdb.connect (host = “192.168.1.1”, user = “username”, passwd = “password”, db = “database_name”)

# prepare a cursor object using cursor() method
cursor = connection.cursor ()

# execute the SQL query using execute() method.
cursor.execute (“SELECT VERSION()”)

# fetch a single row using fetchone() method.
row = cursor.fetchone ()

# print the row[0]
# (Python starts the first row in an array with the number zero – instead of one)
print “Server version:”, row[0]

# close the cursor object
cursor.close ()

# close the connection
connection.close ()

# exit the program
sys.exit()

– – – – END SCRIPT – – – – (do not include this line in the script)

Here is what the script looks like when I run it from the command line:

It is a fairly easy script, but it is limited to displaying only one line of output. I will cover multiple lines of output in a future post.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots   Visit http://2044thebook.com for more information.