The Holy Grail of Binary Data in PostgreSQL and Python

I'm not sure if you've ever had this problem.  I've had it a lot of times and I suppose I'm not alone. The thing is the following, say you want to store binary data in PostgreSQL. For those who use MySQL, I've heard it's a very simple task, however I don't care since I never use MySQL :). On the other hand, doing this from Python to a PostgreSQL database, can be quite tricky. What I used to do is to Base64 encode everything and store it on the database, that worked but it was slow and bloated. There's another way, the "bytea" data type. The problem with bytea is that PostgreSQL wants things escaped and it is quite unclear how to do it. However, today I found it out and I was so happy I decided to blog it! It is actually simple once you know the trick. First you have to create a table with a bytea field (where you store your bytes), for example:

CREATE TABLE IMAGES (ID SERIAL, NAME VARCHAR, DATA BYTEA);

This is the easy part and I think we all have gotten this far. Now the thing is in the Python side. The secret is to escape things properly. To insert binary data to a PostgreSQL database you need to follow this scheme: E' + data in octal + '::bytea For example:

INSERT INTO IMAGES VALUES(default, 'PARIS.JPG', E'\\001\\002\\003\\031\\313'::bytea);

Notice the double "\" since the bar is un-escaped by the database. Easy? Yeah! But hard to find out. Now it's just writing a simple Python function to do the escaping:

def octize(data):
    out = "E'"
    for char in data:
        octdata = oct(ord(char))[1:].zfill(3)
        out += "\\\\" + octdata
     return out + "'::bytea" 

And now we could do something like:

conn = psycopg.connect()
cur = conn.cursor()
cur.insert("INSERT INTO IMAGES VALUES(default, 'PARIS.JPG', %s)"  
           % octize(image_data))
cur.close()
conn.commit()
Hope you liked it! Happy hacking!