name 'connection' is not defined

Question

I'm trying to close the ODBC connection and I'm not sure about the best way to implement this.

My program runs but I'm wanting to close the connection properly with connection.close(). Here is my original:

import pypyodbc


def queryfirst():
    return ("SELECT FIRSTNAME, LASTNAME "      
            "FROM dbo.MAIN "
            "WHERE FIRSTNAME = ?")

def sqlfirst():
    firstname = "Josh"
    if True:    
        connection = pypyodbc.connect('Driver={SQL Server};Server=;Database=;Trusted_Connection=yes;')
        cursor = connection.cursor() 
        SQLCommand = queryfirst()
        Values = [firstname]
        cursor.execute(SQLCommand,Values)
        return cursor.fetchmany(2)


def calculate():
    results = sqlfirst()
    if results:
        print (results[0])  # prints the first and last name


calculate()

I've tried this:

import pypyodbc


def queryfirst():
    return ("SELECT FIRSTNAME, LASTNAME "      
            "FROM dbo.V_LICMAIN_IT "
            "WHERE FIRSTNAME = ?")

def sqlfirst(closeit):
    firstname = "Josh"
    if True:    
        connection = pypyodbc.connect('Driver={SQL Server};Server=;Database=;Trusted_Connection=yes;')
        cursor = connection.cursor() 
        SQLCommand = queryfirst()
        Values = [firstname]
        cursor.execute(SQLCommand,Values)
        return cursor.fetchmany(1)
        connection.close() = closeit

def calculate():
    results = sqlfirst()
    if results:
        print (results[0])  # prints the first and last name
        sqlfirst(closeit)

calculate()

The above says:

connection.close() = closeit
SyntaxError: can't assign to function call

And this with no luck:

import pypyodbc


def queryfirst():
    return ("SELECT FIRSTNAME, LASTNAME "      
            "FROM dbo.MAIN "
            "WHERE FIRSTNAME = ?")

def closeconn():
    return connection.close()

def sqlfirst():
    firstname = "Josh"
    if True:    
        connection = pypyodbc.connect('Driver={SQL Server};Server=;Database=;Trusted_Connection=yes;')
        cursor = connection.cursor() 
        SQLCommand = queryfirst()
        Values = [firstname]
        cursor.execute(SQLCommand,Values)
        return cursor.fetchmany(2)
        testname = closeconn()

def calculate():
    results = sqlfirst()
    if results:
        print (results[0])  # prints the first and last name
        closeconn()

calculate()

The above says:

return connection.close()
NameError: name 'connection' is not defined

UPDATE: Below is my full code:

import os
import pypyodbc
import tkinter
from tkinter import ttk
from tkinter import messagebox
from tkinter import BOTH, END, LEFT
import traceback


class Adder(ttk.Frame):
    """The adders gui and functions."""
    def __init__(self, parent, *args, **kwargs):
        ttk.Frame.__init__(self, parent, *args, **kwargs)
        self.root = parent
        self.init_gui()

    def queryfirst(self):
        return ("SELECT LASTNAME, FIRSTNAME, ID "      
                "FROM dbo.TABLENAME "   # table name
                "WHERE FIRSTNAME = ?")

    def connect(self):
        return pypyodbc.connect('Driver={SQL Server};Server=;Database=;Trusted_Connection=yes;')

    def sqlfirst(self):
        firstname = str(self.first_entry.get())
        lastname = str(self.last_entry.get())     
        license = str(self.lic_entry.get())
        if (firstname and not lastname and not license):  # "You entered first name."
            try:
                connection = self.connect()
            except pypyodbc.Error as ex:
                sqlstate = ex.args[0]
                if sqlstate == '28000':
                    self.output0.delete(0, END)
                    self.output0.insert(0,"You do not have access.")
            cursor = connection.cursor() 
            SQLCommand = self.queryfirst()
            Values = [firstname]
            cursor.execute(SQLCommand,Values)
            return cursor.fetchmany(10)
#           connection.close()  # !!!!!! <<< this is what I'm working on


    def calculate2(self):
        results = self.sqlfirst()
        if results:
            self.output2.delete(0, END)
            self.output2.insert(0,results[2])



    def calculate1(self):
        results = self.sqlfirst()
        if results:
            self.output1.delete(0, END)
            self.output1.insert(0,results[1])



    def calculate(self):
        results = self.sqlfirst()
        if results:

                self.output0.delete(0, END)
                self.output0.insert(0,results[0])

                self.calculate1()
                self.calculate2()



    def init_gui(self):
        """Builds GUI."""
        self.root.title('Verify')
        self.root.option_add('*tearOff', 'FALSE')


        # Input Boxes and Button

        self.first_entry = tkinter.Entry(self, width=28) # first input box
        self.first_entry.grid(sticky='', column=1, row=1) 



        self.output0 = tkinter.Entry(self, width=150, bd=0,)
        self.output0.grid(column=0, row=6, columnspan=5, padx=10)
        self.output0.bind("<Key>", lambda e: "break")
        self.output1 = tkinter.Entry(self, width=150, bd=0,)
        self.output1.grid(column=0, row=7, columnspan=5, padx=10)
        self.output1.bind("<Key>", lambda e: "break")
        self.output2 = tkinter.Entry(self, width=150, bd=0,)
        self.output2.grid(column=0, row=8, columnspan=5, padx=10)
        self.output2.bind("<Key>", lambda e: "break")

        self.blank.grid(row=16,)      

if __name__ == '__main__':
    root = tkinter.Tk()
    Adder(root)
    root.resizable(width=False, height=False) # locks window from being resized
    root.mainloop()

Do I get that by putting traceback.print_exc() in the function? I updated the above with errors I am getting. — Prox, May 12 '17 at 15:44
then you need to make `connection` global since you can't access it unless you are in a class — nickanor, May 12 '17 at 16:10
Would you be able to give me any assistance on doing that? I am in a class. I posted my full code above in the UPDATE — Prox, May 12 '17 at 18:08
store the connection at `self.connection`, it will be easier to refer it from any method. — Paulo Scardine, May 12 '17 at 18:11

Paulo Scardine · Answer 1 · 2017-05-12T18:21:19.040

Looks like you got an exception and masked it because sqlstate was not '28000'.

        try:
            connection = self.connect()
        except pypyodbc.Error as ex:
            sqlstate = ex.args[0]
            if sqlstate == '28000':
                self.output0.delete(0, END)
                self.output0.insert(0,"You do not have access.")
            else:
                self.output0.insert(0,"Some other database error ({})".format(
                    ex.message
                ))
        else:
            cursor = connection.cursor() 
            SQLCommand = self.queryfirst()
            Values = [firstname]
            cursor.execute(SQLCommand,Values)
            try:
                return cursor.fetchmany(10)
            finally:
                connection.close()

Also note that any line after a return statement will not be executed unless it is inside a finally block.

name 'connection' is not defined

1 Answers1