Chapter 6 Working with Relational Tables

Table of Contents

6.1 Syntax of the SQL CRUD Functions

The X DevAPI SQL CRUD functions allow you to work with relational tables in manners similar to using traditional SQL statements. The following code sample shows how to use the add() and select() methods of the X DevAPI SQL CRUD functions, which are similar to running INSERT and SELECT statements on a table with an SQL client. Compare this with the examples found in Section 4.3, “Collection CRUD Function Overview” to see the differences and similarities between the CRUD functions for tables and collections in the X DevAPI.

# Working with Relational Tables
from mysqlsh import mysqlx

# Connect to server using a connection URL
mySession = mysqlx.get_session( {
  'host': 'localhost', 'port': 33060,
  'user': 'user', 'password': 'password'} )

myDb = mySession.get_schema('test')

# Accessing an existing table
myTable = myDb.get_table('my_table')

# Insert SQL Table data
myTable.insert(['name','birthday','age']) \
  .values('Laurie', mysqlx.date_value(2000, 5, 27), 19).execute()

# Find a row in the SQL Table
myResult = myTable.select(['_id', 'name', 'birthday']) \
  .where('name like :name AND age < :age') \
  .bind('name', 'L%') \
  .bind('age', 30).execute()

# Print result
print(myResult.fetch_all())