DAO objects are used to abstract the access to the data stored in a database so you can easily change the database backend. PyDADL defines three classes for that purpose : the DAO class, the RowSet class and the Row class.
The DAO class is initiated using the getDAO function of an SQL handler, this function takes a table name as argument which is the primary table the DAO operates on. The DAO object defines the following functions :
- __setitem__(item, value)
- This function lets you define DAO attributes. You don't have to call this function directly, use this syntax : DAO['price'] = 150. DAO attributes are used in those functions : insert, replace and update.
- reset()
- This function resets all DAO parameters (joins, filters, etc...). This is equivalent to creating a new DAO object on the same table.
- setDistinctSelect(distinct)
- This functions set the distinct option on select queries. If distinct is set to True duplicate rows are removed from result set.
- join(table, field=None, op='=', local_field=None)
- This function defines an inner join with table. field defines the foreign table's field on which the join condition is made, if left to None a natural join is made. op is the condition operator, it defaults to = (equal). local_field defines the primary table's field on which the join condition is made, if left to None the primary key field (if available) is used.
- leftJoin(table, field=None, op='=', local_field=None)
- This function defines a left join with table. Arguments are used like in the join function.
- rightJoin(table, field=None, op='=', local_field=None)
- This function defines a right join with table. Arguments are used like in the join function.
- setFilter(field=None, value=None, op='=', **filters)
- This function defines a filter on the result set. You can specify filters as keyword arguments like this : setFilter(field_1=10, field_2='some_value', ...). If you want to specify the logical operator, use this syntax : setFilter('price', 150, '>='). If you define multiple filters with this function, they will be concatenated as AND filters.
- setOrFilter(field=None, value=None, op='=', **filters)
- This function defines a filter on the result set. Arguments are used like in the setFilter function. If you define multiple filters with this function, they will be concatenated as OR filters.
- groupBy(field)
- This function defines a GROUP BY on field. You can do multiple call to this function.
- setHavingFilter(field, value, op='=')
- This function defines a HAVING filter on the result set. Arguments are used like in the setFilter function. If you define multiple filters with this function, they will be concatenated as AND filters.
- setHavingOrFilter(field, value, op='=')
- This function defines a HAVING filter on the result set. Arguments are used like in the setFilter function. If you define multiple filters with this function, they will be concatenated as OR filters.
- setOrder(field, ascending=True)
- This function sets the order of the result set. field is used as sorting column. If ascending is set to False, sorting is descending. You can do multiple call to this function.
- setLimit(*limits)
- This function sets a limit on the result set count. Syntax is setLimit(row_count) or setLimit(offset, row_count).
- selectRow(fields=None, **filters)
- This function returns a Row object representing one row using the actual DAO parameters. fields is a string that defines the columns of the result set, columns are separated with commas. You could also specify in-line filters like this : selectRow(id=123). Use this function when you expect just one row in the result set since only the first row is returned.
- select(fields=None, **filters)
- This function returns a RowSet object representing the result set using the actual DAO parameters. Arguments are used like in the selectRow function.
- insert(**fields)
- This function inserts a new row in the primary table using the fields passed as keyword arguments or set as DAO attributes. This function returns the last inserted ID which may be the the new row's ID if the table has an auto increment field.
- replace(**fields)
- this function works like the insert function, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
- update(**fields)
- This function updates the primary table's rows that correspond to the defined filters. columns values are passed as keyword arguments or set as DAO attributes.
- delete(**filters)
- This function deletes the primary table's rows that correspond to the defined filters. in-line filters can be passed as keyword arguments.
The RowSet object represents a set of Row objects. The RowSet objects defines the following functions :
- __nonzero__()
- This function returns whenever the RowSet object holds data. You don't have to call this function directly, use this syntax : if RowSet: do something.
- __len__()
- This function returns the number of rows in the result set. You don't have to call this function directly, use this syntax : len(RowSet).
- __iter__()
- This function allows you to use the RowSet object as an iterator. Ex: for row in RowSet: do something with row. In each iteration a Row object is returned.
- __getitem__(index)
- This function returns a Row object representing the rows at index. You don't have to call this function directly, use this syntax : RowSet[index].
- getRows()
- This function returns all rows in the result set as a list. Each list element is a dict representing a row.
The Row object represents an SQL record. The Row objects defines the following functions :
- __contains__(column)
- This function checks the existence of column in the row. You don't have to call this function directly, use this syntax : if 'column_name' in Row: do something.
- __getattr__(column)
- This function returns column from the row. You don't have to call this function directly, use this syntax : Row.column_name.
- __getitem__(column)
- This function returns column from the row. You don't have to call this function directly, use this syntax : Row['column_name'].
- __setitem__(item, value)
- This function lets you define Row attributes. You don't have to call this function directly, use this syntax : Row['price'] = 150. Row attributes are used in the update function.
- getRow()
- This function returns the row data holded by the Row object as a dict.
- update(**fields)
- This function updates the row holded by the Row object. columns values are passed as keyword arguments or set as Row attributes.
- delete()
- This function deletes the row holded by the Row object.