1.1.5.3. gnr.sql.gnrsqldata – Queries and Selections

Last page update: November 23, 2011

Main classes:

• SqlQuery
• SqlSelection

Other classes:

• SqlCompiledQuery
• SqlDataResolver
• SqlQueryCompiler
• SqlRecord
• SqlRecordBag
• SqlRelatedRecordResolver
• SqlRelatedSelectionResolver

Complete reference:

• gnr.sql.gnrsqldata - The complete reference list

1.1.5.3.1. SqlSelection

analyze()	iter_dictlist()	out_generator()	output()
apply()	iter_pkeylist()	out_grid()	remove()
buildAsBag()	iter_records()	out_json()	setKey()
buildAsGrid()	newRow()	out_list()	sort()
colHeaders()	out_bag()	out_listItems()	toTextGen()
extend()	out_baglist()	out_pkeylist()	totalize()
filter()	out_count()	out_recordlist()	totalizer()
freeze()	out_data()	out_records()	totalizerSort()
freezeUpdate()	out_dictlist()	out_selection()	totals()
getByKey()	out_distinct()	out_tabtext()
insert()	out_distinctColumns()	out_xls()
iter_data()	out_fullgrid()	out_xmlgrid()

1.1.5.3.2. SqlQuery

compileQuery()	iterfetch()
count()	selection()
cursor()	servercursor()
fetch()	serverfetch()
fetchAsBag()	setJoinCondition()
fetchAsDict()
fetchGrouped()

1.1.5.3.3. SqlCompiledQuery

• get_sqltext()

1.1.5.3.4. SqlDataResolver

• init()
• onCreate()
• resolverSerialize()

1.1.5.3.5. SqlQueryCompiler

compiledQuery()	getFieldAlias()
compiledRecordQuery()	getJoinCondition()
expandMultipleColumns()	init()
expandPeriod()	recordFields()
getAlias()	updateFieldDict()

1.1.5.3.6. SqlRecord

compileQuery()	out_newrecord()
out_bag()	out_record()
out_dict()	output()
out_json()	setJoinCondition()

1.1.5.3.7. SqlRecordBag

• save()

1.1.5.3.8. SqlRelatedRecordResolver

• load()
• resolverSerialize()

1.1.5.3.9. SqlRelatedSelectionResolver

• load()
• resolverSerialize()

1.1.5.3.10. gnr.sql.gnrsqldata - The complete reference list

class gnr.sql.gnrsqldata.SqlCompiledQuery(maintable, relationDict=None)

SqlCompiledQuery is a private class used by the SqlQueryCompiler class. It is used to store all parameters needed to compile a query string.

get_sqltext(db)

Compile the sql query string based on current query parameters and the specific db adapter for the current db in use.

Parameters:db – am instance of the GnrSqlDb class

class gnr.sql.gnrsqldata.SqlDataResolver(*args, **kwargs)

TODO

init()

TODO

onCreate()

TODO

resolverSerialize()

TODO

class gnr.sql.gnrsqldata.SqlQuery(dbtable, columns=None, where=None, order_by=None, distinct=None, limit=None, offset=None, group_by=None, having=None, for_update=False, relationDict=None, sqlparams=None, bagFields=False, joinConditions=None, sqlContextName=None, excludeLogicalDeleted=True, excludeDraft=True, addPkeyColumn=True, locale=None, **kwargs)

The SqlQuery class represents the way in which data can be extracted from a db. You can get data with these SqlQuery methods:

• the count() method
• the cursor() method
• the fetch() method
• the selection() method (return a SqlSelection class)
• the servercursor() method

The __init__ method passes:

Parameters:

• dbtable – the table on which the query will be focused on
• columns – it represents the table columns to be returned by the “SELECT” clause in the traditional sql query. For more information, check the columns section
• where – the sql “WHERE” clause. For more information check the where section.
• order_by – corresponding to the sql “ORDER BY” operator. For more information check the order_by section
• distinct – boolean, True for getting a “SELECT DISTINCT”
• limit – number of result’s rows. Corresponding to the sql “LIMIT” operator. For more information, check the limit section
• offset – the same of the sql “OFFSET”
• group_by – the sql “GROUP BY” clause. For more information check the group_by section
• having – the sql “HAVING” clause. For more information check the having
• for_update – boolean. TODO
• relationDict – a dict to assign a symbolic name to a relation. For more information check the relationDict documentation section
• sqlparams – a dictionary which associates sqlparams to their values
• bagFields – boolean. If True include fields of type Bag (X) when columns is * or contains *@relname.filter
• joinConditions – special conditions for joining related tables. See the setJoinCondition() method
• sqlContextName –

  the name of the sqlContext to be reported for subsequent related selection.

  (see the

  setJoinCondition() method)

• excludeLogicalDeleted – boolean. If True, exclude from the query all the records that are “logical deleted”
• addPkeyColumn – boolean. If True, add a column with the pkey attribute
• locale – the current locale (e.g: en, en_us, it)

compileQuery(count=False)

Return the compiledQuery() method.

Parameters:count – boolean. If True, optimize the sql query to get the number of resulting rows (like count(*))

count()

Return rowcount. It does not save a selection

cursor()

Get a cursor of the current selection.

fetch()

Get a cursor of the current selection and fetch it

fetchAsBag(key=None)

Return the fetch() as a Bag of the given key

Parameters:key – the key you give (if None, it takes the pkey).

fetchAsDict(key=None, ordered=False)

Return the fetch() as a dict that has as a key the parameter key you gave (or the pkey if you don’t specify any key) and as value the record you get from the query.

Parameters:

• key – the key you give (if None, it takes the pkey).
• ordered – boolean. if True, return the fetch using a GnrDict, otherwise (False) return the fetch using a normal dict.

fetchGrouped(key=None, asBag=False)

Return the fetch() as a dict of the given key

Parameters:

• key – the key you give (if None, it takes the pkey).
• asBag – boolean. If True, return the result as a Bag. If False, return the result as a dict

iterfetch(arraysize=30)

TODO

Parameters:arraysize – TODO

selection(pyWhere=None, key=None, sortedBy=None, _aggregateRows=False)

Execute the query and return a SqlSelection

Parameters:

• pyWhere – a callback that can be used to reduce the selection during the fetch
• key – TODO
• sortedBy – TODO
• _aggregateRows – boolean. TODO

servercursor()

Get a cursor on dbserver

serverfetch(arraysize=30)

Get fetch of the servercursor() method.

Parameters:arraysize – TODO

setJoinCondition(target_fld, from_fld, condition, one_one=False, **kwargs)

TODO

Parameters:

• target_fld – TODO
• from_fld – TODO
• condition – set a condition for the join
• one_one – boolean. TODO

class gnr.sql.gnrsqldata.SqlQueryCompiler(tblobj, joinConditions=None, sqlContextName=None, sqlparams=None, locale=None)

SqlQueryCompiler is a private class used by SqlQuery and SqlRecord to build an SqlCompiledQuery instance.

The __init__ method passes:

Parameters:

• tblobj – the main table to query: an instance of SqlTable, you can get it using db.table(‘pkgname.tablename’)
• joinConditions – special conditions for joining related tables. See the setJoinCondition() method
• sqlContextName –

  the name of the sqlContext to be reported for subsequent related selection.

  (see the

  setJoinCondition() method)

• sqlparams – a dict of parameters used in “WHERE” clause
• locale – the current locale (e.g: en, en_us, it)

compiledQuery(columns='', where='', order_by='', distinct='', limit='', offset='', group_by='', having='', for_update=False, relationDict=None, bagFields=False, count=False, excludeLogicalDeleted=True, excludeDraft=True, addPkeyColumn=True)

