Represents a Table on an Schema, retrieved with a session created using mysqlx module. More...

Methods
TableInsert	insert (...)
	Creates TableInsert object to insert new records into the table. More...

TableSelect	select (...)
	Creates a TableSelect object to retrieve rows from the table. More...

TableUpdate	update ()
	Creates a record update handler. More...

TableDelete	delete ()
	Creates a record deletion handler. More...

bool	is_view ()
	Indicates whether this Table object represents a View on the database. More...

int	count ()
	Returns the number of records in the table.

Methods inherited from DatabaseObject
str	get_name ()
	Returns the name of this database object. Returns The name this database object.

object	get_session ()
	Returns the Session object of this database object. Returns The Session object used to get to this object. More...

object	get_schema ()
	Returns the Schema object of this database object. Returns The Schema object used to get to this object. More...

bool	exists_in_database ()
	Verifies if this object exists in the database. Returns A boolean indicating if the object still exists on the database.

Additional Inherited Members
Properties inherited from DatabaseObject
str	name
	The name of this database object.

object	session
	The Session object of this database object.

object	schema
	The Schema object of this database object.

Detailed Description

Represents a Table on an Schema, retrieved with a session created using mysqlx module.

Member Function Documentation

◆ insert()

TableInsert insert ( ... )

Creates TableInsert object to insert new records into the table.

Full Syntax

Table	.insert(...)
	[.values(value[, value, ...])]
	.execute()

.insert(...)

Overloads

insert‌()
insert‌(columnList)
insert‌(column[, column, ...])
insert‌({column:value[, column:value, ...]})

An insert operation requires the values to be inserted, optionally the target columns can be defined.

If this function is called without any parameter, no column names will be defined yet.

The column definition can be done by three ways:

Passing to the function call an array with the columns names that will be used in the insertion.
Passing multiple parameters, each of them being a column name.
Passing a JSON document, using the column names as the document keys.

If the columns are defined using either an array or multiple parameters, the values must match the defined column names in order and data type.

If a JSON document was used, the operation is ready to be completed and it will insert the associated values into the corresponding columns.

If no columns are defined, insertion will succeed if the provided values match the database columns in number and data types.

.values(value[, value, ...])

Returns: This TableInsert object.

Each parameter represents the value for a column in the target table.

If the columns were defined on the insert() function, the number of values on this function must match the number of defined columns.

If no column was defined, the number of parameters must match the number of columns on the target Table.

This function is not available when the insert() is called passing a JSON object with columns and values.

Using Expressions As Values

If a mysqlx.expr(...) object is defined as a value, it will be evaluated in the server, the resulting value will be inserted into the record.

.execute()

Executes the insert operation.

See also: TableInsert

Examples

Inserting values without specifying the column names

result = table.insert().values('jack', 17, 'male').execute()

print("Affected Rows No Columns:", result.affected_items_count, "\n")

The column names given as a list of strings

result = table.insert(['age', 'name', 'gender']).values(21, 'john', 'male').execute()

print("Affected Rows Columns:", result.affected_items_count, "\n")

The column names given as a sequence of strings

insert = table.insert('name', 'age', 'gender')
crud = insert.values('clark', 22,'male')
crud = insert.values('mary', 13,'female')
result = insert.execute()
print("Affected Rows Multiple Values:", result.affected_items_count, "\n")

The column names and corresponding values given as a JSON document

result = table.insert({'age':14, 'name':'jackie', 'gender': 'female'}).execute()

print("Affected Rows Document:", result.affected_items_count, "\n")

◆ select()

TableSelect select ( ... )

Creates a TableSelect object to retrieve rows from the table.

Full Syntax

Table	.select(...)
	[.where(expression)]
	[.group_by(...)[.having(condition)]]
	[.order_by(...)]
	[.limit(numberOfRows)[.offset(numberOfRows)]]
	[.lock_shared(lockContention)\|.lock_exclusive(lockContention)] <tr><td></td><td>[.bind(name, value)]</td></tr> <tr><td></td><td>.execute()</td></tr>

.select(...)

Overloads

select‌()
select‌(columnList)
select‌(column[, column, ...])

Defines the columns that will be retrieved from the Table.

To define the column list either use a list object containing the column definitions or pass each column definition on a separate parameter.

If the function is called without specifying any column definition, all the columns in the table will be retrieved.

.where(expression)

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

The expression supports Parameter Binding.

.group_by(...)

Overloads

group_by‌(columnList)
group_by‌(column[, column, ...]) Sets a grouping criteria for the retrieved rows.

.having(condition)

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

The condition supports Parameter Binding.

.order_by(...)

Overloads

order_by‌(sortCriteriaList)
order_by‌(sortCriterion[, sortCriterion, ...]) 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.

.limit(numberOfRows)

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

This function can be called every time the statement is executed.

.offset(numberOfRows)

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

.lock_shared(lockContention)

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.

.lock_exclusive(lockContention)

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.

.bind(name, value)

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.

.execute()

Executes the select operation with all the configured options.

See also: TableSelect

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')

◆ update()

TableUpdate update ( )

Creates a record update handler.

Full Syntax

Table	.update()
	.set(attribute, value)
	[.where(expression)]
	[.order_by(...)] <tr><td></td><td>[.limit(numberOfRows)]</td></tr> <tr><td></td><td>[.bind(name, value)]</td></tr> <tr><td></td><td>.execute()</td></tr>

.update()

Initializes the update operation.

.set(attribute, value)

Parameters

attribute	Identifies the column to be updated by this operation.
value	Defines the value to be set on the indicated column.

Returns: This TableUpdate object.

Adds an operation into the update handler to update a column value in the records that were included on the selection filter and limit.

Using Expressions As Values

