#**********************************************************************
# FILENAME :    CallSPEditRecord.py
#
# DESCRIPTION :
#               Illustrates using a stored procedure to perform several
#               actions relating to editing records within a table.
#
#               Calls a stored procedure as follows:
#
#		{CALL pyEdit_Record (?, ? ?, ?, ?, ?)}
#
#               With parameter :
#                   1 -   action (SELECT,UPDATE,INSERT,DELETE or ALL)
#                   2 -   current record ID
#                         Used if action is Select, Update or Delete)
#                   3 - 6 parameters for firstname, lastname, address, coty
#                         Used if action is Insert or Update
#
# ODBC USAGE :
#
#	 	Uses the notion of current Person ID as current record
#	 	(a value of -1 indicates none). Based on current record and
#	 	action, the program loops, executing the stored procedure to
#	 	either Select a specific record, Select all records, Update
#	 	or Delete the current record or Insert a new one.
#
#               Connects to Data Source using Data Source Name
#               Creates cursor on the connection
#               Drops and recreates a procedure 'pyEdit_Record'
#               Prompts user for either a record number or an action.
#               Actions available depend on whether a 'current' record
#               exists. Select One, Update and Delete need a current record ID
#               whereas Selecting all records and Insert don't.
#
import pyodbc

# Current record variables
currPID=-1
currFirstName=""
currLastName=""
currAddress=""
currCity=""

# List of actions
SEL=0           # action SELECT ONE
UPD=1           # action UPDATE
INS=2           # action INSERT
DEL=3           # action DELETE
ALL=4           # action SELECT ALL

#
# FUNCTION: getAction(currPID)
# Based on whether a current record is available, get next action to
# execute via stored procedure
# Returns two values, the action selected and a record ID to use as
# the current record id.
#
def getAction (currPID):

    # Default action select
    action=SEL

    # Get PersonId to look up
    print "\nNext Action: QUIT(0)\nSELECT (Rec No), SELECT ALL (A), INSERT (I),",
    if currPID!=-1:
        print "UPDATE (U), DELETE (D)",

    next=raw_input(" ? : ")

    # Check to see if we have a number or a letter
    try:
        PID=int(next)

        if PID==0:
            quit()
        action=0
    except ValueError:
        PID=0
        next=next.upper()
        if next == "A":
            action=4

        elif next == "I":
            action=2

        elif next == "U":
            PID=currPID
            action=1

        elif next == "D":
            PID=currPID
            action=3
        else:
            exit()

    return (action, PID)

#
# FUNCTION: printRec(rec)
# Function to display the contents of a record retrieved by cursor.fetchone()
# or cursor.fetchall()
#
def printRec (rec):

    print "\nPersonID   : ", rec[0]

    print "First Name : ",          # Comma on end stops new line being output
    if rec[1]!=None:                # None appears for empty column
        print rec[1][0:10]          # Print string from 0 upto 10
    else:
        print "-"                   # Print - for empty column

    print "Last Name  : ",
    if rec[2]!=None:
        print rec[2][0:10]
    else:
        print "-"

    print "Address    : ",
    if rec[3]!=None:
        print rec[3][0:10]
    else:
        print "-"

    print "City       : ",
    if rec[4]!=None:
        print rec[4][0:10]
    else:
        print "-"

#
# Stored Procedure statements
#
# Create Stored Procedure 'pyEdit_Record' statement
#
sqlCreateSP="CREATE PROCEDURE pyEdit_Record (\
			@action int, @RecID int, \
			@pFirstName nvarchar(256), @pLastName nvarchar(256),\
			@pAddress nvarchar(256), @pCity nvarchar(256))\
			AS \
			IF (@action=0) BEGIN \
					SELECT PersonID, FirstName, LastName, Address, City \
					FROM TestTBL1 WHERE PersonID=@RecID; \
				END \
			ELSE \
				IF (@action=1) BEGIN \
					UPDATE TestTBL1 \
					SET FirstName=@pFirstName, LastName=@pLastName, \
					Address=@pAddress, City=@pCity \
					WHERE PersonID=@RecID; \
				END \
			ELSE \
				IF (@action=2) BEGIN \
					INSERT INTO TestTBL1 \
					(FirstName, LastName, Address, City) \
					VALUES (@pFirstName, @pLastName, @pAddress, @pCity);\
					SELECT @@IDENTITY; \
				END \
			ELSE \
				IF (@action=3) BEGIN \
					DELETE FROM TestTBL1 WHERE PersonID=@RecID; \
				END \
			ELSE \
				IF (@action=4) BEGIN \
					SELECT PersonID, FirstName, LastName, Address, City \
					FROM TestTBL1 ORDER BY PersonID; \
				END"
