Trino Python client

Client for Trino, a distributed SQL engine for interactive and batch big data processing. Provides a low-level client and a DBAPI 2.0 implementation and a SQLAlchemy adapter. It supports Python>=3.9 and PyPy.

Development

See DEVELOPMENT for information about code style, development process, and guidelines.

See CONTRIBUTING for contribution requirements.

Usage

The Python Database API (DBAPI)

Installation

$ pip install trino

Quick Start

Use the DBAPI interface to query Trino:

if host is a valid url, the port and http schema will be automatically determined. For example https://my-trino-server:9999 will assign the http_schema property to https and port to 9999.

from trino.dbapi import connect

conn = connect(
    host="<host>",
    port=<port>,
    user="<username>",
    catalog="<catalog>",
    schema="<schema>",
)
cur = conn.cursor()
cur.execute("SELECT * FROM system.runtime.nodes")
rows = cur.fetchall()

This will query the system.runtime.nodes system tables that shows the nodes in the Trino cluster.

The DBAPI implementation in trino.dbapi provides methods to retrieve fewer rows for example Cursor.fetchone() or Cursor.fetchmany(). By default Cursor.fetchmany() fetches one row. Please set trino.dbapi.Cursor.arraysize accordingly.

SQLAlchemy

Prerequisite

Trino server >= 351

Compatibility

trino.sqlalchemy is compatible with the latest 1.3.x, 1.4.x and 2.0.x SQLAlchemy versions at the time of release of a particular version of the client.

Installation

$ pip install trino[sqlalchemy]

Usage

To connect to Trino using SQLAlchemy, use a connection string (URL) following this pattern:

trino://<username>:<password>@<host>:<port>/<catalog>/<schema>

NOTE: password and schema are optional

Examples:

from sqlalchemy import create_engine
from sqlalchemy.schema import Table, MetaData
from sqlalchemy.sql.expression import select, text

engine = create_engine('trino://user@localhost:8080/system')
connection = engine.connect()

rows = connection.execute(text("SELECT * FROM runtime.nodes")).fetchall()

# or using SQLAlchemy schema
nodes = Table(
    'nodes',
    MetaData(schema='runtime'),
    autoload=True,
    autoload_with=engine
)
rows = connection.execute(select(nodes)).fetchall()

In order to pass additional connection attributes use connect_args method. Attributes can also be passed in the connection string.

from sqlalchemy import create_engine
from trino.sqlalchemy import URL

engine = create_engine(
    URL(
        host="localhost",
        port=8080,
        catalog="system"
    ),
    connect_args={
      "session_properties": {'query_max_run_time': '1d'},
      "client_tags": ["tag1", "tag2"],
      "roles": {"catalog1": "role1"},
    }
)

# or in connection string
engine = create_engine(
    'trino://user@localhost:8080/system?'
    'session_properties={"query_max_run_time": "1d"}'
    '&client_tags=["tag1", "tag2"]'
    '&roles={"catalog1": "role1"}'
)

# or using the URL factory method
engine = create_engine(URL(
  host="localhost",
  port=8080,
  client_tags=["tag1", "tag2"]
))

Authentication mechanisms

Basic authentication

The BasicAuthentication class can be used to connect to a Trino cluster configured with the Password file, LDAP or Salesforce authentication type:

DBAPI

from trino.dbapi import connect
from trino.auth import BasicAuthentication

conn = connect(
    user="<username>",
    auth=BasicAuthentication("<username>", "<password>"),
    http_scheme="https",
    ...
)

SQLAlchemy

from sqlalchemy import create_engine

engine = create_engine("trino://<username>:<password>@<host>:<port>/<catalog>")

# or as connect_args
from trino.auth import BasicAuthentication
engine = create_engine(
    "trino://<username>@<host>:<port>/<catalog>",
    connect_args={
        "auth": BasicAuthentication("<username>", "<password>"),
        "http_scheme": "https",
    }
)

JWT authentication

The JWTAuthentication class can be used to connect to a Trino cluster configured with the JWT authentication type:

DBAPI

from trino.dbapi import connect
from trino.auth import JWTAuthentication

