# FILENAME :    CallSPInsertRec.py
#               Illustrates using a stored procedure to perform several
#               actions relating to inserting a record into a table. Returns
#               two sets of results plus several integer values relating to
#               the table used and the record inserted.
#               Calls a stored procedure as follows:
#		{CALL pyInsert_Record (?, ?, ?, ?)}
#               Drops and recreates a procedure 'pyInsert_Record'
#	        Loops asking user to input the values for 4 columns (until
#	        blank FirstName entered). Stored Procedure is called and
#               performs the following:-
#                   Inserts new person record into TestTBL1 using user input
#   	            Returns all records in the table, ordered by ID
#   	            Returns all records that match new record firstname
#   	            Returns number of matching records
#   	            Returns number of records in table
#   		    Returns identity of record inserted
#                   Returns error status
#               The results returned are picked off in the same order that
#               they are generated (important)
import pyodbc

# Function to display the contents of a record
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
        print "-"                   # print - for empty column

    print "Last Name  : ",
    if rec[2]!=None:
        print rec[2][0:10]
        print "-"

    print "Address    : ",
    if rec[3]!=None:
        print rec[3][0:10]
        print "-"

    print "City       : ",
    if rec[4]!=None:
        print rec[4][0:10]
        print "-"

# Create Stored Procedure 'pyInsert_Record' Statement
sqlCreateSP="CREATE PROCEDURE pyInsert_Record (\
            @pFirstName nvarchar(256), @pLastName varchar(256),\
            @pAddress varchar(256), @pCity varchar(256)) AS \
            DECLARE @MatchingRecs INT;\
            DECLARE @TotalRecs INT;\
            INSERT INTO TestTBL1 (FirstName, LastName, Address, City) \
            VALUES (@pFirstName, @pLastName, @pAddress, @pCity); \
            SELECT * FROM TestTBL1 ORDER BY PersonID; \
            SELECT * FROM TestTBL1 WHERE FirstName=@pFirstName; \
            SELECT @MatchingRecs=count(*) FROM TestTBL1 WHERE\
            FirstName=@pFirstName; \
            SELECT @TotalRecs=count(*) FROM TestTBL1; \
            SELECT @MatchingRecs;\
            SELECT @TotalRecs;\
            SELECT @@IDENTITY;\
            SELECT @@ERROR;"

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

# Call Stored Procedure Statement
sqlExecSP="{call pyInsert_Record (?,?,?,?)}"

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

# Create cursor associated with connection

print "\nStored Procedure is : pyInsert_Record"

# Drop SP if exists

# Create SP using Create statement

# Loop - prompt for record details, insert and get results returned
while 1:

    # Data for new record
    firstName=raw_input("\nFirst Name : ")
    if firstName=='':
    lastName =raw_input("Last Name  : ")
    address  =raw_input("Address    : ")
    city     =raw_input("City       : ")

    # Call SP and trap Error if raised
    except pyodbc.Error, err:
        print 'Error !!!!! %s' % err

    # The first set of results is the entire table
    print "\nEntire Table :"

    # Fetch all rowset from execute

    # Process each record individually
    for rec in recs:

    # The next set of results expected is all records matching
    # the FirstName field just added
    print "\nMatching Records :"
    if cursor.nextset()==True:         # value passed IN as as parameter 2
        for rec in cursor:

    # Next comes the number of matching records
    if cursor.nextset()==True:
        for rec in cursor:
            print "\nNumber of records matching '", firstName, "' : ", rec[0],

    # Then the total number of records in the table
    if cursor.nextset()==True:
        for rec in cursor:
            print "\nTotal number of records in table : ", rec[0],

    # Then the identity of the record just added
    if cursor.nextset()==True:
        for rec in cursor:
            print "\nIdentity of record just added : ", rec[0],

    # And finally, the SQL status code
    if cursor.nextset()==True:
        for rec in cursor:
            print "\nSQL Status at end of procedure : ", rec[0]

print ("\n\nComplete.")

# Close and delete cursor
del cursor

# Close Connection

See Also

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.