Prepare the SqlCompiledQuery to get the sql query for a selection.

Parameters:

• columns – it represents the columns to be returned by the “SELECT” clause in the traditional sql query. For more information, check the columns section
• where – the sql “WHERE” clause. For more information check the where section
• order_by – corresponding to the sql “ORDER BY” operator. For more information check the order_by section
• distinct – boolean, True for getting a “SELECT DISTINCT”
• limit – number of result’s rows. Corresponding to the sql “LIMIT” operator. For more information, check the limit section
• offset – the same of the sql “OFFSET”
• group_by – the sql “GROUP BY” clause. For more information check the group_by section
• having – the sql “HAVING” clause. For more information check the having
• for_update – boolean. If True, lock the selected records of the main table (SELECT ... FOR UPDATE OF ...)
• relationDict – a dict to assign a symbolic name to a relation. For more information check the relationDict documentation section
• bagFields – boolean. If True, include fields of Bag type (X) when the columns parameter is * or contains *@relname.filter
• count – boolean. If True, optimize the sql query to get the number of resulting rows (like count(*))
• excludeLogicalDeleted – boolean. If True, exclude from the query all the records that are “logical deleted”
• excludeDraft – TODO
• addPkeyColumn – boolean. If True, add a column with the pkey attribute

compiledRecordQuery(lazy=None, eager=None, where=None, bagFields=True, for_update=False, relationDict=None, virtual_columns=None)

Prepare the SqlCompiledQuery class to get the sql query for a selection.

Parameters:

• lazy – TODO.
• eager – TODO.
• where – the sql “WHERE” clause. For more information check the where section.
• bagFields – boolean, True to include fields of type Bag (X) when columns is * or contains *@relname.filter
• for_update – TODO
• relationDict – a dict to assign a symbolic name to a relation. For more information check the relationDict documentation section
• virtual_columns – TODO.

expandMultipleColumns(flt, bagFields)

Internal method: return a list of columns from a fake column starting with *

Parameters:

• flt –

  it can be:

  • *: returns all columns of the current table
  • *prefix_: returns all columns of the current table starting with prefix_
  • *@rel1.@rel2: returns all columns of rel2 target table
  • *@rel1.@rel2.prefix_: returns all columns of rel2 target table starting with prefix_
• bagFields – boolean. If True, include fields of type Bag (X) when columns is * or contains *@relname.filter.

expandPeriod(m)

TODO

Parameters:m – TODO

getAlias(attrs, path, basealias)

Internal method: returns the alias (t1, t2...) for the join table of the current relation. If the relation is traversed for the first time, it builds the join clause. Here case_insensitive relations and joinConditions are addressed.

Parameters:

• attrs – TODO
• path – TODO
• basealias – TODO

getFieldAlias(fieldpath, curr=None, basealias=None)

Internal method. Translate fields path and related fields path in a valid sql string for the column.

It translates @relname.@rel2name.colname to t4.colname.

It has nothing to do with the AS operator, nor the name of the output columns.

It automatically adds the join tables as needed.

It can be recursive to resolve virtualColumns.

Parameters:

