TableSelect Class Reference

Handler for record selection on a Table. More...

Methods
TableSelect	select ()
	Defines the columns to be retrieved from the table. More...
TableSelect	where (str expression)
	Sets the search condition to filter the records to be retrieved from the Table. More...
TableSelect	group_by (list columns)
	Sets a grouping criteria for the retrieved rows. More...
TableSelect	having (str condition)
	Sets a condition for records to be considered in aggregate function operations. More...
TableSelect	order_by (list sortCriteria)
	Sets the order in which the records will be retrieved. More...
TableSelect	limit (int numberOfRows)
	Sets the maximum number of rows to be returned on the select operation. More...
TableSelect	offset (int numberOfRows)
	Sets number of rows to skip on the resultset when a limit has been defined. More...
TableSelect	lock_shared (str lockContention)
	Instructs the server to acquire shared row locks in documents matched by this find operation. More...
TableSelect	lock_exclusive (str lockContention)
	Instructs the server to acquire an exclusive lock on rows matched by this find operation. More...
TableSelect	bind (str name, Value value)
	Binds a value to a specific placeholder used on this operation. More...
RowResult	execute ()
	Executes the select operation with all the configured options. More...

Detailed Description

Handler for record selection on a Table.

This object provides the necessary functions to allow selecting record data from a table.

This object should only be created by calling the select function on the table object from which the record data will be retrieved.

See also

Table

Member Function Documentation

select()

TableSelect select

(

)

Defines the columns to be retrieved from the table.

Returns

This TableSelect object.

Calling select() will cause all the columns in the table to be retrieved.

To retrieve only certain columns use either select(list columns) and provide a list object containing the column definitions, or select(str column[, str column, ...]) and pass each column definition as a separate parameter.

Method Chaining

After this function invocation, the following functions can be invoked:

• where(str expression)
• group_by(list columns), group_by(str column[, str column, ...])
• order_by(list sortCriteria), order_by(str sortCriterion[, str sortCriterion, ...])
• limit(int numberOfRows)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

where()

TableSelect where

(

str

expression

)

Sets the search condition to filter the records to be retrieved from the Table.

Parameters

expression

A condition to filter the records to be retrieved.

Returns

This TableSelect object.

If used, only those rows satisfying the expression will be retrieved.

The expression supports Parameter Binding.

Method Chaining

This function can be invoked only once after:

• select(), select(list columns), select(str column[, str column, ...]) After this function invocation, the following functions can be invoked:
• group_by(list columns), group_by(str column[, str column, ...])
• order_by(list sortCriteria), order_by(str sortCriterion[, str sortCriterion, ...])
• limit(int numberOfRows)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

group_by()

TableSelect group_by

(

list

columns

)

Sets a grouping criteria for the retrieved rows.

Returns

This TableSelect object.

Method Chaining

This function can be invoked only once after:

• select(), select(list columns), select(str column[, str column, ...])
• where(str expression) After this function invocation the following functions can be invoked:
• having(str condition)
• order_by(list sortCriteria), order_by(str sortCriterion[, str sortCriterion, ...])
• limit(int numberOfRows)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

having()

TableSelect having

(

str

condition

)

Sets a condition for records to be considered in aggregate function operations.

Parameters

condition

A condition to be used with aggregate functions.

Returns

This TableSelect object.

If used the TableSelect operation will only consider the records matching the established criteria.

The condition supports Parameter Binding.

Method Chaining

This function can be invoked only once after:

• group_by(list columns), group_by(str column[, str column, ...]) After this function invocation, the following functions can be invoked:
• order_by(list sortCriteria), order_by(str sortCriterion[, str sortCriterion, ...])
• limit(int numberOfRows)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

order_by()

TableSelect order_by

(

list

sortCriteria

)

Sets the order in which the records will be retrieved.

Returns

This TableSelect object.

If used, the TableSelect operation will return the records sorted with the defined criteria.

Every defined sort criterion follows the format:

