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