ORM Classes

This section documents the ORM classes that provide SQLAlchemy-like interface for MatrixOne.

Query

class matrixone.orm.Query(model_class, client, snapshot_name: str | None = None)[source]

Bases: object

Query builder for ORM operations with SQLAlchemy-style interface.

This class provides a fluent interface for building SQL queries in a way that mimics SQLAlchemy’s query builder. It supports both SQLAlchemy models and custom MatrixOne models.

Key Features:

Fluent method chaining for building complex queries
Support for SELECT, INSERT, UPDATE, DELETE operations
SQLAlchemy expression support in filter(), having(), and other methods
Automatic SQL generation and parameter binding
Snapshot support for point-in-time queries

Usage:

# Basic query building
query = client.query(User)
results = query.filter(User.age > 25).order_by(User.name).limit(10).all()

# Complex queries with joins and aggregations
query = client.query(User, func.count(Order.id).label('order_count'))
results = (query
    .join(Order, User.id == Order.user_id)
    .group_by(User.id)
    .having(func.count(Order.id) > 5)
    .all())

Note: This is the legacy query builder. For new code, consider using MatrixOneQuery which provides enhanced SQLAlchemy compatibility.

__init__(model_class, client, snapshot_name: str | None = None)[source]

snapshot(snapshot_name: str) → Query[source]: Set snapshot for this query - SQLAlchemy style chaining

select(*columns) → Query[source]: Select specific columns

filter(condition: str, *params) → Query[source]: Add WHERE condition

filter_by(**kwargs) → Query[source]: Add WHERE conditions from keyword arguments

join(table: str, condition: str) → Query[source]: Add JOIN clause

group_by(*columns) → Query[source]

Add GROUP BY clause to the query.

The GROUP BY clause is used to group rows that have the same values in specified columns, typically used with aggregate functions like COUNT, SUM, AVG, etc.

Args:

*columns: Columns to group by as strings.
        Can include column names, expressions, or functions.

Returns:

Query: Self for method chaining.

Examples:

# Basic GROUP BY
query.group_by("department")
query.group_by("department", "status")
query.group_by("YEAR(created_at)")

# Multiple columns
query.group_by("department", "status", "category")

# With expressions
query.group_by("YEAR(created_at)", "MONTH(created_at)")

Notes

GROUP BY is typically used with aggregate functions (COUNT, SUM, AVG, etc.)
Multiple columns can be grouped together
For SQLAlchemy expression support, use MatrixOneQuery instead

Raises:

ValueError: If columns are not strings

having(condition: str, *params) → Query[source]

Add HAVING condition to the query.

The HAVING clause is used to filter groups after GROUP BY operations, similar to WHERE clause but applied to aggregated results.

Args:

condition (str): The HAVING condition as a string.
               Can include '?' placeholders for parameter substitution.
*params: Parameters to replace '?' placeholders in the condition.

Returns:

Query: Self for method chaining.

Examples:

# Basic HAVING with placeholders
query.group_by(User.department)
query.having("COUNT(*) > ?", 5)
query.having("AVG(age) > ?", 25)

# HAVING without placeholders
query.group_by(User.department)
query.having("COUNT(*) > 5")
query.having("AVG(age) > 25")

# Multiple HAVING conditions
query.group_by(User.department)
query.having("COUNT(*) > ?", 5)
query.having("AVG(age) > ?", 25)
query.having("MAX(age) < ?", 65)

Notes

HAVING clauses are typically used with GROUP BY operations
Use ‘?’ placeholders for safer parameter substitution
Multiple HAVING conditions are combined with AND logic
For SQLAlchemy expression support, use MatrixOneQuery instead

Raises:

ValueError: If condition is not a string

order_by(*columns) → Query[source]

Add ORDER BY clause to the query.

The ORDER BY clause is used to sort the result set by one or more columns, either in ascending (ASC) or descending (DESC) order.

Args:

*columns: Columns to order by as strings.
        Can include column names with optional ASC/DESC keywords.

Returns:

Query: Self for method chaining.

Examples:

# Basic ORDER BY
query.order_by("name")
query.order_by("created_at DESC")
query.order_by("department ASC", "name DESC")

# Multiple columns
query.order_by("department", "name", "age DESC")

# With expressions
query.order_by("COUNT(*) DESC")
query.order_by("AVG(salary) ASC")

Notes