name [ ASC | DESC ]

ASC is used by default if the sort order is not specified.

Method Chaining

This function can be invoked only once after:

• select(), select(list columns), select(str column[, str column, ...])
• where(str expression)
• group_by(list columns), group_by(str column[, str column, ...])
• having(str condition) After this function invocation, the following functions can be invoked:
• limit(int numberOfRows)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

limit()

TableSelect limit

(

int

numberOfRows

)

Sets the maximum number of rows to be returned on the select operation.

Parameters

numberOfRows

The maximum number of rows to be retrieved.

Returns

This TableSelect object.

If used, the operation will return at most numberOfRows rows.

Method Chaining

This function can be invoked only once after:

• select(), select(list columns), select(str column[, str column, ...])
• where(str expression)
• group_by(list columns), group_by(str column[, str column, ...])
• having(str condition)
• order_by(list sortCriteria), order_by(str sortCriterion[, str sortCriterion, ...]) This function can be called every time the statement is executed.

After this function invocation, the following functions can be invoked:

• offset(int numberOfRows)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

offset()

TableSelect offset

(

int

numberOfRows

)

Sets number of rows to skip on the resultset when a limit has been defined.

Parameters

numberOfRows

The number of rows to skip before start including them on the RowResult.

Returns

This TableSelect object.

If used, the first numberOfRows records will not be included on the result.

Method Chaining

This function can be invoked only once after:

• limit(int numberOfRows) After this function invocation, the following functions can be invoked:
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

lock_shared()

TableSelect lock_shared

(

str

lockContention

)

Instructs the server to acquire shared row locks in documents matched by this find operation.

Parameters

lockContention

optional parameter to indicate how to handle rows that are already locked.

Returns

This TableSelect object.

When this function is called, the selected rows will be locked for write operations, they may be retrieved on a different session, but no updates will be allowed.

The acquired locks will be released when the current transaction is committed or rolled back.

The lockContention parameter defines the behavior of the operation if another session contains an exclusive lock to matching rows.

The lockContention can be specified using the following constants:

• mysqlx.LockContention.DEFAULT
• mysqlx.LockContention.NOWAIT
• mysqlx.LockContention.SKIP_LOCKED

The lockContention can also be specified using the following string literals (no case sensitive):

• 'DEFAULT'
• 'NOWAIT'
• 'SKIP_LOCKED'

If no lockContention or the default is specified, the operation will block if another session already holds an exclusive lock on matching rows until the lock is released.

If lockContention is set to NOWAIT and another session already holds an exclusive lock on matching rows, the operation will not block and an error will be generated.

If lockContention is set to SKIP_LOCKED and another session already holds an exclusive lock on matching rows, the operation will not block and will return only those rows not having an exclusive lock.

This operation only makes sense within a transaction.

Method Chaining

This function can be invoked at any time before bind() or execute() are called.

After this function invocation, the following functions can be invoked:

• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

If lock_exclusive() is called, it will override the lock type to be used on the selected documents.

See also

Usage examples at execute().

lock_exclusive()

TableSelect lock_exclusive

(

str

lockContention

)

Instructs the server to acquire an exclusive lock on rows matched by this find operation.

Parameters

lockContention

optional parameter to indicate how to handle rows that are already locked.

Returns

This TableSelect object.

When this function is called, the selected rows will be locked for read operations, they will not be retrievable by other session.

The acquired locks will be released when the current transaction is committed or rolled back.

The lockContention parameter defines the behavior of the operation if another session contains a lock to matching rows.

The lockContention can be specified using the following constants:

• mysqlx.LockContention.DEFAULT
• mysqlx.LockContention.NOWAIT
• mysqlx.LockContention.SKIP_LOCKED

The lockContention can also be specified using the following string literals (no case sensitive):

• 'DEFAULT'
• 'NOWAIT'
• 'SKIP_LOCKED'

If no lockContention or the default is specified, the operation will block if another session already holds a lock on matching rows until the lock is released.

