How Do I Store A Picture In A Table With Python-sqlite3?

I'm trying to store a picture in sqlite3 table. I'm using python and sqlite3. Please let me know if you have a sample code or how to save a picture to a sqlite3 table.

Solution 1:

You can either encode it as a base64 string, like Yogesh mentioned, or try to store the binary.

import sqlite3
import base64

conn = sqlite3.connect('example.db')
c = conn.cursor()
c.execute('''CREATE TABLE images (image text)''')

# binary
c.execute("INSERT INTO images VALUES ({})".format(sqlite3.Binary(file.read())))

# base64
c.execute("INSERT INTO images VALUES ({})".format(base64.b64encode(file.read())))

conn.commit()
conn.close()

Solution 2:

Using blob type for image data is good.The data stored using the sqlite.Binary type.

Solution 3:

A simple code I have written to manage small amount of images when I learned sqlite3. a little bit long and urgly, but it can works. hope it work for you

#!/usr/bin/envfrom skimage import io, filters

import warnings, os

import numpy as np

import sqlite3

warnings.filterwarnings('ignore')

classSqliteImage(object):

def__init__(self, databaseDir):
    self.databaseDir = databaseDir
    self.conn = sqlite3.connect(databaseDir)
    self.cur = self.conn.cursor()

defcreateTable(self, table):
    self.cur.execute(""" CREATE TABLE %s (
                name     TEXT PRIMARY KEY,
                content  TEXT,
                oldShape INT
                )""" % table)

defdelete(self, table):
    self.cur.execute('DROP TABLE %s' %table)

# put images into sqlite3defshapeToStr(self, arr):
    return' '.join([str(item) for item in arr])
defsaveImage(self, path):
    img = io.imread(path)
    newShape = [1]
    oldShape = img.shape
    for val in oldShape:
        newShape[0] *= val
    img = np.array(img.reshape(newShape), dtype=str)
    img = ' '.join(img)
    self.cur.execute('INSERT INTO img VALUES (?, ?, ?)', 
            [str(os.path.basename(path)), img, self.shapeToStr(oldShape)] )

# get images from sqlite3defdec(self, name):
    return"\'"+name+"\'"defgetImage(self, name):
    self.cur.execute(("SELECT name, content, oldShape FROM img WHERE name=%s;" % self.dec(name)))
    # print([item[0] for item in cur.description])for basename, img, oldShape in self.cur.fetchall():
        oldShape = [int(item) for item in oldShape.strip().split()]
        img = np.array(img.strip().split(), dtype=int)
        img = img.reshape(oldShape)
        print(basename)
        io.imshow(img)
        io.show()

defclose(self):
    self.conn.commit()
    self.conn.close()

# test
db = SqliteImage('images.db')
db.saveImage(os.path.join(r'C:\Users\Administrator\Desktop', 'crocodile.jpg'))
db.getImage('crocodile.jpg')
db.saveImage(os.path.join(r'C:\Users\Administrator\Desktop', 'bear.jpg'))
db.getImage('bear.jpg')
db.close()