#**********************************************************************
# FILENAME : CallSPWithParam.py
#
# DESCRIPTION :
# Simple ODBC example to SELECT data from a table via a
# stored procedure which takes one input parameter.
#
# Illustrates a basic call with one parameter, in the form :
#
# {CALL Find_Record (?)}
#
# ODBC USAGE :
# Drops and recreates a procedure 'pyFind_Record'
#
#
# Connects to Data Source using Data Source Name
# Creates cursor on the connection
# Drops and recreates a procedure 'pyFind_Record'
# Loops asking user to input a PersonID (until zero or
# invalid number entered).
# Executes the procedure using cursor.execute()
# Calls cursor.fetchall() to retrieve any row found
# For each row, displays column values
# Closes and deletes cursor and closed connection
#
import pyodbc
# Function to display the contents of a row
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 "-"
# Create Stored Procedure 'pyInsert_Record' Statement
sqlCreateSP="CREATE PROCEDURE pyFind_Record (@pPersonID INT) \
AS SELECT PersonID, FirstName, LastName, Address, City \
FROM TestTBL1 WHERE PersonID=@pPersonID"
# Drop Stored Procedure Statement
sqlDropSP="IF EXISTS (SELECT * FROM sys.objects \
WHERE type='P' AND name='pyFind_Record') \
DROP PROCEDURE pyFind_Record"
# Call Stored Procedure Statement
sqlExecSP="{call pyFind_Record (?)}"
# Connect to data source
conn=pyodbc.connect('DSN=DATASOURCE', autocommit=True)
# Create cursor associated with connection
cursor=conn.cursor()
print "\nStored Procedure is : pyFind_Record"
# Drop SP if exists
cursor.execute(sqlDropSP)
# Create SP using Create statement
cursor.execute(sqlCreateSP)
# Loop - prompt for PersonID
while 1:
# Get PersonId to look up
userInput=raw_input("\nPerson ID : ")
# Check positive integer entered, quit if
# negative or not a number
try:
id=int(userInput)
except ValueError:
id=0
if userInput != "":
print "\nSorry, NAN"
if id<=0:
quit()
# Call SP and trap execute error, if raised
try:
cursor.execute(sqlExecSP,id)
except pyodbc.Error, err:
print 'Error !!!!! %s' % err
# Fetch row generated by execute
recs=cursor.fetchall()
if len(recs)!=0:
print "\nRecord Found :"
# Only one record per PersonID is possible as PersonID is
# an identifier field in the table. This loop would process
# more however, if there were any.
for rec in recs:
printRec(rec)
print ("\n\nComplete.")
# Close and delete cursor
cursor.close()
del cursor
# Close Connection
conn.close()
Further information
