Servlet NewJavaServlet Test

CHAPTER 12 
■ 
DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
239 
 
Table 12-3. Cursor Methods (continued) 
Method 
Functionality 
versioncolumns 
Columns that are automatically updated when any value in a row is updated. 
close 
Closes the cursor. 
execute 
Executes code contained within the cursor. 
executemany 
Used to execute prepared statements or sql with a parameter list. 
fetchone 
Fetch the next row of a query result set, returning a single sequence, or None if no 
more data exists. 
fetchall 
Fetch all (remaining) rows of a query result, returning them as a sequence of 
sequences. 
fetchmany 
Fetch the next set of rows of a query result, returning a sequence of sequences. 
callproc 
Executes a stored procedure. 
next 
Moves to the next row in the cursor. 
write 
Execute the sql written to this file-like object. 
Table 12-4. Cursor Attributes 
Attribute 
Functionality 
arraysize 
Number of rows fetchmany() should return without any arguments. 
rowcount 
Returns the number of resulting rows. 
rownumber  Returns the current row number. 
description  Returns information regarding each column in the query. 
datahandler  Returns the specified datahandler. 
warnings 
Returns all warnings on the cursor. 
www.it-ebooks.info

---

CHAPTER 12 
■
 DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
240 
 
Table 12-4. Cursor Attributes (continued) 
Attribute 
Functionality 
lastrowid 
Returns the rowid of the last row fetched. 
updatecount 
Returns the number of updates that the current cursor has performed. 
closed 
Returns a boolean representing whether the cursor has been closed. 
connection 
Returns the connection object that contains the cursor. 
A number of the methods and attributes above cannot be used until a cursor has been executed 
with a query or statement of some kind. Most of the time, the particular method or attribute name will 
provide a good enough description of its functionality. 
Creating and Executing Queries 
As you’ve seen previously, it is quite easy to initiate a query against a given cursor. Simply provide a 
select statement in string format as a parameter to the cursor execute() or executemany() methods and 
then use one of the fetch methods to iterate over the returned results. In the following examples we 
query the world data and display some cursor data via the associated attributes and methods. 
Listing 12-10. 
>>> cursor = conn.cursor() 
>>> cursor.execute("select country, region from country") 
 
# Fetch next record 
>>> cursor.fetchone() 
((AFG,Afghanistan,Asia,"Southern and Central 
Asia",652090,1919,22720000,45.9,5976.00,,Afganistan/Afqanestan,"Islamic Emirate","Mohammad 
Omar",1,AF), u'Southern and Central Asia') 
 
# Calling fetchmany() without any parameters returns next record 
>>> cursor.fetchmany() 
[((NLD,Netherlands,Europe,"Western 
Europe",41526,1581,15864000,78.3,371362.00,360478.00,Nederland,"Constitutional 
Monarchy",Beatrix,5,NL), u'Western Europe')] 
 
# Fetch the next two records 
>>> cursor.fetchmany(2) 
[((ANT,"Netherlands Antilles","North 
America",Caribbean,800,,217000,74.7,1941.00,,"Nederlandse Antillen","Nonmetropolitan 
Territory of The Netherlands",Beatrix,33,AN), u'Caribbean'), ((ALB,Albania,Europe,"Southern 
Europe",28748,1912,3401200,71.6,3205.00,2500.00,Shqip?ria,Republic,"Rexhep Mejdani",34,AL), 
u'Southern Europe')] 
www.it-ebooks.info

---

CHAPTER 12 
■ 
DATABASES AND JYTHON: OBJECT RELATIONAL MAPPING AND USING JDBC 
 
241 
 
 
# Calling fetchall() would retrieve the rest of the records 
>>> cursor.fetchall() 
... 
 
# Using description provides data regarding the query in the cursor 
>>> cursor.description 
[('country', 1111, 2147483647, None, None, None, 2), ('region', 12, 2147483647, None, None, 
None, 0)] 
Creating a cursor using the with_statement syntax is easy, please take a look at the following 
example for use with Jython 2.5.1 and beyond. 
Listing 12-11. 
with conn.cursor() as c: 
    do_some_work(c) 
Like connections, you need to ensure the resource is appropriately closed. So you can just do this to 
follow the shorter examples we will look at: 
Listing 12-12. 
>>> c = conn.cursor() 
>>> # work with cursor 
As you can see, queries are easy to work with using cursors. In the previous example, we used the 
fetchall() method to retrieve all of the results of the query. However, there are other options available for 
cases where all results are not desired including the fetchone() and fetchmany() options. Sometimes it is 
best to iterate over results of a query in order to work with each record separately. Listing 12-13 iterates 
over the countries contained within the country table. 
Listing 12-13. 
>>> from com.ziclix.python.sql import zxJDBC 
>>> conn = 
zxJDBC.connect("jdbc:postgresql:test","postgres","jython25","org.postgresql.Driver") 
>>> cursor = conn.cursor() 
>>> cursor.execute("select name from country") 
>>> while cursor.next(): 
...     print cursor.fetchone() 
... 
(u'Netherlands Antilles',) 
(u'Algeria',) 
(u'Andorra',) 
... 
Often, queries are not hard-coded, and we need the ability to substitute values in the query to select 
the data that our application requires. Developers also need a way to create dynamic SQL statements at 
times. Of course, there are multiple ways to perform these feats. The easiest way to substitute variables 
or create a dynamic query is to simply use string concatenation. After all, the execute() method takes a 
www.it-ebooks.info

---