The X Developer Application Programming Interface, or X DevAPI, is a library of classes and methods that implement a new NoSQL interface for MySQL. To be specific, the X DevAPI is designed to allow easy interaction with JSON documents and relational data. The X DevAPI has classes devoted to supporting both concepts allowing developers to use either (or both) in their applications. The X DevAPI together with the X Protocol, X Plugin, and clients written to expose the X DevAPI, forms the new MySQL 8 Document Store feature.
As we will see, there are many aspects to working with the X DevAPI. However, once you master the basics of connecting and requesting object instances, forming expressions, and working with the JSON documents, the X DevAPI is very easy to learn and is efficient for writing document store or relational data applications.
We have already seen several examples of the X DevAPI in action throughout this book for relational data as most database administrators are familiar with that form of database interaction. However, we have not seen a comprehensive list of the classes and methods provided for the document store. This chapter contains nearly all the public classes and methods available in the X DevAPI (some lesser used classes are omitted for brevity).
Although all the X DevAPI client connectors support all the classes, there are some minor differences in how each of the clients implements the X DevAPI. In particular, the names of classes and methods vary slightly to match the development practices for the language. For example, the accepted style guide for a language may discourage camelCase names whereas the style guide for another may suggest the use of underscores and no capitalization.
When learning to use the X DevAPI, it can be helpful to review examples from other languages. Although the naming schemes may differ and the syntax may be quite different, the basic classes and methods are similar enough that you can still learn what methods to use. This is the major reason I use Python examples. You can use the Python examples to see how to use the classes and although the methods may have slightly different naming schemes, the methods and practices are the same from one language to another. Plus, Python is easy to read and you do not need large, complicated development tools (e.g., a C++ or .Net compiler). All you need is a Python interpreter and it is available for almost all platforms.
Although this chapter contains some similar information from other chapters, it uses a stepwise approach to demonstrate the X DevAPI via a series of code examples. A set of tables describing the major classes and their methods is included as a reference to use as a guide when writing your own code for a document store application.
I begin with a comprehensive overview of the characteristics of the X DevAPI and then I move on to a detailed reference of the major classes and methods. Along the way I give many examples using the X DevAPI. We will not see every possible class or method that is part of the X DevAPI, but we will see the major components (classes and methods) that you will need to master to write document store applications. If you need additional information for the less frequently used classes and methods, see the "For More Information" section for references to developer documentation.
Overview
MySQLX: A module used to get a session object resulting from an X Protocol connection to a MySQL server.
Sessions: A connection to a MySQL server.
Collections: An organizational abstraction for storing JSON documents.
Documents: JSON documents are the primary storage mechanism for data in collections.
CRUD operations: Simple methods for create, read, update, and delete operations. Read operations are simple and easy to understand.
Relational data: Implements CRUD operations for traditional relational data including SQL statement execution and results processing.
Expressions: Use modern practices and syntax styles are used to get away from traditional SQL-String-Building for finding things in your collections and documents.
Parallel execution: Nonblocking, asynchronous calls follow common host language patterns.
Method chaining: The API is built so that methods that create or retrieve (get) an object return an instance of that object. This allows us to combine several methods together (called method chaining). Although method chaining is neither a new concept nor unique to the X DevAPI, it is a very powerful mechanism for making our code more expressive and easier to read.
Note
The X DevAPI is only available when using the X Plugin. You cannot use the X DevAPI without the X Plugin installed and then only through an X Protocol enabled client or database connector.
Clients
MySQL Shell: version 8.0.4 and later ( https://dev.mysql.com/downloads/shell/ )
Connector/J: version 8.0.8 and later ( https://dev.mysql.com/downloads/connector/j/ )
Connector/Net: version 8.0.8 and later ( https://dev.mysql.com/downloads/connector/net/ )
Connector/Node.js: version 8.0.8 and later ( https://dev.mysql.com/downloads/connector/nodejs/ )
Connector/Python: version 8.0.5 and later ( https://dev.mysql.com/downloads/connector/python/ )
Connector/C++: version 8.0.6 and later ( https://dev.mysql.com/downloads/connector/cpp/ )
Note
Some of the database connector versions are not yet generally available (GA) releases. In those cases, you can find the correct version by clicking on the Development Releases tab on the download page. As long as you’re not using them in production, using a DMR release should be fine. Be sure to contact your MySQL sales representative for assistance if you do not see a GA release of a component you want to use.
Target Language Conformity
When you encounter a new API such as the X DevAPI, it is often the case that you would expect the names of classes and methods to be the same from one language to another. That is, a class with a method named getSomething() would be spelled the same from one language to another. However, it is a common (and some would say preferred) practice to obey platform- and language-specific naming conventions sacrifice commonality in the API to ensure continued compliance for the language naming standard. If you work with different programming languages as I do, you will find this is common place and thus one knows to expect some variations from one language to another for the same API.
The X DevAPI subscribes to this practice and the clients that implement the API conform to their platform and language standards. In most cases, this may be only a change in the use of capital letters in the name but may also result in the addition (or omission) of underscores. We have already discovered that Connector/Python (C/Py) uses underscores in the names and does not use capital letters. Connector/Java (C/J), Connector/Node.js (C/Node.js), Connector/.Net (C/Net), and Connector/C++ (C/C++) use slightly different capitalization.
It is not just the method names that have different spellings. There also may be subtle differences in how you work with the results from methods or interact with objects. That is, the clients conform to the normal practices for the language for common constructs and concepts such as iteration. For example, if the language has a concept of a list for returning multiple items (say versus an array), the methods will return a list. Although this may seem strange as you read more about the X DevAPI, it does pay benefits. That is, the resulting code you write is compliant with your choice of language standards.
MySQL X Module
Returns | Name | Method | Language | Parameters |
|---|---|---|---|---|
Session Object | MysqlxSessionFactory | getSession() | Java | Connection URI or connection properties |
mysqlx | getSession() | Node.js | Connection URI or connection properties | |
MySQLX | GetSession() | DotNet | Connection URI or connection data object | |
mysqlx | get_session() | Python | Connection dictionary |
There is one other difference you may notice when exploring the X DevAPI. The clients that implement the API have some very different mechanisms for how to work with data. In some cases, such as C/Net, everything is a class and it is common to use a class to contain data but in C/Py, the use of lists and dictionaries are preferred. Thus, the clients (specifically the database connectors) may implement some of the mechanisms for iteration, retrieval, and encapsulation differently. However, as with the naming conventions, the differences are for the benefit of the developer so that the X DevAPI “works” the way it should in the target language.
Session—Create Schema Method
Description | Returns | Language | Method | Parameters |
|---|---|---|---|---|
Create a new schema | Schema object | Java/Node.js | createSchema() | String—schema name |
DotNet | CreateSchema() | String—schema name | ||
Python | create_schema() | String—schema name |
In the next section, we examine the major code module, named mysqlx, for the X DevAPI.
Note
The code examples in this chapter are written in Python as scripts that use the Connector/Python database connector. Thus, you will need the connector installed to use these examples. Finally, to run the examples, you execute them with the python command like this: python ./script1.py.
The mysqlx module (sometimes called a package) works with a Session (X Protocol). There is also a module for working with InnoDB Cluster (named dba), and several common classes including those for columns, rows, and so forth.
Note
This chapter contains a lot of information about objects and classes. Objects are an instance of a code class (at execution) and a class is simply the code construct.
MySQL X Module
The mysqlx module is the entry point for writing your document store applications and communicating with the X DevAPI. We use this module to pass connection information to the server in the form of a connection string or a language-specific construct (e.g., a dictionary in Python) to pass the connection parameters either as a URI or a connection dictionary as the parameter (not both). Recall that a uniform resource identifier (URI) a special string coded uses the following format:
Note that the password, port, and schema are optional but the user and host are required. Schema in this case is the default schema (database) that you want to use when connecting. The method to get a session object is shown in the following.
The following shows examples of getting a session object instance using a dictionary of connection options and getting a session object instance using a connection string (URI) .
The resulting variable will point to an object instance should the connection succeed. If it fails, you could get an error or an uninitialized connection as the result. We will see more about checking errors in a later section.
In the next section, we begin our exploration of the classes and methods (components) in the X DevAPI.
Classes and Methods
The following sections examine each of the major classes and their methods (features) for the mysqlx module. These classes are only accessible from a Session object—the same returned from the get_session( ) method. Because this book is about the document store, we focus on those classes for the mysqlx module.
We will discover the methods including classes and for working with schemas (databases), managing transactions, and checking or closing the connection. The material presented includes the most frequently used classes and methods grouped by use or application rather than a strict hierarchy. This allows for a shorter overview that follows a more logical path exploring the API. If you want to see all the details of the modules and classes as well as the raw Doxygen documentation for the code, see the “For More Information” section at the end of the chapter for links to the API documentation for each database connector. I include examples in this chapter that illustrates many of the methods presented.
Objects in the mysqlx Module
Area | Method | Description |
|---|---|---|
Connection | Session | Enables interaction with an X Protocol enabled MySQL Product |
CRUD | Schema | A client-side representation of a database schema; provides access to the schema contents |
Collection | Represents a collection of documents on a schema | |
Table | Represents a database table on a schema | |
View | Represents a database view on a schema | |
Result | ColumnMetaData | Returns metadata on the columns |
Row | Represents a row element returned from a SELECT query | |
Result | Allows retrieving information about nonquery operations performed on the database | |
BufferingResult | Provides base functionality for buffering result objects | |
RowResult | Allows traversing the Row objects returned by a Table.select operation | |
SqlResult | Represents a result from a SQL statement | |
Statement | DbDoc | Represents a generic document in JSON format |
Statement | Provides base functionality for statement objects | |
FilterableStatement | A statement to be used with filterable statements | |
SqlStatement | A statement for SQL execution | |
FindStatement | A statement document selection on a collection | |
AddStatement | A statement for document addition on a collection | |
RemoveStatement | A statement for document removal from a collection | |
ModifyStatement | A statement for document update operations on a collection | |
SelectStatement | A statement for record retrieval operations on a table | |
InsertStatement | A statement for insert operations on table | |
DeleteStatement | A statement that drops a table | |
UpdateStatement | A statement for record update operations on a table | |
CreateCollectionIndexStatement | A statement that creates an index on a collection | |
ReadStatement | Provide base functionality for read operations | |
WriteStatement | Provide common write operation attributes | |
Errors | DataError | Exception for errors reporting problems with processed data |
DatabaseError | Exception for errors related to the database | |
Error | Exception that is base class for all other error exceptions | |
IntegrityError | Exception for errors regarding relational integrity | |
InterfaceError | Exception for errors related with the interface | |
InternalError | Exception for internal database errors | |
NotSupportedError | Exception for errors when an unsupported database feature was used | |
OperationalError | Exception for errors related to a database operation | |
PoolError | Exception for errors relating to connection pooling | |
ProgrammingError | Exception for errors programming errors |
Let’s begin our tour of the X DevAPI with the Session class.
Session Class
The Session class is the major class we will use to begin working with a document store. Once we have a connection, the next step is to get the session object. From there, we can begin working with the document store. The following is a tour of the classes and methods grouped by area and application. We start with the schema methods.
Schema Methods
The X DevAPI uses the term schema to refer to a set of collections; the collections are a collection of documents. However, when working with relational data, we use “database” to refer to a collection of tables and similar objects. One may be tempted to conclude “schema” is synonymous with “database” and for older versions of MySQL that is true. However, when working with the document store and the X DevAPI, you should use “schema” and when you refer to relational data, you should use “database.”
Schema or Database: Does It Matter?
Since MySQL 5.0.2, the two terms have been synonyms via the CREATE DATABASE and CREATE SCHEMA SQL commands. However, other database systems make a distinction. That is, some state a schema is a collection of tables and a database is a collection of schemas. Others state a schema is what defines the structure of data. If you use other database systems, be sure to check the definitions so that you use the terms correctly.
Session Class—Schema Methods
Method | Returns | Description |
|---|---|---|
create_schema(str name) | Schema | Creates a schema on the database and returns the corresponding object |
get_schema(str name) | Schema | Retrieves a schema object from the current session through its name |
get_default_schema() | Schema | Retrieves the schema configured as default for the session |
drop_schema(str name) | None | Drops the schema with the specified name |
Listing 5-1 shows an example of how you can work with session objects to create a schema object. Once again, we will expand on this example as we examine more classes and methods. In this case, we use the session object to work with a schema.
Working with Schemas
Note the code to retrieve a schema that doesn’t exist. I use a method of the schema object to check to see if it exists then print out the result. Assuming the schema not_there! doesn't exist, the code will print “False.” Finally, I create the schema test_schema at the end of the code. We will see the schema class in more detail in a later section as well as a better way to check to see if a schema exists. If you save this code to a file named listing5-1.py and execute it, you will see output like the following.
Let us now look at the transactional methods for performing ACID compliant transactions.
Transaction Methods
Transactions provide a mechanism that permits a set of operations to execute as a single atomic operation. For example, if a database were built for a banking institution, the macro operations of transferring money from one account to another would preferably be executed completely (money removed from one account and placed in another) without interruption.
Transactions permit these operations to be encased in an atomic operation that will back out any changes should an error occur before all operations are complete, thus avoiding data being removed from one table and never making it to the next table. A sample set of operations in the form of SQL statements encased in transactional commands is the following:
MySQL’s InnoDB storage engine (the default storage engine) supports ACID transactions that ensure data integrity with the ability to only commit (save) the resulting changes if all operations succeed or rollback (undo) the changes if any one of the operations fail.
What is Acid?
ACID stands for atomicity, consistency, isolation, and durability. Perhaps one of the most important concepts in database theory, it defines the behavior that database systems must exhibit to be considered reliable for transaction processing.
Atomicity means that the database must allow modifications of data on an “all or nothing” basis for transactions that contain multiple commands. That is, each transaction is atomic. If a command fails, the entire transaction fails, and all changes up to that point in the transaction are discarded. This is especially important for systems that operate in highly transactional environments, such as the financial market. Consider for a moment the ramifications of a money transfer. Typically, multiple steps are involved in debiting one account and crediting another. If the transaction fails after the debit step and doesn’t credit the money back to the first account, the owner of that account will be very angry. In this case, the entire transaction from debit to credit must succeed, or none of it does.
Consistency means that only valid data will be stored in the database. That is, if a command in a transaction violates one of the consistency rules, the entire transaction is discarded, and the data is returned to the state they were in before the transaction began. On the other hand, if a transaction completes successfully, it will alter the data in a manner that obeys the database consistency rules.
Isolation means that multiple transactions executing at the same time will not interfere with one another. This is where the true challenge of concurrency is most evident. Database systems must handle situations in which transactions cannot violate the data (alter, delete, etc.) being used in another transaction. There are many ways to handle this. Most systems use a mechanism called locking that keeps the data from being used by another transaction until the first one is done. Although the isolation property does not dictate which transaction is executed first, it does ensure they will not interfere with one another.
Durability means that no transaction will result in lost data nor will any data created or altered during the transaction be lost. Durability is usually provided by robust backup-and-restore maintenance functions. Some database systems use logging to ensure that any uncommitted data can be recovered on restart.
Transaction Methods
Method | Returns | Description |
|---|---|---|
start_transaction() | None | Starts a transaction context on the server |
commit() | None | Commits all the operations executed after a call to startTransaction() |
rollback() | None | Discards all the operations executed after a call to startTransaction() |
set_savepoint(str name="") | str | Creates or replaces a transaction savepoint with the given name |
release_savepoint(str name) | None | Removes a savepoint defined on a transaction |
rollback_to(str name) | None | Rolls back the transaction to the named savepoint without terminating the transaction |
Note that the last three methods allow you to create a named transaction savepoint, which is an advanced form of transaction processing. See the online MySQL reference manual for more information about savepoints and transactions.
We will see an example of transactions later in this chapter. Now, let’s look at the methods that concern the connection to the server.
Connection Methods
Connection Methods
Method | Returns | Description |
|---|---|---|
close() | None | Closes the session |
is_open() | Bool | Returns true if session is known to be open |
The following shows how to use these methods if you want to check the connection as an extra step in your application.
Working with Sessions
If you save this code to a file named listing5-2.py and execute it, you will see output like the following.
Miscellaneous Methods
Miscellaneous Methods
Method | Returns | Description |
|---|---|---|
Is_open() | Bool | True if the connection is open and active |
sql(str sql) | SqlStatement | Creates a SqlStatement object to allow running the received SQL statement on the target MySQL server |
CRUD Operations
The X DevAPI implements a create, read, update, and delete (CRUD) model for working with the objects that are contained in a schema. A schema can contain any number of collections, documents, tables, views, and other relational data objects (i.e., triggers). In this section, we see an overview of the schema, collection, tables (relational data), and data sets. The CRUD model is implemented for all objects in the schema that can contain data for both document store and relational data.
Most of the examples in the book up to this point have used relational data for demonstration because most readers are familiar with working with SQL. This chapter continues the discussion from Chapter 3 to complete the introduction to working with the X DevAPI to build document store applications.
CRUD Operations for Document Store and Relational Data
CRUD Operation | Description | Document Store | Relational Data |
|---|---|---|---|
Create | Add a new item/object | collection.add() | table.insert() |
Read | Retrieve/search for data | collection.find() | table.select() |
Update | Modify data | collection.modify() | table.update() |
Delete | Remove item/object | collection.remove() | table.delete() |
We will see the methods specific to each class (Schema, Collection, Table, and View) in the following sections. Let’s begin with a look at the details of the Schema class.
Schema Class
The schema is a container for the objects that store your data. Recall that this can be a collection for document store data or a table or view for relational data. Much like the old days working with relational data, you must select (or use) a schema for storing data in either a collection, table, or view.
Although you can mix the use of document store data (collections) and relational data (tables, views), to keep things easy to remember, we will examine the Schema class methods as they pertain to each in turn starting with the document store methods.
Schema Class—Document Store and Table Methods
Method | Returns | Description |
|---|---|---|
get_tables() | List | Returns a list of tables for this schema |
get_collections() | List | Returns a list of collections for this schema |
get_table(str name) | Table | Returns the table of the given name for this schema |
get_collection(str name) | Collection | Returns the collection of the given name for this schema |
get_collection_as_table(str name) | Table | Returns a Table object representing a collection on the database |
create_collection(str name) | Collection | Creates in the current schema a new collection with the specified name and retrieves an object representing the new collection created |
Now, let’s continue our example and show some of the Schema methods for working with collections in action. Listing 5-3 shows how to create a schema and create several collections then list the collections in the schema. Note that I use the name property of the collection object.
Collection Methods
If you save this code to a file named listing5-3.py and execute it, you will see output like the following.
Note that in the table there is a method to retrieve a document as a relational table. This method, get_collection_as_table() allows developers who want to store standard SQL columns with documents can convert (cast) a collection to a table. That is, the collection can be fetched as a table object, which then behaves as a normal relational table. Accessing data in the table object using CRUD operations use the following syntax.
This syntax is supported by most connectors.1 You can form complex document paths (like those we saw in Chapter 3) as well.
The reason we need this syntax is because a collection returned as a table results in a table with only two fields: doc and _id, where doc is where the document is store and _id is the document id. Listing 5-4 shows how to use this syntax.
Collection as Table Example
If you save this code to a file named listing5-4.py and execute it, you will see output like the following.
Collection Class
Collection Class
Method | Returns | Description |
|---|---|---|
add(*values) | AddStatement | Inserts one or more documents into a collection |
find(str search_condition) | FindStatement | Retrieves documents from a collection, matching a specified criterion |
remove(str search_condition) | RemoveStatement | Creates a document deletion handler |
modify(str search_condition) | ModifyStatement | Modifies documents matching a specified criterion |
drop_index(str name) | None | Drops an index from a collection |
replace_one(str id, document doc) | Result | Replaces an existing document with a new document |
add_or_replace_one(str id, document doc) | Result | Replaces or adds a document in a collection |
remove_one(str id) | Result | Removes document with the given _id value |
get_one(str id) | Document | Fetches the document with the given _id from the collection |
Note one thing about this table that each of the CRUD operations returns an object instance for the operation. For example, the find() method returns a FindStatement object. As you may surmise, this means that the resulting object instance has methods we can use to do more with the statement. We will see those classes and methods next. For now, let’s see an example using the base CRUD operations.
Now that we have enough knowledge about the X DevAPI, we can start reviewing examples that are more complete. That is, examples that do something with data. Listing 5-5 shows a complete Python script that demonstrates how to work with a collection. I include the session code and connection error handling as we’ve seen previously. The example is a simple document store for recoding information about pets.
CRUD Example Using a Collection
The script creates a new schema then creates a new collection named animals and within the schema a collection named pets_json. The script then adds several documents (pets) to the collection. To demonstrate the find operation, the script calls the find() method on the pets collection looking for all of the fish. That is, a document that has a type equal to ‘fish’. We will see more about expressions you can use in the find() method in a later section.
If you save this code to a file named listing5-5.py and execute it, you will see output like the following. We found the fish!
Rather than issue a separate add() method for each document, you can add more than one document at the same time by combining the data in a list (array). This is like using a bulk insert option for relational data. The following code is equivalent to the five add() method calls above.
Note the syntax used inside the add() method. This is a special notation that all document store class methods use for specifying JSON documents and listing expressions. In this example, the syntax is optional syntax and is normally used to specify multiple documents. That is, you enclose the documents inside [] comma-separated as in the following. In this case, I am adding two documents with one method call. Thus, for one document, the [] are optional.
Although this streamlines the code a bit, there may be a reason you would want to add a document at a time. For example, if you need to use the resulting object returned from the add() method to get more information or check warnings, you may want to add one document at a time.
Classes for CRUD Operations for Document Store Data
Class | Method | Returns | Description |
|---|---|---|---|
AddStatement | A statement for document addition on a collection | ||
add(*values) | AddStatement | Adds a list of documents into a collection | |
execute() | Result | Executes the statement | |
get_values() | list | Returns the list of values | |
is_doc_based() | bool | Checks if it is document based | |
is_upsert() | bool | Returns true if it’s an upsert | |
schema | Schema | The Schema object | |
target | object | The database object target | |
upsert(val=True) | Sets the upset flag to the boolean of the value provided | ||
FindStatement | Find documents in a collection | ||
bind(*args) | FilterableStatement | Binds a value to a specific placeholder | |
execute() | Result | Executes the statement | |
fields(*fields) | FindStatement | Sets a document field filter | |
get_binding_map() | dict | Returns the binding map dictionary | |
get_bindings() | list | Returns the bindings list | |
get_grouping() | list | Returns the grouping expression list | |
get_having() | object | Returns the having expression | |
get_limit_offset() | int | Returns the limit offset | |
get_limit_row_count() | int | Returns the limit row count | |
get_projection_expr() | object | Returns the projection expression | |
get_sort_expr() | object | Returns the sort expression | |
get_where_expr() | object | Returns the where expression | |
group_by(*fields) | ReadStatement | Sets a grouping criterion for the resultset | |
having(condition) | ReadStatement | Sets a condition for records to be considered in aggregate function operations | |
is_doc_based() | bool | Checks if it is document based | |
is_lock_exclusive() | bool | Returns true if is EXCLUSIVE LOCK | |
is_lock_shared() | bool | Returns true if is SHARED LOCK | |
limit(row_count, offset=0) | FilterableStatement | Sets the maximum number of records or documents to be returned | |
lock_exclusive() | ReadStatement | Executes a read operation with EXCLUSIVE LOCK; only one lock can be active at a time | |
lock_shared() | ReadStatement | Executes a read operation with SHARED LOCK; only one lock can be active at a time | |
schema | Schema | The Schema object | |
sort(*sort_clauses) | FilterableStatement | Sets the sorting criteria | |
target | object | The database object target | |
where(condition) | FilterableStatement | Sets the search condition to filter | |
ModifyStatement | Modify documents in a collection | ||
array_append(doc_path, value) | ModifyStatement | Inserts a value into a specific position in an array attribute in documents of a collection | |
array_insert(field, value) | ModifyStatement | Inserts a value into the specified array in documents of a collection | |
bind(*args) | FilterableStatement | Binds a value to a specific placeholder | |
change(doc_path, value) | ModifyStatement | Adds an update to the statement setting the field, if it exists at the document path, to the given value | |
execute() | Result | Executes the statement. | |
get_binding_map() | dict | Returns the binding map dictionary | |
get_bindings() | list | Returns the bindings list | |
get_grouping() | list | Returns the grouping expression list | |
get_having() | object | Returns the having expression | |
get_limit_offset() | int | Returns the limit offset | |
get_limit_row_count() | int | Returns the limit row count | |
get_projection_expr() | object | Returns the projection expression | |
get_sort_expr() | object | Returns the sort expression | |
get_update_ops() | list | Returns the list of update operations | |
get_where_expr() | object | Returns the where expression | |
is_doc_based() | bool | Checks if it is document based | |
limit(row_count, offset=0) | FilterableStatement | Sets the maximum number of records or documents to be returned | |
patch(doc) | ModifyStatement | Inserts a value into a specific position in an array attribute in documents of a collection | |
schema | Schema | The Schema object | |
set(doc_path, value) | ModifyStatement | Sets or updates attributes on documents in a collection. | |
sort(*sort_clauses) | FilterableStatement | Sets the sorting criteria. | |
target | object | The database object target | |
unset(*doc_paths) | ModifyStatement | Removes attributes from documents in a collection | |
where(condition) | FilterableStatement | Sets the search condition to filter | |
RemoveStatement | Remove documents from a collection | ||
bind(*args) | FilterableStatement | Binds a value to a specific placeholder | |
execute() | Result | Executes the statement | |
get_binding_map() | dict | Returns the binding map dictionary | |
get_bindings() | list | Returns the bindings list | |
get_grouping() | list | Returns the grouping expression list | |
get_having() | object | Returns the having expression | |
get_limit_offset() | int | Returns the limit offset | |
get_limit_row_count() | int | Returns the limit row count | |
get_projection_expr() | object | Returns the projection expression | |
get_sort_expr() | object | Returns the sort expression | |
get_where_expr() | object | Returns the where expression | |
is_doc_based() | bool | Checks if it is document based | |
limit(row_count, offset=0) | FilterableStatement | Sets the maximum number of records or documents to be returned | |
schema | Schema | The Schema object | |
sort(*sort_clauses) | FilterableStatement | Sets the sorting criteria | |
target | object | The database object target | |
where(condition) | FilterableStatement | Sets the search condition to filter | |
Note that we now see there is more you can do than simply call the add(), find(), modify(), and remove() methods. Because each of these returns an object instance of another class, we can either use a variable to store the object instance and then if you need to specify additional information for the operation, we can call the appropriate method of the new object.
Common Methods for Searching Documents
Method | Description | Used By |
|---|---|---|
[.fields(...)] | This function sets the fields to be retrieved from each document matching the criteria on this find operation. | find(), |
[.group_by(...)[.having(searchCondition)]] | Sets a grouping criteria for the result set. The having clause sets a condition for records to be considered in aggregate function operations. | find(), |
[.sort(...)] | If used, the operation will return the records sorted with the defined criteria. | find(), remove(), modify() |
[.limit(numberOfRows) | If used, the operation will return at most numberOfRows documents. | find(), remove(), modify() |
[.bind(placeHolder, value)[.bind(...)]] | Binds a value to a specific placeholder used on this object | find(), remove(), modify() |
execute() | Executes the operation with all the configured options | add(), find(), remove(), modify() |
[.set(...)] | Adds an operation into the modify handler to set an attribute on the documents that were included on the selection filter and limit | modify() |
[.unset(String attribute)] | Removes attributes from documents in a collection | modify() |
[.patch(...)] | Performs modifications on a document based on a patch JSON object | modify() |
[.array_insert(...)] | Adds an operation into the modify handler to insert a value into an array attribute on the documents that were included on the selection filter and limit | modify() |
[.array_append(...)] | Adds an operation into the modify handler to append a value into an array attribute on the documents that were included on the selection filter and limit | modify() |
For example, suppose we want to limit the fields for the find() call in the example code used in Listing 5-5. That is, we only want the name and breed of the pet that meets the criteria. We can use the fields() method of the FindStatement class to project the correct fields. Listing 5-6 shows the code to do this.
Demonstration of the FindStatement Class
Note the find() method, here we see once again the use of [] to specify a list. In this case, it is a list of fields for the operations. This is a common syntax that you will see in many of the CRUD methods.
If you save this code to a file named listing5-6.py and execute it, you will see output like the following.
Note also that we have set a variable to receive the object instance from each method. However, we can chain these methods into a single line of code as follows. Just replace the three lines in Listing 5-6 with the one chained method call.
Although these new classes may seem a lot of extra work, as you become more accustomed to using them, they will become more intuitive. Indeed, if you are used to working with relational data, some of the methods may seem familiar in concept.
Note also that some of the methods allow you to pass in conditions, which are expressions that you can build to form criteria for the operation. We will discuss expressions in a later section. Now, let’s look at the Table class.
Table Class
Table Class
Method | Returns | Description |
|---|---|---|
am_i_real() | bool | Verifies if this object exists in the database |
count() | int | Counts the rows in the table. |
delete(condition=None) | DeleteStatement | Creates a new mysqlx.DeleteStatement object |
exists_in_database() | bool | Verifies if this object exists in the database |
get_connection() | Connection | Returns the underlying connection |
get_name() | String | Returns the name of this database object |
get_schema() | Schema | Returns the schema object of this database object |
insert(*fields) | InsertStatement | Creates a new mysqlx.InsertStatement object |
is_view() | bool | Determines if the underlying object is a view or not |
name | str | The name of this database object |
schema | Schema | The Schema object |
select(*fields) | SelectStatement | Creates a new mysqlx.SelectStatement object |
update() | UpdateStatement | Creates a new mysqlx.UpdateStatement object |
who_am_i() | String | Returns the name of this database object |
Note that there aren’t methods for creating the table. We must use the CREATE TABLE SQL command to do this or the sql() method to execute the SQL statement. In fact, there are no methods to create any relational data objects. You must use SQL to issue the appropriate create statement to create the objects. For example, to create a table for our pets data in the previous example, we can use the following CREATE TABLE statement.
Tip
There are no create methods to create table or views. You must pass the SQL command to the sql() method to create these (and other relational data) objects.
Let’s take the script from the previous document store example and rewrite it to use relational data. In this case, I create a new table named pets_sql in the schema named animals and insert a few rows then select one of them. Listing 5-7 shows the code for this example.
CRUD Example Using a Table
If you save this code to a file named listing5-7.py and execute it, you will see output like the following.
Although I put the CREATE TABLE statement in the example code, it is not normal practice to do so. In fact, most developers will create the table separately from the application. That is, they would execute the CREATE SQL statements manually (or possibly through a DevOps2 tool) and not include them in the application. However, there are some arguments for using temporary tables in which case you would likely include those in the application, but in general, permanent database objects are created separately from the application. The next example shows how to get the table from an existing schema.
Note that there are some interesting new method calls. First, unlike the add() method for collections, the insert() method uses additional chained methods. In this case, we needed the values() method to add the values. This is because the insert() method returns an instance of the InsertStatement class.
This may seem strange until you consider the syntax for the SQL INSERT statement. In particular, the equivalent statements in SQL for these operations are as follows. As you can see, we have a VALUES clause.
Classes for CRUD Operations for Relational Data
Class | Method | Returns | Description |
|---|---|---|---|
SelectStatement | A statement for record retrieval operations on a table. | ||
bind(*args) | FilterableStatement | Binds a value to a specific placeholder | |
execute() | Result | Executes the statement | |
get_binding_map() | dict | Returns the binding map dictionary | |
get_bindings() | list | Returns the bindings list | |
get_grouping() | list | Returns the grouping expression list | |
get_having() | object | Returns the having expression | |
get_limit_offset() | int | Returns the limit offset | |
get_limit_row_count() | int | Returns the limit row count | |
get_projection_expr() | object | Returns the projection expression | |
get_sort_expr() | object | Returns the sort expression | |
get_sql() | String | Returns the generated SQL | |
get_where_expr() | object | Returns the where expression | |
group_by(*fields) | ReadStatement | Sets a grouping criterion for the resultset | |
having(condition) | ReadStatement | Sets a condition for records to be considered in aggregate function operations | |
is_doc_based() | bool | Checks if it is document based | |
is_lock_exclusive() | bool | Returns true if is EXCLUSIVE LOCK | |
is_lock_shared() | bool | Returns true if is SHARED LOCK | |
limit(row_count, offset=0) | FilterableStatement | Sets the maximum number of records or documents to be returned | |
lock_exclusive() | ReadStatement | Executes a read operation with EXCLUSIVE LOCK; only one lock can be active at a time | |
lock_shared() | ReadStatement | Executes a read operation with SHARED LOCK; only one lock can be active at a time | |
order_by(*clauses) | SelectStatement | Sets the order by criteria. | |
schema | Schema | The Schema object | |
sort(*sort_clauses) | FilterableStatement | Sets the sorting criteria | |
target | object | The database object target | |
where(condition) | FilterableStatement | Sets the search condition to filter | |
InsertStatement | A statement for insert operations on table | ||
execute() | Result | Executes the statement | |
get_values() | list | Returns the list of values | |
is_doc_based() | bool | Checks if it is document based | |
is_upsert() | bool | Returns true if it’s an upsert | |
schema | Schema | The Schema object | |
target | object | The database object target | |
upsert(val=True) | Sets the upsert flag to the boolean of the value provided; setting of this flag allows updating of the matched rows/documents with the provided value | ||
values(*values) | InsertStatement | Sets the values to be inserted | |
UpdateStatement | A statement for record update operations on a table | ||
bind(*args) | FilterableStatement | Binds a value to a specific placeholder | |
execute() | Result | Executes the statement | |
get_binding_map() | dict | Returns the binding map dictionary | |
get_bindings() | list | Returns the bindings list | |
get_grouping() | list | Returns the grouping expression list | |
get_having() | object | Returns the having expression | |
get_limit_offset() | int | Returns the limit offset | |
get_limit_row_count() | int | Returns the limit row count | |
get_projection_expr() | object | Returns the projection expression | |
get_sort_expr() | object | Returns the sort expression | |
get_update_ops() | list | Returns the list of update operations | |
get_where_expr() | object | Returns the where expression | |
is_doc_based() | bool | Checks if it is document based | |
limit(row_count, offset=0) | FilterableStatement | Sets the maximum number of records or documents to be returned | |
schema | Schema | The Schema object | |
set(field, value) | UpdateStatement | Updates the column value on records in a table | |
sort(*sort_clauses) | FilterableStatement | Sets the sorting criteria | |
target | object | The database object target | |
where(condition) | FilterableStatement | Sets the search condition to filter | |
DeleteStatement | A statement that drops a table | ||
bind(*args) | FilterableStatement | Binds a value to a specific placeholder | |
execute() | Result | Executes the statement | |
get_binding_map() | dict | Returns the binding map dictionary | |
get_bindings() | list | Returns the bindings list | |
get_grouping() | list | Returns the grouping expression list | |
get_having() | object | Returns the having expression | |
get_limit_offset() | int | Returns the limit offset | |
get_limit_row_count() | int | Returns the limit row count | |
get_projection_expr() | object | Returns the projection expression | |
get_sort_expr() | object | Returns the sort expression | |
get_where_expr() | object | Returns the where expression | |
is_doc_based() | bool | Checks if it is document based | |
limit(row_count, offset=0) | FilterableStatement | Sets the maximum number of records or documents to be returned | |
schema | Schema | The Schema object | |
sort(*sort_clauses) | FilterableStatement | Sets the sorting criteria | |
target | object | The database object target | |
where(condition) | FilterableStatement | Sets the search condition to filter | |
Before we proceed, let us review the sample data needed to execute the examples in the rest of this chapter.
What About Classicsession?
If you’ve read the documentation for the MySQL Shell, you may have encountered a global object named mysqlx, which mirrors the mysqlx module. You also may have encountered a session object named ClassicSession that exists in the mysql global object. This object is only available via the MySQL Shell and is not to be confused with the module named mysql in the Connector/Python code—they are not the same. In fact, the X DevAPI does not have any objects named ClassicSession.
close(): Closes the internal connection to the MySQL server held on this session object.
start_transaction(): Starts a transaction context on the server.
commit() : Commits all the operations executed after a call to startTransaction().
rollback(): Discards all the operations executed after a call to startTransaction().
get_uri(): Retrieves the URI string.
run_sql(str query, list args=[]): Executes a query and returns the corresponding ClassicResult object.
query(str query, list args=[]): Executes a query and returns the corresponding ClassicResult object.
is_open(): Returns True if the session is open.
Once again, these methods are for the ClassicSession class, which is only available through the MySQL Shell. This brief sidebar was included for completeness and to clarify the origins of the class.
Example Data Used in this Chapter
The example code for the rest of this chapter uses data we created in the previous examples. I include it here for your convenience. More specific, I include the SQL statements for creating the relational data and a short script for creating the document store data. Listing 5-8 is the code needed for creating the sample document store.
Sample Document Store
You may note that this resembles many of the previous listings. However, because from this point on we will be using the animals schema, we have omitted the drop_schema( ) call at the end.
Listing 5-9 includes the SQL statements for creating the sample relational data.
Sample Relational Data
Although the preceding examples create these objects, you may want to refer to this section when experimenting with the examples and when running the examples later in the chapter.
Now let’s see the classes for working with results and data sets from the find(), select(), and other methods that return results.
Working with Data Sets
Result Classes (Object Instances) Returned
Object Instance | Origin | Description | Content Returned |
|---|---|---|---|
Result | Create, update, delete | Returned by add().execute(), modify().execute(), remove().execute() | affected_item_count, auto_increment_value, last_document_id |
SqlResult | Session | Returned by session.sql() | auto_increment_value, affected_row_count, fetched data – data set |
RowResult | Relational Data select | Returned by select().execute() | fetched data—data set |
Note that the content column shows either a result or data set as the content returned. The X DevAPI uses the term data set to refer to the data returned from the read CRUD operation (find(), select(), and sql() methods) and result3 to refer to the data returned from a create, update and delete CRUD operation.
Also, note that there are different objects returned for each of the class of operations. The classes RowResult and SqlResult inherit from a base class (BaseResult) and thus have a lot of the same methods. What sets these apart from the Result class returned from the create, update, and delete operations is the Result class does not support an iterator. This is because a Result object contains the data returned from the server pertaining to the create, update, and delete operations, which do not return any data but may return warnings and similar metadata and is equivalent to the results returned from traditional SQL INSERT, UPDATE, and DELETE statements in MySQL.
Classes and Methods for Working with Data Sets and Results
Class | Method | Returns | Description |
|---|---|---|---|
RowResult | Allows traversing the Row objects returned by a Table.select operation | ||
columns | list | The list of columns | |
count | int | The total of items | |
fetch_all() | list | Fetches all items | |
fetch_one() | mysqlx.Row or mysqlx.DbDoc | Fetches one item | |
get_warnings() | list | Returns the warnings | |
get_warnings_count() | int | Returns the number of warnings | |
index_of(col_name) | int | Returns the index of the column | |
set_closed(flag) | Sets if resultset fetch is done | ||
set_generated_id(generated_id) | Sets the generated ID | ||
set_has_more_results(flag) | Sets if has more resultsets | ||
set_rows_affected(total) | Sets the number of rows affected | ||
SqlResult | Represents a result from a SQL statement | ||
columns | list | The list of columns | |
count | int | The total of items | |
fetch_all() | list | Fetches all items | |
fetch_one() | mysqlx.Row or mysqlx.DbDoc | Fetches one item | |
get_autoincrement_value() | string | Returns the identifier for the last record inserted | |
get_warnings() | list | Returns the warnings | |
get_warnings_count() | int | Returns the number of warnings | |
index_of(col_name) | int | Returns the index of the column | |
next_result() | bool | Processes the next result | |
set_closed(flag) | Sets if resultset fetch is done | ||
set_generated_id(generated_id) | Sets the generated ID | ||
set_has_more_results(flag) | Sets if has more resultsets | ||
BufferingResult | Provides base functionality for buffering result objects | ||
count | int | The total of items | |
fetch_all() | list | Fetches all items | |
fetch_one() | mysqlx.Row or mysqlx.DbDoc | Fetches one item | |
get_warnings() | list | Returns the warnings | |
get_warnings_count() | int | Returns the number of warnings | |
index_of(col_name) | int | Returns the index of the column | |
set_closed(flag) | Sets if resultset fetch is done | ||
set_generated_id(generated_id) | Sets the generated ID | ||
set_has_more_results(flag) | Sets if has more resultsets | ||
set_rows_affected(total) | Sets the number of rows affected | ||
Result | Allows retrieving information about nonquery operations performed on the database | ||
append_warning(level, code, msg) | Appends a warning | ||
get_affected_items_count() | int | Returns the number of affected items for the last operation | |
get_autoincrement_value() | int | Returns the last insert id auto generated | |
get_document_id() | String | Returns ID of the last document inserted into a collection | |
get_document_ids() | list | Returns the list of generated documents IDs | |
get_warnings() | list | Returns the warnings | |
The three classes that have iterators implement two methods: fetch_one() and fetch_all(). They work as you would imagine and return either a data set or a set of objects for a set of documents. The fetch_one() method returns the next data item in the data set or NULL if there are no more data items and fetch_all() returns all the data items. More specific, fetch_one() retrieves one data item at a time from the server whereas fetch_all() retrieves all the data from the server in one pass. Which one you would use depends on the size of the data set and how you would want to process the data.
Note
Once you fetch a data item, you cannot fetch it again. That is, the iterators are forward only.
Before we look at how to access data in the data set, let us review document identifiers and auto increment columns.
Tip
From this point on in the examples, you should have the JSON data loaded as described in Listing 5-8 and the relational data as described in Listing 5-9.
Document Identifiers
Recall each document you store in a document store collection has a document identifier (doc id or document id), which is a string of characters that uniquely identifies the document in a collection.4 You do not need to create your own document ids—they are assigned for you automatically.
There are two methods available to retrieve the document id from the Result class (the content returned for create, update, and delete operations). In particular, you can use the get_document_id() method to retrieve the last document id assigned or the get_document_ids() to return a list of the document ids for the bulk add option for the add() method as described above. Listing 5-10 demonstrates retrieving the document ids when adding documents.
Note
The listing from this point on assumes the animals collection does not exist. If you are planning to run the code examples one after another, you should add the drop_schema() call shown in Listing 5-5.
Getting Document Ids
If you run the code snippet, you will see the document ids as in the following.
Auto Increment
If you are working with relational data and have specified an auto increment field, you can retrieve the last auto increment value using the get_autoincrement_value() method of the SqlResult and Result classes. This method returns the auto increment value generated, which can be helpful if you need to retrieve the last row inserted by the surrogate primary key.
Accessing Data in Data Sets
Let us consider accessing data in data sets. In this case, we issue a find() method on a collection that returns several documents as represented by a specific result object. In this case, we have a set of DbDoc objects to fetch.
There are three ways we can access the data in the data item; we can simply get the data item as a string (naturally), we can access the data elements via a property with the name of the key for the data element, or we can use an array index to find the data element with its key. Listing 5-11 shows a complete script with an example of each mechanism. Note that you should have created the schema and collection populating it with the data using Listing 5-8.
Reading Data from a Data Set
Note how I retrieved the data set with the find().execute() method, which returns an object that I can iterate over. In this case, I fetch the first data item then a while loop to loop through the items. Inside the while loop, I print the string returned from the fetch and demonstrate how to retrieve data elements by property (e.g., res.age, res.name) or by array index using the key name (e.g., res['breed']).
If you save this code to a file named listing5-11.py and execute it, you will see output like the following.
Now lets’ see how to get rows from a relational data query.
Accessing Metadata in Results
When using relational data and the table or view select() method. This returns an SQL data set that represents the rows you would expect to get from a typical SQL SELECT query. We can then access the data in the row by column name as a property, column index number as the array index, or by column name as the array index. Listing 5-12 demonstrates both methods of getting the data from the row.
Data Set Example—Relational Data
If you save this code to a file named listing5-12.py and execute it, you will see output as the following.
Note how I retrieve the data set with the select().execute() method, which returns an object that I can iterate over. In this case, I fetch the items (rows) using a for loop. Inside the for loop, I use the Row object’s get_string() method, which takes a key name for the column or in this case column name. I use a little trick with iterating over the columns inside a nested for loop. I discuss how to work with the column metadata in the next section.
Column Metadata
ColumnMetaData Class
Method | Returns | Description |
|---|---|---|
get_schema_name() | str | Retrieves the name of the schema where the column is defined |
get_table_name() | str | Retrieves table name where the column is defined |
get_table_label() | str | Retrieves table alias where the column is defined |
get_column_name() | str | Retrieves column name |
get_column_label() | str | Retrieves column alias |
get_type() | Type | Retrieves column type |
get_length() | int | Retrieves column length |
get_fractional_digits() | int | Retrieves the fractional digits if applicable |
is_number_signed() | bool | Indicates if a numeric column is signed |
get_collation_name() | str | Retrieves the collation name |
get_character_set_name() | str | Retrieves the character set name |
Note that there are several interesting methods including those for discovering the type, character and collation, size, and more. Note also there are methods for getting the name or label of the column. The name is the name from the operation whereas the label is alias or alternative labeling specified in the operation. To see the difference, consider the following SQL statement.
When you call the get_column_name() and get_column_label() methods, you get the following values. Listing 5-13 demonstrates how to work with these methods.
Working with Column Names and Labels
If you save this code to a file named listing5-13.py and execute it, you will see output like the following.
Now let’s discuss the use of expressions to filter data.
Expressions
Expressions are another element in the X DevAPI that is a simple, yet powerful feature. Expressions are synonymous with the clauses we use in SQL statements for filtering data in CRUD statements. There are several forms of expressions. We can use strings, Boolean expressions, or embed actual expressions such as equality or inequality. Let’s examine each of these.
Expression Strings
Expression strings are those strings that need to be evaluated at runtime. Typically, they use one or more variables “bound” (called parameter binding ) to placeholders in the string. This permits you to assign values at runtime for dynamic filtering rather than static values as we will see in the next section. We will see more about parameter binding in a later section.
Listing 5-14 shows an example like one we used in a previous example looking for the fish in our pets_json collection. However, in this case, we use a parameter to contain the type, which could presumably be read at runtime and thus allow us to make our code dynamically filter the collection find results.
Expression Strings
If you save this code to a file named listing5-14.py and execute it, you will see output like the following.
Boolean Expression Strings
This form of expression uses a string much like we use in the WHERE clause for SQL statements. That is, we express the filter using natural language where the comparison is either true or false. Listing 5-15 are Boolean expression strings from previous examples. The first line is a relational data example in which we want the results to include only those items whose type column is equal to “dog.” The second is a document store example in which we want the results to include only those items whose type element has the value “fish.”
Boolean Expression Strings
If you save this code to a file named listing5-15.py and execute it, you will see output like the following.
Tip
You can find a complete set of extended Backus-Naar form5 drawings for expressions and method chaining in the X DevAPI Users guide at https://dev.mysql.com/doc/x-devapi-userguide/en/ .
Warnings and Errors
Another area in which we need to spend some time learning about is the report of warnings sent from the server and handling errors from the X DevAPI. Fortunately, the X DevAPI has facilities for getting the warnings. However, errors will take a bit more work. Let’s look at warnings first.
Warnings from the Server
Level—level of the warning
Code—warning code
Message—warning message
Note
By default, all warnings are sent from the server to the client. However, you can suppress warnings to save bandwidth. Use the set_fetch_warnings() in the Session class to control whether warnings are discarded at the server or sent to the client. Use the get_fetch_warnings() method to get the active setting.
In fact, we can use the get_warnings() method to check to see if there are warnings we need to process. However, the X DevAPI sends warnings to the client each time they occur so if you want to check for warnings, you must do so after each execution. Listing 5-16 shows one way you can write code to handle errors. It is by no means the only way, but does demonstrate the Warning class methods.
Note
This example requires the animals database setup. See the “Example Data Used in this Chapter” section previously for how to setup the database.
Processing Warnings
Note that I wrote a method named process_warnings() that takes a result object and checks to see if there are errors by calling the get_warnings_count() method. If this method returns a positive integer, it means there are warnings and if so, I get the type, code, and message from the warning object and print the data. If there are no warnings, I print a message stating there were no errors (but you probably don’t want to know that).
If you save this code to a file named listing5-16.py and execute it, you will see the following results. Note that you may have to run it a second time if you deleted the animals collection.
Now let’s see how we can handle errors from the X DevAPI.
Errors from the X DevAPI
As I mentioned, there is nothing implemented in the X DevAPI specifically for handling errors, but there are facilities that we can use. In this case, we’re going to get some help from our database connectors. That is, the database connectors implement the language-specific error handling (exception handling) mechanisms making it natural to handle errors from the X DevAPI methods. In other words, they implement exception handling.6
Using Python as an example, the Python language implements a try...exception block (sometimes called a try or exception block ). This construct allows code that “raises” an exception in the form of the raise() method to have the exception captured by the calling code (the code with the nearest try block). The syntax is as follows.
What this allows us to do is “try” an operation (or more) and if they fail by raising an exception, the code will skip any remaining operations in the try segment and skip to the except segment.
Let’s look at what happens when you do not use exception handling and the code fails. That is, the X DevAPI throws an exception. Listing 5-17 shows a simple script with errors. Can you spot them? Hint: check the password and what happens when you try to create a table that already exists?
Not Handling Errors
If you save this code to a file named listing5-17.py and execute it, you will see the following results (extraneous data removed for brevity).
Oh, dear, that’s terrible! What we’ve got here is a traceback dump, which is how Python communicates unhandled exceptions. The key message we should heed is the first line that shows us the line of code in the script that started a sequence of method calls that resulted in the exception thrown as shown in the last two lines. Here we see that the get_session() call resulted in a mysqlx.errors.InterfaceError thrown from the X Protocol code in the connector. This demonstrates how badly things can go if you do not use exception handling. But we can make it a lot better.
Let’s look at an example with exception handling. Listing 5-18 shows a script with deliberate errors that will cause the X DevAPI to throw exceptions. In this case, it is the CREATE TABLE SQL statement that will fail. More specific, it will fail because the table already exists.
If you run this script and it does not fail, be sure to check that the table already exists. We are using the fact that the table already exists so when the CREATE is executed, we will get an exception. As you will see, the exception is not easily understood either.
Handling Errors—Global Exception
When we run this code, we get a much better result. If you save this code to a file named listing5-18.py and execute it, you will see the following results. Note that the output you can expect from this improved version. It is easier to read and more informative.
Although there is no tried and true rule for how much you can place in an exception block, you should keep exception blocks small—say isolated to a single concept or process—to avoid cases where debugging the code makes it difficult to know which of the dozens of method calls triggered the exception. If you use a language like Python that throws a call stack trace, that may not be difficult but if your language doesn’t have it or rerunning the code to create one is not possible, keeping the exception blocks small can help you isolate the code where the problem occurred.
Listing 5-19 shows an example that includes try blocks around each X DevAPI statement. It also demonstrates how to capture specific exceptions thrown. That is, the except: syntax allows you to specify a specific exception. In this case, I capture the exception thrown by the X DevAPI.
Handling Errors—Local Exceptions
If you save this code to a file named listing5-19.py and execute it, you will see the following results. Be sure to enter the correct password when prompted. This is because there is only one test for a correct password. Your challenge is to determine a way to improve the code by allowing multiple retries. Hint: use a loop.
The example also shows an interesting way you can handle exceptions—retrying the statement. Normally, you would place the statement you want to retry in a loop or similar structure with a time or attempt limit. Here I just retry the session method once prompting the user for the password.
Tip
For best results, encapsulate your code using shorter exception blocks so that you can isolate the code that caused the error easily.
Now let’s look at the additional features available when using the X DevAPI.
Additional Features
Now that we have seen all the major classes and methods available in the X DevAPI, let us now examine some of the features that are exposed by the X DevAPI; specifically, examples of parameter binding, chaining methods, prepared statements, and asynchronous execution.
Note
This example uses the world_x database, which you can download from https://dev.mysql.com/doc/index-other.html . Simply download the compressed file, uncompress it, then include it in the MySQL Shell with the source command or use the mysql client and the source command. For a walk-through of how to install the world_x database, see the “Installing the Sample Database” section in Chapter 4.
Parameter Binding
Parameter binding allows us to apply values to expressions at runtime. Parameter binding is typically used for filters and is done prior to executing the operation (hence you will see .bind().execute() often). Therefore, the benefits of parameter binding are that it allows you to separate values from your expressions. This is accomplished with the bind() method for all classes that support parameter binding.
Parameters can be "bound" using one of two methods: you can use anonymous parameters or you can use named parameters. However, there are restrictions on when you can use each. In particular, anonymous parameters can only be used in SQL strings (expression) whereas named parameters are used in CRUD operations. Let's see an example of each.
Listing 5-20 shows an example of using anonymous parameters. Anonymous parameters are signified by using a question mark. Note how we do this in the SQL statements in the following.
Parameter Binding Example (MySQL Shell)
We can take away a couple of things from this example. First, anonymous parameters are only used in SQL statements. Second, anonymous parameters are completed (values provided) in the order they appear in the SQL statement. Third, and finally anonymous parameters can be used with prepared statements.7
Listing 5-21 shows several examples of using named parameters. The key point to notice is how the parameter is given a name preceded by a colon. When the bind() method is called, we supply the named parameter (without the colon) and its value.
Parameter Binding Example
Note how we pass multiple parameters to be bound. In this case, we simply call bind() as many times as we have parameters to bind. This is possible due to the method chaining feature as described in the next section. That is, the bind() method returns an instance of itself and thus when we call the next bind() method, it is repeating the call but with a different parameter and its value.
Tip
Named parameters may not start with a number. For example,:1test is not a valid named parameter name.
If you save this code to a file named listing5-21.py and execute it, you will see the following results.
Now let's look at method chaining and how it works.
Method Chaining
Method chaining (also known as named parameter idiom), is a design constraint in object-oriented programming where each method (that supports chaining) returns an instance of an object. Thus, one can access (call) any method on the returned object simply by adding the call to the end of the first method.
For example, if a class X has a method a() that returns object Y with a method b(), we can chain calls together as follows.
In this case, the x.a() method executes first, then when it returns with a Y object instance, it calls the b() method on the Y object instance.
Where method chaining shines in the X DevAPI is in the implementation of the relational data methods. In particular, those classes and methods that support the SQL CRUD commands. Listing 5-22 is an example of a complex SELECT operation for a table.
Method Chaining
If you save this code to a file named listing5-22.py and execute it, you will see the following results.
Here we see two lines of code and several object instances in use and a host of methods. On the second code line (ignoring comments), we use a mysqlx session object to get a schema object then chain that with a call to the Schema class method get_table(), which returns a table object instance.
On the third code line, we are using the table object instance calling the select() method, which returns a SelectStatement object instance that we chain by calling its where() method, which returns the same SelectStatement object and we call its order_by() method, which returns the same SelectStatement object then we bind the parameter with the bind() method that returns the same SelectStatement object, and finally we call the execute() method, which returns a SqlResult object. Wow!
If you're getting the idea that method chaining hides a lot of the details about objects and avoids repetitious code of storing object instances in variables, you're right! That's exactly what we're doing.
As you can see, method chaining allows us to express concepts in our code much more clearly that the older style of classes with methods do not return object instances (or even older styles that simply return 0 or 1 to indicate success or failure8). Mastering the X DevAPI means mastering how you can chain methods together to simplify and make your code easier to read and understand. Cool, eh?
For more information about the concepts of method chaining, see https://en.wikipedia.org/wiki/Method_chaining .
CRUD Prepared Statements
Prepared CRUD statements are cases in which we want to perform a number of operations on an object prior to calling the execute() method. In this way, we "prepare" the object instance (statement) for execution. That is, instead of directly binding and executing CRUD operations by chaining bind() and execute() or simply execute() it, we can manipulate the CRUD operation to store such things as filters and other criteria in a variable for later execution.
The advantage of doing this means we can bind several parameters or sets of variables to the expressions. This gives us better performance because we can “prepare” variables ahead of time and execute them later. This can give us get better performance when executing many similar operations.
You may be thinking that CRUD prepared statements are similar in concept to SQL prepared statements. This is true, but unlike SQL prepared statements, CRUD prepared statements are implemented in class methods and thus can be incorporated in our code with very little effort.
Let's look at an example. Listing 5-23 shows an example of preparing a CRUD statement. In this case, we prepare a find() statement using a parameter and save the result (the FindStatement object) to a variable. When we want to execute this statement, we use the variable to call the bind() method providing a value then the execute() method to execute the FindStatement.
CRUD Prepared Statements
Note the three find_pet.bind() method calls. Here we execute the find statement three times; once for each pet’s name we want to find. Clearly, this is only a small example but demonstrates the power of using CRUD prepared statements.
If you save this code to a file named listing5-23.py and execute it, you will see the following results.
Asynchronous Execution
For those clients that support asynchronous programming such as C/J, C/Node.js, and C/Net, the X DevAPI permits the use of asynchronous mechanisms such as callbacks, async() calls, and so forth. These mechanisms make it possible to allow an operation to run in parallel with other operations. Let's see an example from Java.
Note
Currently, neither the C/Py nor C/C++ permit asynchronous execution but may in the future. Check new releases of these connectors for updates.
Here we see the executeAsync() method, which is how the Java connector permits the asynchronous execution of the execute() method. That is, the select() runs asynchronously and when it returns (finishes), it triggers the future defined by the CompletableFuture template/class (or generic class in Java9).
Note
Depending on which language you are using, the X DevAPI may implement a function such as executeAsync() in addition to or instead of execute(). Check the X DevAPI documentation for your chosen connector for the correct method names and uses.
For more information about asynchronous execution, see the X DevAPI guide for the connector that matches your choice of language.
For More Information
- MySQL Shell: there are several resources available including the following
MySQL Connector/J: http://dev.mysql.com/doc/dev/connector-j/
MySQL Connector/Node.js: http://dev.mysql.com/doc/dev/connector-nodejs/
MySQL Connector/Net: http://dev.mysql.com/doc/dev/connector-net/
MySQL Connector/Python: http://dev.mysql.com/doc/dev/connector-python
MySQL Connector/C++: https://dev.mysql.com/doc/dev/connector-cpp/
Note
Some of the documentation for these components may not match the version numbers listed at the beginning of the chapter. It is fine if the documentation is for a newer release and you should install the newest releases. However, at the time of this writing, the MySQL Shell Users Guide was in the process of being updated. Check back regularly to ensure you are using the latest documentation available.
Summary
The X DevAPI is a marvel of sophistication to the point of simplification for NoSQL interface with a MySQL server. The X DevAPI introduces a new, modern and easy-to-learn way to work with your data.
The X DevAPI is the primary mechanism you will use to build document store applications. Although the X DevAPI isn’t a standalone library—you must use a client that exposes the X DevAPI through the X Protocol—the X DevAPI is still a major effort to change the way you interact with MySQL. For the first time, we now have both an SQL and a NoSQL interface for MySQL.
In this chapter, we explored the X DevAPI and examined the major classes and methods available for connecting to the MySQL server, creating collections, working with results, and even how to work with relational data. Finally, we also saw a set of quick references tables that you can use as the primary reference for developing document store applications.
In Chapter 6, we have a deep dive into the X Plugin, which will give you a better understanding of what the X Plugin does, how to configure it, and how best to manage it as part of your normal database administration tasks. Following that chapter, we will see the details of the X Protocol and later a working example of a document store application.