Assigning NativeSQL query to a variable

[ezrakahn]

How can I set the output of an sql query on an openLCA database to a new variable?

For example, the following will print the id and name of a category, but the variable "output" is None. I would like output to be the string id + name.

query = 'select * from tbl_categories'

def fn(r):
    print(r.getString('id') + r.getString('name'))
    return True

output = NativeSql.on(db).query(query, fn)

print output

[msrocka]

The query function just takes the SQL query and a function that is called for each result of the SQL query. It returns nothing. If you want to store the result of a query you can define a variable in the outer scope of the function that is passed into the query function, e.g.:

import org.openlca.core.database.NativeSql as NativeSql

query = 'select * from tbl_categories'
categories = []

def fn(r):
    categories.append((r.getLong('id'), r.getString('name')))
    return True

NativeSql.on(db).query(query, fn)
print categories

Note that you can stop iterating over the results by returning False:

import org.openlca.core.database.NativeSql as NativeSql

query = 'select * from tbl_categories'
category = None

def fn(r):
    global category
    cat_id = r.getLong('id')    
    if cat_id == 42:
        category = r.getString('name')
        return False  # we found what we searched for
    return True  # continue searching

NativeSql.on(db).query(query, fn)
print(category)