system.db.runQuery
This function is used in Python Scripting.
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.
Client Permission Restrictions
Permission Type: Legacy Database Access
Client access to this scripting function is blocked to users that do not meet the role/zone requirements for the above permission type. This function is unaffected when run in the Gateway scope.
Syntax
system.db.runQuery(query, database, tx)
Parameters
Type | Parameter | Description |
---|---|---|
String | query | A SQL query, usually a SELECT query, to run. |
String | database | The name of the database connection to execute against. |
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
Gateway
Syntax
system.db.runQuery(query, database, tx)
Parameters
Type | Parameter | Description |
---|---|---|
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
Vision Client
Code Examples
Assuming the following dataset:
ID | Value |
---|---|
3 | 9.87 |
2 | 67.2 |
1 | 3.55 |
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]]