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:

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

insert() is the exception: it runs straight away (see Inserting Data below).

A query object runs once. where(), by_muid() and order_by() modify the query and return it, so build a new query from the table for each request rather than reusing one.

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.

The following examples assume you have already imported mikeplus, and have an active database connection:

import mikeplus as mp

with mp.open("your_model.sqlite") as db:
    ...

Select specific columns from the msm_Node table:

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

Filter rows using where(). Conditions are SQL-like strings. Values in the condition string should be formatted to valid SQL using mp.to_sql() to ensure correct quoting and type handling (e.g., strings are quoted, numbers are not).

min_diam = mp.to_sql(0.5)
df_large_nodes = nodes_table.select(columns).where(f"Diameter > {min_diam}").to_pandas()
Note

mp.to_sql() is not needed for column names, only for values in the condition string.

Select rows for a specific MUID, or list of MUIDs is common. You can use by_muid() in a similar way as where().

df_specific_node = nodes_table.select(columns).by_muid("Node_123").to_pandas()
df_specific_nodes = nodes_table.select(columns).by_muid(["Node_123", "Node_456"]).to_pandas()

You can further sort the results:

df_sorted_nodes = nodes_table.select(columns) \
                             .where(f"Diameter > {min_diam}") \
                             .order_by("GroundLevel", descending=True) \
                             .to_pandas()

A single column can be passed as a string, e.g. nodes_table.select("Diameter"). 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. insert() runs immediately and returns the MUID (a string) of the newly created row. Pass execute=False to get an InsertQuery back instead, and call its execute() later.

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
})
print(f"Inserted new node with MUID: {new_muid}")

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)"
})
print(f"Inserted new node with geometry, MUID: {new_node_geom_muid}")

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 by_muid(), or update all rows using all(). The execute() method returns a list of MUIDs of the updated rows.

Update the diameter of a specific node:

nodes_table = db.tables.msm_Node
muid_to_update = "Node_New1"
updated_muids = nodes_table.update({"Diameter": 0.35}) \
                           .by_muid(muid_to_update) \
                           .execute()
print(f"MUIDs of updated nodes: {updated_muids}")

Operations like update().all().execute() or delete().all().execute() will affect every row in the table. This can lead to irreversible data loss or unintended widespread changes.

  • Always double-check your table selection and conditions.
  • Consider backing up your database before performing such operations.
  • Use these commands with extreme care.

To prevent accidental modification of all rows, execute() raises a ValueError if the update has no where(), by_muid() or all(). To update all rows in a table (use with caution):

# This would attempt to set Description for all nodes
# Ensure this is intended and the table is correct!
# all_updated_muids = nodes_table.update({"Description": "Updated for all nodes"}) \
#                          .all().execute()
# print(f"Updated {len(all_updated_muids)} nodes.")

Deleting Data

To remove rows, use the delete() method. Similar to update(), you must specify which rows to delete using where() or by_muid(), or delete all rows using all(). The execute() method returns a list of MUIDs of the deleted rows.

Delete a specific node:

nodes_table = db.tables.msm_Node
muid_to_delete = "Node_New1"
deleted_muids = nodes_table.delete().by_muid(muid_to_delete).execute()
print(f"MUIDs of deleted nodes: {deleted_muids}")

Executing a delete without where(), by_muid() or all() raises a ValueError. To delete all rows (use with caution, see warning above):

# This would attempt to delete all nodes from the msm_Node table.
# Ensure this is intended and the table is correct!
# all_deleted_muids = nodes_table.delete().all().execute()
# print(f"Deleted {len(all_deleted_muids)} nodes.")

Query Execution

Select, update and delete queries are constructed by chaining methods and are only performed when execute() (or to_pandas() for select) is called; insert runs immediately unless execute=False is passed. A query can only be executed once, and executing it again raises RuntimeError. 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.