CRUD with Python & MySQL (Read)

Here’s a few simple templates for performing basic crud operations using Python and MySQL. The snippets below lean on the MySQLdb interface, which exposes the database API to Python. To install MySQLdb download a copy and follow the instructions in the README file. To get this working on Mac OSX, I needed to add the following export statement to my .bash_profile:

export DYLD_LIBRARY_PATH=/usr/local/mysql/lib/

The steps required for database access, regardless of language and dbms, are connect to the database, build a sql or dml statement, execute the statement, process the results, disconnect from the database. As you’ll see below, linking up Python and MySQL is no different:

#!/usr/bin/env python

import MySQLdb

# Instantiate connection object and connect to MySQL database
db = MySQLdb.connect("localhost", "username", "password", "irent")

# Instantiate cursor object
cursor = db.cursor()

# Build sql statement using python's multiline quotation syntax
sql = """select address_line_1 , address_line_2,
        city, state, zip from property"""

try:

    # Execute statement and fetch results
    cursor.execute(sql)
    results = cursor.fetchall()

    # Loop through rows and print all columns
    for row in results:
        print row[0:]

except:
    print "Error: Unable to fetch data from MySQL"

# Close database connection
db.close()


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s