Notes on using the MySQLdb Python module

Getting and installing

MySQLdb on SourceForge

Or try "sudo apt-get install python-mysqldb" if you're on Linux.

Using

# first, import the module
import MySQLdb
# create a connection object
conn = MySQLdb.connect(host='192.168.1.80', user='db_user', passwd='youllneVerkn0w', db='schema_name')
# create a cursor. You can have more than one of these if you need em.
curs1 = conn.cursor()

# Here's the simplest way to use your cursor-
curs1.execute("SELECT * FROM tblUsers")
for row in curs1.fetchall():
    print row

# Row will always be a tuple! Even if your query only returns one field per row!
# Use the index to get at the actual field values.
curs1.execute("SELECT UserName FROM tblUsers")
for row in curs1.fetchall():
    print row[0]
# or use a list of variables in the for statement
curs1.execute("SELECT UserName, UserID FROM tblUsers")
for UserName, UserID in curs1.fetchall():
    print "%s is UserID = %d" % (UserName, UserID)

# you can see how many rows you got back
num_rows = curs1.execute("SELECT UserName, UserID FROM tblUsers")
# There is also the curs1.rowcount property, but I use it
# with caution. It doens't always behave the way I think it should.

# Passing values
# You can pass values in the sql statement
UserID = 20
sql = "SELECT UserName, UserBDay WHERE UserID = %d" % UserID
curs1.execute(sql)
# ..but you SHOULDN'T!
# You should create your sql using placeholders and pass the values
# using the parameter part of the cursor's execute method.
# This ensures that they are escaped propperly!
sql = "SELECT UserName, UserBDay WHERE UserID = %s"
curs1.execute(sql, (UserID,))
# Notes! We use %s instead of %d like you might expect.
# This is because paramstyle = 'format' by default. (see the API doc for more info)
# Also, we always use a tupple, even for a single variable.

# Inserts -
sql = "INSERT INTO tblUsers (UserName, UserBDay, UserPhone) VALUES ( %s, %s, %s)"
curs1.execute(sql, (UserName, UserBDay, UserPhone))
# I tend to name the Python variables the same as the column names, but
# there is no connection between the two-
UserName = "Bob Dobbs"
# -doesn't do anything to the database on its own.

# Don't be commitment shy!
# inserts and updates don't stick untill you commit- even if you are using MyISAM tables.
conn.commit()

# Handling errors-
# Lets say UserName is defined with a unique index, you already have a UserName 'Bob Dobbs'
# in the table and you try to insert another. The integrity error is raised.
sql = "INSERT INTO tblUsers (UserName, UserBDay, UserPhone) VALUES ( %s, %s, %s)"
try:
    curs1.execute(sql, (UserName, UserBDay, UserPhone))
except conn.IntegrityError:
    print "UserName is taken!"
else:
    print "User added OK!"

# Getting the value of an auto increment field-
# say UserID is your auto incremented primary key for tblUsers.
# Here's how to get it right after an INSERT that creates a new UserID
    sql = "SELECT LAST_INSERT_ID()"
    UserID = curs1.execute(sql).fetchone()[0]
# LAST_INSERT_ID() is a MySQL function, not a MySQLdb thing, so it will break if you
# decide to use a different db- just so you know.

More info

Python Database API Specification v2.0