1.4.16.3 Cursor Objects

close( ): Close the cursor now (rather than whenever __del__ is called). The cursor will be unusable from this point forward; an Error (or subclass) exception will be raised if any operation is attempted with the cursor.

export( tables, [includeCreate=True])

Export the tables specified by tables as portable SQL including statements to create the tables if includeCreate is True.

Importing the SQL is then simply a matter of executing the SQL. Here is an example:

backup = cursor.export(tables=['testTable'])
cursor.drop(table='testTable')
for sql in backup.split('\n'):
    cursor.execute(sql, mode='portable')

The testTable should be exactly the same as it was before the code was executed.

Cursor objects have the following attributes:

connection: This read-only attribute return a reference to the Connection object on which the cursor was created. The attribute simplifies writing polymorph code in multi-connection environments.

info: A list of Column objects for in the order of the fields from the last SELECT or None if the last SQL operation was not a SELECT. Column objects contain all the information about a particular field and provide conversion methods for that field.

baseCursor: The DB-API 2.0 Cursor object

sql: A list of tuples of parameters passed to the execute() methods

1.4.16.3.1 Execute SQL

web.database compliant databases support qmark style parameters for substitutions as follows:

cursor.execute('SELECT * FROM Test WHERE columnName=?',['textEntry'])

execute( sql[, parameters][,mode]): Prepare and execute a database operation. Parameters are provided as a sequence and will be bound to ? variables in the operation. mode can be 'direct' to pass the parameters to the underlying DB-API 2.0 cursor or 'portable' to execute the code in a portable fashion.

executemany( sql, manyParameters[,mode]): Similar to execute() but the operation is executed for each sequence in manyParameters.

1.4.16.3.2 Fetch Results

All these methods take the parameters format and convert. If they are not specified the values set in the cursor() method of the Connection object is used.

fetchone( [format],[convert])

Fetch the next row of a query result set, returning a single sequence, or None when no more data is available. [6]

An Error (or subclass) exception is raised if the previous call to executeXXX() did not produce any result set or no call was issued yet.

format: The format of the results returned. Can be 'dict' to return them as a tuple of dictionary objects, 'tuple' to return them as a tuple of tuples, 'object' to return them as a tuple of dtuple objects which can be treated as a tuple or a dictionary (or via attribute access for the majority of column names) or 'text' to return tables designed to be displayed in a terminal 80 characters wide. If not specified takes the value specified in the cursor which by default is 'tuple'
convert: Can be True to convert the results to the correct types, False to leave the results as they are returned from the base cursor. If not specified takes the value specified in the cursor which by default is True

fetchall( [format],[convert])

Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples).

An Error (or subclass) exception is raised if the previous call to an execute() method did not produce any result set or no call was issued yet.

The values format and convert are as specified in fetchone()

1.4.16.3.3 Cursor Abstraction Methods

It is assumed that if execute is True in the following methods then you wish to be executing the code in portable mode, otherwise it is unlikely you would be using abstraction methods.

If you did wish to execute code in direct mode (through the DB-API 2.0 cursor) you could do the following:

sql = cusror.select(columns=['*'], tables=['table'], execute=False)
cursor.execute(sql, mode='direct')

Warning: It is possible to get the cursor abstraction methods to perform operations they were not designed for. For example, in cursor.select() you could specify one of the columns as 'AVG(columnName)'. This would produce an SQL statement which would return the mean value of the column columnName on some databases but certainly not on all and therefore breaks the specification which states that columns should be a list of column names. To ensure database portability please stick to the published API.

select( tables, columns, [values=[], ][where=None, ][order=None, ][execute=None, ][fetch=None, ][**params])

Build an SQL string according to the options specified and optionally execute the SQL and return the results in the format specified. No error checking on field names if the SQL string is only being built. Strict error checking is only performed when executing the code.

tables: A string containing the name of the table to select from or if selecting from multiple tables, a sequence of table names.
columns: A sequence of column names to select. Can be a string if only one column is being selected. If selecting from multiple tables, all column names should be in the form 'tableName.columnName'
values: A list of values to substitute for ? in the WHERE clause specified by where.
where: The WHERE clause as a web.database list as returned by cursor.where(). If where is a string it is converted to the correct format.
order: The ORDER BY clause as a web.database list as returned by cursor.order(). If order is a string it is converted to the correct format.
execute: If False the method returns the SQL string needed to perform the desired operations. If True the SQL is executed and the results converted and returned in the appropriate form. If not specified takes the value specified in the cursor which by default is True
fetch: Whether or not to fetch the results. If True and execute is not specified execute is set to True. If True and execute False an error is raised.
**params: The parameters to be passed to the fetchall() method if fetch is True

insert( table, columns, values, _sqlValues, [execute])

Insert values into the columns in table. Either values or _sqlValues can be specified but not both.

table: The name of the table to insert into
columns: A sequence of column names in the same order as the values which are going to be inserted into those columns. Can be a string if only one column is going to have values inserted
values: A sequence of Python values to be inserted into the columns named in the columns variable. Can be the value rather than a list if there is only one value. If values is specified then _sqlValues must be either an empty sequence or contain a list of all quoted SQL strings for the columns specified in which case values contains the Python values of the SQL strings to be substituted for ? parameters in the _sqlValues sequence.
_sqlValues: A sequence of quoted SQL strings to be inserted into the columns named in the columns variable. Can be the value rather than a list if there is only one value. If _sqlValues is specified and contains ? parameters for substitution then values contains the values to be substituted. Otherwise values must be an empty sequence.
execute: If False the method returns the SQL string to perform the desired operations. If True the SQL is executed. If not specified takes the value specified in the cursor which by default is True

