Using Parameter Markers

Our next example is slightly more complicated. It uses what are known as parameter markers to allow dynamic substitution of Python variable information into an SQL statement. This means that the values for parameter markers come from outside the SQL statement. In order to make this work, we have to use some different ibm_db API calls to prepare the SQL statement, substitute the variables, and then execute the statement.

Remember, the code before this section and after it is the same as in Listing 8-1 with the exception of two Python statements, so be careful in recreating this code if you are not using the packaged code from Apress.

The SQL statement is pretty normal except for the last character in the statement. The question mark (?) identifies a parameter marker. This is where we will substitute a Python variable later in the code. An SQL statement can have as many parameter markers as you need and can contain data or even SQL keywords. In our case, it will contain the specific department number we are looking for.

The next line is a call to ibm_db.prepare(). This will parse the SQL statement and make note of any parameter markers. This must be done prior to substituting the data into the SQL statement. We then check the return code from ibm_db.prepare() to make sure it succeeded before we proceed.

The next statement sets the Python variable we will substitute into the SQL statement. Where this value comes from is entirely up to you. It could be an input parameter to the Python program, it could come from a file, or you could even prompt the user for the value. Just make sure you do the proper error checking to the value prior to using it.

The next statement calls ibm_db.bind_param() to bind the Python variable to the SQL statement. The first parameter to the function identifies the prepared statement output from the previous call to ibm_db.prepare(). The second parameter is the Python variable to be substituted (or bound) in the SQL statement. In this case, it is the department number to be used. We need this in case you have coded more than one parameter marker in the SQL statement. You will need a separate call to ibm_db.bind_param() for each parameter marker. The third parameter specifies whether the marker is an input, output, or input/output variable. The fourth parameter specifies the SQL type of variable that is being passed.

The next set of statements surround our call to ibm_db.execute() for error checking purposes. This function actually executes the prepared SQL statement. The code that follows checks to make sure that the execution succeeded.

After executing the SQL statement, the code after is our calls to the three functions and then printing the results of the SQL statement. This code is unchanged from our previous example.

It is highly recommended that you read the documentation for the ibm_db.prepare() function . There are a large number of parameter types to identify the SQL data types that you should become familiar with.

SQL statements with parameter markers are probably the most used statement types in programs. They are highly flexible and require only a little more code to accommodate in your Python code.

Our SQL statement only specified the retrieval of two columns, and there is only one department with the designation B01. The report is small because there is only one department with the number specified, but it gets to the point about how parameter markers can be very useful.

More on Parameter Markers

The next example uses multiple parameter markers to create a selection of values that are used to query the project table. Basically, we want to list all the project names that have specified department numbers.

Remember, the code before this section and after it is the same as in Listing 8-1 with the exception of two Python statements, so be careful in recreating this code if you are not using the packaged code from Apress.

The select statement for this query contains two parameter values. The first marker will be labeled as 1; the second is labeled as 2. Parameter markers are always labeled from left to right in the SQL statement.

Next, we prepare the SQL statement with a call to ibm_db.prepare() just as all SQL statements with parameter markers need to be. If the call returns False, then we produce an error message and exit the Python program.

The assignments to Python variables that will be substituted into the parameter markers are next in the listing. Again, these can come from anywhere. We assign with them values here just to make what is happening as clear as possible.

Now we call ibm_db.bind_param() twice, once for each parameter marker. Once the value is bound to the SQL prepared statement, it remains bound until another call to ibm_cb.bind_param() is made.

Finally, we call ibm_db.execute() to obtain the query result.

The report shows that we have three projects that have the specified department numbers we used as parameter markers, two projects for department 'D01' and one for department ‘B01’. This is another good example of how parameter markers can be very useful in your programs. You can store the needed data in a file or feed it as parameters to the Python program and change the report without any modifications to this Python script.

Producing Multiple Reports with Parameter Markers

The Listing 8-7 shows how to create multiple reports with a single SQL statement with parameter markers. As you will see, this is actually easier than it sounds.

At first glance, this listing looks a little different from our other examples. The main reason is that we have moved the report printing code into the loop. We do this because we need a report printed for each invocation of the ibm_db.execute() function .

Our SQL statement is slightly more complicated because we have added an ORDER BY clause at the end so that the output is ordered. It still has two parameter markers that specify a range of salaries.

