Icon

kn_​example_​db_​sqlite_​jpg_​images

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

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

SQLite - store and read JPEG Files in databases - also using Python Exploring the Power of Python Graphics with KNIME: A Collection of Exampleshttps://medium.com/p/841df87b5563 import knime.scripting.io as knio# This example script creates an output table containing randomly drawn integers using numpy and pandas.import numpy as npimport pandas as pdimport sqlite3import osfrom PIL import Imageimport 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 nametable_name = 'images_01'# SQL command with variable table namesql_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 datadef 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 imagesimage_directory = knio.flow_variables['context.workflow.data-path']# List to hold filenamesfilenames = []# Loop through each file in the directoryfor 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 connectionconn.commit()conn.close()# Create a DataFrame from the list of filenamesdf_filenames = pd.DataFrame(filenames, columns=['Filename'])knio.output_tables[0] = knio.Table.from_pandas(df_filenames) locate and create/data/ folderwith absolute pathsPath_SQLite/data/iris.csvwrite jpg files to disk/data/knime://knime.workflow/data/my_database.sqliteinsert jpgimages from path into SQLite databasePath_SQLiteimages_01images_01bring binary images (jpg)back from SQLiteexport JPGfiles to discPath_Export_JPG_01 Collect LocalMetadata Create File/FolderVariables CSV Reader Image Writer Concatenate SQLite Connector Python Script Path to String(Variable) DB Table Selector DB Reader Binary Objectsto Files Create File/FolderVariables Create JPG images SQLite - store and read JPEG Files in databases - also using Python Exploring the Power of Python Graphics with KNIME: A Collection of Exampleshttps://medium.com/p/841df87b5563 import knime.scripting.io as knio# This example script creates an output table containing randomly drawn integers using numpy and pandas.import numpy as npimport pandas as pdimport sqlite3import osfrom PIL import Imageimport 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 nametable_name = 'images_01'# SQL command with variable table namesql_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 datadef 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 imagesimage_directory = knio.flow_variables['context.workflow.data-path']# List to hold filenamesfilenames = []# Loop through each file in the directoryfor 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 connectionconn.commit()conn.close()# Create a DataFrame from the list of filenamesdf_filenames = pd.DataFrame(filenames, columns=['Filename'])knio.output_tables[0] = knio.Table.from_pandas(df_filenames) locate and create/data/ folderwith absolute pathsPath_SQLite/data/iris.csvwrite jpg files to disk/data/knime://knime.workflow/data/my_database.sqliteinsert jpgimages from path into SQLite databasePath_SQLiteimages_01images_01bring binary images (jpg)back from SQLiteexport JPGfiles to discPath_Export_JPG_01 Collect LocalMetadata Create File/FolderVariables CSV Reader Image Writer Concatenate SQLite Connector Python Script Path to String(Variable) DB Table Selector DB Reader Binary Objectsto Files Create File/FolderVariables Create JPG images

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: jafarsharifs
Created at: 2019-02-14
On NodePit since: 2024-06-28
Last update: 2024-11-21
Created with KNIME version: v4.7.8
Tags: pythongraphicsknimeirispngexportjpgjpegsqlitedatabasesql

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!