MySQL

Intro

I’m still relatively new to Python. I dabble once in a while. Usually when I catch myself writing something overly complicated in bash. I figured doing MySQL from a Python script would be easier and better implemented in Python. Well, I was in for a few surprises I’d like to share with others looking to integrate with MySQL in Python before you encounter the same problems I did.

Getting on with it

As a Python noob, it made sense to me to use the python-mysqldb module. It “works” but not well. Read through the [working] code below and see if you can find the problem and how I circumvented it.

#!/bin/python
# Tyler Mulligan (tyler@doknowevil.net)
# MySQL the WRONG way in Python
import MySQLdb

def connection_settings():
    global DB_ADMIN, DB_ADMIN_PASSWORD, DB_NAME, DB_HOST, DB_USER, DB_PASSWORD
    DB_ADMIN="root"
    DB_ADMIN_PASSWORD="local"
    DB_NAME="foobardb"
    DB_HOST="localhost"
    DB_USER="foobar"
    DB_PASSWORD="local"

def run_admin_sql():
    conn = MySQLdb.Connection(host=DB_HOST, user=DB_ADMIN, passwd=DB_ADMIN_PASSWORD)
    cursor = conn.cursor()

    #cursor.execute("DROP USER %s@%s", (DB_USER, DB_HOST))
    #cursor.execute("DROP DATABASE %s" % DB_NAME)

    cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (DB_USER, DB_HOST, DB_PASSWORD))
    cursor.execute("CREATE DATABASE %s" % DB_NAME)
    cursor.execute("GRANT USAGE ON %s.* TO %s@%s IDENTIFIED BY '%s'" % (DB_NAME, DB_USER, DB_HOST, DB_PASSWORD))
    cursor.execute("GRANT ALL PRIVILEGES ON *.* to %s@%s IDENTIFIED BY %s", (DB_USER, DB_HOST, DB_PASSWORD))

    cursor.close()
    conn.close()

    conn = MySQLdb.Connection(db=DB_NAME, host=DB_HOST, user=DB_USER, passwd=DB_PASSWORD)
    cursor = conn.cursor()

    cat_table_sql = """CREATE TABLE cats
    (
    `id` int(3) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    `age` int(2) NOT NULL,
    `type` int(2) NOT NULL,
    PRIMARY KEY (`id`)
    )"""

    cursor.execute(cat_table_sql)
    cursor.execute("INSERT INTO cats (name, age, type) VALUES (%s, %s, %s)", ("fluffy", 5, 1))
    cursor.execute("INSERT INTO cats (name, age, type) VALUES (%s, %s, %s)", ("meow meow", 6, 1))
    cursor.execute("INSERT INTO cats (name, age, type) VALUES (%s, %s, %s)", ("purrfect", 3, 1))

    cursor.execute("SELECT * FROM cats")

    results = cursor.fetchall()
    cursor.close()
    conn.close()
    return results

connection_settings()
print run_admin_sql()

The problem is parameterization. The python-mysqldb module sucks at it. It’s glaringly obvious when you look at the follow snippet and how I worked around it.

cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (DB_USER, DB_HOST, DB_PASSWORD)) # GOOD
cursor.execute("CREATE DATABASE %s" % DB_NAME) # BAD NEWS BEARS
cursor.execute("CREATE DATABASE %s", (DB_NAME)) # but the module won't support this

The difference, is that the first implementation uses the module’s parameterization to escape the variables that are passed to it, making it safe. The second is using Python’s string formatter which means I’d have to do all the escaping and sanitization prior, which is obviously more dangerous and annoying.

The MySQLdb docs bury this little note, “Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.”

You can implement it this way but you’ll be wasting your time. There are better modules out there to make up for this one’s shortcomings. OurSQL and SQLAlchemy were the two libraries recommended to me in #python on irc.freenode.org. I will make another post when I develop the same example above using one of these libraries.