Next, we prepare the SQL statement and test the outcome for errors.

The next statement sets up a Python array of lists that specify the range of salaries we want. There are two ranges specified, and each range will be used in separate invocations of the ibm_db.execute() function .

The rest of the code is a loop that takes a list of salary values to query the employee table. The code works just like the previous examples except that the report printing code has been moved inside the loop so that both queries are able to print their report.

One of the things to note in this example is that we did not have to prepare the SQL statement multiple times. This is by design so that the prepared SQL statement can be used multiple times just as our example does. Rebinding new values to the parameter markers and then executing the statement are all that needs to be done to produce possibly different reports.

We have successfully produced two reports on two different salary ranges and ordered the records by the salary field. The only change we made to the report printing code was to add a newline character at the end of the report so the two reports have some spacing between them.

Using Parameter Markers Without Binding Variables

This example is best used when you have a lot of data to load or update into a table. The ibm_db.execute() function has an additional optional parameter that can be used to pass parameter marker values instead of calling the ibm_db.bind_param() function for each parameter marker.

This use of ibm_db.execute() works best when the rows to be inserted/updated come from a file or another external resource. In our example, we use a file that contains rows to be inserted into the employee table. The file is constructed as a comma-separated values (CSV) file with each row of data representing a new row to be added to the employee table.

The first statements are the standard Python import statements needed for our program.

The next section of code is our standard setup for accessing a Db2 database, prompting the user for a userid and password, and connecting to the Db2 database.

The next section of code is different from our previous examples. We set up an SQL INSERT statement with parameter markers for each column of data we will insert. In this case, that happens to be all the columns in the employee table. Next, we prepare the statement for eventual execution.

The next block of code is where all the work happens. We open the file containing the information to be inserted into the table first. Then we read it in a line at a time. Next, since the file is a CSV construct, we split the line at each comma. The split pieces have data we do not need in each fragment, so we eliminate the leading and trailing spaces and the single quotes. Finally, we change the Python list of column values into a Python tuple.

Now we can call ibm_db.execute() with our tuple of column values as the second parameter in the function call. We then check to make sure that the insert works and then read in the next line.

The last thing we do is to delete the lines we added to the employee table. This will keep the database in its original state.

Last, we close the database connection.

What this example shows is that you can eliminate 14 calls to the ibm_db.bind_param() function with a little work inside a loop. It also eliminates the use of 14 different variables to hold the information and replaces them with a simple Python list and a single tuple.

We should add another note here to possibly clear up an item. Tuples in Python are immutable. It means that once they are created, they cannot be modified or extended with additional members. That is why we had to convert the Python list, once it was ready, to a tuple in one call.

Joining Tables

Db2 supports the joining of tables in queries. There are two types of joins – inner and outer joins. Inner joins have been supported since the inception of Db2, but outer joins are a relatively new concept. We will discuss both types in this section.

Inserts, Updates, and Deletes

In this section, we will look at SQL insert, update, and delete statements and how they relate to Python and parameter markers. We actually saw delete statements in the previous section’s example, but here we will discuss all these statements in more detail.

The example listing starts out the same as all of our examples, with one exception. In order to update the salary, which internally is a decimal field, we need to import the decimal module. This way we can keep the adjustment consistent with the SQL field.

The first task is to insert a new record into the employee table. We use parameter fields in an INSERT statement. That is what all the question marks are in the SQL statement. We then call ibm_db.prepare() to prepare the statement.

Next, we create the tuple for the data to be inserted. Then, we call ibm_db.execute() to insert the new row into the database.

The next task is to update the record we just inserted. We create the SQL UPDATE statement with a single parameter marker for the new salary and then prepare the statement. The next statement converts the current salary to a decimal field as well as increases it by 10%. The result of this is a new field that is also a decimal field. We then bind the new salary to the UPDATE SQL statement and execute the statement.

Just to make sure the update succeeded and to prove to ourselves that it worked, we fetch the data from the database with a SELECT statement and display the results. The results display the employee number, the old salary, and the new salary.

Last, we delete the new row to get the database back to its original starting state.

This example is a little convoluted, but it is an example of all the SQL data manipulation statements. This example can also be used as a starting point for many of the types of activities you will encounter almost every day.

It is important to note that the calculation of the 10% increase is exactly correct and does not suffer from the inaccuracies from a floating-point calculation. By using the decimal package to perform the calculation, we have ensured the correct increase was applied.