#
# Drop Stored Procedure statement
#
sqlDropSP="IF EXISTS (SELECT * FROM sys.objects \
           WHERE type='P' AND name='pyEdit_Record') \
           DROP PROCEDURE pyEdit_Record"
#
# Call Stored Procedure statement
# Parameters are action, recid, firstname, lastname, address, city
#
#	 6 Parameters -- 1 - Action
#	                 2 - Record ID
#	                 3-6 READ - used in UPD and INS
#
#
sqlExecSP="{call pyEdit_Record (?,?,?,?,?,?)}"

# Connect to data source
conn=pyodbc.connect('DSN=DATASOURCE', autocommit=True) # either autocommit here

# Create cursor associated with connection
cursor=conn.cursor()

print "\nStored Procedure is : pyEdit_Record"

# Drop SP if exists
cursor.execute(sqlDropSP)

# Create SP using Create statement
cursor.execute(sqlCreateSP)

# Loop - prompt for next action. getAction() will call quit() if either
# blank or 0 entered
while currPID != 0:

    #
    # Returns action selected in next[0], and record ID in next[1]
    #
    next=getAction (currPID)
    action=next[0]
    currPID=next[1]

    # If Select not chosen, it is either Update, Insert or Delete
    # selected
    if action!=SEL and action!=ALL:
        ok='Y'
        if action==UPD:
            # Updating current record
            print "\nPersonID  : ", currPID
            print "FirstName : ", currFirstName,
            currFirstName=raw_input("\nFirst Name : ")
            print "LastName  : ", currLastName,
            currLastName=raw_input("\nLast Name : ")
            print "Address   : ", currAddress,
            currAddress=raw_input("\nAddress : ")
            print "City      : ", currCity,
            currCity=raw_input("\nCity : ")

        elif action==INS:
            # Insert new record
            currFirstName=raw_input("\nFirst Name : ")
            currLastName=raw_input("Last Name  : ")
            currAddress=raw_input("Address    : ")
            currCity=raw_input("City       : ")

        elif action==DEL:
            # Delete current record
            print "\nDeleting record : ",currPID," confirm Y/N : ",
            ok=raw_input()
            ok=ok.upper()
            if ok!='Y':
                ok='N'

        # If OK to continue
        if ok=='Y':

            # Execute Stored Procedure for Update, Insert or Delete
            # and trap Error if raised
            try:
                 cursor.execute(sqlExecSP,action,currPID,currFirstName,\
                                currLastName,currAddress,currCity)
            except pyodbc.Error, err:
                print 'Execute DML Error %s' % err

            # All OK. If Insert set new records as current record
            if action==INS:
                rec=cursor.fetchone()
                currPID=rec[0]
                print "\nNew Record is : ", currPID

            # If Delete current record is no longer available
            elif action==DEL:
                currPID=-1

            # If we have a current record, reselect and display
            if action==INS or action==UPD:
                action=SEL
                try:
                    cursor.execute(sqlExecSP,action,currPID,currFirstName,currLastName,currAddress,currCity)
                except pyodbc.Error, err:
                    print 'Execute ReSelect Error %s' % err

                recs=cursor.fetchall()
                for rec in recs:
                    printRec(rec)

    else:
        #
        # Either select one or select all
        #
        # Clear variables
        currFirstName=""
        currLastName=""
        currAddress=""
        currCity=""

        # Action select one (using current record) or select all
        try:
            cursor.execute(sqlExecSP,action,currPID,currFirstName,currLastName,currAddress,currCity)
        except pyodbc.Error, err:
            print 'Execute SELECT error %s' % err

        # Display results - one record if select one, many for select all
        recs=cursor.fetchall()
        for rec in recs:
            printRec(rec)

        if len(recs)==0:
            print ("\nNo matching records found")
            currPID=-1
        elif action==SEL:
            # If selected one record, it becomes the current record
            currFirstName=rec[1]
            currLastName=rec[2]
            currAddress=rec[3]
            currCity=rec[4]

        if action==ALL:
            # If selected all, we have no current record
            currPID=-1;

print ("\n\nComplete.")

# Close and delete cursor
cursor.close()
del cursor

# Close connection
conn.close()

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.