ORDER BY sorts the result set in ascending order by default
Use “DESC” for descending order, “ASC” for explicit ascending order
Multiple columns are ordered from left to right
For SQLAlchemy expression support, use MatrixOneQuery instead

Raises:

ValueError: If columns are not strings

limit(count: int) → Query[source]: Add LIMIT clause

offset(count: int) → Query[source]: Add OFFSET clause

all() → List[source]: Execute query and return all results

first() → Optional[source]: Execute query and return first result

one()[source]

Execute query and return exactly one result.

This method executes the query and expects exactly one row to be returned. If no rows are found or multiple rows are found, it raises appropriate exceptions.

Returns::: Model instance: The single result row as a model instance.
Raises::: NoResultFound: If no results are found. MultipleResultsFound: If more than one result is found.

Examples:

# Get a user by unique ID
user = client.query(User).filter(User.id == 1).one()

# Get a user by unique email
user = client.query(User).filter(User.email == "admin@example.com").one()

Notes::

Use this method when you expect exactly one result
For cases where zero or one result is acceptable, use one_or_none()
For cases where multiple results are acceptable, use all() or first()

one_or_none()[source]

Execute query and return exactly one result or None.

This method executes the query and returns exactly one row if found, or None if no rows are found. If multiple rows are found, it raises an exception.

Returns::

Model instance or None: The single result row as a model instance,: or None if no results are found.

Raises::

MultipleResultsFound: If more than one result is found.

Examples:

# Get a user by ID, return None if not found
user = client.query(User).filter(User.id == 999).one_or_none()
if user:
    print(f"Found user: {user.name}")

# Get a user by email, return None if not found
user = client.query(User).filter(User.email == "nonexistent@example.com").one_or_none()
if user is None:
    print("User not found")

Notes::

Use this method when zero or one result is acceptable
For cases where exactly one result is required, use one()
For cases where multiple results are acceptable, use all() or first()

scalar()[source]

Execute query and return the first column of the first result.

This method executes the query and returns the value of the first column from the first row, or None if no results are found. This is useful for getting single values like counts, sums, or specific column values.

Returns::

Any or None: The value of the first column from the first row,: or None if no results are found.

Examples:

# Get the count of all users
count = client.query(User).select(func.count(User.id)).scalar()

# Get the name of the first user
name = client.query(User).select(User.name).first().scalar()

# Get the maximum age
max_age = client.query(User).select(func.max(User.age)).scalar()

# Get a specific user's name by ID
name = client.query(User).select(User.name).filter(User.id == 1).scalar()

Notes::

This method is particularly useful with aggregate functions
For custom select queries, returns the first selected column value
For model queries, returns the first column value from the model
Returns None if no results are found

count() → int[source]: Execute query and return count of results

insert(**kwargs) → Query[source]: Start INSERT operation

bulk_insert(values_list: List[Dict[str, Any]]) → Query[source]: Bulk insert multiple records

update(**kwargs) → Query[source]: Start UPDATE operation

delete() → Any[source]: Execute DELETE operation

execute() → Any[source]: Execute the query based on its type

BaseMatrixOneQuery

class matrixone.orm.BaseMatrixOneQuery(model_class, client, transaction_wrapper=None, snapshot=None)[source]

Bases: object

Base MatrixOne Query builder that contains common SQL building logic.

This base class provides SQLAlchemy-compatible query building with: - Full SQLAlchemy expression support in having(), filter(), and other methods - Automatic SQL generation and parameter binding - Support for both SQLAlchemy expressions and string conditions - Method chaining for fluent query building

Key Features:

SQLAlchemy expression support (e.g., func.count(User.id) > 5)
String condition support (e.g., “COUNT(*) > ?”, 5)
Automatic column name resolution and SQL generation
Full compatibility with SQLAlchemy 1.4+ and 2.0+

Note: This is a base class. For most use cases, use MatrixOneQuery instead.

__init__(model_class, client, transaction_wrapper=None, snapshot=None)[source]

select(*columns) → BaseMatrixOneQuery[source]: Add SELECT columns - SQLAlchemy style

cte(name: str, recursive: bool = False) → CTE[source]

Create a CTE (Common Table Expression) from this query - SQLAlchemy style

Args:

name: Name of the CTE
recursive: Whether this is a recursive CTE

Returns:

CTE object that can be used in other queries

Examples:

# Create a CTE from a query
user_stats = client.query(User).filter(User.active == True).cte("user_stats")

# Use the CTE in another query
result = client.query(user_stats).all()

