Selecting Data

MIKE+Py allows you to programmatically read data from your MIKE+ model database. This is done by constructing and executing queries against specific tables. This section focuses on SELECT queries, which are used to retrieve data.

Queries in MIKE+Py are typically initiated from a Table object, which you access through an opened Database object.

First, let’s open our MIKE+ project database. We’ll use the db object for all subsequent examples in this section.

import mikeplus as mp

# Open the database
db = mp.open("data/Dyrup_uncalibrated.sqlite")

Remember to close the database when you’re finished if not using a context manager: db.close(). For simplicity in these examples, we’ll assume it’s closed at the end of the script or notebook session.

Selecting Data

The primary way to retrieve data is by using the select() method on a table object (e.g., db.tables.msm_Node). This creates a SelectQuery object, which you can then refine and execute.

All Columns

To select all columns from a table, you can call select() without arguments, followed by to_dataframe() to get the results as a Pandas DataFrame.

Let’s retrieve all data from the msm_Catchment table:

df_all_catchments = db.tables.msm_Catchment.select().to_dataframe()
df_all_catchments.head()
MUID Enabled GeomCentroidX GeomCentroidY Area GeomArea Persons HydrologicalModelNo LossDefinition ModelAImpArea ... SWMM_InitDef SWMM_Conduct SWMM_RunoffCN SWMM_CRegen SWMM_Tag DataSource AssetName Element_S NetTypeNo Description
G61F011_7292 G61F011_7292 1 585625.618252 6134811.993345 4501.460376 4501.456959 0.0 1 1 0.375906 ... None None 0 None None None None None 3 D10F050
G61F013_7293 G61F013_7293 1 585673.073141 6134756.306611 2376.508964 2376.511229 0.0 1 1 0.327839 ... None None 0 None None None None None 3 D10F050
G61F030_7294 G61F030_7294 1 585564.527279 6134874.864836 4567.727442 4567.727991 0.0 1 1 0.416389 ... None None 0 None None None None None 3 D10F050
G61F031_7295 G61F031_7295 1 585533.822109 6134832.92312 3759.297083 3759.301193 0.0 1 1 0.387001 ... None None 0 None None None None None 3 D10F050
G61F032_7296 G61F032_7296 1 585499.225735 6134786.067181 5635.766493 5635.764436 0.0 1 1 0.344482 ... None None 0 None None None None None 3 D10F050

5 rows × 109 columns

Tip

When retrieving all columns, a shortcut is simply calling to_dataframe() on the table itself, omitting select().

Specific Columns

Often, you only need a subset of columns. You can specify these by passing a list of column names to the select() method.

Let’s select only the Area, and ModelAImpArea for all catchments:

df_catchment_subset = db.tables.msm_Catchment.select(['Area', 'ModelAImpArea']).to_dataframe()
df_catchment_subset.head()
Area ModelAImpArea
G61F011_7292 4501.460376 0.375906
G61F013_7293 2376.508964 0.327839
G61F030_7294 4567.727442 0.416389
G61F031_7295 3759.297083 0.387001
G61F032_7296 5635.766493 0.344482

Expressions can be made more readable to help better understand complex queries. Additionally, specifying column names by object rather than string helps reduce typo errors.

df_catchment_subset = (
    db.tables.msm_Catchment
        .select([
            db.tables.msm_Catchment.columns.Area,
            db.tables.msm_Catchment.columns.ModelAImpArea,
            db.tables.msm_Catchment.columns.ModelAConcTime,
            db.tables.msm_Catchment.columns.ModelARFactor,
        ])
        .to_dataframe()
)
df_catchment_subset.head()
Area ModelAImpArea ModelAConcTime ModelARFactor
G61F011_7292 4501.460376 0.375906 1200.0 1.0
G61F013_7293 2376.508964 0.327839 1200.0 1.0
G61F030_7294 4567.727442 0.416389 1200.0 1.0
G61F031_7295 3759.297083 0.387001 1200.0 1.0
G61F032_7296 5635.766493 0.344482 1200.0 1.0

Although the expression above is more verbose, it’s now much more readable, especially when you read it a year later.

Note

Use parentheses to break long statements across lines for readability. Alternatively, use backslashes.

MUIDs

If you only need the MUIDs (unique identifiers) for all records in a table, you can use the get_muids() method.

catchment_muids = db.tables.msm_Catchment.get_muids()
catchment_muids[:3]
['G61F011_7292', 'G61F013_7293', 'G61F030_7294']

Filtering Rows

MIKE+Py provides several ways to filter rows based on conditions.

Filtering with by_muid()

If you know the MUID(s) of the specific record(s) you want, the by_muid() method is a convenient way to filter.