insertMany( table, columns, values, _sqlValues, [execute]): Same as insert() except that values or _sqlValues contain a sequence of sequences of values to be inserted.

update( table, columns, values, _sqlValues [, where] [, execute])

Update the columns in table with the values. Either values or _sqlValues can be specified but not both.

table: A string containing the name of the table to update
columns: A sequence of column names in the same order as the values which are going to be updated in those columns. Can be a string if only one column is going to have values inserted
values: A sequence of Python values to be inserted into the columns named in the columns variable. Can be the value rather than a list if there is only one value. If values is specified then _sqlValues must be either an empty sequence or contain a list of all quoted SQL strings for the columns specified in which case values contains the Python values of the SQL strings to be substituted for ? parameters in the _sqlValues sequence. If there are more values specified in values than _sqlValues the remaining values are used to substitute for ? parameters in where.
_sqlValues: A sequence of quoted SQL strings to be inserted into the columns named in the columns variable. Can be the value rather than a list if there is only one value. If _sqlValues is specified and contains ? parameters for substitution then values contains the values to be substituted. Otherwise values must be an empty sequence.
where: The WHERE clause as a web.database list as returned by cursor.where(). If where is a string it is converted to the correct format.
execute: If False the method returns the SQL string to perform the desired operations. If True the SQL is executed. If not specified takes the value specified in the cursor which by default is True

delete( table, [values=[]][, where] [, execute])

Delete records from the table according to where.

table: A string containing the name of the table to select from or if selecting from multiple tables, a sequence of table names.
values: A list of values to substitute for ? in the WHERE clause specified by where.
where: The WHERE clause as a web.database list as returned by cursor.where(). If where is a string it is converted to the correct format.
execute: If False the method returns the SQL string to perform the desired operations. If True the SQL is executed. If not specified takes the value specified in the cursor which by default is True

create( table, columns [, values=[]] [, execute] )

Create table with fields specified by fields. fields is a tuple of field tuples which can be obtained as follows:

columns = [
    cursor.column( field options... ),
    cursor.column( field options... ),
    cursor.column( field options... ),
    cursor.column( field options... ),
]

table: The table name as a string.
columns: A sequence of field tuples returned by cursor.column()
values: A sequence of values to substitute for default values in the columns
execute: If False the method returns the SQL string to perform the desired operations. If True the SQL is executed. If not specified takes the value specified in the cursor which by default is True

drop( table[, execute] )

Remove a table

table: A string containing the name of the table to drop.
execute: If False the method returns the SQL string to perform the desired operations. If True the SQL is executed. If not specified takes the value specified in the cursor which by default is True

_function( function, table, column, [,where=None][, values=[]])

Returns the result of applying the specified function to the field

function: The function to be applied, can be 'max', 'min', 'sum' or 'count'
table: The name of the table
column: The name of the field
where: An optional where clause
values: A list of values to substitute for ? parameters in the WHERE clause

max( table, column [,where=None][,values=[]])

Returns the highest value of the column.

table: The name of the table
column: The name of the column
where: An optional where clause
values: Values to substitute for ? parameters in the where clause.

min( table, column [,where=None][,values=[]])

Returns the lowest value of the column.

table: The name of the table
column: The name of the column
where: An optional where clause
values: Values to substitute for ? parameters in the where clause.

count( table, column [,where=None][,values=[]])

Count the number of rows in the table matching where. If where is not specified, count all rows.

table: The name of the table
column: The name of the column
where: An optional where clause
values: Values to substitute for ? parameters in the where clause.

1.4.16.3.4 Helper Methods

Helper methods build the data structures which should be passed to the Cursor abstraction methods.

column( name, type[, required=0][, unique=0][, primaryKey=0][, foreignKey=None][, default=None])

Return a column tuple suitable for use in the columns tuple used in the create() method.

Bool, Float, Binary and Text columns cannot be used as primary keys or unique columns. Binary and Text columns cannot have default values.

name: The name of the field as a string.
type: The field type. This can take one of the values: 'Bool', 'String', 'Text', 'Binary', 'Long', 'Integer', 'Float', 'Date', 'Time', 'Datetime'
required: Whether or not the field is required. Setting to True means the field cannot have NULL values.
unique: Set to True if the value must be unique. Two fields in the column cannot have the same value unless that value is NULL
primaryKey: The field is to be used as a primary key, the field has the same behaviour as being unique and required but no default value can be set
foreignKey: The field is to be used as a foreign key, the value should be the name of the table for which this is a child table. Note: There is no need to specify the column name as tables can only have one primary key.
default: The default value for the field to be set to. If not specified the default is NULL

where( where, [values=[]])

Return a parsed WHERE clause suitable for use in the select(), update() and delete() methods of the cursor object.

where

A string containing the WHERE clause. Can include the LIKE operator which is used as follows:

WHERE columnName LIKE %s1%s2%s

Every % sign is matched against zero or more characters.

Note: where should not include the string 'WHERE' at the beginning.

values

A list of values to substitute for ? parameters in the WHERE clause

order( order)

Return a parsed ORDER BY clause suitable for use in the select() method of the cursor object.

order: A string containing the ORDER BY clause. Note: order should not include the string 'ORDER BY' at the beginning.

1.4.16.3.5 Utility Methods

export( )

Export the database as a series of SQL strings which can be executed to completely recreate precicely the original database structure. This is useful for database backups or for moving data from one database to another.

Warning: This is a new feature and has not had a lot of testing.