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()



Leave a Reply