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()

Now, to perform and update or delete operations, simply replace the insert statement with one of the following:

# Prepare a dml statement
sql = "update property set address_line_2 = '%s' \
        where id = '%d'" % ('Apt B', 1)
# Prepare a dml statement
sql = "delete from property where id > '%d'" % (10)

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