Queries

MIKE+Py allows you to interact with data in your model’s tables using a query-based approach. This involves building a query object by specifying the operation (select, insert, update, delete), optionally filtering or ordering, and then executing it.

Workflow

Most query operations follow a general pattern:

Start with a table object (e.g., nodes_table = db.tables.msm_Node).
Call an operation method on the table (e.g., nodes_table.select()).
Optionally chain methods like where() or order_by().
Finally, call execute() to perform the database action, or to_pandas() for select queries.

Selecting Data

To read data, use the select() method. You can specify columns, filter rows with where(), and order results with order_by(). The execute() method typically returns a dictionary, but converting to a pandas DataFrame with to_pandas() is often more convenient for analysis.

Select specific columns from the msm_Node table:

nodes_table = db.tables.msm_Node
query = nodes_table.select(columns=["MUID", "GroundLevel", "Diameter"])

Filter rows using where(). Conditions are SQL-like strings. For values, it’s best to use named parameters:

min_diam = 0.5
# Get nodes with Diameter greater than min_diam
df_large_nodes = query.where("Diameter > :d", params={"d": min_diam}).to_pandas()

You can further sort the results:

df_sorted_nodes = query.where("Diameter > :d", params={"d": min_diam}) \
                       .order_by("GroundLevel", descending=True) \
                       .to_pandas()

If columns is not specified in select(), all columns are returned.

Inserting Data

To add new rows to a table, use the insert() method. Provide a dictionary where keys are column names and values are the data to be inserted. The insert() method, when executed, returns the MUID (unique identifier) of the newly created row.

Insert a new node into the msm_Node table:

nodes_table = db.tables.msm_Node
new_muid = nodes_table.insert({
    "MUID": "Node_New1", 
    "GroundLevel": 10.5, "Diameter": 0.3
})

If the “MUID” is not provided, MIKE+Py will attempt to auto-generate one. For tables with geometry, like msm_Node, you can include a “geometry” key with a WKT (Well-Known Text) string:

new_node_geom_muid = nodes_table.insert({
    "MUID": "Node_New_Geom", "Diameter": 0.4,
    "geometry": "POINT (12345.0 67890.0)" 
})

Updating Data

To modify existing data, use the update() method, providing a dictionary of columns and their new values. You must specify which rows to update using where() or update all rows using all().

Update the diameter of a specific node:

nodes_table = db.tables.msm_Node
updated_muids = nodes_table.update({"Diameter": 0.35}) \
                           .where("MUID == 'Node_New1'") \
                           .execute()

To prevent accidental modification of all rows, mikepluspy will raise a ValueError if update() is called without where() or all(). To update all rows in a table (use with caution):

# This would attempt to set Description for all nodes
# all_updated = nodes_table.update({"Description": "Updated all"}) \
#                          .all().execute()

Deleting Data

To remove rows, use the delete() method. Similar to update(), you must specify which rows to delete using where() or delete all rows using all().

Delete a specific node:

nodes_table = db.tables.msm_Node
deleted_muids = nodes_table.delete().where("MUID == 'Node_New1'").execute()

Attempting to delete without where() or all() will result in a ValueError. To delete all rows (use with caution):

# This would attempt to delete all nodes from the msm_Node table
# all_deleted = nodes_table.delete().all().execute()

Query Execution

Queries are constructed by chaining methods and are only performed when execute() (for insert, update, delete) or to_pandas() (for select) is called. If you need to re-run a query with the exact same parameters (uncommon), you can call reset() on the query object before calling execute() again. Typically, you would just define and execute a new query.