#**********************************************************************
# FILENAME :    CallSP.py
#
# DESCRIPTION :
#               Simple ODBC (pyodbc) example to SELECT data from a table
#               via a stored procedure
#
#               Illustrates the most basic call, in the form :
#
#               {CALL pyStored_Procedure ()}
#
# ODBC USAGE :
#               Connects to Data Source using Data Source Name
#               Creates cursor on the connection
#               Drops and recreates a procedure 'pySelect_Records'
#               Executes the procedure using cursor.execute()
#               Calls cursor.fetchall() to retrieve a rowset of all rows
#               For each record displays column values
#               Closes and deletes cursor and closed connection
#
import pyodbc

# Stored Procedure Create Statement
sqlCreateSP="CREATE PROCEDURE pySelect_Records AS \
			 SELECT PersonID, FirstName, LastName, Address, City \
			 FROM TestTBL1 ORDER BY PersonID"

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

# Stored Procedure Call Statement
sqlExecSP="{call pySelect_Records ()}"

# Connect to datasource
conn=pyodbc.connect('DSN=DATASOURCE', autocommit=True)

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

print "\nStored Procedure is : pySelect_Records"

# Drop SP if exists
cursor.execute(sqlDropSP)

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

# Call SP and trap Error if raised
try:
    cursor.execute(sqlExecSP)
except pyodbc.Error, err:
    print 'Error !!!!! %s' % err

print "\nResults :"

# Fetch all rowset from execute
recs=cursor.fetchall()

# Process each record individually
for rec in recs:
    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 "-"

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.