Executing an SQL Statement Multiple Times with Different Values

In this example you use the methods sql and prepare of the sdb.sql module. You execute an SQL statement multiple times with different values without the database system having to convert Python values.

Procedure

...

       1.      Create a Python script sample.py with the following contents:

#

# Import Python modules

# -------------------------------------------

import sys

import sdb.sql

#

# Connect to the database instance

# -------------------------------------------

database_user = sys.argv [1]

database_user_password = sys.argv [2]

database_name = sys.argv [3]

session = sdb.sql.connect (database_user, database_user_password, database_name)

#

# Create customer test table

# (to ensure that this table does not already exist,

# the SQL statement for deleting the table is executed

# first)

# ----------------------------------------------

try:

    session.sql ('DROP TABLE hotel.customer')

except sdb.sql.SQLError:

    pass

session.sql ("""CREATE TABLE customer (cno FIXED(4) PRIMARY KEY)""")

#

# Generate object of the class SapDB_Prepared

# ------------------------------------------------

insert = session.prepare ('INSERT INTO customer (cno) VALUES (?)')

for i in xrange (1,11):

    # The execute method executes the prepared

    # SQL statement with all parameters in the list

    insert.execute ([i])

cursor = session.sql ('SELECT cno FROM customer')

for row in cursor: print row

#

# Close connection to the database instance

# -----------------------------------------------

session.release ()

       2.      Call the Python script:

python sample.py MONA RED DEMODB

Result

(1,)

(2,)

(3,)

(4,)

(5,)

(6,)

(7,)

(8,)

(9,)

(10,)