Python Forum

Hello - I have what I would think should be a fairly simple problem, but I just cannot get it to work. The app I'm building is pretty straightforward. User opens the app, enters a value (a problem ID, called PROBLEMID) and clicks a button. The app checks a SQL table for that value and returns back a user name.

I have gotten it to work when the problem ID is FOUND in the database table, but when the id doesn't exist, I just get an error. Can you help set me straight? I can't get TRY to work, or any IF not or IF !=

Thank you in advance for any help.

Here's the entirety of my code (and yes, I know it's lousy and inefficient)

import tkinter as tk #python3.x
from tkinter import messagebox
#import Tkinter as tk #python2.x
from tkinter import *
import pyodbc

#Set Up App Window & defaults
root = tk.Tk()
root.option_add("*Font", "Verdana")
root.configure(background='black')
root.geometry('220x125') # Size WxH
root.title("SERV OWNER FINDER")

#create labels and entry boxes
PROBIDLABEL = Label(root, text="Enter Problem ID ", bg = "Black", fg = "gray")
PROBIDENTRY = Entry(root, bg="light yellow")

PROBIDLABEL.place(x=1,y=3)
PROBIDENTRY.place(x=1,y=30)

def close_window(): 
    root.destroy()

def checktable():
        problemid = "'" + PROBIDENTRY.get() + "'"
        connstring = 'DRIVER={SQL Server};SERVER=OBDEVSQLAGL05;DATABASE=DCollection;UID=xxxx;PWD=yyyy'
        SQLstr="select OWNER from GD_SERV_ADMIN where PROBLEMID = " + problemid
        conn=pyodbc.connect(connstring)
        cursor=conn.cursor()
        cursor.execute(SQLstr)
        row = cursor.fetchone()
        messagebox.showinfo("Owner found", "The ADMIN for this problem ID is:  " + row[0])
        cursor.close()
        PROBIDENTRY.delete(0,END)
        
#Create Buttons
SubmitButton = Button(root, text="Submit", width = 8, command=checktable)
QuitButton = Button(root, text="Quit",width = 8, command=close_window)

#Place Buttons on Window
QuitButton.place(x=5, y=70)
SubmitButton.place(x=120, y=70)

root.mainloop()

Please show example error code (unaltered, and complete).
It tells a lot about the reason, I suspect you need to enclose the code in a try, except clause.
The error traceback should show the specific error.

Quote: but when the id doesn't exist, I just get an error

Check that row[0] has something in it, i.e. a length

row = cursor.fetchone()
msg="That ID is not found"
if len(row):
    msg="Owner found", "The ADMIN for this problem ID is:  " + row[0])
messagebox.showinfo(msg) 

Note that the SQL server you are using may return None or an error message when not found, so you would then print row to see which not found error condition you should test for.

Thank you both for taking the time to have a look and assist!

Larz60+
The full error I'm currently getting back is
Exception in Tkinter callback

Error:
Traceback (most recent call last):
  File "C:\Program Files\Anaconda3\lib\tkinter\__init__.py", line 1549, in __call__
    return self.func(*args)
  File "C:\Users\jromano1\AppData\Local\Programs\Python\Python36-32\Scripts\SERV_CHECK.py", line 32, in checktable
    messagebox.showinfo("Owner found", "The ADMIN for this problem ID is:  " + row[0])
TypeError: 'NoneType' object is not subscriptable

WOOEE
I get this error
Exception in Tkinter callback

Error:
Traceback (most recent call last):
  File "C:\Program Files\Anaconda3\lib\tkinter\__init__.py", line 1549, in __call__
    return self.func(*args)
  File "C:\Users\jromano1\AppData\Local\Programs\Python\Python36-32\Scripts\SERV_CHECK2.py", line 33, in checktable
    if len(row):
TypeError: object of type 'NoneType' has no len()

it should be

row = cursor.fetchone()
if row:
    messagebox.showinfo("Owner found", "The ADMIN for this problem ID is:  " + row[0])

buran
Thank you (for your response and for fixing my tags again).

I made the change you suggested and now when I run it, I don't get any response nor do I get any error messages. It just does this line

PROBIDENTRY.delete(0,END)

---

FYI, I did put that line in the python code tags but it's not showing up that way here now. No idea why

---

I found my error and it's now working as expected. Thank you all SO MUCH for your help!

row = cursor.fetchone()
        if row:
            messagebox.showinfo("Owner found", "The ADMIN for this problem ID is:  " + row[0])
            
        else:
            messagebox.showinfo("Owner Not Found", "This problem ID was not found in the database!")
        cursor.close()
        PROBIDENTRY.delete(0,END)

obviously your SELECT statement does not return anything. And thus cursor.fetchone() returns None. As a result your previous code was raising an exception. Now it just continue.
try

def checktable():
        problemid = PROBIDENTRY.get()
        connstring = 'DRIVER={SQL Server};SERVER=OBDEVSQLAGL05;DATABASE=DCollection;UID=xxxx;PWD=yyyy'
        SQLstr="select OWNER from GD_SERV_ADMIN where PROBLEMID=?"
        conn=pyodbc.connect(connstring)
        cursor=conn.cursor()
        cursor.execute(SQLstr, problemid )
        row = cursor.fetchone()
        if row:
            messagebox.showinfo("Owner found", "The ADMIN for this problem ID is:  " + row[0])
        cursor.close()
        PROBIDENTRY.delete(0,END)

Excellent, thank you!