Servlet NewJavaServlet Test

CHAPTER 12 
■
 DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
236 
 
Table 12-2. Connection Attributes (continued) 
Method 
Functionality 
dbversion 
Returns the version of database. 
drivername 
Returns the database driver name. 
driverversion  Returns the database driver version. 
closed 
Returns a Boolean stating whether connection is closed. 
Of course, we can always use the connection to obtain a listing of all methods and attributes using 
the syntax shown in Listing 12-5. 
Listing 12-5. 
>>> conn.__methods__ 
['close', 'commit', 'cursor', 'rollback', 'nativesql'] 
>>> conn.__members__ 
['autocommit', 'dbname', 'dbversion', 'drivername', 'driverversion', 'url', 
'__connection__', '__cursors__', '__statements__', 'closed'] 
■
 Note Connection pools help ensure for more robust operation, by providing for reuse of connections while 
ensuring the connections are in fact valid. Often naive code will hold a connection for a very long time, to avoid the 
overhead of creating a connection, and then go to the trouble of managing reconnecting in the event of a network 
or server failure. It's better to let that be managed by the connection pool infrastructure instead of reinventing it. 
All transactions, if supported, are done within the context of a connection. We will be discussing 
transactions further in the subsection on data modification, but Listing 12-6 is the basic recipe. 
Listing 12-6. Transaction Recipe 
try: 
    # Obtain a connection that is not using auto-commit (default for zxJDBC) 
    conn  = zxJDBC.connect(jdbc_url, username, password, driver) 
    # Perform all work on connection 
    do_something(conn) 
    # After all work is complete, commit 
    conn.commit() 
except: 
www.it-ebooks.info

---

CHAPTER 12 
■ 
DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
237 
 
    # If a failure occurs along the way, rollback all previous work 
    conn.rollback() 
ZxJDBC.lookup 
In a managed container, you would use zxJDBC.lookup instead of zxJDBC.connect. If you have code that 
needs to run both inside and outside containers, we recommend you use a factory to abstract this. Inside 
a container, like an app server, you should use JDNI to allocate the resource. Generally the connection 
will be managed by a connection pool (see Listing 12-7). 
Listing 12-7. 
factory = "com.sun.jndi.fscontext.RefFSContextFactory" 
db = zxJDBC.lookup('jdbc/postgresDS', 
    INITIAL_CONTEXT_FACTORY=factory) 
This example assumes that the datasource defined in the container is named “jdbc/postgresDS,” 
and it uses the Sun FileSystem JNDI reference implementation. This lookup process does not require 
knowing the JDBC URL or the driver factory class. These aspects, as well as possibly the user name and 
password, are configured by the administrator of the container using tools specific to that container. 
Most often by convention you will find that JNDI names typically resemble a jdbc/NAME format. 
Cursors 
Once you have a connection, you probably want to do something with it. Because you can do multiple 
things within a transaction, such as query one table, update another, you need one more resource, 
which is a cursor. A cursor in zxJDBC is a wrapper around the JDBC statement and resultSet objects that 
provides a very Pythonic syntax for working with the database. The result is an easy to use and extremely 
flexible API. Cursors are used to hold data that has been obtained via the database, and they can be used 
in a variety of fashions which we will discuss. There are two types of cursors available for use, static and 
dynamic. A static cursor is the default type, and it basically performs an iteration on an entire resultSet at 
once. The latter dynamic cursor is known as a lazy cursor and it only iterates through the resultSet on an 
as-needed basis. The following listings are examples of creating each type of cursor. 
Listing 12-8. Creating all possible cursor types 
# Assume that necessary imports have been performed 
# and that a connection has been obtained and assigned 
# to a variable 'conn' 
 
cursor = conn.cursor() # static cursor creation 
 
cursor = conn.cursor(True) # dynamic cursor creation with the Boolean argument 
Dynamic cursors tend to perform better due to memory constraints; however, in some cases they 
are not as convenient as working with a static cursor. For example, if you’d like to query the database to 
find a row count it is very easy with a static cursor because all rows are obtained at once. This is not 
possible with a dynamic cursor and one must perform two queries in order to achieve the same result. 
 
 
www.it-ebooks.info

---

CHAPTER 12 
■
 DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
238 
 
Listing 12-9. 
# Using a static cursor to obtain rowcount 
>>> cursor = conn.cursor() 
>>> cursor.execute("select * from country") 
>>> cursor.rowcount 
239 
 
# Using a dynamic cursor to obtain rowcount 
>>> cursor = conn.cursor(1) 
>>> cursor.execute("select * from country") 
>>> cursor.rowcount 
0 
 
# Since rowcount does not work with dynamic, we must 
# perform a separate count query to obtain information 
>>> cursor.execute("select count(*) from country") 
>>> cursor.fetchone() 
(239L,) 
Cursors are used to execute queries, inserts, updates, deletes, and/or issue database commands. 
Like connections, cursors have a number of methods and attributes that can be used to perform actions 
or obtain metadata information. See Tables 12-3 and 12-4. 
Table 12-3. Cursor Methods 
Method 
Functionality 
tables 
Retrieves a list of tables (catalog, schema-pattern, table-pattern, types). 
columns 
Retrieves a list of columns (catalog, schema-pattern, table-name-pattern, column-
name-pattern). 
primarykeys 
Retrieves a list of primary keys (catalog, schema, table). 
foreignkeys 
Retrieves a list of foreign keys (primary-catalog, primary-schema, primary-table, 
foreign-catalog, foreign-schema, foreign-table). 
procedures 
Retrieves a list of procedures (catalog, schema, tables). 
procedurecolumns  Retrieves a list of procedure columns (catalog, schema-pattern, procedure-
pattern, column-pattern). 
statistics 
Obtains statistics on the query (catalog, schema, table, unique, approximation). 
bestrow 
Optimal set of columns that uniquely identify a row. 
www.it-ebooks.info

---