1.1.5.5. gnr.sql.gnrsqltable – Database Tables

Last page update: November 23, 2011

Main class:

• SqlTable

Exceptions classes:

• GnrSqlSaveException
• GnrSqlDeleteException
• GnrSqlProtectUpdateException
• GnrSqlProtectDeleteException
• GnrSqlProtectValidateException

Complete reference:

• gnr.sql.gnrsqltable - The complete reference list

1.1.5.5.1. SqlTable

This class has a consistent number of methods. They are listed here in alphabetical order.

attributes()	diagnostic_errors()	newPkeyValue()	relations_one()
baseViewColumns()	diagnostic_warnings()	newrecord()	rowcaption()
batchUpdate()	empty()	noChangeMerge()	rowcaptionDecode()
buildrecord()	exception()	onInited()	sqlWhereFromBag()
buildrecord_()	existsRecord()	onIniting()	sql_deleteSelection()
checkPkey()	exportToAuxInstance()	pkey()	touchRecords()
check_deletable()	frozenSelection()	pkg()	trigger_onDeleted()
check_updatable()	fullRelationPath()	protect_delete()	trigger_onDeleting()
colToAs()	getColumnPrintWidth()	protect_update()	trigger_onInserted()
column()	getQueryFields()	protect_validate()	trigger_onInserting()
columns()	getResource()	query()	trigger_onUpdated()
columnsFromString()	importFromAuxInstance()	readColumns()	trigger_onUpdating()
copyToDb()	importFromXmlDump()	record()	touchRecords()
copyToDbstore()	indexes()	recordAs()	update()
db()	insert()	recordCaption()	touchRecords()
dbroot()	insertOrUpdate()	recordCoerceTypes()	writeRecordCluster()
defaultValues()	lastTS()	relationExplorer()	xmlDebug()
delete()	lock()	relationName()	xmlDump()
deleteRelated()	logicalDeletionField()	relations()
deleteSelection()	model()	relations_many()

1.1.5.5.2. GnrSqlSaveException

there is no public method.

1.1.5.5.3. GnrSqlDeleteException

there is no public method.

1.1.5.5.4. GnrSqlProtectUpdateException

there is no public method.

1.1.5.5.5. GnrSqlProtectDeleteException

there is no public method.

1.1.5.5.6. GnrSqlProtectValidateException

there is no public method.

1.1.5.5.7. gnr.sql.gnrsqltable - The complete reference list

exception gnr.sql.gnrsqltable.GnrSqlDeleteException(description=None, **kwargs)

Standard Genro SQL Delete Exception

• code: GNRSQL-004
• description: Genro SQL Delete Exception

exception gnr.sql.gnrsqltable.GnrSqlProtectDeleteException(description=None, **kwargs)

Standard Genro SQL Protect Delete Exception

• code: GNRSQL-012
• description: Genro SQL Protect Delete Exception

exception gnr.sql.gnrsqltable.GnrSqlProtectUpdateException(description=None, **kwargs)

Standard Genro SQL Save Exception

• code: GNRSQL-011
• description: Genro SQL Save Exception

exception gnr.sql.gnrsqltable.GnrSqlProtectValidateException(description=None, **kwargs)

Standard Genro SQL Protect Validate Exception

• code: GNRSQL-013
• description: Genro SQL Protect Validate Exception

exception gnr.sql.gnrsqltable.GnrSqlSaveException(description=None, **kwargs)

Standard Genro SQL Save Exception

• code: GNRSQL-003
• description: Genro SQL Save Exception

class gnr.sql.gnrsqltable.SqlTable(tblobj)

The base class for database tables.

Your tables will inherit from it (altough it won’t be explicit in your code, since it’s done by GenroPy mixin machinery).

In your webpage, package or table methods, you can get a reference to a table by name it in this way:

self.db.table('packagename.tablename')

You can also get them from the application instance:

app = GnrApp('instancename')
app.db.table('packagename.tablename')

attributes

TODO

baseViewColumns()

TODO

batchUpdate(updater=None, _wrapper=None, _wrapperKwargs=None, **kwargs)

A batch used to update a database. For more information, check the batchUpdate section

Parameters:

• updater – MANDATORY. It can be a dict() (if the batch is a simple substitution) or a method
• **kwargs –

  insert all the query parameters, like the where parameter

buildrecord(fields, resolver_one=None, resolver_many=None)

Build a new record and return it

Parameters:

• fields – TODO
• resolver_one – TODO
• resolver_many – TODO

buildrecord_(fields)

TODO

Parameters:fields – TODO

checkPkey(record)

TODO

Parameters:record – TODO

check_deletable(record)

TODO

Parameters:record – TODO

check_updatable(record)

TODO

Parameters:record – TODO

colToAs(col)

TODO

Parameters:col – TODO

column(name)

Returns a column object.

Parameters:name – A column’s name or a relation starting from the current table. (eg. @director_id.name)

columns

Returns the columns DbColumnListObj object

columnsFromString(columns=None)