# Recursive CTE example
hierarchy = client.query(Employee).filter(Employee.manager_id == None).cte("hierarchy", recursive=True)

join(target, onclause=None, isouter=False, full=False) → BaseMatrixOneQuery[source]

Add JOIN clause - SQLAlchemy style

Args:

target: Table or model to join with
onclause: ON condition for the join (optional, will be inferred if not provided)
isouter: If True, creates LEFT OUTER JOIN (default: False for INNER JOIN)
full: If True, creates FULL OUTER JOIN (default: False)

Returns:

Self for method chaining

Examples:

# Basic inner join with explicit condition
query.join(Address, User.id == Address.user_id)

# Inner join with string condition
query.join('addresses', 'users.id = addresses.user_id')

# Left outer join
query.join(Address, isouter=True)

# Join without explicit condition (will be inferred if possible)
query.join(Address)

innerjoin(target, onclause=None) → BaseMatrixOneQuery[source]: Add INNER JOIN clause - SQLAlchemy style (alias for join with isouter=False)

leftjoin(target, onclause=None) → BaseMatrixOneQuery[source]: Add LEFT JOIN clause - SQLAlchemy style (alias for join with isouter=True)

rightjoin(target, onclause=None) → BaseMatrixOneQuery[source]: Add RIGHT JOIN clause - SQLAlchemy style

fullouterjoin(target, onclause=None) → BaseMatrixOneQuery[source]: Add FULL OUTER JOIN clause - SQLAlchemy style (alias for join with full=True)

outerjoin(target, onclause=None) → BaseMatrixOneQuery[source]: Add LEFT OUTER JOIN clause - SQLAlchemy style (alias for leftjoin)

group_by(*columns) → BaseMatrixOneQuery[source]

Add GROUP BY clause to the query - SQLAlchemy style compatibility.

The GROUP BY clause is used to group rows that have the same values in specified columns, typically used with aggregate functions like COUNT, SUM, AVG, etc.

Args:

*columns: Columns to group by. Can be:
    - SQLAlchemy column expressions (e.g., User.department, func.year(User.created_at))
    - String column names (e.g., "department", "created_at")
    - SQLAlchemy function expressions (e.g., func.year(User.created_at))

Returns:

BaseMatrixOneQuery: Self for method chaining.

Examples:

# SQLAlchemy column expressions (recommended)
query.group_by(User.department)
query.group_by(User.department, User.status)
query.group_by(func.year(User.created_at))
query.group_by(func.date(User.created_at), User.department)

# String column names
query.group_by("department")
query.group_by("department", "status")

# Complex expressions
query.group_by(
    User.department,
    func.year(User.created_at),
    func.month(User.created_at)
)

Notes

GROUP BY is typically used with aggregate functions (COUNT, SUM, AVG, etc.)
SQLAlchemy expressions provide better type safety and integration
Multiple columns can be grouped together
Column references in SQLAlchemy expressions are automatically converted to MatrixOne-compatible format

Raises:

ValueError: If invalid column type is provided
SQLAlchemyError: If SQLAlchemy expression compilation fails

having(condition, *params) → BaseMatrixOneQuery[source]

Add HAVING clause to the query - SQLAlchemy style compatibility.

The HAVING clause is used to filter groups after GROUP BY operations, similar to WHERE clause but applied to aggregated results.

Args:

condition: The HAVING condition. Can be:
    - SQLAlchemy expression (e.g., func.count(User.id) > 5)
    - String condition with placeholders (e.g., "COUNT(*) > ?")
    - String condition without placeholders (e.g., "COUNT(*) > 5")
*params: Additional parameters for string-based conditions.
        Used to replace '?' placeholders in condition string.

Returns:

BaseMatrixOneQuery: Self for method chaining.

Examples:

# SQLAlchemy expression syntax (recommended)
query.group_by(User.department)
query.having(func.count(User.id) > 5)
query.having(func.avg(User.age) > 25)
query.having(func.count(func.distinct(User.id)) > 3)

# String-based syntax with placeholders
query.group_by(User.department)
query.having("COUNT(*) > ?", 5)
query.having("AVG(age) > ?", 25)

# String-based syntax without placeholders
query.group_by(User.department)
query.having("COUNT(*) > 5")
query.having("AVG(age) > 25")

# Multiple HAVING conditions
query.group_by(User.department)
query.having(func.count(User.id) > 5)
query.having(func.avg(User.age) > 25)
query.having(func.max(User.age) < 65)

