Image (Upload, Insert and Retrieval) using BLOBs type in zope

This document describes the handling of BLOBs, esp. images, with MySQL and Zope 2.5.1.
You should be able to adapt this procedures easily to other DBMS, MIME types or to CLOBs. Knowledge of Python, Z SQL Methods and HTML is required to understand this How-To.
The following sample assumes you'd like to upload a person's photo, insert it into a MySQL table Photo with the attributes

Name	Type
Person_ID	INT UNSIGNED, primary key
Pic	BLOB, not null
Type	VARCHAR(4), not null

and later retrieve it to display it in a Zope document.

---

The HTML form for upload may look like the following sample:

  <form action="newPhoto.py" method="post" enctype="multipart/form-data">
    <input name="pid" type="hidden" value="&dtml-ID;">
    <input name="Photo" type="file" maxlength="65535" accept="image/*">
    <input type="submit" value="Insert Picture">
  </form>

Due to the size of the uploaded data you'll have to use the post method. A hidden field provides the person's ID, here retrieved from a variable in DTML namespace. The attributes maxlength and accept may be given for the file input, but aren't supported reliably by every browser, so this characteristics of the image should be checked in the processing script again.
A Python script with the ID newPhoto.py takes care of recognizing the MIME type of the uploaded file by the file extension:

  photo = container.REQUEST.form['Photo']
  filedata = photo.read()
  filetype = string.split(photo.headers['Content-Type'], "/")

---

This section describes an alternative to get the MIME type. I used this possibility myself, but the above way seems to be more convenient. Skip this part if you want to keep it simple.
However, using headers['Content-Type'] relies on the ability of the client browser to detect the correct MIME type, so the alternative way below, using the built-in facilities of Zope, could be of use in some cases.
Replace the last line in the above script by

    filetype = string.split(guess_content_type(photo.filename)[0], "/")
  

This makes use of the Zope core function OFS.content_types.guess_content_type(), which is normally not allowed to be used in Python scripts. For accessing it you'll have to do a

    allow_module('OFS.content_types.guess_content_type')
  

in the file PATH-TO-ZOPE/lib/python/Products/GlobalModules/__init__.py
Warning: Allowing the execution of functions and modules this way may raise security issues on your Zope system and scripts. A better solution would be the usage of an External Method to access the mentioned function. In the present case it is used by the Zope classes Image and File, too, so I don't see too much of a problem here. Anyway you should be very restrictive in allowing access this way and only enable it for single functions, not for whole modules!
Now the function can be imported with

    from OFS.content_types import guess_content_type
  

and called.

---

In the code snippet above the returned MIME type is splitted into two strings by the separating "/" (the string module must have been imported, of course). After checking the filedata for size by len() (not more than suitable for the corresponding attribute type of the DBMS) and the right MIME type (here only images are admissible) in the same script newPhoto.py you can store Person-ID, image data and the second part of the type into the DB:

  context.insertPhoto(Person_ID=container.REQUEST.pid, Pic=filedata, Type=filetype[1])

insertPhoto as the corresponding Z SQL Method accepts this three parameters and looks like this:

  insert into Photo
  set <dtml-sqltest Person_ID op="eq" type="int">,
      <dtml-sqltest Pic op="eq" type="string">,
      <dtml-sqltest Type op="eq" type="string">

---

Retrieving the image is a bit less complicated. You need of course a Z SQL Method (getPhoto) fetching the data by the person's ID (Parameter Person_ID):

  select Pic, Type
  from Photo
  where <dtml-sqltest Person_ID op="eq" type="int">

Calling this method with the PID, setting the correct MIME type for the response and eventually returning the image data takes place in another Python script called fetchPhoto, which consists mainly of three lines:

  photo = context.getPhoto(Person_ID = pid)
  container.REQUEST.RESPONSE.setHeader('content-type', 'image/' + photo[0].Type)
  return photo[0].Pic

This script can be used in an <img> or <a> tag like a file reference and only needs to be given the correct PID:

  <img src="fetchPhoto?pid=&dtml-ID;" alt="Photo">