PythonMySQL

From XPUB & Lens-Based wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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'],))