python sqlite数据库存储图片_如何用python在sqlite数据库中存储jpg

存储和检索blobimport 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)