system.db.runQuery

Description

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. See also: Working with Datatypes / Datasets.

Syntax

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

Examples

Assuming the following dataset:

If you executed the following code:

table = system.db.runQuery("SELECT * FROM TEST")

then 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 results printed out 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]]