A simple MySQLdb example python script

Posted: August 17th, 2011 | Author: | Filed under: MySQL, Python | No Comments »

I mostly stick to mongodb nowadays, but every now and again I need to access data stored in a MySQL table. In my last post I talked about a MySQLdb error. This is a variant of the script which induced the error. It takes a .csv file with application ids piped to the script and joins them with price, category and name data from a db. This script uses the simplejson and MySQLdb

#!/usr/bin/env python

import sys
import simplejson
import MySQLdb
import re

def connect_db(host, port, user, password, db):
try:
return MySQLdb.connect(host=host, port=port, user=user, passwd=password, db=db)
except MySQLdb.Error, e:
sys.stderr.write(“[ERROR] %d: %s\n” % (e.args[0], e.args[1]))
return False

def main():
# the line below won’t work for you unless you put in your working credentials
# you didn’t think I’d put working credentials on my blog did you?
dbconn = connect_db(ip, port, user, password, db)

for line in sys.stdin.readlines():
app_id = line.split(“,”)[0]
sql = “SELECT info FROM apps WHERE id = ‘%s'” % app_id
try:
cursor = dbconn.cursor()
cursor.execute(sql)
result = cursor.fetchone()
except MySQLdb.Error, e:
sys.stderr.write(“[ERROR] %d: %s\n” % (e.args[0], e.args[1]))
continue

data = simplejson.loads(result[0])
price = data[“price”] if data[“price”] else “null”
categories = data[“categories”] if data[“categories”] else “null”
name = data[“appName”] if data[“appName”] else “null”
print “%s,%s,%s,%s” % (name, line.strip(), price, categories)

if __name__ == “__main__”:
main()


Library not loaded: libmysqlclient.18.dylib

Posted: August 12th, 2011 | Author: | Filed under: MySQL, Python | 6 Comments »

Last week I upgraded to Lion from Snow Leopard. While I love the subtle touches of the new OS and even the natural (inverted) scrolling, it seriously screwed with my existing python packages and torched gcc until I installed X Code 4. I have a python script which uses the MySQLdb package. After a supposedly successful installation of MySQLdb, running my python script yielded the error:

ImportError: dlopen(/Users/alex/.python-eggs/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg-tmp/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib
Referenced from: /Users/alex/.python-eggs/MySQL_python-1.2.3-py2.7-macosx-10.7-intel.egg-tmp/_mysql.so
Reason: image not found

For some reason the install pointed itself to the wrong place. Adding the following to your ~/.profile or ~/.bash_profile should fix the issue (assuming this is where you MySQL installation sits):

export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH

Open up a new terminal and you should be good to go.

Update! This also fixes some ruby 1.9 and rails 3 installation issues on OSX Lion. Thanks to Mauro Morales (@noiz777 for finding this!