# Mixed string and expression syntax
query.group_by(User.department)
query.having("COUNT(*) > ?", 5)  # String
query.having(func.avg(User.age) > 25)  # Expression

Notes

HAVING clauses are typically used with GROUP BY operations
SQLAlchemy expressions provide better type safety and integration
String conditions with placeholders are safer against SQL injection
Multiple HAVING conditions are combined with AND logic
Column references in SQLAlchemy expressions are automatically converted to MatrixOne-compatible format

Supported SQLAlchemy Functions:

func.count(): Count rows or distinct values
func.avg(): Calculate average
func.sum(): Calculate sum
func.min(): Find minimum value
func.max(): Find maximum value
func.distinct(): Get distinct values

Raises:

ValueError: If invalid condition type is provided
SQLAlchemyError: If SQLAlchemy expression compilation fails

snapshot(snapshot_name: str) → BaseMatrixOneQuery[source]: Add snapshot support - SQLAlchemy style chaining

alias(alias_name: str) → BaseMatrixOneQuery[source]: Set table alias for this query - SQLAlchemy style chaining

subquery(alias_name: str | None = None) → str[source]: Convert this query to a subquery with optional alias

filter(condition, *params) → BaseMatrixOneQuery[source]: Add WHERE conditions - SQLAlchemy style unified interface

filter_by(**kwargs) → BaseMatrixOneQuery[source]: Add WHERE conditions from keyword arguments - SQLAlchemy style

where(condition: str, *params) → BaseMatrixOneQuery[source]: Add WHERE condition - alias for filter method

logical_in(column, values) → BaseMatrixOneQuery[source]

Add IN condition with support for various value types.

This method provides enhanced IN functionality that can handle: - Lists of values: [1, 2, 3] - SQLAlchemy expressions: func.count(User.id) - FulltextFilter objects: boolean_match(“title”, “content”).must(“python”) - Subqueries: client.query(User).select(User.id)

Args:

column: Column to check (can be string or SQLAlchemy column)
values: Values to check against. Can be:
    - List of values: [1, 2, 3, "a", "b"]
    - SQLAlchemy expression: func.count(User.id)
    - FulltextFilter object: boolean_match("title", "content").must("python")
    - Subquery object: client.query(User).select(User.id)

Returns:

BaseMatrixOneQuery: Self for method chaining.

Examples:

# List of values
query.logical_in("city", ["北京", "上海", "广州"])
query.logical_in(User.id, [1, 2, 3, 4])

# SQLAlchemy expression
query.logical_in("id", func.count(User.id))

# FulltextFilter
query.logical_in("id", boolean_match("title", "content").must("python"))

# Subquery
subquery = client.query(User).select(User.id).filter(User.active == True)
query.logical_in("author_id", subquery)

Notes

This method automatically handles different value types
For FulltextFilter objects, it creates a subquery using the fulltext search
For SQLAlchemy expressions, it compiles them to SQL
For lists, it creates standard IN clauses with proper parameter binding

order_by(*columns) → BaseMatrixOneQuery[source]

Add ORDER BY clause to the query - SQLAlchemy style compatibility.

The ORDER BY clause is used to sort the result set by one or more columns, either in ascending (ASC) or descending (DESC) order.

Args:

*columns: Columns to order by. Can be:
    - SQLAlchemy column expressions (e.g., User.name, User.created_at.desc())
    - String column names (e.g., "name", "created_at DESC")
    - SQLAlchemy function expressions (e.g., func.count(User.id))
    - SQLAlchemy desc/asc expressions (e.g., desc(User.name), asc(User.age))

Returns:

BaseMatrixOneQuery: Self for method chaining.

Examples:

# SQLAlchemy column expressions (recommended)
query.order_by(User.name)
query.order_by(User.created_at.desc())
query.order_by(User.department, User.name.asc())

# String column names
query.order_by("name")
query.order_by("created_at DESC")
query.order_by("department ASC", "name DESC")

# SQLAlchemy desc/asc functions
from sqlalchemy import desc, asc
query.order_by(desc(User.created_at))
query.order_by(asc(User.name), desc(User.age))

# Function expressions
query.order_by(func.count(User.id).desc())
query.order_by(func.avg(User.salary).asc())

# Mixed expressions
query.order_by(User.department, "name DESC")
query.order_by(func.count(User.id).desc(), User.name.asc())

# Complex expressions
query.order_by(
    User.department.asc(),
    func.count(User.id).desc(),
    User.name.asc()
)

