Presto Python Client

Warnings

• gotcha Confusion with Trino/PrestoSQL Clients: Users often confuse `presto-python-client` (designed for PrestoDB) with clients for Trino (formerly PrestoSQL). Ensure you are installing and using `presto-python-client` if your backend is PrestoDB, and not `trino` or `presto-client` packages, which serve Trino clusters.
  Fix: Verify your Presto server type (PrestoDB vs. Trino) and install the corresponding client library (`presto-python-client` for PrestoDB, `trino` for Trino).
• deprecated Older Python Version Support: As of version 0.8.4, the library officially supports Python 2.7, 3.5, 3.6, and 3.7. While it may function on newer Python versions (3.8+), these older versions are approaching or past their end-of-life and may not be actively tested or fully compatible with future changes.
  Fix: Develop and test your application against a compatible Python version, ideally within the officially supported range. Be prepared for potential compatibility issues if using newer Python interpreters.
• gotcha `Cursor.fetchmany()` Default Behavior: By default, the `Cursor.fetchmany()` method retrieves only a single row. For efficient retrieval of multiple rows in batches, it is crucial to explicitly set `prestodb.dbapi.Cursor.arraysize` to the desired number of rows before calling `fetchmany()`.
  Fix: Set `cursor.arraysize = <desired_batch_size>` before calling `cursor.fetchmany()` to retrieve more than one row at a time.
• gotcha Autocommit Mode vs. Explicit Transactions: The client operates in autocommit mode by default. To enable explicit transaction management (e.g., `commit()` or `rollback()`), the `isolation_level` parameter in `prestodb.dbapi.connect()` must be set to a value other than `IsolationLevel.AUTOCOMMIT`, such as `IsolationLevel.READ_COMMITTED`.
  Fix: When creating a connection, specify `isolation_level=IsolationLevel.READ_COMMITTED` (or another desired level) to enable transaction control.
• gotcha SSL/TLS Certificate Verification Issues: When connecting to Presto via HTTPS, especially with self-signed certificates, `SSLCertVerificationError` is a common issue. This occurs if the client cannot verify the server's certificate.
  Fix: Ensure the `http_scheme` is set to 'https'. If using self-signed certificates, you may need to provide a `ca_bundle` path to the `auth` object or, for testing purposes only, disable verification by setting `conn._http_session.verify = False` (not recommended for production).
• gotcha Connection Timeouts and Refused Connections: Errors like `CONNECTION_TIMEOUT` or 'Connection Refused' typically indicate underlying network issues (firewalls, routing), an unresponsive or overloaded Presto server, or incorrect `host` and `port` configurations in the connection string.
  Fix: Verify network connectivity between client and server (e.g., using `ping` or `telnet`). Confirm the Presto server is running and accessible, and double-check the `host` and `port` parameters in your `connect()` call.

Install

• pip install presto-python-client Install stable version

Imports

• connect

  from prestodb.dbapi import connect

  The primary function to establish a connection to Presto.
• BasicAuthentication

  from prestodb.auth import BasicAuthentication

  Used for authenticating with Presto using username and password.
• IsolationLevel

  from prestodb.transaction import IsolationLevel

  Enum for setting transaction isolation levels.
• PrestoQueryError

  from prestodb.exceptions import PrestoQueryError

  Specific exception for Presto query-related errors, inheriting from base Exception.

Quickstart

This quickstart demonstrates how to establish a connection to a Presto server using the DBAPI interface, execute a simple query, and fetch the results. It includes optional basic authentication and uses environment variables for configuration, making it suitable for secure execution in various environments.

import os
from prestodb.dbapi import connect
from prestodb.auth import BasicAuthentication

host = os.environ.get('PRESTO_HOST', 'localhost')
port = int(os.environ.get('PRESTO_PORT', 8080))
user = os.environ.get('PRESTO_USER', 'the-user')
password = os.environ.get('PRESTO_PASSWORD', '') # Only if basic auth is needed
catalog = os.environ.get('PRESTO_CATALOG', 'the-catalog')
schema = os.environ.get('PRESTO_SCHEMA', 'the-schema')

auth = None
if password:
    auth = BasicAuthentication(user, password)

try:
    conn = connect(
        host=host,
        port=port,
        user=user,
        catalog=catalog,
        schema=schema,
        http_scheme=os.environ.get('PRESTO_HTTP_SCHEME', 'http'),
        auth=auth
    )
    cur = conn.cursor()

    cur.execute('SELECT * FROM system.runtime.nodes')
    rows = cur.fetchall()

    print("Successfully connected to Presto and fetched data.")
    for row in rows:
        print(row)

    cur.close()
    conn.close()
except Exception as e:
    print(f"An error occurred: {e}")