I got this error sqlite3.OperationalError: no such column: id, when trying to fetch image from database sqlite3. in this code I write two functions one to insert data to the database other is to fetch data from the database, fetching the the blob file is what I am trying to solve, please help.
import tkinter as tk
from tkinter import filedialog
from PIL import ImageTk
from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
import sqlite3
my_w = tk.Tk()
my_w.geometry("700x600") # Size of the window
my_conn = create_engine("sqlite:///D:\\testing\\my_db\\my_db.db")
try:
my_conn = sqlite3.connect('my_db.db')
c = my_conn.cursor()
c.execute('''
CREATE TABLE IF NOT EXISTS student(
photo blob
)''')
my_conn.commit()
except SQLAlchemyError as e:
#print(e)
error = str(e.__dict__['orig'])
print(error)
else:
print("Student Table created successfully..")
def upload_file():
my_conn = sqlite3.connect('my_db.db')
c = my_conn.cursor()
c.execute('''
CREATE TABLE IF NOT EXISTS student(
photo blob
)''')
file = filedialog.askopenfilename()
fob=open(file,'rb')
blob_data=fob.read() # Binary data is ready
my_data=[(blob_data)] # Data to store
q="INSERT INTO student values(?)" # query with place holders
try:
r_set=my_conn.execute(q,my_data)
except SQLAlchemyError as e:
error=str(e.__dict__['orig'])
print(error)
else:
l1.config(text="Data Added , ID: "+str(r_set.lastrowid))
my_conn.commit()
l1 = tk.Label(my_w,text='Upload File & add Binary data ',width=30,font=20)
l1.grid(row=0,column=1,padx=10,pady=10)
b1 = tk.Button(my_w, text='Upload File',
width=20,command = lambda:upload_file())
b1.grid(row=1,column=1)
img=[]
def show_data():
global img # Image variable to display
my_conn = sqlite3.connect('my_db.db')
# ID of the row to display
q = "SELECT * FROM student WHERE id=?" # query with place holders
#my_data = item[0]
try:
my_cursor = my_conn.execute(q)
r_sett = my_cursor.fetchone()
except SQLAlchemyError as e:
error = str(e.__dict__['orig'])
print(error)
else:
for it in r_sett:
pic = it[0]
"""student = str(r_set[0]) + ',' + r_set[1] + ',' + r_set[2] + ',' + str(r_set[3])
l2.config(text=student) # show student data other than image"""
img = ImageTk.PhotoImage(data=pic) # create image
l3.config(image=img) # display image
l1 = tk.Label(my_w,text='Display Image from SQLite Database',width=30,font=20)
l1.grid(row=0,column=2,padx=10,pady=10)
b1 = tk.Button(my_w, text='Show Data',
width=20,command = lambda:show_data())
b1.grid(row=1,column=2)
l2 = tk.Label(my_w, text='Data here ', font=20)
l2.grid(row=2, column=2)
l3 = tk.Label(my_w, text='Image here ')
l3.grid(row=3, column=2)
my_w.mainloop() # Keep the window open
I tried:
for it in r_sett:
pic = it[0]
"""student = str(r_set[0]) + ',' + r_set[1] + ',' + r_set[2] + ',' + str(r_set[3])
l2.config(text=student) # show student data other than image"""
img = ImageTk.PhotoImage(data=pic) # create image
l3.config(image=img) # display image