Python SDK Quickstart

Trilogy's reference implementation is in Python. It gives you:

• A CLI to run .preql files
• An API to evaluate Trilogy queries inline
• A fully programmatic way to construct queries without writing Trilogy text

Pick the interface that fits your workflow—CLI or Python-native.

Installation

pip install pytrilogy

In Python

Trilogy is easy to embed in Python. It returns sql-alchemy result sets, which many tools integrate with.

from trilogy import Dialects, Environment

executor = Dialects.DUCK_DB.default_executor(environment=Environment())

results = executor.execute_text("SELECT 1 + 1->test;")

for rs in results:
    for row in rs.fetchall():
        print(row)

Defining Data Inline

To go beyond toy examples, you'll define your own data sources.

Environments

An Environment defines available tables, properties, and grain. You can:

• Build one inline (as above)
• Load it from a .preql file

Imported:

from trilogy import Environment, Dialects
from pathlib import Path

env = Environment.from_file(Path("./model.preql"))
executor = Dialects.DUCK_DB.default_executor(environment=env)

Inline:

from trilogy import Environment

executor = Dialects.DUCK_DB.default_executor(environment=Environment())

results = executor.execute_text("""
key user_id int;
property user_id.name string;

datasource users (
    uid:user_id,
    name:name
)
grain(user_id)
query '''
select 1 uid, 'Bach' as "name"
union all
select 2, 'Mozart',
union all
select 3, 'Beethoven'
''';

# run our query
select
    user_id,
    name,
    len(name)->name_length
;

""")

for rs in results:
    for row in rs.fetchall():
        print(row)

Backend Support

Trilogy uses sqlalchemy to connect to databases. You can install the necessary drivers with the following commands:

Tips

DuckDB is the default backend, and requires no additional configuration.

pip install pytrilogy[bigquery]
pip install pytrilogy[snowflake]
...etc

Snowflake Config

Snowflake requires account info.

from trilogy.dialect.config import SnowflakeConfig
executor = Dialects.SNOWFLAKE.default_executor(
            environment=presto_model,
            conf=SnowflakeConfig(
                account="account", username="user", password="password"
            ),
        )

Postgres Config

Postgres requires a connection string.

from trilogy import Dialects
from trilogy.dialect.config import  PostgresConfig

executor = Dialects.POSTGRES.default_executor(
    environment=presto_model,
    conf=PostgresConfig(
        host="account", port="port", username="user", password="password", database="db"
    ),
)

Loading Model File

Models can be imported from a path.

from trilogy import Environment, Dialects
from pathlib import Path

executor = Dialects.DUCK_DB.default_executor(environment=Environment.from_file(Path('./model.preql')))

For more information, see the concepts page.

VSCode Extension

Trilogy a VSCode extension to help with authoring. You can install it from the standard extension store.