• fieldpath – a field path. (e.g: ‘$colname’; e.g: '@relname.@rel2name.colname’)
• curr – TODO.
• basealias – TODO.

getJoinCondition(target_fld, from_fld, alias)

Internal method: get optional condition for a join clause from the joinConditions dict.

A joinCondition is a dict containing:

• condition: the condition as a WHERE clause, the columns of the target table are referenced as $tbl.colname
• params: a dict of params used in the condition clause
• one_one: True if a many relation becomes a one relation due to the condition

Parameters:

• target_fld – TODO
• from_fld – TODO
• alias – TODO

init(lazy=None, eager=None)

TODO

Parameters:

• lazy – TODO.
• eager – TODO.

updateFieldDict(teststring, reldict=None)

Internal method: search for columns or related columns in a string, add found columns to the relationDict (reldict) and replace related columns (@rel.colname) with a symbolic name like $_rel_colname. Return a string containing only columns expressed in the form $colname, so the found relations can be converted in sql strings (see getFieldAlias() method) and replaced into the returned string with templateReplace (see compiledQuery()).

Parameters:

• teststring – TODO
• reldict – a dict of custom names for db columns: {‘asname’:'@relation_name.colname‘}

class gnr.sql.gnrsqldata.SqlRecord(dbtable, pkey=None, where=None, lazy=None, eager=None, relationDict=None, sqlparams=None, ignoreMissing=False, ignoreDuplicate=False, bagFields=True, for_update=False, joinConditions=None, sqlContextName=None, virtual_columns=None, **kwargs)

TODO

compileQuery()

TODO

out_bag(resolver_one=True, resolver_many=True)

TODO

Parameters:

• resolver_one – boolean. TODO
• resolver_many – boolean. TODO

out_dict()

TODO

out_json()

TODO

out_newrecord(resolver_one=True, resolver_many=True)

TODO

Parameters:

• resolver_one – boolean. TODO
• resolver_many – boolean. TODO

out_record()

TODO

output(mode, **kwargs)

TODO

Parameters:mode – TODO

setJoinCondition(target_fld, from_fld, condition, one_one=False, **kwargs)

TODO

Parameters:

• target_fld – TODO
• from_fld – TODO
• condition – set a condition for the join
• one_one – boolean. TODO

class gnr.sql.gnrsqldata.SqlRecordBag(db=None, tablename=None)

TODO

save(**kwargs)

TODO

class gnr.sql.gnrsqldata.SqlRelatedRecordResolver(*args, **kwargs)

TODO

load()

TODO

resolverSerialize()

TODO

class gnr.sql.gnrsqldata.SqlRelatedSelectionResolver(*args, **kwargs)

TODO

load()

TODO

resolverSerialize()

TODO

class gnr.sql.gnrsqldata.SqlSelection(dbtable, data, index=None, colAttrs=None, key=None, sortedBy=None, joinConditions=None, sqlContextName=None, explodingColumns=None, _aggregateRows=False)

It is the resulting data from the execution of an istance of the SqlQuery. Through the SqlSelection you can get data into differents modes: you can use the output() method or you can freeze() it into a file. You can also use the sort() and the filter() methods on a SqlSelection.

analyze(*args, **kwargs)

Warning

deprecated since version 0.7

apply(cb)

TODO

Parameters:cb – TODO

buildAsBag(outsource, recordResolver)

TODO

Parameters:

• outsource – TODO
• recordResolver – boolean. TODO

buildAsGrid(outsource, recordResolver)

TODO

Parameters:

• outsource – TODO
• recordResolver – boolean. TODO

colHeaders

TODO

extend(selection, merge=True)

TODO

Parameters:

• selection – TODO
• merge – boolean. TODO

filter(filterCb=None)

TODO

Parameters:filterCb – TODO.

freeze(fpath, autocreate=False)

TODO

Parameters:

• fpath – the freeze path
• autocreate – boolean. if True, TODO

freezeUpdate()

TODO

getByKey(k)

TODO

Parameters:k – TODO

insert(i, values)

TODO

Parameters:

• i – TODO
• values – TODO

iter_data(outsource)

Return the outsource

Parameters:outsource – TODO

iter_dictlist(outsource)

A generator function that returns a dict of the outsource’s rows.

Parameters:outsource – TODO

iter_pkeylist(outsource)

TODO

Parameters:outsource – TODO

iter_records(outsource)

TODO

Parameters:outsource – TODO

newRow(values)

Add a new row and return it

Parameters:values – TODO

out_bag(outsource, recordResolver=False)

TODO

Parameters:

• outsource – TODO
• recordResolver – boolean. TODO

out_baglist(outsource, recordResolver=False, labelIsPkey=False)

TODO

Parameters:

• outsource – TODO
• recordResolver – boolean. TODO
• caption – boolean. TODO

out_count(outsource)

Return the number of rows in the outsource.

Parameters:outsource – TODO

out_data(outsource)

Return a list of the outsource’s rows.

Parameters:outsource – TODO

out_dictlist(outsource)

TODO

Parameters:outsource – TODO

out_distinct(outsource)

TODO

Parameters:outsource – TODO

out_distinctColumns(outsource)

TODO

Parameters:outsource – TODO

out_fullgrid(outsource, recordResolver=True)

TODO

Parameters:

• outsource – TODO
• recordResolver – boolean. TODO

out_generator(outsource)

Return the outsource

Parameters:outsource – TODO

out_grid(outsource, recordResolver=True)

TODO

Parameters:

• outsource – TODO
• recordResolver – boolean. TODO

out_json(outsource)

TODO

Parameters:outsource – TODO

out_list(outsource)

TODO

Parameters:outsource – TODO

out_listItems(outsource)

Return the outsource.

Parameters:outsource – TODO

out_pkeylist(outsource)

TODO

Parameters:outsource – TODO

out_recordlist(outsource, recordResolver=True)

TODO

Parameters:

• outsource – TODO
• recordResolver – boolean. TODO

out_records(outsource)

TODO

Parameters:outsource – TODO

out_selection(outsource, recordResolver=False, caption=False)

TODO

Parameters:

• outsource – TODO
• recordResolver – boolean. TODO
• caption – boolean. TODO

out_tabtext(outsource)

TODO

Parameters:outsource – TODO

out_xls(outsource, filepath=None)

TODO

Parameters:

• outsource – TODO
• filePath – boolean. TODO.

out_xmlgrid(outsource)

Return a Bag

Parameters:outsource – TODO

output(mode, columns=None, offset=0, limit=None, filterCb=None, subtotal_rows=None, formats=None, locale=None, dfltFormats=None, asIterator=False, asText=False, **kwargs)

Return the selection into differents format

Parameters:

• mode –

  There are different options you can set:

  • mode=’pkeylist’: TODO
  • mode=’records’: TODO
  • mode=’data’: TODO
  • mode=’tabtext’: TODO
• columns – it represents the columns to be returned by the “SELECT” clause in the traditional sql query. For more information, check the columns section
• offset – the same of the sql “OFFSET”
• limit – number of result’s rows. Corresponding to the sql “LIMIT” operator. For more information, check the limit section
• filterCb – TODO
• subtotal_rows – TODO
• formats – TODO
• locale – the current locale (e.g: en, en_us, it)
• dfltFormats – TODO
• asIterator – boolean. TODO
• asText – boolean. TODO

remove(cb)

TODO

Parameters:cb – TODO

setKey(key)

Internal method. Set the data of a SqlQuery in a dict

Parameters:key – the key.

sort(*args)

TODO

toTextGen(outgen, formats, locale, dfltFormats)

TODO

Parameters:

• outgen – TODO
• formats – TODO
• locale – the current locale (e.g: en, en_us, it)
• dfltFormats – TODO

totalize(group_by=None, sum=None, collect=None, distinct=None, keep=None, key=None, captionCb=None, **kwargs)

TODO

Parameters:

• group_by – the sql “GROUP BY” clause. For more information check the group_by section
• sum – TODO
• collect – TODO
• distinct – boolean, True for getting a “SELECT DISTINCT”
• keep – TODO
• key – TODO
• captionCb – TODO

totalizer(path=None)

TODO

Parameters:path – TODO.

totalizerSort(path=None, pars=None)

TODO

Parameters:

• path – TODO.
• pars – TODO.

totals(path=None, columns=None)

TODO

Parameters:

• path – TODO
• columns – it represents the columns to be returned by the “SELECT” clause in the traditional sql query. For more information, check the columns section.