Avatar of Dolamite Jenkins
Dolamite Jenkins
Flag for United States of America asked on

sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

Storing images in Sqlite ...I have no choice I have to use sqlite .... every thing works except when I try to execute the Insert query

line 36, in <module>
    cursor.execute(""" INSERT INTO my_table (name, image) VALUES (?,?)""",(name,image))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

my code snippet

import sqlite3
import os
import sys


#=============Create DataBase========================

conn = sqlite3.connect("my_db_example.db")
cursor = conn.cursor()

cursor.execute("""CREATE TABLE IF NOT EXISTS my_table (name TEXT,image BLOB)""")

conn.commit()
cursor.close()
conn.close()

#==============get & convert Data ================================================
image_name =[]
conn = sqlite3.connect("my_db_example.db")
cursor = conn.cursor()

arr = os.listdir('Images')
#print(arr)
for item in arr:
    image_name.append(item)
    name = image_name
    if ".jpg" in item:
        with open(item, "rb") as f:
            data = f.read()
            name = image_name
            image = data
            print("{} File Name ".format(name))
            print("{} File RB ".format(image))
            cursor.execute(""" INSERT INTO my_table (name, image) VALUES (?,?)""",(name,image))
            #print("{} Added to database ".format(image_name))

conn.commit()
cursor.close()
conn.close()

Open in new window

"Images" directory
Image 1.jpg
image 2.jpg
image3.jpg

when I print the output it is correct ...see sample output snippet

\xe6\x8dK\xbd\xbb~:\xdb\xe5:\xc5\nA\xb6u\x15`\x18\x04MFGt\xe5\x97\x90\x9c\x9ek\xe3<r\xfa\x7f\xf4p\xdd\xa3\x96\xe0\xf6mJ\xa7\xb8\xa6\xdfh\xe4\xbe\xb3A\x82\xec\xda.nw\xcf-\x17\xf5\x9a\x11kd\x00\xe9\t\xa7S\x9b`\xa2D\x14\x80\x16\x02\xc0r\x8d\xde\xd2[\t\x1a\xb1\xb1\x03\x9f\xba \x14\x1f\xc6\xde\xe8#_G\x04`\x7f\xff\xd9' File Read Binary
['maid5.jpg', 'Unknown.jpg', 'big5.jpg'] File Name 

Open in new window

If I just enter the Binary data into my BLOB it works, but I can't, get it to accept the the name & data(RB) with out throwing an error .... any help or suggestions 
* sqlite3SQLPythonDatabases

Avatar of undefined
Last Comment
Dolamite Jenkins

8/22/2022 - Mon
slightwv (䄆 Netminder)

Not a Pyton or SQLLite person but it appears you need to use convertToBinaryData to get it into the correct format.

There appears to be a complete example here:
https://pynative.com/python-sqlite-blob-insert-and-retrieve-digital-data/
Dolamite Jenkins

ASKER
The (convertToBinaryData) is calling the function convertToBinaryData and passing the filename... I'm doing that ... this code is prettier but convert to binary none the same 
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dolamite Jenkins

ASKER
Thank you ... you put me on the right path and helped me solve and learn along the way
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy