The MySQL X DevApi: Working with NULL Values

There was an excellent question from Francis in my MySQL X DevAPI tutorial about how to work NULL Values:

Is it possible to store an attribute with a null value, and how to find it (xxx is null?

This may sound like a trivial question, but the answer is not so simple, so instead of just answering a direct comment, I think it’s worth covering the topic. NULL Value in X DevAPI in a separate post.

The short story

In short, there are two ways to work with “not value” in X DevPI. One way is not to set a value for fields in which case you can compare retrieved values. IS NULL As you would for a relational table, for example in MySQL Connector / Python:

# Find documents where "Birthday IS NULL"
stmt_find = my_docs.find('Birthday IS NULL')
stmt_find.fields('First_name', 'Surname', 'Birthday')
result = stmt_find.execute()

If you want the field to exist, you must set the value to JSON null Values ​​like:

john = {
    'First_name': 'John',
    'Surname': 'Doe',
    'Birthday': None,
}

Or if you want to specify a JSON document as a string:

john2 = """{
    "First_name": "John",
    "Surname": "Doe",
    "Birthday": null
}"""

You can then retrieve the value by comparing JSON null value:

# Find documents where Birthday is equal to a JSON document
# just containing a null value
stmt_find = my_docs.find("Birthday = CAST('null' AS JSON)")
stmt_find.fields('First_name', 'Surname', 'Birthday')
result = stmt_find.execute()

The remainder of this post goes through a complete example program with tips to determine the correct way to query JSON null Values.

MySQL Connector / Python Revealed

Book

If you want to know more about MySQL Connector / Python, I wrote MySQL Connector / Python Revealed Published by Apress. The book includes both the traditional Python database API (PEP 249) and X DevAPI which is as new as MySQL 8.

Available from book Apres (Print and DRM free ePub + PDF), Adventuress (Print and kindle), Barnes & Noble (Print), and others.

Example program

The code used for this blog is the Python program used mysqlx MySQL Connector / Python module. It can be downloaded here:

Example code for working with a NULL value in X DevAPIDownload

The program requires that [email protected] User exists and has access my_collections Schema:

CREATE USER [email protected] IDENTIFIED BY '[email protected]';
GRANT CREATE, INSERT, SELECT, DROP
      ON my_collections.* TO [email protected];

my_collections The schema is not allowed to exist when you execute the script. To allow you to manually explore documents from MySQL Shell, the schema is not dropped at the end of the script, so if you want to execute the script multiple times, you will need to manually release the schema:

DROP SCHEMA my_collections;

Now you can make a connection and create a document archive:

import mysqlx

connect_args = {
    'host': '127.0.0.1',
    'port': 33060,
    'user': 'pyuser',
    'password': '[email protected]',
};

# Create the database connection
db = mysqlx.get_session(**connect_args)

# Create the my_collections schema
schema = db.create_schema('my_collections')

# Create the my_docs collection in the my_collections schema
my_docs = schema.create_collection('my_docs')

Avoid

For simplicity, this argument is coded as a dictionary near the top of this script. Never hard code the password (and preferably other options as well) in your actual script.

Insert data

The insertion of the data is done in the same manner as the previous tutorial, so I will not go into details with that. In this case, there are also documents of three persons, but the birthday of two of them is unknown:

# Define three documents to insert
kate = {
    'First_name': 'Kate',
    'Surname': 'Lee',
    'Birthday': '1982-08-09',
}

john = {
    'First_name': 'John',
    'Surname': 'Doe',
    'Birthday': None,
}


jane = {
    'First_name': 'Jane',
    'Surname': 'Doe'
}

# Insert the document for Adam
db.start_transaction()
result = my_docs.add(kate).add(john).add(jane).execute()
db.commit()

It is not John and Jane Joe’s birthday here. For john doe Birthday Set to region None (Stored as JSON null value). If you specify a JSON document as a string instead of a dictionary, then you need to use null (Instead of unqualified within the string). The three documents are put together in a transaction chasing methods.

Warning

To keep the example simple, the example has no error checking and focuses on code related to working with it. NULL Values. In an actual program, please care and code defensively with error checking.

Now the question is how to retrieve the data. Let’s try some different methods.

Binding not the documents by no

In the tutorial, there was an example to find people based on their birthdays:

# Find the person born on 9 August 1982
print("")
stmt_find = my_docs.find("Birthday = :birthday")
stmt_find.fields("First_name", "Surname")
stmt_find.bind("birthday","1982-08-09")
result = stmt_find.execute()
person = result.fetch_one()
print("Person born on 9 August 1982: {First_name} {Surname}".format(**person))

So naive approach is to use the same code and just bind None Service birthday Instead of an actual birthday and hope, the MySQL connector/python handles it from there:

# Find documents by binding None
stmt_find = my_docs.find('Birthday = :birthday')
stmt_find.fields('First_name', 'Surname', 'Birthday')
stmt_find.bind('birthday', None)
result = stmt_find.execute()
print('People with Birthday bound to None:')
print('-' * 35)
for person in result.fetch_all():
    print("{First_name} {Surname} ({Birthday})".format(**person))

However, he offers nothing. For relational tables like you can not find NULL Values ​​using the equal sign. You may think that you only need to change the equal sign (=) of the NULL-safe equal operator (=<=>), However X is not supported in DevPI and will have an exception:

ValueError: Unknown token type = 27  when expecting atomic expression at 3

So, instead, let’s see what happens when you use IS NULL Expression.

Find a document using IS NULL

In relational questions, you can use IS NULL To find NULL Values, such as WHERE mycol IS NULL. Does this also work for documents with X DevPI? Let’s try:

# Find documents where "Birthday IS NULL"
stmt_find = my_docs.find('Birthday IS NULL')
stmt_find.fields('First_name', 'Surname', 'Birthday')
result = stmt_find.execute()
print('')
print('People with Birthday IS NULL:')
print('-' * 29)
for person in result.fetch_all():
    print("{First_name} {Surname} ({Birthday})".format(**person))

It returns the document for Jane Doe (ie without Birthday field):

People with Birthday IS NULL:
-----------------------------
Jane Doe (None)

So why is the document returned for John Doe? Let’s go to SQL and look at the document in more detail.

John doe document

To examine the John Doe document in more detail, you can use the MySQL Shell to query the document using SQL. To make it easier to understand what is going on, you can enable MySQL Shell to show column information. showColumnTypeInfo the option:

sql option showColumnTypeInfo true

Now get a birthday present for John Doe:

sql SELECT doc-'$.First_name' AS FirstName,
            doc-'$.Surname' AS Surname,
            doc-'$.Birthday' AS Birthday
       FROM my_collections.my_docs
      WHERE doc-'$.First_name' = 'John';
Field 1
Name:      `FirstName`
Org_name:  ``
Catalog:   `def`
Database:  ``
Table:     ``
Org_table: ``
Type:      String
DbType:    VAR_STRING
Collation: utf8mb4_bin (46)
Length:    4294967292
Decimals:  0
Flags:

Field 2
Name:      `Surname`
Org_name:  ``
Catalog:   `def`
Database:  ``
Table:     ``
Org_table: ``
Type:      String
DbType:    VAR_STRING
Collation: utf8mb4_bin (46)
Length:    4294967292
Decimals:  0
Flags:

Field 3
Name:      `Birthday`
Org_name:  ``
Catalog:   `def`
Database:  ``
Table:     ``
Org_table: ``
Type:      Json
DbType:    JSON
Collation: utf8mb4_bin (46)
Length:    4294967295
Decimals:  0
Flags:     BLOB BINARY

+-----------+---------+----------+
| FirstName | Surname | Birthday |
+-----------+---------+----------+
| John      | Doe     | null     |
+-----------+---------+----------+
1 row in set (0.0014 sec)

The result is accompanied by a description of each column. Interesting here is that the type Birthday The column is JSON. it means that null Not result in NULL As you used in SQL, but a JSON document that contains a single value null.

You can easily display a JSON null Is not the same as SQL NULL As JSON null A JSON document is:

sql SELECT NULL,
            CAST('null' AS JSON),
            CAST('null' AS JSON) IS NULL;
Field 1
Name:      `NULL`
Org_name:  ``
Catalog:   `def`
Database:  ``
Table:     ``
Org_table: ``
Type:      Bytes
DbType:    VAR_STRING
Collation: binary (63)
Length:    0
Decimals:  0
Flags:

Field 2
Name:      `CAST('null' AS JSON)`
Org_name:  ``
Catalog:   `def`
Database:  ``
Table:     ``
Org_table: ``
Type:      Json
DbType:    JSON
Collation: utf8mb4_bin (46)
Length:    4294967295
Decimals:  0
Flags:     BLOB BINARY

Field 3
Name:      `CAST('null' AS JSON) IS NULL`
Org_name:  ``
Catalog:   `def`
Database:  ``
Table:     ``
Org_table: ``
Type:      Integer
DbType:    TINY
Collation: binary (63)
Length:    1
Decimals:  0
Flags:     NOT_NULL NUM

*************************** 1. row ***************************
                        NULL: NULL
        CAST('null' AS JSON): null
CAST('null' AS JSON) IS NULL: 0
1 row in set (0.0004 sec)

CAST() The function is used to create a JSON document null Price, and it can be seen from the comparison against IS NULL It is not considered the same as SQL NULL Values. when you…

Stay on top - Get the daily news in your inbox

DMCA / Correction Notice

Recent Articles

Related Stories