Retrieve data for a catchment with a specific MUID, for example, ‘G61F011_7292’.

(
    db.tables.msm_Catchment
        .select()
        .by_muid('G61F011_7292')
        .to_dataframe()
        .head()
)
MUID Enabled GeomCentroidX GeomCentroidY Area GeomArea Persons HydrologicalModelNo LossDefinition ModelAImpArea ... SWMM_InitDef SWMM_Conduct SWMM_RunoffCN SWMM_CRegen SWMM_Tag DataSource AssetName Element_S NetTypeNo Description
G61F011_7292 G61F011_7292 1 585625.618252 6134811.993345 4501.460376 4501.456959 0.0 1 1 0.375906 ... None None 0 None None None None None 3 D10F050

1 rows × 109 columns

You can also provide a list of MUIDs.

(
    db.tables.msm_Catchment
        .select()
        .by_muid(['G61F011_7292', 'G61F013_7293'])
        .to_dataframe()
        .head()
)
MUID Enabled GeomCentroidX GeomCentroidY Area GeomArea Persons HydrologicalModelNo LossDefinition ModelAImpArea ... SWMM_InitDef SWMM_Conduct SWMM_RunoffCN SWMM_CRegen SWMM_Tag DataSource AssetName Element_S NetTypeNo Description
G61F011_7292 G61F011_7292 1 585625.618252 6134811.993345 4501.460376 4501.456959 0.0 1 1 0.375906 ... None None 0 None None None None None 3 D10F050
G61F013_7293 G61F013_7293 1 585673.073141 6134756.306611 2376.508964 2376.511229 0.0 1 1 0.327839 ... None None 0 None None None None None 3 D10F050

2 rows × 109 columns

Filtering with where()

For more complex filtering based on column values, use the where() method. This method accepts an SQL-like condition string.

Select catchments where the Area is greater than 0.8 hectares.

(
    db.tables.msm_Catchment
        .select(['Area'])
        .where("Area > 8000")
        .to_dataframe()
)
Area
G61F080_7311 8936.923187
G61F163_7318 8633.103482
G61F430_7362 8076.876771
G62F090_7429 9543.676343
Units in MIKE+ GUI versus what’s in the database

Values displayed in the MIKE+ GUI may have different units than what is stored in the database. The examples above show catchment area in ‘hectares’, whereas the database stores them in ‘m²’. Stored units depend on the ‘Unit system’ defined for the specific model.

Select pipes having node ‘G61F351’ as its start node.

(
    db.tables.msm_Link
        .select(['FromNodeID'])
        .where("FromNodeID = 'G61F351'")
        .to_dataframe()
)
FromNodeID
G61F351_G61F350_l1 G61F351

Notice the following about string condition passed to where():

  • column names are not in quotes (e.g. FromNodeID)
  • string values are in single quotes (e.g. ‘G61F351’)
  • numeric values are not in quotes (e.g. 8000)

Wrongly formatted condition strings is a common source of errors. MIKE+Py provides a helper function mp.to_sql() that helps with this formatting by preventing mistakes like forgetting to close quote pairs, or forgetting them entirely.

(
    db.tables.msm_Link
        .select(['FromNodeID'])
        .where(f"FromNodeID = {mp.to_sql("G61F351")}")
        .to_dataframe()
)
FromNodeID
G61F351_G61F350_l1 G61F351

You can chain multiple where() calls, or combine conditions within a single where() string using AND or OR. Chaining where() calls implies an AND relationship.

Select catchments where Area > 8000 AND ModelAImpArea (impervious area for Model A) is less than 0.5.

(
    db.tables.msm_Catchment
        .select(['MUID', 'Area', 'ModelAImpArea'])
        .where(f"Area > {mp.to_sql(8000)}")
        .where(f"ModelAImpArea < {mp.to_sql(0.5)}")
        .to_dataframe()
)
MUID Area ModelAImpArea
G61F080_7311 G61F080_7311 8936.923187 0.115279
G61F163_7318 G61F163_7318 8633.103482 0.249552
G61F430_7362 G61F430_7362 8076.876771 0.36946
G62F090_7429 G62F090_7429 9543.676343 0.447018

This is equivalent to:

(
    db.tables.msm_Catchment
        .select(['MUID', 'Area', 'ModelAImpArea'])
        .where(f"Area > {mp.to_sql(8000)} AND ModelAImpArea < {mp.to_sql(0.5)}")
        .to_dataframe()
)
MUID Area ModelAImpArea
G61F080_7311 G61F080_7311 8936.923187 0.115279
G61F163_7318 G61F163_7318 8633.103482 0.249552
G61F430_7362 G61F430_7362 8076.876771 0.36946
G62F090_7429 G62F090_7429 9543.676343 0.447018