If lockContention is set to NOWAIT and another session already holds a lock on matching rows, the operation will not block and an error will be generated.

If lockContention is set to SKIP_LOCKED and another session already holds a lock on matching rows, the operation will not block and will return only those rows not having an exclusive lock.

This operation only makes sense within a transaction.

Method Chaining

This function can be invoked at any time before bind() or execute() are called.

After this function invocation, the following functions can be invoked:

• lock_shared(str lockContention)
• bind(str name, Value value)
• execute()

If lock_shared() is called, it will override the lock type to be used on the selected documents.

See also

Usage examples at execute().

bind()

TableSelect bind	(	str	name,
		Value	value
	)

Binds a value to a specific placeholder used on this operation.

Parameters

name	The name of the placeholder to which the value will be bound.
value	The value to be bound on the placeholder.

Returns

This TableSelect object.

Binds the given value to the placeholder with the specified name.

An error will be raised if the placeholder indicated by name does not exist.

This function must be called once for each used placeholder or an error will be raised when the execute() method is called.

Method Chaining

This function can be invoked multiple times right before calling execute().

After this function invocation, the following functions can be invoked:

• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

execute()

RowResult execute

(

)

Executes the select operation with all the configured options.

Returns

A RowResult object that can be used to traverse the rows returned by this operation.

Method Chaining

This function can be invoked after any other function on this class.

Examples

Fetching all the records

records = table.select().execute().fetch_all()
print("All:", len(records), "\n")

Fetching records matching specified criteria

records = table.select().where('gender = "male"').execute().fetch_all()
print("Males:", len(records), "\n")
 
records = table.select().where('gender = "female"').execute().fetch_all()
print("Females:", len(records), "\n")
 
records = table.select().where('age = 13').execute().fetch_all()
print("13 Years:", len(records), "\n")
 
records = table.select().where('age = 14').execute().fetch_all()
print("14 Years:", len(records), "\n")
 
records = table.select().where('age < 17').execute().fetch_all()
print("Under 17:", len(records), "\n")
 
records = table.select().where('name like "a%"').execute().fetch_all()
print("Names With A:", len(records), "\n")
 
records = table.select().where('name LIKE "a%"').execute().fetch_all()
print("Names With A:", len(records), "\n")
 
records = table.select().where('NOT (age = 14)').execute().fetch_all()
print("Not 14 Years:", len(records), "\n")

Selecting which columns to fetch

result = table.select(['name','age']).execute()
record = result.fetch_one()
columns = dir(record)
print(columns)
# In python, members are returned in alphabetic order
# We print the requested columns here (get_length and get_field are members too)
print('1-Metadata Length:', len(columns), '\n')
print('1-Metadata Field:', columns[5], '\n')
print('1-Metadata Field:', columns[0], '\n')
 
result = table.select(['age']).execute()
record = result.fetch_one()
columns = dir(record)
# In python, members are returned in alphabetic order
# We print the requested columns here (get_length and get_field are members too)
print('2-Metadata Length:', len(columns), '\n')
print('2-Metadata Field:', columns[0], '\n')

Sorting the results

records = table.select().order_by(['name']).execute().fetch_all()
for index in range(7):
    print('Select Asc', index, ':', records[index].name, '\n')
 
records = table.select().order_by(['name desc']).execute().fetch_all()
for index in range(7):
    print('Select Desc', index, ':', records[index].name, '\n')

Setting limit and offset

records = table.select().limit(4).execute().fetch_all()
print('Limit-Offset 0 :', len(records), '\n')
 
for index in range(7):
    records = table.select().limit(4).offset(index + 1).execute().fetch_all()
    print('Limit-Offset', index + 1, ':', len(records), '\n')

Using parameter binding

records = view.select().where('my_age = :years and my_gender = :heorshe').bind('years', 13).bind('heorshe', 'female').execute().fetch_all()
print('Select Binding Length:', len(records), '\n')
print('Select Binding Name:', records[0].my_name, '\n')