system.db.runQuery
Runs a SQL query, usually a SELECT query, against a database, returning the results as a dataset. If no database is specified, or the database is the empty-string "" , then the current project's default database connection will be used. The results are returned as a PyDataSet, which is a wrapper around the standard dataset that is convenient for scripting.
system.db. runQuery( query, database, tx )
-
Parameters
String query - A SQL query, usually a SELECT query, to run.
String database - The name of the database connection to execute against. If omitted or "", the project's default database connection will be used.
String tx - A transaction identifier. If omitted, the query will be executed in its own transaction.
-
Returns
PyDataSet - The results of the query as a PyDataSet.
-
Scope
All
Assuming the following dataset:
ID |
Value |
1 |
3.55 |
2 |
67.2 |
3 |
9.87 |
If you executed the following code:
table
=
system.db.runQuery(
"SELECT * FROM TEST"
)
Table[
2
]
would access the third row (rows are zero-indexed), and both
table[
2
][
0
]
and
table[
2
][
"ID"
]
would access the ID value of the third row. As further example of how to use the results of runQuery, here are seven different ways to print out the table, and their results follow. Note that some of the later methods exercise some more advanced Jython concepts such as list comprehensions and string formatting, but their intent should be obvious. Generally speaking, the more concise Jython code becomes, the more readable it is.
table
=
system.db.runQuery(
"SELECT * FROM Test"
)
print
"Printing TEST Method 1..."
for
row
in
table:
for
col
in
row:
print
col,
print
""
print
""
print
"Printing TEST Method 2..."
for
row
in
table:
print
row[
0
], row[
1
]
print
""
print
"Printing TEST Method 3..."
for
row
in
table:
print
row[
"ID"
], row[
"VALUE"
]
print
""
print
"Printing TEST Method 4..."
for
rowIdx
in
range
(
len
(table)):
print
"Row "
,
str
(rowIdx)
+
": "
, table[rowIdx][
0
], table[rowIdx][
1
]
print
""
print
"Printing TEST Method 5..."
print
[
str
(row[
0
])
+
", "
+
str
(row[
1
])
for
row
in
table]
print
""
print
"Printing TEST Method 6..."
print
[
"%s, %s"
%
(row[
"ID"
],row[
"VALUE"
])
for
row
in
table]
print
""
print
"Printing TEST Method 7..."
print
[[col
for
col
in
row]
for
row
in
table]
print
""
The result would be:
Printing TEST Method 1...
0 3.55
1 67.2
2 9.87
Printing TEST Method 2...
0 3.55
1 67.2
2 9.87
Printing TEST Method 3...
0 3.55
1 67.2
2 9.87
Printing TEST Method 4...
Row 0: 0 3.55
Row 1: 1 67.2
Row 2: 2 9.87
Printing TEST Method 5...
['0, 3.55', '1, 67.2', '2, 9.87']
Printing TEST Method 6...
['0, 3.55', '1, 67.2', '2, 9.87']
Printing TEST Method 7...
[[0, 3.55], [1, 67.2], [2, 9.87]]