Practical Example

Let’s say we defined a selection of catchments in MIKE+, for which we we want to retrieve their Area, ModelAImpArea, and ModelAConcTime.

First let’s find the table that selections are stored in. Notice there’s no hover tooltips in MIKE+ GUI for selections. However, we find the table m_Selection anyways by searching MIKE+Py’s documentation for selection. Alternatively, we could have guessed it with help from IDE auto-completion, typing something like db.tables.selec.

Start by displaying the whole table.

db.tables.m_Selection.to_dataframe()
MUID SelectionID TableName ItemMUID
Sel_1 Sel_1 Flow_Meter_B_Catchments msm_Catchment G61F180_7321
Sel_2 Sel_2 Flow_Meter_B_Catchments msm_Catchment G62F060_7424
Sel_3 Sel_3 Flow_Meter_B_Catchments msm_Catchment G62F070_7425
Sel_4 Sel_4 Flow_Meter_B_Catchments msm_Catchment G62F071_7426
Sel_5 Sel_5 Flow_Meter_B_Catchments msm_Catchment G62F072_7427
... ... ... ... ...
Sel_108 Sel_108 Flow_Meter_A_Catchments msm_Catchment G62F014_7419
Sel_109 Sel_109 Flow_Meter_A_Catchments msm_Catchment G62F015_7420
Sel_110 Sel_110 Flow_Meter_A_Catchments msm_Catchment G62F020_7421
Sel_111 Sel_111 Flow_Meter_A_Catchments msm_Catchment G62F022_7422
Sel_112 Sel_112 Flow_Meter_A_Catchments msm_Catchment G62F023_7423

112 rows × 4 columns

We notice the column SelectionID corresponds with our selection names. TableName and ItemMUID together seem to refer to the items of the selection. We want the catchment MUIDs for selection Flow_Meter_B_Catchments.

muids = (
    db.tables.m_Selection
        .select(["ItemMUID"])
        .where(f"{db.tables.m_Selection.columns.SelectionID} = {mp.to_sql("Flow_Meter_B_Catchments")}")
        .where(f"{db.tables.m_Selection.columns.TableName} = {mp.to_sql(db.tables.msm_Catchment.name)}")
        .to_dataframe()["ItemMUID"]
        .values.tolist()
)
muids[:3]
['G61F180_7321', 'G62F060_7424', 'G62F070_7425']

Now that we have a list of MUIDs, we can use by_muid() to get our desired data.

(
    db.tables.msm_Catchment
        .select(['Area', 'ModelAImpArea', 'ModelAConcTime'])
        .by_muid(muids)
        .to_dataframe()
)
Area ModelAImpArea ModelAConcTime
G61F180_7321 3426.042426 0.471066 2500.0
G62F060_7424 5248.974839 0.372547 2500.0
G62F070_7425 3483.333978 0.493153 2500.0
G62F071_7426 7680.506712 0.422575 2500.0
G62F072_7427 6110.634318 0.403768 2500.0
G62F073_7428 3291.502189 0.337902 2500.0
G62F090_7429 9543.676343 0.447018 2500.0
G62F091_7430 6482.144842 0.350343 2500.0
G62F092_7431 4877.124584 0.356388 2500.0
G62F093_7432 5069.445488 0.339430 2500.0
G62F094_7433 5118.905991 0.358003 2500.0
G62F095_7434 5029.988743 0.383146 2500.0
G62F110_7435 6385.861122 0.398525 2500.0
G62F112_7436 7932.460472 0.354533 2500.0
G62F113_7437 4526.396075 0.361819 2500.0
G62F114_7438 3481.524653 0.406584 2500.0
G62F115_7439 1952.681519 0.489942 2500.0
G62F116_7440 3796.497897 0.475596 2500.0
G62F117_7441 1745.517658 0.387302 2500.0
G62F121_7442 6943.703429 0.327055 2500.0
G62F122_7443 4712.980255 0.395809 2500.0
G62F130_7444 3543.567635 0.399932 2500.0
G62F140_7445 6021.019428 0.359946 2500.0
G62F160_7446 6060.401422 0.358035 2500.0
G62F170_7447 5339.049153 0.362818 2500.0
G62F180_7448 7958.281468 0.351433 2500.0
G62F191_7449 4628.034499 0.420837 2500.0
G62F195_7450 3289.125097 0.544337 2500.0
G62F197_7451 4284.428469 0.467404 2500.0
G62F199_7452 3187.640319 0.429698 2500.0
G62F402_7453 1495.125317 0.487788 2500.0
G62F403_7454 2068.891662 0.605979 2500.0

In the next section, you’ll discover how to modify data and that it uses the same filter mechanisms.