Preparing and executing a single SQL statement in Python

To prepare and execute a single SQL statement, use the ibm_db.exec_immediate function. To avoid the security threat of SQL injection attacks, use the ibm_db.exec_immediate function only to execute SQL statements that are composed of static strings. Interpolation of Python variables representing user input into the SQL statement can expose your application to SQL injection attacks.

Before you begin

Obtain a connection resource by calling one of the connection functions in the ibm_db API. For more information, see Connecting to an IBM database server in Python.

Procedure

To prepare and execute a single SQL statement, call the ibm_db.exec_immediate function, passing the listed arguments:

connection: A valid database connection resource that is returned from the ibm_db.connect or ibm_db.pconnect function.
statement: A string that contains the SQL statement. This string can include an XQuery expression that is called by the XMLQUERY function.
options: Optional: A dictionary that specifies the type of cursor to return for result sets. You can use this parameter to request a scrollable cursor for database servers that support this type of cursor. By default, a forward-only cursor is returned.

If the function call fails (returns False), you can use the ibm_db.stmt_error or ibm_db.stmt_errormsg function to retrieve diagnostic information about the error.

If the function call succeeds, you can use the ibm_db.num_rows function to return the number of rows that the SQL statement returned or affected. If the SQL statement returns a result set, you can begin fetching the rows.

For more information about the ibm_db API, see https://github.com/ibmdb/python-ibmdb/wiki/APIs.

Example

Example 1: Execute a single SQL statement

import ibm_db
conn = ibm_db.connect("database","username","password")
stmt = ibm_db.exec_immediate(conn, "UPDATE employee SET bonus = '1000' WHERE job = 'MANAGER'")
print "Number of affected rows: ", ibm_db.num_rows(stmt)

Example 2: Execute an XQuery expression

import ibm_db
conn = ibm_db.connect("database","username","password")
if conn:
    sql = "SELECT XMLSERIALIZE(XMLQUERY('for $i in $t/address where $i/city = \"Olathe\" return <zip>{$i/zip/text()}</zip>' passing c.xmlcol as \"t\") AS CLOB(32k)) FROM xml_test c WHERE id = 1"
    stmt = ibm_db.exec_immediate(conn, sql)
    result = ibm_db.fetch_both(stmt)
    while( result ):
        print "Result from XMLSerialize and XMLQuery:", result[0]
        result = ibm_db.fetch_both(stmt)

What to do next

If the SQL statement returns one or more result sets, you can begin fetching rows from the statement resource.