CODEWITHSUNDEEP
pythonsqlitejpegpygtkgtk3
Filippos
Filippos

Reputation: 457

how to store a jpg in an sqlite database with python

I've been trying for many days to find a solution to this problem. I need to write a small jpg image for each record in an sqlite database. Finally I managed to insert the file but judging from the size it was written in the database as raw instead of a (compressed) jpg. The code I used is:

imgobj = Image.open('./fotocopies/checks/633.jpg')
con = sqlite3.connect("pybook.db")
cur = con.cursor()
cur.execute("UPDATE data_fotocopies SET fotocopy=? WHERE refid=633 and reftype=0", [ buffer(imgobj.tobytes()) ] )

If I try to open the file it cannot be inserted in the database so, the following code:

imgobj = open('./fotocopies/checks/632.jpg')
con = sqlite3.connect("pybook.db")
cur = con.cursor()
cur.execute("UPDATE data_fotocopies SET fotocopy=? WHERE refid=632 and reftype=0", [sqlite3.Binary(imgobj)] )

gives the following error:

cur.execute("UPDATE data_fotocopies SET fotocopy=? WHERE refid=632 and reftype=0", [sqlite3.Binary(imgobj)] )
TypeError: buffer object expected

Unfortunately no previous answer in stackoverflow covers me as I've tried them all. Furthermore all the storing retrieving has to be done via a gtk3 interface which I suspect will mean another (series of) problem(s) i.e. how to set an existing image to get its data from the db response etc. Can anyone help?

Upvotes: 5

Views: 12294

Answers (2)

Filippos
Filippos

Reputation: 457

Finally I got it working thanks to Andrej Kesely's comment. The working solution is

imgobj = base64.b64encode(open('./fotocopies/checks/624.jpg').read())
con = sqlite3.connect("pybook.db")
cur = con.cursor()
qf="UPDATE data_fotocopies SET fotocopy='%s' WHERE refid=%d AND reftype=0"%(lite.Binary(fotocopy_blob),id)
cur.execute(qf) #yes, it is dangerous for injection`

and retrieving the image from the database is done as:

qf="SELECT fotocopy FROM data_fotocopies WHERE refid=%d and reftype=0"%self.check_id
self.cur.execute(qf)
try:
    fd=base64.b64decode(self.cur.fetchall()[0][0])
    byting = GLib.Bytes(fd)
    self.fotocopy = Gio.MemoryInputStream.new_from_bytes(byting)
    ...
self.fotocopy_ent=self.builder.get_object("fotocopy")         # as it is made in glade
pixbuf = GdkPixbuf.Pixbuf.new_from_stream(self.fotocopy,None) #finally the pixbuf although 
                                                              #it produces errors if I have
                                                              #no stream/image to "feed" it.
self.fotocopy_ent.set_from_pixbuf(pixbuf)

Still can't figure out why all other solutions I've found don't work. I use Python 2.7.6 ang gtk3, but this one I subit does.

Thank you all for your help.

Upvotes: 2

Kasun
Kasun

Reputation: 7331

Storing and retrieving BLOBs

import sqlite3
import os.path
from os import listdir, getcwd
from IPython.core.display import Image

def get_picture_list(rel_path):
    abs_path = os.path.join(os.getcwd(),rel_path)
    print 'abs_path =', abs_path
    dir_files = os.listdir(abs_path)
    return dir_files

def create_or_open_db(db_file):
    db_is_new = not os.path.exists(db_file)
    conn = sqlite3.connect(db_file)
    if db_is_new:
        print 'Creating schema'
        sql = '''create table if not exists PICTURES(
        ID INTEGER PRIMARY KEY AUTOINCREMENT,
        PICTURE BLOB,
        TYPE TEXT,
        FILE_NAME TEXT);'''
        conn.execute(sql) # shortcut for conn.cursor().execute(sql)
    else:
        print 'Schema exists\n'
    return conn

def insert_picture(conn, picture_file):
    with open(picture_file, 'rb') as input_file:
        ablob = input_file.read()
        base=os.path.basename(picture_file)
        afile, ext = os.path.splitext(base)
        sql = '''INSERT INTO PICTURES
        (PICTURE, TYPE, FILE_NAME)
        VALUES(?, ?, ?);'''
        conn.execute(sql,[sqlite3.Binary(ablob), ext, afile]) 
        conn.commit()
def extract_picture(cursor, picture_id):
    sql = "SELECT PICTURE, TYPE, FILE_NAME FROM PICTURES WHERE id = :id"
    param = {'id': picture_id}
    cursor.execute(sql, param)
    ablob, ext, afile = cursor.fetchone()
    filename = afile + ext
    with open(filename, 'wb') as output_file:
        output_file.write(ablob)
    return filename


conn = create_or_open_db('picture_db.sqlite')
picture_file = "./pictures/Chrysanthemum50.jpg"
insert_picture(conn, picture_file)
conn.close()

conn = create_or_open_db('picture_db.sqlite')
cur = conn.cursor()
filename = extract_picture(cur, 1)
cur.close()
conn.close()
Image(filename='./'+filename)

Upvotes: 4

Related Questions