CRUD with Python & MySQL (Write)

For details on getting Python and MySQL wired, check out the previous post. This post is about performing create, update, and delete operations against a MySQL database from within a Python script. First, let’s create a new row in the property table of a database named iRent:

#!/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()

# Prepare a dml statement
sql = "insert into property (address_line_1, user_id, type_id) \
        values ('%s', '%d', '%d')" % ('234 Sathorn Rd', 1, 1)

try:
    # Execute dml and commit changes
    cursor.execute(sql)
    db.commit()
    
except:
    # Rollback changes
    db.rollback()

# Close database connection
db.close()

Continue reading

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:

Continue reading

Simple Table #4 – CSS

This is part 4 of the Simple Table tutorial. In this chapter we’ll be styling the application using CSS. If you’ll be following along, I strongly suggest watching the original videos hosted on vimeo as the video is being downsampled when played directly from wordpress. To get to the original videos simply click on the word “Vimeo” in the bottom right hand corner of each clip.

Continue reading

Simple Table #3 – HTML

This is part 3 of the Simple Table tutorial. In this chapter we’ll be marking up our pages with a few nifty HTML5 tags and building out each page’s content using PHP.

If you’ll be following along, I strongly suggest watching the original videos hosted on vimeo as the video is being downsampled when played directly from wordpress. To get to the orginal videos simply click on the word “Vimeo” in the bottom right hand corner of each clip.

Continue reading

Simple Table #2 – PHP

This is part 2 of the Simple Table tutorial. In this chapter we’ll be developing the PHP functions to pull data from MySql. We’ll be developing 4 functions:

  1. getDatabases() to retrieve a list of databases from the MySql instance.
  2. getTables() to retrieve a list of tables for the selected database.
  3. getRows() to retrieve a list of rows for the selected table.
  4. getColumns() to retrieve a list of column names for the selected table.

If you’ll be following along, I strongly suggest watching the original videos hosted on vimeo as the video is being downsampled when played directly from wordpress. To get to the orginal videos simply click on the word “Vimeo” in the bottom right hand corner of each clip.

Continue reading

Simple Table #1 – Introduction

In this tutorial we’ll be creating a table navigator for MySQL using HTML, CSS, PHP, and jQuery. In this chapter we’ll be walking through the final product.

If you’ll be following along, I strongly suggest watching the original videos hosted on vimeo as the video is being downsampled when played directly from wordpress. To get to the orginal videos simply click on the word “Vimeo” in the bottom right hand corner of each clip.

Download

You can download the source code for the Simple Table app at box.net.
Download Source