Notes

ORDER BY sorts the result set in ascending order by default
Use .desc() or desc() for descending order
Use .asc() or asc() for explicit ascending order
Multiple columns are ordered from left to right
SQLAlchemy expressions provide better type safety and integration
Column references in SQLAlchemy expressions are automatically converted to MatrixOne-compatible format

Raises:

ValueError: If invalid column type is provided
SQLAlchemyError: If SQLAlchemy expression compilation fails

limit(count: int) → BaseMatrixOneQuery[source]: Add LIMIT clause - SQLAlchemy style

offset(count: int) → BaseMatrixOneQuery[source]: Add OFFSET clause - SQLAlchemy style

update(**kwargs) → BaseMatrixOneQuery[source]

Start UPDATE operation - SQLAlchemy style

This method allows you to update records in the database using a fluent interface similar to SQLAlchemy’s update() method. It supports both SQLAlchemy expressions and simple key-value pairs for setting column values.

Args:

**kwargs: Column names and their new values to set

Returns:

Self for method chaining

Examples:

# Update with simple key-value pairs
query = client.query(User)
query.update(name="New Name", email="new@example.com").filter(User.id == 1).execute()

# Update with SQLAlchemy expressions
from sqlalchemy import func
query = client.query(User)
query.update(
    last_login=func.now(),
    login_count=User.login_count + 1
).filter(User.id == 1).execute()

# Update multiple records with conditions
query = client.query(User)
query.update(status="inactive").filter(User.last_login < "2023-01-01").execute()

# Update with complex conditions
query = client.query(User)
query.update(
    status="premium",
    premium_until=func.date_add(func.now(), func.interval(1, "YEAR"))
).filter(
    User.subscription_type == "paid",
    User.payment_status == "active"
).execute()

delete() → Any[source]: Execute DELETE operation

MatrixOneQuery

class matrixone.orm.MatrixOneQuery(model_class, client, transaction_wrapper=None, snapshot=None)[source]

Bases: BaseMatrixOneQuery

MatrixOne Query builder that mimics SQLAlchemy Query interface.

This class provides full SQLAlchemy compatibility including: - SQLAlchemy expression support in having(), filter(), and other methods - Type-safe column references and function calls - Automatic SQL generation and parameter binding - Full integration with SQLAlchemy models and functions

Key Features:

Supports SQLAlchemy expressions (e.g., func.count(User.id) > 5)
Supports traditional string conditions (e.g., “COUNT(*) > ?”, 5)
Automatic column name resolution and SQL generation
Method chaining for fluent query building
Full compatibility with SQLAlchemy 1.4+ and 2.0+

Examples

# SQLAlchemy expression syntax (recommended) query = client.query(User) query.group_by(User.department) query.having(func.count(User.id) > 5) query.having(func.avg(User.age) > 25)

# String-based syntax (also supported) query.having(“COUNT(*) > ?”, 5) query.having(“AVG(age) > ?”, 25)

# Mixed syntax query.having(func.count(User.id) > 5) # Expression query.having(“AVG(age) > ?”, 25) # String

__init__(model_class, client, transaction_wrapper=None, snapshot=None)[source]

with_cte(*ctes) → MatrixOneQuery[source]

Add CTEs to this query - SQLAlchemy style

Args:

*ctes: CTE objects to add to the query

Returns:

Self for method chaining

Examples:

# Add a single CTE
user_stats = client.query(User).filter(User.active == True).cte("user_stats")
result = client.query(Article).with_cte(user_stats).join(user_stats, Article.user_id == user_stats.id).all()

# Add multiple CTEs
result = client.query(Article).with_cte(user_stats, category_stats).all()

all() → List[source]: Execute query and return all results - SQLAlchemy style

first() → Optional[source]: Execute query and return first result - SQLAlchemy style

one()[source]

Execute query and return exactly one result - SQLAlchemy style.

This method executes the query and expects exactly one row to be returned. If no rows are found or multiple rows are found, it raises appropriate exceptions. This method provides SQLAlchemy-compatible behavior for MatrixOne queries.

Returns::: Model instance: The single result row as a model instance.
Raises::: NoResultFound: If no results are found. MultipleResultsFound: If more than one result is found.

Examples:

# Get a user by unique ID using SQLAlchemy expressions
from sqlalchemy import and_
user = client.query(User).filter(and_(User.id == 1, User.active == True)).one()

# Get a user by unique email with complex conditions
user = client.query(User).filter(User.email == "admin@example.com").one()

Notes::

Use this method when you expect exactly one result
For cases where zero or one result is acceptable, use one_or_none()
For cases where multiple results are acceptable, use all() or first()
This method supports SQLAlchemy expressions and operators

one_or_none()[source]

Execute query and return exactly one result or None - SQLAlchemy style.

This method executes the query and returns exactly one row if found, or None if no rows are found. If multiple rows are found, it raises an exception. This method provides SQLAlchemy-compatible behavior for MatrixOne queries.

Returns::

Model instance or None: The single result row as a model instance,: or None if no results are found.

Raises::

MultipleResultsFound: If more than one result is found.

Examples:

# Get a user by ID, return None if not found
from sqlalchemy import and_
user = client.query(User).filter(and_(User.id == 999, User.active == True)).one_or_none()
if user:
    print(f"Found user: {user.name}")

# Get a user by email with complex conditions
user = client.query(User).filter(User.email == "nonexistent@example.com").one_or_none()
if user is None:
    print("User not found")

Notes::

Use this method when zero or one result is acceptable
For cases where exactly one result is required, use one()
For cases where multiple results are acceptable, use all() or first()
This method supports SQLAlchemy expressions and operators

scalar()[source]

Execute query and return the first column of the first result - SQLAlchemy style.

This method executes the query and returns the value of the first column from the first row, or None if no results are found. This is useful for getting single values like counts, sums, or specific column values. This method provides SQLAlchemy-compatible behavior for MatrixOne queries.

Returns::

Any or None: The value of the first column from the first row,: or None if no results are found.

Examples:

# Get the count of all users using SQLAlchemy functions
from sqlalchemy import func
count = client.query(User).select(func.count(User.id)).scalar()

# Get the name of the first user
name = client.query(User).select(User.name).first().scalar()

# Get the maximum age with complex conditions
max_age = client.query(User).select(func.max(User.age)).filter(User.active == True).scalar()

# Get a specific user's name by ID
name = client.query(User).select(User.name).filter(User.id == 1).scalar()

Notes::

This method is particularly useful with aggregate functions
For custom select queries, returns the first selected column value
For model queries, returns the first column value from the model
Returns None if no results are found
This method supports SQLAlchemy expressions and operators

count() → int[source]: Execute query and return count of results - SQLAlchemy style

execute() → Any[source]: Execute the query based on its type

to_sql() → str[source]

Generate the complete SQL statement for this query.

Returns the SQL string that would be executed, with parameters properly substituted for better readability.

Returns:
str: The complete SQL statement as a string.
Examples

query = client.query(User).filter(User.age > 25).order_by(User.name)

sql = query.to_sql() print(sql) # “SELECT * FROM users WHERE age > 25 ORDER BY name”

query = client.query(User).update(name=”New Name”).filter(User.id == 1) sql = query.to_sql() print(sql) # “UPDATE users SET name = ‘New Name’ WHERE id = 1”

Notes:

This method returns the SQL with parameters substituted
Use this for debugging or logging purposes
The returned SQL is ready to be executed directly

explain(verbose: bool = False) → Any[source]

Execute EXPLAIN statement for this query.

Shows the query execution plan without actually executing the query. Useful for understanding how MatrixOne will execute the query and optimizing query performance.

Args:

verbose (bool): Whether to include verbose output.
               Defaults to False.

Returns:

Any: The result set containing the execution plan.

Examples:

# Basic EXPLAIN
plan = client.query(User).filter(User.age > 25).explain()

# EXPLAIN with verbose output
plan = client.query(User).filter(User.age > 25).explain(verbose=True)

# EXPLAIN for complex queries
plan = (client.query(User)
       .filter(User.department == 'Engineering')
       .order_by(User.salary.desc())
       .explain(verbose=True))

Notes

EXPLAIN shows the execution plan without executing the query
Use verbose=True for more detailed information
Helpful for query optimization and performance tuning

explain_analyze(verbose: bool = False) → Any[source]

Execute EXPLAIN ANALYZE statement for this query.

Shows the query execution plan and actually executes the query, providing both the plan and actual execution statistics. Useful for understanding query performance with real data.

Args:

verbose (bool): Whether to include verbose output.
               Defaults to False.

Returns:

Any: The result set containing the execution plan and statistics.

Examples:

# Basic EXPLAIN ANALYZE
result = client.query(User).filter(User.age > 25).explain_analyze()