TODO

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

copyToDb(dbsource, dbdest, empty_before=False, excludeLogicalDeleted=True, excludeDraft=True, source_records=None, bagFields=True, **querykwargs)

TODO

Parameters:

• dbsource – sourcedb
• dbdest – destdb
• empty_before – boolean. TODO
• excludeLogicalDeleted – boolean. If True, exclude from the query all the records that are “logical deleted”
• excludeDraft – TODO
• source_records – TODO

copyToDbstore(pkey=None, dbstore=None, bagFields=True, **kwargs)

TODO

Parameters:

• pkey – the record primary key
• dbstore – TODO
• bagFields – TODO

db

Return the GnrSqlDb object

dbroot

Return the GnrSqlDb object

defaultValues()

Override this method to assign defaults to new record. Return a dictionary - fill it with defaults

delete(record, **kwargs)

Delete a single record from this table.

Parameters:record – a dictionary, bag or pkey (string)

deleteRelated(record)

TODO

Parameters:record – a dictionary, bag or pkey (string)

deleteSelection(condition_field=None, condition_value=None, excludeLogicalDeleted=False, excludeDraft=False, condition_op='=', where=None, **kwargs)

TODO

Parameters:

• condition_field – TODO
• condition_value – TODO
• excludeLogicalDeleted – boolean. If True, exclude from the query all the records that are “logical deleted”
• excludeDraft – TODO
• condition_op – TODO

diagnostic_errors(record, old_record=None)

TODO

Parameters:

• record – TODO
• old_record – TODO

diagnostic_warnings(record, old_record=None)

TODO

Parameters:

• record – TODO
• old_record – TODO

draftField

Return the draftField DbColumnObj object

empty()

TODO

exception(exception, record=None, msg=None, **kwargs)

TODO

Parameters:

• exception – the exception raised.
• record – TODO.
• msg – TODO.

existsRecord(record)

Check if a record already exists in the table and return it (if it is not already in the keys)

Parameters:record – the record to be checked

exportToAuxInstance(instance, empty_before=False, excludeLogicalDeleted=True, excludeDraft=True, source_records=None, **querykwargs)

TODO

Parameters:

• instance – the name of the instance
• empty_before – boolean. TODO
• excludeLogicalDeleted – boolean. If True, exclude from the query all the records that are “logical deleted”
• excludeDraft – TODO
• source_records – TODO

frozenSelection(fpath)

Get a pickled selection and return it

Parameters:fpath – TODO

fullRelationPath(name)

TODO

Parameters:name – TODO

getColumnPrintWidth(column)

Allow to find the correct width for printing and return it

Parameters:column – the column to print

getQueryFields(columns=None, captioncolumns=None)

TODO

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
• captioncolumns – TODO

getResource(path)

TODO

Parameters:path – TODO

importFromAuxInstance(instance, tbl_name=None, empty_before=False, excludeLogicalDeleted=True, excludeDraft=True, source_records=None, **querykwargs)

TODO

Parameters:

• instance – the name of the instance
• tbl_name – the name of the database table
• empty_before – boolean. TODO
• excludeLogicalDeleted – boolean. If True, exclude from the query all the records that are “logical deleted”
• excludeDraft – TODO
• source_records – TODO

importFromXmlDump(*args, **kwargs)

TODO

Parameters:path – TODO

indexes

Returns the indexes DbIndexListObj object

insert(record, **kwargs)

Insert a single record

Parameters:record – a dictionary representing the record that must be inserted

insertOrUpdate(record)

Insert a single record if it doesn’t exist, else update it

Parameters:record – a dictionary that represent the record that must be updated

lastTS

Return the lastTS DbColumnObj object

lock(mode='ACCESS EXCLUSIVE', nowait=False)

TODO

Parameters:

• mode – TODO
• nowait – boolean. TODO

logicalDeletionField

Return the logicalDeletionField DbColumnObj object

model

Return the corresponding DbTableObj object

newPkeyValue()

Get a new unique id to use as primary key on the current database table

newrecord(assignId=False, resolver_one=None, resolver_many=None, **kwargs)

TODO

Parameters:

• assignId – TODO
• resolver_one – TODO
• resolver_many – TODO

noChangeMerge

Return the noChangeMerge DbColumnObj object

onInited()

Hook method called on... TODO

onIniting()

Hook method called on... TODO

pkey

Return the pkey DbColumnObj object

pkg

Return the DbPackageObj object that contains the current table

protect_delete(record)

TODO

Parameters:record – TODO

protect_update(record, old_record=None)

TODO

Parameters:

• record – TODO
• old_record – TODO

protect_validate(record, old_record=None)

TODO

Parameters:

• record – TODO
• old_record – TODO

query(columns='*', where=None, order_by=None, distinct=None, limit=None, offset=None, group_by=None, having=None, for_update=False, relationDict=None, sqlparams=None, excludeLogicalDeleted=True, excludeDraft=True, addPkeyColumn=True, locale=None, mode=None, **kwargs)

