sqlite3.OperationalError: no such column: id -- fetching image to the screen sqlite3

Viewed 29

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

0 Answers
Related