# EXPLAIN ANALYZE with verbose output
result = client.query(User).filter(User.age > 25).explain_analyze(verbose=True)

# EXPLAIN ANALYZE for complex queries
result = (client.query(User)
        .filter(User.department == 'Engineering')
        .order_by(User.salary.desc())
        .explain_analyze(verbose=True))

Notes

EXPLAIN ANALYZE actually executes the query and shows statistics
Use verbose=True for more detailed information
Provides actual execution time and row counts
Use with caution on large datasets as it executes the full query

export_to_file(filepath: str, format: str = 'csv', fields_terminated_by: str | None = None, fields_enclosed_by: str | None = None, lines_terminated_by: str | None = None, header: bool = False, max_file_size: int | None = None, force_quote: list | None = None) → Any[source]

Export query results to a file using SELECT … INTO OUTFILE.

This is a convenience method that builds the SELECT query and exports it to a file on the MatrixOne server’s filesystem.

Parameters:

filepath – Absolute path on server filesystem where file will be created
format – Export file format (‘csv’ or ‘jsonline’)
fields_terminated_by – Field delimiter (default: ‘,’ for CSV)
fields_enclosed_by – Field enclosure character (default: ‘”’ for CSV)
lines_terminated_by – Line terminator (default: ‘n’)
header – Whether to include column headers (default: False)
max_file_size – Maximum size per file in bytes (for splitting large exports)
force_quote – List of column names/indices to always quote

Returns:

ResultSet with export operation results

Examples

>>> # Export filtered users to CSV with headers
>>> client.query(User).filter(User.age > 25).export_to_file(
...     filepath="/tmp/users_over_25.csv",
...     format='csv',
...     header=True
... )

>>> # Export aggregated data
>>> from sqlalchemy import func
>>> client.query(Order, func.sum(Order.amount).label('total')) \
...     .group_by(Order.customer_id) \
...     .export_to_file("/tmp/customer_totals.csv", format='csv', header=True)

Note

The filepath must be on the MatrixOne server’s filesystem
The MatrixOne server process must have write permissions
For more control, use client.export.to_file() with the query’s SQL

export_to_stage(stage_name: str, filename: str, format: str = 'csv', fields_terminated_by: str | None = None, fields_enclosed_by: str | None = None, lines_terminated_by: str | None = None, header: bool = False, max_file_size: int | None = None, force_quote: list | None = None, compression: str | None = None) → Any[source]

Export query results to a stage using SELECT … INTO STAGE (Recommended).

This is the recommended way to export query results. It builds the SELECT query and exports it to an external stage (S3, local filesystem, etc.).

Parameters:

stage_name – Name of the target stage
filename – Filename to create in the stage
format – Export file format (‘csv’ or ‘jsonline’)
fields_terminated_by – Field delimiter (default: ‘,’ for CSV)
fields_enclosed_by – Field enclosure character (default: ‘”’ for CSV)
lines_terminated_by – Line terminator (default: ‘n’)
header – Whether to include column headers (default: False)
max_file_size – Maximum size per file in bytes (for splitting large exports)
force_quote – List of column names/indices to always quote
compression – Compression format (e.g., ‘gzip’, ‘bzip2’)

Returns:

ResultSet with export operation results

Examples

>>> # Export users to S3 stage with compression
>>> client.query(User).filter(User.active == True).export_to_stage(
...     stage_name='s3_backup',
...     filename='active_users.csv',
...     format='csv',
...     header=True,
...     compression='gzip'
... )

>>> # Export aggregated sales data
>>> from sqlalchemy import func
>>> client.query(Sale) \
...     .select(Sale.product_id, func.sum(Sale.quantity).label('total_sold')) \
...     .group_by(Sale.product_id) \
...     .having(func.sum(Sale.quantity) > 100) \
...     .export_to_stage('data_warehouse', 'sales_summary.csv', header=True)

>>> # Export to JSONLINE format
>>> client.query(Event).filter(Event.timestamp > '2025-01-01').export_to_stage(
...     stage_name='event_archive',
...     filename='events_2025.jsonl',
...     format='jsonline'
... )

Note

The stage must exist before exporting
The stage must have appropriate write permissions
Use compression for large exports to save storage and transfer costs
This is more flexible than export_to_file() as it supports cloud storage

export_to(stage_or_filepath: str, filename: str | None = None, **kwargs) → Any[source]

Smart export method that automatically chooses between file or stage export.

This is a convenience method that determines whether to export to a file or stage based on the arguments provided. If filename is provided, it exports to a stage. Otherwise, it exports to a file.

