Thursday, May 27, 2010

Images in SQL Server

A simple .NET class to dump image data into/from SQL Server. I'd like to explore a potential alternative to this using FILESTREAM.


update version set [file] = BulkColumn from
openrowset(bulk 'e:\....jpg', single_blob) as [file]
where ...;

In Python/MySQL, this is done like this: (the image column, image_data, is defined as BLOB in MySQL)

>>> import MySQLdb
>>> connection = MySQLdb.connect('','root','','RTest')
>>> blob = open('d:\\pic1.jpg', 'rb').read()
>>> sql = 'INSERT INTO rtest.mm_image(image_data, mm_person_id_mm_person) VALUES(%s, 1)'
>>> args = (blob,)
>>> cursor = connection.cursor()
>>> cursor.execute(sql, args)
>>> connection.commit()

blob is a string type.

Wednesday, May 26, 2010


There seems to be precious little information on data management in the context of CMS (Content Management Systems). To me, this would seem very important - data admin, modeling, etc, all should be hugely important, and yet some of the CMS I come across are little more than bit buckets with a data dictionary. While web-based CMS have certainly driven the development of NoSQL (due to performance reasons, given the underoptimal use of the RDBMS by the CMS), certainly some thought has gone into the data layer of the CMS... where is it at?

Speaking of CMS: Here's Plone, which is running on a NoSQL incidentally.

Thursday, May 20, 2010

Python ORM

A first shot at ORMing in Python.

The second, improved shot. Inheritance/polymorphism in weakly-typed languages such as Python is a bit hard to grasp at first. Anyway, this seems quite cool.

Class diagram: (I am not an expert @ UML)

Existing solutions:
- for PHP
- for Python

Tuesday, May 18, 2010

Monday, May 10, 2010

BLOBing in Mongo

SQL Server, Oracle, Cache, etc, all have binary streams, and offer various ways of storing binary data (such as images) directly into the database. I was curious to see how this would work with Mongo, and here is the result:

import pymongo
import urllib2
import wx
import sys
from pymongo import Connection

class Image:

def __init__(self):
self.connection = pymongo.Connection()
self.database = self.connection.newStore
self.collection = self.database.newColl
self.imageName = "Uninitialized"
self.imageData = ""

def loadImage(self, imageUrl, imageTitle = "Undefined"):
ptrImg = urllib2.Request(imageUrl)
ptrImgReq = urllib2.urlopen(ptrImg)
imageFeed =
self.imageData = pymongo.binary.Binary(imageFeed, pymongo.binary.BINARY_SUBTYPE)
self.imageName = imageTitle

self.imageName = "Error " + str(sys.exc_info())
self.imageData = None

def persistImage(self):
if self.imageData == None:
print 'No data to persist'
print 'Persisting ' + self.imageName
self.collection.insert({"name":self.imageName, "data":self.imageData})
self.imageData = None

def renderImage(self, parm = None):
if parm == None:
self.imageData = self.collection.find_one({"name":self.imageName})
self.imageName = parm
self.imageData = self.collection.find_one({"name":self.imageName})

ptrApp = wx.PySimpleApp()
fout = file('d:/tmp.jpg', 'wb')
wximg = wx.Image('d:/tmp.jpg',wx.BITMAP_TYPE_JPEG)
wxbmp = wximg.ConvertToBitmap()
ptrFrame = wx.Frame(None, -1, "Show JPEG demo")
wx.StaticBitmap(ptrFrame, -1, wxbmp, (0,0))

img = Image()
img.loadImage('', 'Acapulco at night')
img.renderImage('Acapulco at night')

I have had some problems with installing PIL, so this is certainly not optimal (I have to use wx for image rendering instead, and I have not found a way of feeding a JPG datastream to an image constructor, hence the ugly recourse to a temporary file). However, the idea was to test how the database can store an image, which seems to work quite well, despite taking a few seconds to load a 300kb file.

A findOne query returns:

> db.newColl.findOne()
"_id" : ObjectId("4be82f74c7ccc11908000000"),
"data" : BinData type: 2 len: 345971,
"name" : "Acapulco at night"

Thanks are due for some of the wx code.