Update operators for tables (Python)

Some Data API commands, like Update a row (Python), use update operators to modify rows in a table.

Set

The $set operator sets the value of the specified column to the specified value.

Example setting a non-map column:

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

# Update a row
table.update_one(
    {"title": "Hidden Shadows of the Past", "author": "John Anthony"},
    {
        "$set": {"rating": 4.5, "genres": ["Fiction", "Drama"]},
    },
)

Example setting a map column:

If the updated map includes non-string keys, you must use an array of key-value pairs to update the map column. With the Python client, you can also use DataAPIMap to encode maps that use non-string keys.

Otherwise, you can use an array of key-value pairs or a normal map.

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment
from astrapy.data_types import DataAPIMap

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

# Update a row
table.update_one(
    {"title": "Hidden Shadows of the Past", "author": "John Anthony"},
    {
        "$set": {
            # This map has non-string keys,
            # so the update is an array of key-value pairs
            "map_column_int_str": [[1, "value1"], [2, "value2"]],
            # Alternatively, use DataAPIMap to encode maps with non-string keys
            "map_column_int_str_2": DataAPIMap(
                {1: "value1", 2: "value2"}
            ),
            # This map does not have non-string keys,
            # so the update does not need to be an array of key-value pairs
            "map_column_str_str": {"key1": "value1", "key2": "value2"},
        }
    },
)

Unset

The $unset operator sets the specified column’s value to null or the equivalent empty form, such as [] or {} for map, list, and set types.

Unsetting a column produces a tombstone. Excessive tombstones can impact query performance.

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

# Update a row
table.update_one(
    {"title": "Hidden Shadows of the Past", "author": "John Anthony"},
    {"$unset": {"genres": ""}},
)

Push

The $push operator appends a single element to a map, list, or set.

To append multiple items, use the $each operator with $push.

Example appending to a list or set column:

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

# Update a row
table.update_one(
    {"title": "Hidden Shadows of the Past", "author": "John Anthony"},
    {
        "$push": {
            # Appends a single element to the "genres" list
            "genres": "SciFi",
            # Appends two elements to the "topics" list
            "topics": {"$each": ["robots", "AI"]},
        }
    },
)

Example appending to a map column:

When appending to a map, if the appended key-value pair has a non-string key, you must represent the key-value pair as an array. Otherwise, you can either use an array or a normal map.

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment
from astrapy.data_types import DataAPIMap

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

# Update a row
table.update_one(
    {"title": "Hidden Shadows of the Past", "author": "John Anthony"},
    {
        "$push": {
            # This update includes non-string keys,
            # so the update is a key-value pair represented as an array
            "map_column_int_str": [1, "value1"],
            # This update does not include non-string keys,
            # so the update can be a key-value pair represented as an array or a map
            "map_column_str_str": {"key1": "value1"},
            # When using $each, use an array of key-value pairs for non-string keys
            "map_column_int_str_2": {
                "$each": [[1, "value1"], [2, "value2"]]
            },
            # When using $each, use an array of key-value pairs or maps for string keys
            "map_column_str_str_2": {
                "$each": [{"key1": "value1"}, ["key2", "value2"]]
            },
        }
    },
)

Each

The $each operator modifies the $push operator to append multiple items to a map, list, or set.

Example appending to a list or set column:

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

# Update a row
table.update_one(
    {"title": "Hidden Shadows of the Past", "author": "John Anthony"},
    {
        "$push": {
            # Appends a single element to the "genres" list
            "genres": "SciFi",
            # Appends two elements to the "topics" list
            "topics": {"$each": ["robots", "AI"]},
        }
    },
)

Example appending to a map column:

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment
from astrapy.data_types import DataAPIMap

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

# Update a row
table.update_one(
    {"title": "Hidden Shadows of the Past", "author": "John Anthony"},
    {
        "$push": {
            # This update includes non-string keys,
            # so the update is a key-value pair represented as an array
            "map_column_int_str": [1, "value1"],
            # This update does not include non-string keys,
            # so the update can be a key-value pair represented as an array or a map
            "map_column_str_str": {"key1": "value1"},
            # When using $each, use an array of key-value pairs for non-string keys
            "map_column_int_str_2": {
                "$each": [[1, "value1"], [2, "value2"]]
            },
            # When using $each, use an array of key-value pairs or maps for string keys
            "map_column_str_str_2": {
                "$each": [{"key1": "value1"}, ["key2", "value2"]]
            },
        }
    },
)

Pull all

The $pullAll operator removes the specified elements from a list or set, or removes entries that match the specified keys from a map.

Example removing matches from a list or set column:

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

# Update a row
table.update_one(
    {"title": "Hidden Shadows of the Past", "author": "John Anthony"},
    {
        "$pullAll": {
            "genres": ["SciFi", "Romance"],
        }
    },
)

Example removing matches from map column:

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

# Update a row
table.update_one(
    {"title": "Hidden Shadows of the Past", "author": "John Anthony"},
    {
        "$pullAll": {
            "metadata": ["language", "edition"],
        }
    },
)

Combine operators

You can use multiple update operators in a single update.

from astrapy import DataAPIClient
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment

# Get an existing table
client = DataAPIClient(environment=Environment.HCD)
database = client.get_database(
    "API_ENDPOINT",
    token=UsernamePasswordTokenProvider("USERNAME", "PASSWORD"),
    keyspace="KEYSPACE_NAME",
)
table = database.get_table("TABLE_NAME")

# Update a row
table.update_one(
    {"title": "Hidden Shadows of the Past", "author": "John Anthony"},
    {
        "$set": {"rating": 4.5, "genres": ["Fiction", "Drama"]},
        "$unset": {"borrower": ""},
    },
)

Unsupported operators

$rename isn’t supported. Instead, use alterTable to add and drop columns.
$currentDate isn’t supported.
The array operators $addToSet, $pop, and $position aren’t supported.