select()

Sends a SQL statement to the server via ado.ExecuteSQL and returns the parsed ADO Recordset XML as an ECMDbResult. Positional parameters are safely bound into the query before it is sent — never use string formatting for user input.

Only SELECT queries are supported without additional server configuration. Data manipulation statements (INSERT, UPDATE, DELETE) must be explicitly enabled via a registry setting on the application server.

1. Signature

  • Sync

  • Async

ecm.db.select(sql: str, *params: int | str | float, flags: int = 0) -> ECMDbResult
await ecm.db.select(sql: str, *params: int | str | float, flags: int = 0) -> ECMDbResult

2. Parameters

Parameter Type Default Description

sql

str

—

SQL template string with optional placeholders.

*params

int | str | float

—

Values substituted into the placeholders in order. The number of values must match the number of non-%% placeholders.

flags

int

0

Bitmask passed as Flags to ado.ExecuteSQL. 0 = automatic N-prefix for string literals on Unicode servers (default). 2 = disable automatic N-prefixing.

3. Placeholders

Placeholder Type Behaviour

%s

String

Single quotes are escaped (' → ''); value is enclosed in '…​'.

%w

Word string

Like %s, but additionally rejects whitespace and SQL comment markers (--, /, /).

%d

Integer

Value is cast to int; non-numeric values raise ValueError.

%f

Float

Value is cast to float; non-numeric values raise ValueError.

%u

Unquoted identifier

Only letters, digits, _, @, $, # are allowed. Value is inserted without quotes. Use for table or column names.

%%

Literal %

No parameter is consumed.

4. Return value

An ECMDbResult instance containing columns and rows.

4.1. ECMDbResult

Attribute / Method Type Description

.columns

list[ECMDbColumn]

Ordered list of column definitions, sorted by 1-based column index.

.rows

list[ECMDbRow]

List of result rows, one per <z:row> element.

len(result)

int

Number of rows.

for row in result

—

Iterates over .rows directly.

4.2. ECMDbColumn fields

Field Type Description

name

str

Column name as returned by the server.

number

int

1-based column index.

data_type

str

ADO data type string, e.g. "string", "int", "dateTime".

max_length

int | None

Maximum byte length declared in the schema, or None if absent.

nullable

bool

Whether the column allows NULL values.

base_table

str | None

Source table name, or None if not provided by the schema.

base_column

str | None

Source column name, or None if not provided by the schema.

4.3. ECMDbRow access

Method / Syntax Return type Description

row["column_name"]

str | None

Raw string value, or None for NULL.

row.get("column_name")

str | None

Same as getitem — raw string or None for missing / NULL.

row.typed("column_name", str)

str | None

Value coerced to the given type. Supported: str, int, float, bool, datetime.

NULL values are always returned as None, regardless of access method.

5. Examples

5.1. Simple query

  • Sync

  • Async

result = ecm.db.select("SELECT benutzer, osemail FROM benutzer")
print(f"{len(result)} rows")
for row in result:
    print(row["benutzer"], row["osemail"])
result = await ecm.db.select("SELECT benutzer, osemail FROM benutzer")
print(f"{len(result)} rows")
for row in result:
    print(row["benutzer"], row["osemail"])

5.2. Query with parameters

  • Sync

  • Async

result = ecm.db.select(
    "SELECT id, benutzer FROM benutzer WHERE benutzer = %s AND aktiv = %d",
    "john",
    1,
)
for row in result:
    print(row["id"], row["benutzer"])
result = await ecm.db.select(
    "SELECT id, benutzer FROM benutzer WHERE benutzer = %s AND aktiv = %d",
    "john",
    1,
)
for row in result:
    print(row["id"], row["benutzer"])

5.3. Typed value access

  • Sync

  • Async

result = ecm.db.select("SELECT benutzer, aktiv, logincount FROM benutzer")
for row in result:
    username: str | None = row.typed("benutzer", str)
    active: bool | None = row.typed("aktiv", bool)
    login_count: int | None = row.typed("logincount", int)
    print(username, active, login_count)
result = await ecm.db.select("SELECT benutzer, aktiv, logincount FROM benutzer")
for row in result:
    username: str | None = row.typed("benutzer", str)
    active: bool | None = row.typed("aktiv", bool)
    login_count: int | None = row.typed("logincount", int)
    print(username, active, login_count)

5.4. Dynamic table name

  • Sync

  • Async

# %u inserts an unquoted identifier — only safe characters allowed
table = "benutzer"
result = ecm.db.select("SELECT * FROM %u WHERE id = %d", table, 42)
table = "benutzer"
result = await ecm.db.select("SELECT * FROM %u WHERE id = %d", table, 42)

5.5. Inspect column metadata

  • Sync

  • Async

result = ecm.db.select("SELECT * FROM benutzer WHERE 1 = 0")
for col in result.columns:
    print(col.name, col.data_type, col.max_length, col.nullable)
result = await ecm.db.select("SELECT * FROM benutzer WHERE 1 = 0")
for col in result.columns:
    print(col.name, col.data_type, col.max_length, col.nullable)

6. See also

  • ecm.db — Namespace overview with SQL injection protection notes