Return a SqlQuery (a method of gnr/sql/gnrsqldata) object representing a query. This query is executable with different modes.

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 – corresponding to the sql “OFFSET” operator
• 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 path. For more information check the relationDict documentation section
• sqlparams – an optional dictionary for sql query parameters
• 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
• locale – the current locale (e.g: en, en_us, it)
• mode – TODO
• **kwargs – another way to pass sql query parameters

readColumns(pkey=None, columns=None, where=None, **kwargs)

TODO

Parameters:

• pkey – the record primary key
• 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

record(pkey=None, where=None, lazy=None, eager=None, mode=None, relationDict=None, ignoreMissing=False, virtual_columns=None, ignoreDuplicate=False, bagFields=True, joinConditions=None, sqlContextName=None, for_update=False, **kwargs)

Get a single record of the table. It returns a SqlRecordResolver.

The record can be identified by:

• its primary key
• one or more conditions passed as kwargs (e.g. username=’foo’)
• a “where” condition

Parameters:

• pkey – the record primary key
• where – the sql “WHERE” clause. For more information check the where section
• lazy – TODO
• eager – TODO
• mode – bag, dict, json
• relationDict – a dict to assign a symbolic name to a relation path. For more information check the relationDict documentation section
• ignoreMissing – TODO
• virtual_columns – TODO
• ignoreDuplicate – TODO
• 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 – TODO
• for_update – TODO

recordAs(record, mode='bag', virtual_columns=None)

Accept and return a record as a bag, dict or primary pkey (as a string)

Parameters:

• record – a bag, a dict or a string (i.e. the record’s pkey)
• mode – ‘dict’ or ‘bag’ or ‘pkey’
• virtual_columns – TODO

recordCaption(record, newrecord=False, rowcaption=None)

TODO

Parameters:

• record – TODO
• newrecord – boolean. TODO
• rowcaption – TODO

recordCoerceTypes(record, null='NULL')

Check and coerce types in record.

Parameters:

• record – an object implementing dict interface as colname, colvalue
• null – TODO

relationExplorer(omit='', prevRelation='', dosort=True, pyresolver=False, **kwargs)

TODO

Parameters:

• omit – TODO
• prevRelation – TODO
• dosort – boolean. TODO
• pyresolver – boolean. TODO

relationName(relpath)

TODO

Parameters:relpath – TODO

relations

Returns the relations DbColumnListObj object

relations_many

Return a bag of relations that point to the current table

relations_one

Return a Bag of relations that start from the current table

rowcaption

Return the table’s rowcaption

rowcaptionDecode(rowcaption=None)

TODO

Parameters:rowcaption – TODO

sqlWhereFromBag(wherebag, sqlArgs=None, **kwargs)

TODO

Parameters:

• wherebag – TODO
• sqlArgs – TODO

Not sure what this is, but here is the previous existing docstrings in all their glory:

<c_0 column="invoice_num" op="ISNULL" rem='without invoice' />
<c_1 column="@anagrafica.provincia" op="IN" jc='AND'>MI,FI,TO</condition>
<c_2 not="true::B" jc='AND'>
        <condition column="" op=""/>
        <condition column="" op="" jc='OR'/>
</c_2>

sql_deleteSelection(where, **kwargs)

Delete a selection from the table. It works only in SQL so no python trigger is executed

Parameters:

• where – the sql “WHERE” clause. For more information check the where section
• **kwargs – optional arguments for the “where” attribute

touchRecords(where=None, **kwargs)

TODO

Parameters:where – the sql “WHERE” clause. For more information check the where section

trigger_onDeleted(record)

Hook method. Allow to act on record after the record delete

Parameters:record – the new record

trigger_onDeleting(record)

Hook method. Allow to act on record during the record delete

Parameters:record – the new record

trigger_onInserted(record)

Hook method. Allow to act on record after the record insertion

Parameters:record – the record

trigger_onInserting(record)

Hook method. Allow to act on record during the record insertion

Parameters:record – the record

trigger_onUpdated(record, old_record=None)

Hook method. Allow to act on record and old_record after the record update

Parameters:

• record – the new record
• old_record – the old record to be substituted by the new one

trigger_onUpdating(record, old_record=None)

Hook method. Allow to act on record and old_record during the record update

Parameters:

• record – the new record
• old_record – the old record to be substituted by the new one

update(record, old_record=None, pkey=None, **kwargs)

Update a single record

Parameters:

• record – TODO
• old_record – TODO
• pkey – the record primary key

use_dbstores()

TODO

writeRecordCluster(recordCluster, recordClusterAttr, debugPath=None)

Receive a changeSet and execute insert, delete or update

Parameters:

• recordCluster – TODO
• recordClusterAttr – TODO
• debugPath – TODO

xmlDebug(data, debugPath, name=None)

TODO

Parameters:

• data – TODO
• debugPath – TODO
• name – TODO

xmlDump(*args, **kwargs)

TODO

Parameters:path – TODO