Some Other ibm_db APIs

In this section, we will look at some other ibm_db package APIs that you may have occasion to use. These are not data manipulation statements, but instead can provide information about various aspects of the database and the processing environment of your Python program.

It should be noted that this does not cover all the remaining APIs in the ibm_db package. The remaining APIs are rarely used and are sufficiently documented so they can be easily incorporated in your programs.

As always, the program has the same starting code as our other examples. The first test is the ibm_db.active() API. This API determines if the connection passed as a parameter is still active. While not usually important in most programs, it is used in programs that use the ibm_db.pconnect() API.

The next API used is the ibm_db.autocommit(). This API can both get and set the AUTOCOMMIT flag for the specified connection.

The next API is the ibm_db.client_info(). This API returns information about the client side of the connection. It lists the code pages used, the driver names and versions, and the SQL standard conformance.

Next, the ibm_db.column_priviliges() queries tables about the privileges assigned to specific columns. These privileges may or may not exist. The tables in the sample database usually do not have special privileges assigned to them, so the API does not return any data from them.

The ibm_db.columns() API is used to query the metadata that has been assigned to a column in a table. The sample database has no metadata assigned to columns that we have been able to determine.

The last API we test is the ibm_db.commit() API. This API forces a COMMIT to the database at the point it is called. This should work whether the AUTOCOMMIT flag is on or not.

This output from is pretty simple. Mostly, just one line results from the API being tested. The client info API has more data, all of it of interest to the programmer who requires the information.

Creating Database Objects

The ibm_db library can also help you create database objects such as tables, indexes, and tablespaces. It can also remove existing database objects when necessary. These actions are done through the CREATE and DROP SQL statements.

In addition, this section will cover two more ibm_db APIs, the stmt_error and the stmt_errormsg. These APIs are used to report error conditions and explanations. They can be used after the prepare(), exec_immediate(), and callproc() APIs and contain information that will help you to diagnose the problem with your SQL statement.

There are a few things to note about this example. First, the table is meaningless except as an example. It is not tied to any other table in the sample database. It is just an example of some of the column data types available to the user by Db2.

Second, the try/except block adds some nice exception processing. It looks for a non-zero return code from the exec_immidiate() API, and if not found it executes the except block of code.

Third, the except block has a call to the stmt_errormsg() API, which will print out the error message associated with the error in the SQL statement.

Fourth, there is no formatting code in this example because it does not return a result table, just a simple return code.

The C01 column is an example of an incrementing column. Each time a new row is inserted or altered, the database will generate a value to be placed in this column. This column is also the primary key for the table as defined by the last clause in the SQL statement.

The C02 is a character string that always has 50 characters associated with it. When you assign a character string to this column that is shorter than 50 characters, the system will pad the string on the right with blanks until it is 50 characters long. If you try to add a string longer than 50 characters, it will be truncated to 50 characters.

The C03 column is a variable character string. This type of string is not padded on the right with blanks. Instead, the actual length of the string is recorded. But if the string is longer than 70 characters, it will be truncated to 70 characters.

The C04 column is a fixed decimal number with a length of 15 numeric values with 2 decimal places.

The C05 column is a floating-point field that can hold a total of 21 numeric characters.

The C06 column is a variable graphic field with a maximum of 1,000,000 bytes.

The C07 column is a variable graphic field with a maximum of 200 bytes.

The C08 column is a binary LOB with a length of 1,000,000 bytes.

The C09 column is a field that contains a data value.

The C10 column is a field that contains a time value.

The C11 column is a field that contains a timestamp value. This field contains both a date and a time value concatenated together to form an instance of time and date.

The C12 column is an XML field. This field actually is a pointer to the file system that contains the XML data.

The C13 column is a Boolean field for simple yes/no values.

This program is very similar to the previous one except that it uses the DROP SQL statement to remove the table we created at the beginning of this section.

Obtaining Attributes of an Existing Table

There are a number of attributes available for a column. Some of these will not be applicable depending on the data type defined on the column or through not being defined. The main attribute you will be interested in is the DATA TYPE or the SQL DATA TYPE, which determines the kind of data stored in the column. There are specific attributes that only apply to certain types of data, that is, the SCALE attribute for instance only applies to the DECIMAL data type.

