PythonMySQL

From Media Design: Networked & Lens-Based wiki
Jump to navigation Jump to search

You'll need the "MySQLdb" library installed.

Connecting to MySQL

If you put this function in a file (say "settings.py"), you can easily import if from different scripts... and also you only need to change the login data in one location (useful for local vs. server-based testing).

import MySQLdb

def connect_db():
    host_name = "localhost"
    db_name = "dbname"
    user_name = "dbuser"
    password = "dbpass"
    
    try:
        conn = MySQLdb.connect (db = db_name,
                                host = host_name,
                                user = user_name,
                                passwd = password)
        return conn
    except MySQLdb.Error, e:
        print "Cannot connect to server"
        print "Error code:", e.args[0]
        print "Error message:", e.args[1]
        sys.exit(1)

Executing a Query

Many rows

import settings
name = "foo"
dbconn = settings.connect_db()
cursor = dbconn.cursor()
q = "SELECT id FROM items WHERE status='active' AND name=%s ORDER BY id"
cursor.execute(q, (name,))
while 1:
	row = cursor.fetchone()
	if (row == None): break
	print row[0]

# if you forget this, it happens automatically
cursor.close()
dbconn.close()

Gordo

dbconn = settings.connect_db()
cursor = dbconn.cursor(MySQLdb.cursorx.DictCursor)
while 1:
	q = "SELECT * FROM commands ORDER BY t_create LIMIT 1"
	cursor.execute()
        row = cursor.fetchone()
	print row['command']
	#
	if (row['command'] == "foo"):
		print "doing foo command"
	elif (row['command'] == "print"):
		pass
	#
	q = "DELETE FROM commands WHERE id=%s"
	cursor.execute(q, (row['id'],))