DB SQL - SQLite - store and read JPEG Files in databases - also using Python

import knime.scripting.io as knio

# This example script creates an output table containing randomly drawn integers using numpy and pandas.

import numpy as np
import pandas as pd

import sqlite3
import os
from PIL import Image
import io

# Connect to SQLite database (it will create the database file if it does not exist)
conn = sqlite3.connect(knio.flow_variables['Path_SQLite_location'])
cursor = conn.cursor()

# Variable for the table name
table_name = 'images_01'

# SQL command with variable table name
sql_command = f'''
CREATE TABLE IF NOT EXISTS {table_name} (
id INTEGER PRIMARY KEY AUTOINCREMENT,
image BLOB
)
'''
try:
cursor.execute(sql_command)
except sqlite3.OperationalError as e:
print(f"Error creating table: {e}")

# Function to convert image to binary data
def convert_image_to_blob(image_path):
with Image.open(image_path) as image:
blob = io.BytesIO()
image.save(blob, 'JPEG')
return blob.getvalue()

# Directory containing images
image_directory = knio.flow_variables['context.workflow.data-path']

# List to hold filenames
filenames = []

# Loop through each file in the directory
for filename in os.listdir(image_directory):
if filename.endswith('.jpg'):
filenames.append(filename)
image_path = os.path.join(image_directory, filename)
image_blob = convert_image_to_blob(image_path)
# Insert image into the database using a dynamic table name
insert_sql = f'INSERT INTO {table_name} (image) VALUES (?)'
cursor.execute(insert_sql, (image_blob,))

# Commit changes and close connection
conn.commit()
conn.close()

# Create a DataFrame from the list of filenames
df_filenames = pd.DataFrame(filenames, columns=['Filename'])


knio.output_tables[0] = knio.Table.from_pandas(df_filenames)