If a mysqlx.expr(...) object is defined as a value, it will be evaluated in the server, the resulting value will be set at the indicated column.

The expression also can be used for Parameter Binding.

.where(expression)

If used, only those rows satisfying the expression will be updated

The expression supports Parameter Binding.

.order_by(...)

Overloads

order_by‌(sortCriteriaList)
order_by‌(sortCriterion[, sortCriterion, ...]) If used, the TableUpdate operation will update the records in the order established by the sort criteria.

Every defined sort criterion follows the format:

name [ ASC | DESC ]

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

.limit(numberOfRows)

If used, the operation will update only numberOfRows rows.

This function can be called every time the statement is executed.

.bind(name, value)

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.

.execute()

Executes the delete operation with all the configured options.

See also: TableUpdate

Examples

Updating a single field in a record

result = result = table.update().set('name', 'aline').where('age = 13').execute()
print('Affected Rows:', result.affected_items_count, '\n')
 
result = table.select().where('name = "aline"').execute()
record = result.fetch_one()
print("Updated Record:", record.name, record.age)

Updating a single field using expressions

result = table.update().set('age', mysqlx.expr('13+10')).where('age = 13').execute()
print('Affected Rows:', result.affected_items_count, '\n')
 
result = table.select().where('age = 23').execute()
record = result.fetch_one()
print("Updated Record:", record.name, record.age)

Updating a single field using expressions and parameter binding

result = table.update().set('age', mysqlx.expr(':new_year')).where('age = :old_year').limit(2).bind('new_year', 16).bind('old_year', 15).execute()
print('Affected Rows:', result.affected_items_count, '\n')
 
records = table.select().where('age = 16').execute().fetch_all()
print('With 16 Years:', len(records), '\n')
 
records = table.select().where('age = 15').execute().fetch_all()
print('With 15 Years:', len(records), '\n')

Updating a view

result = view.update().set('my_gender', 'female').execute()
print('Updated Females:', result.affected_items_count, '\n')
 
# Result gets reflected on the target table
records = table.select().where('gender = "female"').execute().fetch_all()
print('All Females:', len(records), '\n')

◆ delete()

TableDelete delete ( )

Creates a record deletion handler.

Full Syntax

Table	.delete()
	[.where(expression)]
	[.order_by(...)] <tr><td></td><td>[.limit(numberOfRows)]</td></tr> <tr><td></td><td>[.bind(name, value)]</td></tr> <tr><td></td><td>.execute()</td></tr>

.delete()

Initializes the deletion operation.

.where(expression)

If used, only those rows satisfying the expression will be deleted

The expression supports Parameter Binding.

.order_by(...)

Overloads

order_by‌(sortCriteriaList)
order_by‌(sortCriterion[, sortCriterion, ...]) If used, the TableDelete operation will delete the records in the order established by the sort criteria.

Every defined sort criterion follows the format:

name [ ASC | DESC ]

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

.limit(numberOfRows)

If used, the operation will delete only numberOfRows rows.

This function can be called every time the statement is executed.

.bind(name, value)

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.

.execute()

Executes the delete operation with all the configured options.

See also: TableDelete

Examples

Deleting records with a condition

result = table.delete().where('age = 15').execute()
print('Affected Rows:', result.affected_items_count, '\n')
 
records = table.select().execute().fetch_all()
print('Records Left:', len(records), '\n')

Deleting records with a condition and parameter binding

result = table.delete().where('gender = :heorshe').limit(2).bind('heorshe', 'male').execute()
print('Affected Rows:', result.affected_items_count, '\n')
 
records = table.select().execute().fetch_all()
print('Records Left:', len(records), '\n')

Deleting all records using a view

view = schema.get_table('view1')
result = view.delete().execute()
print('Affected Rows:', result.affected_items_count, '\n')
 
# Deletion is of course reflected on the target table
records = table.select().execute().fetch_all()
print('Records Left:', len(records), '\n')

Deleting records with a limit

result = table.delete().limit(2).execute()
print('Affected Rows:', result.affected_items_count, '\n')
 
records = table.select().execute().fetch_all()
print('Records Left:', len(records), '\n')

◆ is_view()

Bool is_view ( )

Indicates whether this Table object represents a View on the database.

Returns: True if the Table represents a View on the database, False if represents a Table.

Methods

Additional Inherited Members

Detailed Description

Member Function Documentation

◆ insert()

Full Syntax

.insert(...)

Overloads

.values(value[, value, ...])

.execute()

Examples

Inserting values without specifying the column names

The column names given as a list of strings

The column names given as a sequence of strings

The column names and corresponding values given as a JSON document

◆ select()

Full Syntax

.select(...)

Overloads

.where(expression)

.group_by(...)

Overloads

.having(condition)

.order_by(...)

Overloads

.limit(numberOfRows)

.offset(numberOfRows)

.lock_shared(lockContention)

.lock_exclusive(lockContention)

.bind(name, value)

.execute()

Examples

Fetching all the records

Fetching records matching specified criteria

Selecting which columns to fetch

Sorting the results

Setting limit and offset

Using parameter binding

◆ update()

Full Syntax

.update()

.set(attribute, value)

.where(expression)

.order_by(...)

Overloads

.limit(numberOfRows)

.bind(name, value)

.execute()

Examples

Updating a single field in a record

Updating a single field using expressions

Updating a single field using expressions and parameter binding

Updating a view

◆ delete()

Full Syntax

.delete()

.where(expression)

.order_by(...)

Overloads

.limit(numberOfRows)

.bind(name, value)

.execute()

Examples

Deleting records with a condition

Deleting records with a condition and parameter binding

Deleting all records using a view

Deleting records with a limit

◆ is_view()