sqlite - insert blob

The ‘sqlite3’ is one of python3 builtin modules. I am attempting to make so-called ‘model provider’ in my own tensorflow example. Here is small and simple codes for you to insert a blob for h5 and select it. I will make an output file just the same as input file.

Make an env

If you need a seperate env from previous posts, just create new one by:

conda create -n sqlite-test python=3.7
  • Make a project directory

I made a project directory, and every paths in codes will be relative to the directory. Let’s call it sqlite-example. Then prepare res directory in the project to save files. That’s it, here’s two scripts for this test.

  • makeh5.py
import numpy as np
import h5py

import zipfile

d = np.random.random(size=(1000,20))

with h5py.File("./res/testh5.h5", 'w') as hf:
    hf['ds'] = d # create dataset

    print(hf.keys()) # Just for check
    print(hf.get('ds')) # Just for check

with zipfile.ZipFile('./res/h5zip.zip', 'w') as h5zip:
    h5zip.write('./res/testh5.h5', compress_type=zipfile.ZIP_DEFLATED)







import sqlite3

import os
import sys

DB_PATH = os.path.join(os.path.dirname(__file__), "res")
DB_FILE = os.path.join(DB_PATH, "test.db")

conn = sqlite3.connect(DB_FILE)
cs = conn.cursor()

sql = (
    "create table testtable ("
    "id integer primary key autoincrement not null,"
    "file blob,"
    "filename text);"
)
conn.execute(sql)

zipfile = os.path.join(DB_PATH, sys.argv[1])

with open(zipfile, "rb") as input_file:
    zip_blob = input_file.read()
    cs.execute(
        "insert into testtable (file, filename) values(?,'" + zipfile + "')", [sqlite3.Binary(zip_blob)]
    )
    conn.commit()

cs.execute(
    "select * from testtable"
)

out_zipfile = os.path.join(DB_PATH, "output.zip")

with open(out_zipfile, "wb") as output_file:
    cs.execute(
        "select file from testtable where id = 1"
    )
    zip_blobs = cs.fetchone()
    output_file.write(zip_blobs[0])
    cs.close()

conn.close()

Test

(sqlite-test) /path/to/sqlite-example$: python makeh5.py
(sqlite-test) /path/to/sqlite-example$: python ziptest.py h5zip.zip

간단한 두 스크립트를 통해 sqlite에 .h5 파일을 포함하는 .zip 파일을 넣었다가 빼보았다. 이걸 하는 이유는 어떤 DB에 tensorflow 모델을 저장하고 불러오는 구조가 필요해서다. 아마 어떤 모듈이 keras모델의 weight와 config를 저장할 것이다. 그럼 이걸 zip으로 묶어서 모델 DB에다가 binary로 저장했다가 후에 다시 불러서 zip파일을 training module에 던져주는 형식이 될 것이다.