Each column along with all attributes is shown in the complete output file. Listing 8-20 is just a small excerpt of the complete listing.

The Listing 8-14 Python program is very useful in a number of circumstances such as discovering if NULLs are allowed in a column, if there is a DEFAULT VALUE specified for a column, the SIZE attribute that can provide a clue as to the maximum size of a column, and many other attributes.

Obtaining Attributes of a Result Set

Unlike obtaining the attributes of an existing Db2 table, a result set is a temporary table that holds the result of a query. The reason we have this API is that a result set may be a joining of two or more tables and some columns may be modified in the join in such a way that their attributes are modified by the join process. Thus, it may be hard to determine the actual attributes on a joined column except thru trial and error.

This example is somewhat similar to the Listing 8-19 program except that there are fewer attributes available. This is mostly due to the nature of a result set, which is mostly for display purposes in a Python program.

The output here is similar to Listing 8-14. The addition of the DISPLAY SIZE attribute is extremely valuable as this is the number of characters needed to properly display the column data.

The ibm_db_dbi Module

The ibm_db_dbi interface has been discussed in the previous section, but it is worth mentioning here. This interface is included when you install the ibm_db module. It is based on the PEP 248 specification. PEP 249 describes a generic interface that should support almost any native database query interface. The ibm_db_dbi interface fully supports this specification.

The previous section has an example of how to use the ibm_db_dbi interface. This interface calls APIs in the ibm_db interface to gain access to a Db2 database. But it is important to note that if your database changes in the future, the only change needed by your Python program is to modify the import statement so that it points to the interface file used by the new database.

By using a common generic database interface, your program becomes more portable and also much easier to maintain. The disadvantage to using a common database interface is that some of the more advanced features of a native database interface are lost to the programmer. So weigh these choices carefully when choosing your program’s interface to a database.

The Django Database Interface

The Django system is a very powerful mechanism for building web-based pages. It has many useful features and is used by a large percentage of the web programmers around the world. Django also has a generic database interface that a database supplier can use to produce a translation to the API the database supports. This is very similar to the way the PEP 248 interface works. Db2 supplies the ibm_db_django module that performs the translations necessary to access Db2 from Django.

The generic Django database API is fully supported by the ibm_db_django module, so no special coding is needed in your Python application. However, you may need a Db2 administrator to establish the database on the Db2 server if you do not have permission to do so.

Django by default executes without transactions, that is, in auto-commit mode. This default is generally not what you want in web applications. You should remember to turn on transaction support in Django.

To learn more about Django, you should visit the www.djangoproject.com/ website.

The SQLAlchemy Adapter

SQLAlchemy is an object-relational adapter. It transforms object information in a Python program and maps it to a relational database to allow an object to be saved from one execution of the program to the next. The transform is performed via a set of well-known patterns so that reliability of the stored object is persistent.

The SQLAlchemy toolkit enjoys wide use in the Python universe because it uses well-established rules in the transform process. These tools are well understood and have a long history of use in other object-oriented languages as well.

Once the CLI configuration file has the proper settings, you are ready to start using the SQLAlchemy module in your program to create persistent copies of Python program objects to be stored in Db2.

To learn more about SQLAlchemy, you should visit the www.sqlalchemy.org/ website.

The Alembic Adapter

The Alembic project is a subproject of the SQLAlchemy project. It is a migrations tool that allows the objects stored in an SQLAlchemy relational database to be migrated to another relational database. Whatever the reason may for using a different relational database than what is currently being used by you project, Alembic will allow you to migrate all your data to a new relational database, that is, migrate an SQLite SQLAlchemy database to Db2.

While this tool is not used on a daily basis in most environments, it is available when you need it.

This will install the latest version of the ibm_db_alembic and ibm_db modules on your machine.

To learn more about Alembic, you should visit the https://alembic.sqlalchemy.org/ website.

The Future

The ibm_db module and its associated subprojects have made a really good start in allowing Python access to the Db2 environment. There are a number of possibilities for increasing this coverage, but at the time this book was written, there is one thing holding the new projects back – lack of developers. This is the same problem that prevents many open source projects from becoming all they could be. However, the ibm_db project has a slight advantage in that there is a small dedicated number of developers from IBM that are trying to move the project forward. But they need your help and support. So, if you have the time and inclination, please volunteer for the project.