conn = connect(
    user="<username>",
    auth=JWTAuthentication("<jwt_token>"),
    http_scheme="https",
    ...
)

SQLAlchemy

from sqlalchemy import create_engine

engine = create_engine("trino://<username>@<host>:<port>/<catalog>/<schema>?access_token=<jwt_token>")

# or as connect_args
from trino.auth import JWTAuthentication
engine = create_engine(
    "trino://<username>@<host>:<port>/<catalog>",
    connect_args={
        "auth": JWTAuthentication("<jwt_token>"),
        "http_scheme": "https",
    }
)

OAuth2 authentication

The OAuth2Authentication class can be used to connect to a Trino cluster configured with the OAuth2 authentication type.

A callback to handle the redirect url can be provided via param redirect_auth_url_handler of the trino.auth.OAuth2Authentication class. By default, it will try to launch a web browser (trino.auth.WebBrowserRedirectHandler) to go through the authentication flow and output the redirect url to stdout (trino.auth.ConsoleRedirectHandler). Multiple redirect handlers are combined using the trino.auth.CompositeRedirectHandler class.

The OAuth2 token will be cached either per trino.auth.OAuth2Authentication instance and username or, when keyring is installed, it will be cached within a secure backend (MacOS keychain, Windows credential locker, etc) under a key including host of the Trino connection. Keyring can be installed using pip install 'trino[external-authentication-token-cache]'.

[!WARNING] If username is not specified then the OAuth2 token cache is shared and stored per host.

DBAPI

from trino.dbapi import connect
from trino.auth import OAuth2Authentication

conn = connect(
    user="<username>",
    auth=OAuth2Authentication(),
    http_scheme="https",
    ...
)

SQLAlchemy

from sqlalchemy import create_engine
from trino.auth import OAuth2Authentication

engine = create_engine(
"trino://<username>@<host>:<port>/<catalog>",
    connect_args={
        "auth": OAuth2Authentication(),
        "http_scheme": "https",
    }
)

Certificate authentication

CertificateAuthentication class can be used to connect to Trino cluster configured with certificate based authentication. CertificateAuthentication requires paths to a valid client certificate and private key.

DBAPI

from trino.dbapi import connect
from trino.auth import CertificateAuthentication

conn = connect(
    user="<username>",
    auth=CertificateAuthentication("/path/to/cert.pem", "/path/to/key.pem"),
    http_scheme="https",
    ...
)

SQLAlchemy

from sqlalchemy import create_engine
from trino.auth import CertificateAuthentication

engine = create_engine("trino://<username>@<host>:<port>/<catalog>/<schema>?cert=<cert>&key=<key>")

# or as connect_args
engine = create_engine(
"trino://<username>@<host>:<port>/<catalog>",
    connect_args={
        "auth": CertificateAuthentication("/path/to/cert.pem", "/path/to/key.pem"),
        "http_scheme": "https",
    }
)

Kerberos authentication

Make sure that the Kerberos support is installed using pip install trino[kerberos]. The KerberosAuthentication class can be used to connect to a Trino cluster configured with the Kerberos authentication type:

DBAPI

from trino.dbapi import connect
from trino.auth import KerberosAuthentication

conn = connect(
    user="<username>",
    auth=KerberosAuthentication(...),
    http_scheme="https",
    ...
)

SQLAlchemy

from sqlalchemy import create_engine
from trino.auth import KerberosAuthentication

engine = create_engine(
    "trino://<username>@<host>:<port>/<catalog>",
    connect_args={
        "auth": KerberosAuthentication(...),
        "http_scheme": "https",
    }
)

GSSAPI authentication

Make sure that the GSSAPI support is installed using pip install trino[gssapi]. The GSSAPIAuthentication class can be used to connect to a Trino cluster configured with the Kerberos authentication type:

It follows the interface for KerberosAuthentication, but is using requests-gssapi, instead of [

trino

Description

Trino Python client

Development

Usage

The Python Database API (DBAPI)

SQLAlchemy

Authentication mechanisms

Basic authentication

JWT authentication

OAuth2 authentication

Certificate authentication

Kerberos authentication

GSSAPI authentication