I manage a site that uses Google Apps for hosting their domain services (email, calendar, etc…), and they had a requirement to have the ability to email images to an inbox and have those images automatically inserted into a mysql database, where a forward-facing web application would later display the upload date/time, a comment about the image, and the image itself. The process needed to be fairly simple, but it also needed to use GMail’s SSL ports for pulling down the information (is there even another way to do it nowadays?)… Additionally, they needed height and width information about the image stored in the table, so that the front-end application (which I also wrote) could perform on-the-fly thumbnail resizing to the client — basically so that we could store the entire image in the database and produce the much more bandwidth friendly thumbnail images in real-time for the client. Processing power wasn’t a huge deal since this is a private site and would have maybe four or five simultaneous clients viewing the images.

Python’s Image library is a thing of magic, and anytime I get the opportunity to work with image manipulation or anything of the sort, I always use Python to do it. As I’m sure you have already gathered from my other posts, I prefer the quick and dirty path to the end result, and Python delivers this in a very elegant way.

The example that I am about to post is a much scaled down version of the actual code and does not handle any error checking, or sender validation and should not be used in a production environment. My hope with this example is that you get the bare metal basic idea, and emphasize on it from there. So please, be reserved with your comments on how I haven’t handled any validation or checking, because my goal with this is not to deliver an enterprise-class application, but rather to give you a taste of how you can accomplish this feat…

Alright, disclaimer out of the way… Here’s the bare metal:

#! python

# Import the poplib classes that we'll need to pull messages from gmail
from poplib import POP3, POP3_SSL

# Import the classes that we will need for working with the image and
# getting them into the mysql db
import email,mimetypes,Image,StringIO,base64,time,MySQLdb

def getImageSize(pl):
        ''' Returns the image dimensions from the scraped image '''
        file=StringIO.StringIO(pl)
        img=Image.open(file)
        return img.size

def encodedImage(pl):
        ''' Base64 encodes the image so that we can store it in the mysql db '''
        return base64.encodestring(pl)

def uploadImage(fn,comment,x,y,b64):
        ''' Takes the filename, a comment, the base64 encoded image, and the dimensions as data
            that will be stored in the table. The structure of the table is as such:

                mysql> explain Images;
                +----------+--------------+------+-----+---------+----------------+
                | Field    | Type         | Null | Key | Default | Extra          |
                +----------+--------------+------+-----+---------+----------------+
                | id       | int(11)      | NO   | PRI | NULL    | auto_increment |
                | filename | varchar(255) | NO   |     | NULL    |                |
                | image    | longblob     | NO   |     | NULL    |                |
                | comment  | varchar(255) | YES  |     | NULL    |                |
                | width    | int(5)       | YES  |     | NULL    |                |
                | height   | int(5)       | YES  |     | NULL    |                |
                +----------+--------------+------+-----+---------+----------------+
                6 rows in set (0.11 sec)

                mysql>

        '''

        conn = MySQLdb.connect ( host = "mysqlserver.example.com",
                                 user = "mysqluser",
                                 passwd = "mysqlpass",
                                 db = "ImagesDB" )
        cursor = conn.cursor()
        sql="INSERT INTO Images (filename,comment,image,width,height) VALUES ('%s','%s','%s',%i,%i)" % (filename,MySQLdb.escape_string(comment),b64,x,y)
        cursor.execute(sql)
        cursor.close()
        conn.commit()
        conn.close()

# Instantiate the POP3 SSL object
p = POP3_SSL("pop.gmail.com")
try:
        # Supply your username and password
        p.user("user@gmail.com")
        p.pass_("password")

        # Get the list of messages
        msg_list = p.list()

        # Iterate the message list
        for msg in msg_list[1]:
                # Capture the message number... We'll need this later
                msg_num, _ = msg.split()
                # Retrieve the message
                resp = p.retr(msg_num)

                # This will tell us if we're working with a valid message
                if resp[0].startswith('+OK'):
                        # Get the message object from the email
                        parsed_msg = email.message_from_string('\n'.join(resp[1]))

                        # We'll capture the subject line as the comment
                        comment = parsed_msg.get('Subject')

                        # Walk the parsed message
                        for part in parsed_msg.walk():
                                # We're not working with multipart messages, so continue
                                # through this
                                if part.is_multipart():
                                        continue

                                # Is this an image? Yes.
                                if part.get_content_maintype() == 'image':
                                        # Get the filename
                                        fn = part.get_filename()
                                        # "pl" is the image data
                                        pl = part.get_payload(decode=True)
                                        # Get the image dimensions
                                        x,y=getImageSize(pl)
                                        # Base64 encode the image so that we can
                                        # put it into the database
                                        b64=encodedImage(pl)
                                        # We'll get the extension from the text after the first
                                        # dot, because I know that images will always come as
                                        # filename.extension -- you can change this to [-1] so
                                        # that you grab the last text after the dot
                                        ext=fn.split('.')[1]
                                        # Construct the filename as a unix timestamp and
                                        # concatenate the extension on the end
                                        filename="%s.%s" % (str(time.time()).replace('.','_'),ext)
                                        # Now perform the upload sequence
                                        uploadImage(filename,comment,str(x),str(y),b64)
                                        # And delete the message (this won't actually delete it, but it will make it so that next time we pull messages using pop3, it will not show up in the list
                                        p.dele(msg_num)
finally:
        # Disconnect
        p.quit()

We store the image in the database as a base64 encoded string, which can be readily and easily retrieved… This is easier than trying to handle raw binary data in memory with MySQL, because we’re not ever actually touching the filesystem to store the image temporarily.

I set this script up on a cronjob running every hour (since time was not of the essence), and the images get automatically inserted to the database, and are available to the clients on the front end as soon as they’re inserted.

But, again, I want to stress that if you intend to use a derivation of this script, ensure that you modify it to perform sender validation so that we’re not just inserting any old image into the database…

-dan

Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

© 2013 Dan's Blog Suffusion theme by Sayontan Sinha