Python Subprocess Wrapper for MySQL

Compiling MySQL support for Python under OS X Leopard is a pain, and, so far as I can see, there is no handy way to do it with a redistributable universal binary.

So, I decided to call MySQL from within Python using a subprocess. This is the code that I came up with, which should work fine with a default installation of MAMP. It likely has more overhead than using MySQLdb, and would likely choke if you got a really large result-set back. However, I think it will serve my purposes admirably.

#!/usr/bin/env python

from subprocess import Popen, PIPE

# Set the command you need to connect to your database
# WARNING: password will be visible in a process listing!
mysql_cmd_line = "/Applications/MAMP/Library/bin/mysql -u root -p"
mysql_password = "root"

def RunSqlCommand(sql_statement, database=None):
    
    """Pass in the SQL statement that you would like executed.
    Optionally, specify a database to operate on.  Returns the result."""
    
    command_list = mysql_cmd_line.split()
    if database:
        command_list.append(database)
        
    # Run mysql in a subprocess
    process = Popen(command_list, stdin=PIPE, stdout=PIPE,
                    stderr=PIPE, close_fds=True)
    
    # pass it our commands, and get the results
    (stdout, stderr) = process.communicate( mysql_password )

    return stdout

def test():
    """Performs a simple test connection."""

    print "This specified MySQL server has the following databases:"
    print
    print RunSqlCommand("SHOW DATABASES;")

    print RunSqlCommand("SELECT * FROM USER_PRIVILEGES LIMIT 2;", "information_schema")

if __name__ == "__main__":
    test()

The output of that command, against a pristine MAMP installation, is:

This specified MySQL server has the following databases:

Database
information_schema
mysql
test

GRANTEE TABLE_CATALOG PRIVILEGE_TYPE IS_GRANTABLE
'root'@'localhost' NULL SELECT YES
'root'@'localhost' NULL INSERT YES