Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Cursor
A zxJDBC cursor is the object used to actually interact with the data in the database. A zxJDBC cursor is actually a wrapper around the JDBC Statement and ResultSet objects. The handling of the result sets is what differentiates the static and dynamic cursor types available in zxJDBC. A dynamic cursor is lazy. It iterates through the result set only as needed. This saves memory and evenly distributes processing time. A static cursor is not lazy. It iterates through the entire result set immediately, and incurs the memory overhead of doing so. The advantage of a static cursor is that you know the rowcount soon after executing a statement, something you cannot know when using a dynamic cursor.
To get a cursor, call the zxJDBC connection object's cursor() method. An example of connecting to the MySQL database server and retrieving a cursor object appears here:
from com.ziclix.python.sql import zxJDBC
url = "jdbc:mysql://localhost/test"
user = "jyuser"
password = "beans"
driver = "org.gjt.mm.mysql.Driver"
con = zxJDBC.connect(url, user, password, driver,
autoReconnect="true")
cursor = con.cursor() # Static cursor
# Alternatively, you can create a dynamic cursor
cursor = con.cursor(1) # Optional boolean arg for dynamic
A cursor object's execute method executes SQL statements. The following example shows how to execute an SQL statement that selects all the data in a table called random. This article assumes the random table consists of a primary key column called "pkey", a "letter" column of type char that includes random letters from the alphabet (lower case and upper), and a "number" column of type int that represents the absolute index of where the letter appears in the list of lower and upper case letters.
>>> from com.ziclix.python.sql import zxJDBC
>>> url = "jdbc:mysql://localhost/test"
>>> user, password, driver = "jyuser", "beans",
"org.gjt.mm.mysql.Driver"
>>> con = zxJDBC.connection(url, user, password, driver)
>>> cursor = con.cursor()
>>> cursor.execute("SELECT * FROM random")
To iterate through the results of a statement, you must use the cursor's fetchone, fetchmany, and fetchall methods. The fetchone and fetchall methods do exactly as their names imply, fetch one result set row, or fetch all rows. The fetchmany method accepts an optional argument which specifies the number of rows to return. Each time multiple rows are returned, they are returned as a sequence of sequences (list of tuples). You can see the usage of these three methods as the preceding example is continued:
>>> cursor.fetchone() (41, 'O', 1) >>> cursor.fetchmany() [(6, 'f', 2)] >>> cursor.fetchmany(4) [(49, 'W', 4), (35, 'I', 5), (43, 'Q', 6), (37, 'K', 3)] >>> cursor.fetchall() # All remaining in this case [(3, 'c', 7), (17, 'q', 8), (29, 'C', 9), (36, 'J', 10), (43, 'Q', 11), (23, 'w', 12), (49, 'W', 13), (25, 'y', 14), (40, 'N', 15), (50, 'X', 16), (46, 'T', 17), (51, 'Y', 18), (8, 'h', 19), (25, 'y', 20), (7, 'g', 21), (11, 'k', 22), (1, 'a', 23)]
After a query has been executed, you can view the row information for those rows in the result set with the cursor's description attribute. The description attribute is read-only, and contains a sequence for each row in the result set. Each sequence includes the name, type, display size, internal size, precision, scale, and nullable information for a column of the row set. A description of the previous query looks like this:
>>> cursor.description
[('number', -6, 4, None, None, None, 1), ('letter', 1, 1, None, None,
None, 1), ('pkey', 4, 10, None, 10, 0, 0)]
Table 1 shows the complete set of the cursor object's methods and attributes.
Table 1—The Cursor Object's Methods and Attributes
|
Method/Attribute |
Description |
|
description |
Information describing each column that appears in the results of a query. The information is a seven-item tuple containing name, type code, display size, internal size, precision, scale, and nullability. |
|
rowcount |
The number of rows in the results. This only works when the cursor is a static cursor, or after completely traversing the result set with a dynamic cursor. |
|
callproc(procedureName,[parameters]) |
Calls stored procedures and applies only to those databases that implement stored procedures. |
|
close() |
Closes the cursor. |
|
execute(statement) |
Executes a statement. |
|
executemany(statement, parameterList) |
Executes a statement with a parameter list. With this, you can use question marks in the statement for values and include a tuple of values to the parameterList which are replaced in the statement. |
|
fetchone() |
Retrieves one row of the query results. |
|
fetchmany([size]) |
Retrieves arraysize number of rows if no argument is given. If the argument arg is supplied, it returns arg numbers of result rows. |
|
fetchall() |
Retrieves all remaining result rows. |
|
nextset() |
Continues with the next result set. This applies only to those databases that support multiple result sets. |
|
arraysize |
The number of rows fetchmany() should return without arguments. |