#**********************************************************************
# 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()
Further information