Parameters:

stage_or_filepath – Stage name (if filename provided) or file path (if filename not provided)
filename – Optional filename for stage export. If provided, exports to stage.
**kwargs – Additional export options (format, header, compression, etc.)

Returns:

ResultSet with export operation results

Examples

>>> # Export to stage (recommended)
>>> client.query(User).filter(User.age > 25).export_to(
...     'my_stage',
...     'users_over_25.csv',
...     format='csv',
...     header=True
... )

>>> # Export to file
>>> client.query(Order).filter(Order.status == 'completed').export_to(
...     '/tmp/completed_orders.csv',
...     format='csv',
...     header=True
... )

>>> # Export with compression
>>> client.query(Event).export_to(
...     'event_archive',
...     'events_2025.csv',
...     format='csv',
...     header=True,
...     compression='gzip'
... )

Note

If filename is provided, exports to stage (more flexible, supports cloud storage)
If filename is not provided, exports to local file on server
For better clarity, consider using export_to_stage() or export_to_file() directly

CTE

class matrixone.orm.CTE(name: str, query, recursive: bool = False)[source]

Bases: object

CTE (Common Table Expression) class for MatrixOne queries

__init__(name: str, query, recursive: bool = False)[source]

as_sql() → tuple[str, list][source]: Get the compiled SQL and parameters for this CTE

LogicalIn

class matrixone.orm.LogicalIn(column, values)[source]

Bases: object

Helper class for creating IN conditions that can be used in filter() method.

This class provides a way to create IN conditions with support for various value types including FulltextFilter objects, lists, and SQLAlchemy expressions.

Usage

# List of values query.filter(logical_in(“city”, [“北京”, “上海”, “广州”])) query.filter(logical_in(User.id, [1, 2, 3, 4]))

# FulltextFilter query.filter(logical_in(“id”, boolean_match(“title”, “content”).must(“python”)))

# Subquery subquery = client.query(User).select(User.id).filter(User.active == True) query.filter(logical_in(“author_id”, subquery))

__init__(column, values)[source]

compile(compile_kwargs=None)[source]: Compile to SQL expression for use in filter() method

logical_in

matrixone.orm.logical_in(column, values)[source]

Create a logical IN condition for use in filter() method.

This function provides enhanced IN functionality that can handle various types of values and expressions, making it more flexible than standard SQLAlchemy IN operations. It automatically generates appropriate SQL based on the input type.

Key Features:

Support for lists of values with automatic SQL generation
Integration with SQLAlchemy expressions
FulltextFilter support for complex search conditions
Subquery support for dynamic value sets
Automatic parameter binding and SQL injection prevention

Args:

column: Column to check against. Can be:
    - String column name: "user_id"
    - SQLAlchemy column: User.id
    - Column expression: func.upper(User.name)
values: Values to check against. Can be:
    - List of values: [1, 2, 3, "a", "b"]
    - Single value: 42 or "test"
    - SQLAlchemy expression: func.count(User.id)
    - FulltextFilter object: boolean_match("title", "content").must("python")
    - Subquery object: client.query(User).select(User.id)
    - None: Creates "column IN (NULL)" condition

Returns:

LogicalIn: A logical IN condition object that can be used in filter().
           The object automatically compiles to appropriate SQL when used.

Examples

# List of values - generates: WHERE city IN (‘北京’, ‘上海’, ‘广州’) query.filter(logical_in(“city”, [“北京”, “上海”, “广州”])) query.filter(logical_in(User.id, [1, 2, 3, 4]))

# Single value - generates: WHERE id IN (42) query.filter(logical_in(“id”, 42))

# SQLAlchemy expression - generates: WHERE id IN (SELECT COUNT(*) FROM users) query.filter(logical_in(“id”, func.count(User.id)))

# FulltextFilter - generates: WHERE id IN (SELECT id FROM table WHERE MATCH(…)) query.filter(logical_in(User.id, boolean_match(“title”, “content”).must(“python”)))

# Subquery - generates: WHERE user_id IN (SELECT id FROM active_users) active_user_ids = client.query(User).select(User.id).filter(User.active == True) query.filter(logical_in(“user_id”, active_user_ids))

# NULL value - generates: WHERE id IN (NULL) query.filter(logical_in(“id”, None))

Note: This function is designed to work seamlessly with MatrixOne’s query builder and provides better integration than standard SQLAlchemy IN operations.