trino
trino is the official Python client for a Trino distributed SQL cluster. It implements Python DB API 2.0 for direct cursor queries and includes a SQLAlchemy dialect for applications that use engines and SQL expression objects. The client speaks Trino's HTTP protocol, supports basic, JWT, OAuth2, certificate, Kerberos, and GSSAPI authentication, and can carry session properties, catalog roles, tags, extra credentials, time zones, transactions, and compressed spooled results.
Use this client for Python access to a real Trino deployment; it is the obvious default and exposes the protocol's important controls. The operational work remains in TLS, identity, server compatibility, catalog behavior, result volume, and query governance.
Use it if
- You query an existing Trino cluster from Python and want the client maintained by the Trino organization
- You need DB API cursors or SQLAlchemy without implementing Trino's paginated HTTP protocol yourself
- Your cluster uses OAuth2, JWT, client certificates, Kerberos, GSSAPI, or catalog-specific roles
- You need session properties, client tags, source labels, compressed spooled results, or transaction controls exposed at connection time
- You do not operate or have access to a Trino coordinator: this is only a client and does not embed a query engine
- You need one client that also targets Hive or legacy Presto clusters; PyHive or a vendor-supported driver may match that mixed estate better
- You expect every database connector to support writes or transactions: Trino capabilities and isolation ultimately depend on the server version, catalog, and connector
- You cannot align client and cluster behavior: SQLAlchemy support requires Trino server 351 or newer, and newer protocol features such as spooling need compatible server configuration
- You plan to disable TLS verification to get authentication working; the README exposes verify=False, but that makes credentials and query traffic vulnerable and should not be a production fix
Setup reality
pip install trino installs the DB API client on Python 3.9 or newer. The base package includes requests, date and timezone support, LZ4, Zstandard, and orjson outside PyPy. SQLAlchemy, Kerberos, GSSAPI, and secure external-auth token caching are optional extras, and the Kerberos or GSSAPI paths may also need operating-system libraries, compiler headers, realm configuration, tickets, and network access to a KDC. A connection needs the coordinator host, port or full URL, a user identity, and usually catalog and schema. Use HTTPS for any credential-bearing authentication. BasicAuthentication repeats a username in both the connection and auth object; JWT values, passwords, and extra credentials should come from a secret store, not a checked-in URL. OAuth2 opens a browser by default and prints a redirect URL through its composed handlers, which surprises headless jobs. Without keyring, its token cache lives only on the authentication instance; with the external-authentication-token-cache extra, storage depends on a working OS keyring backend. The docs warn that omitting the username shares the OAuth token cache per host, so always set a stable user. Certificate authentication needs readable certificate and private-key paths. TLS verification accepts a CA bundle path for private CAs; do that instead of verify=False. fetchmany defaults to one row unless arraysize is changed. The client defaults to autocommit and to the client machine's local time zone, which changed from the older UTC behavior after 0.320. Set timezone='UTC' when reproducibility matters. Transactions begin on the first statement and only work where the Trino connector supports them. For large results, request a compatible json+zstd or json+lz4 spooled encoding and iterate or fetch in batches rather than calling fetchall blindly.
Patterns
Run a DB API queryrun-dbapi-query
from trino.dbapi import connect
conn = connect(
host="trino.example.com",
port=8443,
http_scheme="https",
user="analyst@example.com",
catalog="lakehouse",
schema="analytics",
)
cur = conn.cursor()
cur.execute("SELECT current_date")
print(cur.fetchone())The connection talks to an existing coordinator; it does not start or embed Trino.
Fetch rows in useful batchesfetch-result-batches
cur.arraysize = 1000
cur.execute("SELECT * FROM lakehouse.analytics.events")
while rows := cur.fetchmany():
process(rows)fetchmany defaults to one row unless arraysize is changed. Avoid fetchall for unbounded results.
Connect with password authenticationuse-basic-authentication
from trino.auth import BasicAuthentication
conn = connect(
host="trino.example.com",
port=8443,
http_scheme="https",
user=username,
auth=BasicAuthentication(username, password),
catalog="system",
)Use HTTPS. Password file, LDAP, and Salesforce server auth types use this client class.
Authenticate with a JWTuse-jwt-authentication
from trino.auth import JWTAuthentication
conn = connect(
host="trino.example.com",
port=8443,
http_scheme="https",
user=subject,
auth=JWTAuthentication(token),
catalog="lakehouse",
)Keep the token out of logs and URLs; its subject must satisfy the cluster's user-mapping rules.
Authenticate through OAuth2use-oauth-authentication
from trino.auth import OAuth2Authentication
auth = OAuth2Authentication()
conn = connect(
host="trino.example.com",
port=8443,
http_scheme="https",
user="analyst@example.com",
auth=auth,
)The default flow may open a browser and print a redirect URL. Set user so token caching is not shared only by host.
Verify TLS with a private CAtrust-private-ca
conn = connect(
host="trino.internal",
port=8443,
http_scheme="https",
user="service-account",
verify="/etc/ssl/certs/company-ca.pem",
)A CA bundle preserves certificate verification. Do not replace this with verify=False in production.
Set session properties, tags, roles, and timezoneset-session-controls
conn = connect(
host="trino.example.com",
user="analyst",
catalog="hive",
schema="sales",
session_properties={"query_max_run_time": "10m"},
client_tags=["finance", "scheduled"],
roles={"hive": "analyst_role"},
timezone="UTC",
)Property names, role availability, and enforcement come from the cluster; UTC avoids dependence on the client machine zone.
Request compressed spooled resultsenable-spooled-results
conn = connect(
host="trino.example.com",
user="analyst",
catalog="hive",
encoding=["json+zstd", "json+lz4", "json"],
)The coordinator must support the spooling protocol and one of the offered encodings.
Run an explicit transactionrun-transaction
from trino.transaction import IsolationLevel
conn = connect(
host="trino.example.com",
user="writer",
catalog="iceberg",
isolation_level=IsolationLevel.REPEATABLE_READ,
)
cur = conn.cursor()
try:
cur.execute("INSERT INTO audit_log VALUES (current_timestamp, 'start')")
conn.commit()
except Exception:
conn.rollback()
raiseTransactions begin with the first statement and only work when the selected catalog connector supports them.
Use the SQLAlchemy dialectcreate-sqlalchemy-engine
from sqlalchemy import create_engine, text
from trino.sqlalchemy import URL
engine = create_engine(URL(
host="trino.example.com",
port=8443,
catalog="system",
schema="runtime",
))
with engine.connect() as conn:
rows = conn.execute(text("SELECT * FROM nodes")).all()Install trino[sqlalchemy]. The README requires Trino server 351 or newer for SQLAlchemy usage.
Alternatives
| Package | Registry | Pick it when |
|---|---|---|
| pyhive | PyPI | You need Python DB API access across Hive and Presto-style engines as well as SQLAlchemy |
| sqlalchemy-trino | PyPI | You maintain an older project already coupled to the former standalone Trino SQLAlchemy dialect |
| duckdb | PyPI | Your data fits a local or embedded analytics workflow and you do not need a remote Trino federation layer |