#**********************************************************************
# FILENAME :    CallSPWithParamArray.py
#
# DESCRIPTION :
#               This example emulates using a single parameter array with a stored
#               procedure by emulating the parameter arrays using tuples in
#               python.
#
#               We take a random list of identity values and use them to find records
#		where they match with the PersonID field of the record.
#
#		Stored procedures can only be called though the cursor
#               execute() or executemany() methods at this time since
#               CallProc(), the intended method, has yet to be implemented.
#
# ODBC USAGE :
#
#               Emulates parameter array by creating a tuple array containing
#               a random list of PersonID (identity) values.
#
#               Connects to Data Source using Data Source Name
#               Creates cursor on the connection
#               Drops and recreates a procedure 'pyFind_Record' which takes
#               two parameters, one for each tuple entry.
#               For each tuple,
#                   executes the procedure using cursor.execute()
#                   retrieves the results using cursor.fetchall() for the
#                   record details.
#               Closes and deletes cursor and closed connection

import pyodbc

# The array is a random list of PersonIDs which are mapped to @P1 in the
# stored procedure.
params = [ (26),(34),(15),(17),(44),(21),(56),(33),(78),\
		   (45),(37),(32),(74),(69),(66),(13),(15),(92),\
		   (67),(72),(57),(44),(38),(73),(26),(42),(6) ]

# Stored Procedure Create Statement
sqlCreateSP="CREATE PROCEDURE pyFind_Record (@pPersonID INT) \
			 AS SELECT PersonID, FirstName, LastName, Address, City \
			 FROM TestTBL1 WHERE PersonID=@pPersonID;"

# Stored Procedure Drop Statement
sqlDropSP="IF EXISTS (SELECT * FROM sys.objects \
           WHERE type='P' AND name='pyFind_Record') \
           DROP PROCEDURE pyFind_Record"

# Stored Procedure Call Statement
sqlExecSP   = "{CALL pyFind_Record(?)}";

# Connect to datasource
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)

# Call SP with pairs of param values. We could use cursor.executemany() here
# to take the whole tuple list, but this would only give us the results from
# the last param tuple. i.e. the record with PersonID = 6. This is realistic
# since drivers often emulate parameter arrays by executing a SQL statement
# once for each set of parameter values. This is in effect what we are doing
# here.
for id in params:
    try:
        cursor.execute(sqlExecSP, id)
    except pyodbc.Error, err:
        print 'Error !!!!! %s' % err

    print "\nRequested  : ", id         # Person ID passed as @P1
    recs = cursor.fetchall()            # Rows generated by @P1 if any
    print "Recs Found : ", len (recs)   # Number of records found
    for rec in recs:                    # Display records found for @P1
        print "\nPersonID   : ", rec[0]
        print "First Name : ",          # Comma on end stops new line being output
        if rec[1]!=None:                # None indicates 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 "-"

print ("\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.