# 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.


# <img src="data:image/svg+xml;base64,PHN2ZyBzdHlsZT0iaGVpZ2h0OjFlbTt3aWR0aDoxZW07dmVydGljYWwtYWxpZ246LTAuMTI1ZW07Zm9udC1zaXplOmluaGVyaXQ7b3ZlcmZsb3c6dmlzaWJsZTtwb3NpdGlvbjpyZWxhdGl2ZTsiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgdmlld2JveD0iMCAwIDI0IDI0IiBmaWxsPSJub25lIiBzdHJva2U9ImN1cnJlbnRDb2xvciIgc3Ryb2tlLSBzdHJva2UtbGluZWNhcD0icm91bmQiIHN0cm9rZS1saW5lam9pbj0icm91bmQiIGNsYXNzPSJnZC1pY29uIiBhcmlhLWhpZGRlbj0idHJ1ZSI+PHBhdGggZD0ibTE2IDE4IDYtNi02LTYiIC8+PHBhdGggZD0ibTggNi02IDYgNiA2IiAvPjwvc3ZnPg==" class="gd-icon" /> 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()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.where) or [order_by()](../reference/queries.SelectQuery.md#mikeplus.queries.SelectQuery.order_by).
4.  Finally, call [execute()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.execute) to perform the database action, or [to_pandas()](../reference/queries.SelectQuery.md#mikeplus.queries.SelectQuery.to_pandas) for select queries.

[insert()](../reference/tables.BaseTable.md#mikeplus.tables.BaseTable.insert) is the exception: it runs straight away (see Inserting Data below).

A query object runs once. [where()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.where), [by_muid()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.by_muid) and [order_by()](../reference/queries.SelectQuery.md#mikeplus.queries.SelectQuery.order_by) modify the query and return it, so build a new query from the table for each request rather than reusing one.


# <img src="data:image/svg+xml;base64,PHN2ZyBzdHlsZT0iaGVpZ2h0OjFlbTt3aWR0aDoxZW07dmVydGljYWwtYWxpZ246LTAuMTI1ZW07Zm9udC1zaXplOmluaGVyaXQ7b3ZlcmZsb3c6dmlzaWJsZTtwb3NpdGlvbjpyZWxhdGl2ZTsiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgdmlld2JveD0iMCAwIDI0IDI0IiBmaWxsPSJub25lIiBzdHJva2U9ImN1cnJlbnRDb2xvciIgc3Ryb2tlLSBzdHJva2UtbGluZWNhcD0icm91bmQiIHN0cm9rZS1saW5lam9pbj0icm91bmQiIGNsYXNzPSJnZC1pY29uIiBhcmlhLWhpZGRlbj0idHJ1ZSI+PGVsbGlwc2UgY3g9IjEyIiBjeT0iNSIgcng9IjkiIHJ5PSIzIj48L2VsbGlwc2U+PHBhdGggZD0iTTMgNVYxOUE5IDMgMCAwIDAgMjEgMTlWNSIgLz48cGF0aCBkPSJNMyAxMkE5IDMgMCAwIDAgMjEgMTIiIC8+PC9zdmc+" class="gd-icon" /> Selecting Data

To read data, use the [select()](../reference/tables.BaseTable.md#mikeplus.tables.BaseTable.select) method. You can specify columns, filter rows with [where()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.where), and order results with [order_by()](../reference/queries.SelectQuery.md#mikeplus.queries.SelectQuery.order_by). The [execute()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.execute) method typically returns a dictionary, but converting to a pandas DataFrame with [to_pandas()](../reference/queries.SelectQuery.md#mikeplus.queries.SelectQuery.to_pandas) is often more convenient for analysis.

> **Note: Assuming Imports**
>
> The following examples assume you have already imported `mikeplus`, and have an active database connection:
>
> ``` python
> import mikeplus as mp
>
> with mp.open("your_model.sqlite") as db:
>     ...
> ```

Select specific columns from the `msm_Node` table:

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

Filter rows using [where()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.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).

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

> **Note: 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()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.by_muid) in a similar way as [where()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.where).

``` python
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:

``` python
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](../reference/tables.BaseTable.md#mikeplus.tables.BaseTable.columns) is not specified in [select()](../reference/tables.BaseTable.md#mikeplus.tables.BaseTable.select), all columns are returned.


# <img src="data:image/svg+xml;base64,PHN2ZyBzdHlsZT0iaGVpZ2h0OjFlbTt3aWR0aDoxZW07dmVydGljYWwtYWxpZ246LTAuMTI1ZW07Zm9udC1zaXplOmluaGVyaXQ7b3ZlcmZsb3c6dmlzaWJsZTtwb3NpdGlvbjpyZWxhdGl2ZTsiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgdmlld2JveD0iMCAwIDI0IDI0IiBmaWxsPSJub25lIiBzdHJva2U9ImN1cnJlbnRDb2xvciIgc3Ryb2tlLSBzdHJva2UtbGluZWNhcD0icm91bmQiIHN0cm9rZS1saW5lam9pbj0icm91bmQiIGNsYXNzPSJnZC1pY29uIiBhcmlhLWhpZGRlbj0idHJ1ZSI+PHBhdGggZD0iTTUgMTJoMTQiIC8+PHBhdGggZD0iTTEyIDV2MTQiIC8+PC9zdmc+" class="gd-icon" /> Inserting Data

To add new rows to a table, use the [insert()](../reference/tables.BaseTable.md#mikeplus.tables.BaseTable.insert) method. Provide a dictionary where keys are column names and values are the data to be inserted. [insert()](../reference/tables.BaseTable.md#mikeplus.tables.BaseTable.insert) runs immediately and returns the MUID (a string) of the newly created row. Pass `execute=False` to get an [InsertQuery](../reference/queries.InsertQuery.md#mikeplus.queries.InsertQuery) back instead, and call its [execute()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.execute) later.

Insert a new node into the `msm_Node` table:

``` python
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:

``` python
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}")
```


# <img src="data:image/svg+xml;base64,PHN2ZyBzdHlsZT0iaGVpZ2h0OjFlbTt3aWR0aDoxZW07dmVydGljYWwtYWxpZ246LTAuMTI1ZW07Zm9udC1zaXplOmluaGVyaXQ7b3ZlcmZsb3c6dmlzaWJsZTtwb3NpdGlvbjpyZWxhdGl2ZTsiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgdmlld2JveD0iMCAwIDI0IDI0IiBmaWxsPSJub25lIiBzdHJva2U9ImN1cnJlbnRDb2xvciIgc3Ryb2tlLSBzdHJva2UtbGluZWNhcD0icm91bmQiIHN0cm9rZS1saW5lam9pbj0icm91bmQiIGNsYXNzPSJnZC1pY29uIiBhcmlhLWhpZGRlbj0idHJ1ZSI+PHBhdGggZD0iTTIxLjE3NCA2LjgxMmExIDEgMCAwIDAtMy45ODYtMy45ODdMMy44NDIgMTYuMTc0YTIgMiAwIDAgMC0uNS44M2wtMS4zMjEgNC4zNTJhLjUuNSAwIDAgMCAuNjIzLjYyMmw0LjM1My0xLjMyYTIgMiAwIDAgMCAuODMtLjQ5N3oiIC8+PHBhdGggZD0ibTE1IDUgNCA0IiAvPjwvc3ZnPg==" class="gd-icon" /> Updating Data

To modify existing data, use the [update()](../reference/tables.BaseTable.md#mikeplus.tables.BaseTable.update) method, providing a dictionary of columns and their new values. You must specify which rows to update using [where()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.where) or [by_muid()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.by_muid), or update all rows using [all()](../reference/queries.UpdateQuery.md#mikeplus.queries.UpdateQuery.all). The [execute()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.execute) method returns a list of MUIDs of the updated rows.

Update the diameter of a specific node:

``` python
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()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.execute) raises a `ValueError` if the update has no [where()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.where), [by_muid()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.by_muid) or [all()](../reference/queries.UpdateQuery.md#mikeplus.queries.UpdateQuery.all). To update all rows in a table (use with caution):

``` python
# 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.")
```


# <img src="data:image/svg+xml;base64,PHN2ZyBzdHlsZT0iaGVpZ2h0OjFlbTt3aWR0aDoxZW07dmVydGljYWwtYWxpZ246LTAuMTI1ZW07Zm9udC1zaXplOmluaGVyaXQ7b3ZlcmZsb3c6dmlzaWJsZTtwb3NpdGlvbjpyZWxhdGl2ZTsiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgdmlld2JveD0iMCAwIDI0IDI0IiBmaWxsPSJub25lIiBzdHJva2U9ImN1cnJlbnRDb2xvciIgc3Ryb2tlLSBzdHJva2UtbGluZWNhcD0icm91bmQiIHN0cm9rZS1saW5lam9pbj0icm91bmQiIGNsYXNzPSJnZC1pY29uIiBhcmlhLWhpZGRlbj0idHJ1ZSI+PHBhdGggZD0iTTEwIDExdjYiIC8+PHBhdGggZD0iTTE0IDExdjYiIC8+PHBhdGggZD0iTTE5IDZ2MTRhMiAyIDAgMCAxLTIgMkg3YTIgMiAwIDAgMS0yLTJWNiIgLz48cGF0aCBkPSJNMyA2aDE4IiAvPjxwYXRoIGQ9Ik04IDZWNGEyIDIgMCAwIDEgMi0yaDRhMiAyIDAgMCAxIDIgMnYyIiAvPjwvc3ZnPg==" class="gd-icon" /> Deleting Data

To remove rows, use the [delete()](../reference/tables.BaseTable.md#mikeplus.tables.BaseTable.delete) method. Similar to [update()](../reference/tables.BaseTable.md#mikeplus.tables.BaseTable.update), you must specify which rows to delete using [where()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.where) or [by_muid()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.by_muid), or delete all rows using [all()](../reference/queries.UpdateQuery.md#mikeplus.queries.UpdateQuery.all). The [execute()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.execute) method returns a list of MUIDs of the deleted rows.

Delete a specific node:

``` python
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()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.where), [by_muid()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.by_muid) or [all()](../reference/queries.UpdateQuery.md#mikeplus.queries.UpdateQuery.all) raises a `ValueError`. To delete all rows (use with caution, see warning above):

``` python
# 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.")
```


# <img src="data:image/svg+xml;base64,PHN2ZyBzdHlsZT0iaGVpZ2h0OjFlbTt3aWR0aDoxZW07dmVydGljYWwtYWxpZ246LTAuMTI1ZW07Zm9udC1zaXplOmluaGVyaXQ7b3ZlcmZsb3c6dmlzaWJsZTtwb3NpdGlvbjpyZWxhdGl2ZTsiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyIgdmlld2JveD0iMCAwIDI0IDI0IiBmaWxsPSJub25lIiBzdHJva2U9ImN1cnJlbnRDb2xvciIgc3Ryb2tlLSBzdHJva2UtbGluZWNhcD0icm91bmQiIHN0cm9rZS1saW5lam9pbj0icm91bmQiIGNsYXNzPSJnZC1pY29uIiBhcmlhLWhpZGRlbj0idHJ1ZSI+PHBhdGggZD0iTTUgNWEyIDIgMCAwIDEgMy4wMDgtMS43MjhsMTEuOTk3IDYuOTk4YTIgMiAwIDAgMSAuMDAzIDMuNDU4bC0xMiA3QTIgMiAwIDAgMSA1IDE5eiIgLz48L3N2Zz4=" class="gd-icon" /> Query Execution

Select, update and delete queries are constructed by chaining methods and are only performed when [execute()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.execute) (or [to_pandas()](../reference/queries.SelectQuery.md#mikeplus.queries.SelectQuery.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()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.reset) on the query object before calling [execute()](../reference/queries.BaseQuery.md#mikeplus.queries.BaseQuery.execute) again. Typically, you would just define and execute a new query.