SDKs

Python SDK

The Python SDK spicepy queries Spice.ai from Python. It uses Apache Arrow Flight to stream results, returning Apache Arrow records that convert directly into pandas dataframes.

Requirements

  • Python 3.11 or later

The following packages are installed automatically:

  • pyarrow
  • pandas
  • certifi
  • requests

Installation

Install from the GitHub repository, pinned to a release tag:

pip install git+https://github.com/spiceai/spicepy@v4.0.0

To use parameterized queries, two additional packages are required:

pip install adbc-driver-flightsql adbc-driver-manager

Usage

Create a Client, then call sql():

from spicepy import Client

client = Client(
    api_key='API_KEY',
    flight_url='grpc+tls://us-east-1-prod-aws-flight.spiceai.io',
)

data = client.sql('SELECT trip_distance, total_amount FROM taxi_trips ORDER BY trip_distance DESC LIMIT 10;', timeout=5*60)
pd = data.read_pandas()

Client has the following arguments, all optional:

  • api_key (string): Project API key, used to authenticate with Spice.ai Cloud. Falls back to the SPICE_API_KEY environment variable.

  • flight_url (string): Arrow Flight endpoint (default: grpc://localhost:50051). Use grpc+tls:// for TLS and grpc:// for plaintext.

  • http_url (string): HTTP endpoint, used for dataset refreshes (default: http://localhost:8090).

    Both default to the local runtime, so querying Spice.ai Cloud means passing the Cloud endpoint explicitly. The examples on this page use us-east-1; for a project in another region, substitute your project's CNAME — see Default endpoints. The region-agnostic flight.spiceai.io and data.spiceai.io hostnames also still work, and resolve to the us-east-1 endpoints.

  • tls_root_cert (Path or string): Path to the TLS certificate to use for the secure connection (omit for automatic detection).

  • user_agent (string): Overrides the reported user agent.

Once a Client is obtained, queries can be made using the sql() function, which returns a pyarrow.flight.FlightStreamReader. It has the following arguments:

  • query (string, required): The SQL query.
  • timeout (int, optional): The timeout in seconds.

If no timeout is specified, it will default to a 10 min timeout then cancel the query, and a TimeoutError exception will be raised.

Call read_pandas() to read the whole result into a dataframe, or read it incrementally — see Streaming.

Usage with local Spice runtime

Follow the quickstart guide to install and run spice locally. flight_url and http_url both already default to the local runtime, so no arguments are required:

from spicepy import Client

client = Client()
data = client.sql('SELECT trip_distance, total_amount FROM taxi_trips ORDER BY trip_distance DESC LIMIT 10;', timeout=5*60)
pd = data.read_pandas()

Parameterized queries

sql_with_params(sql, params) binds positional $1, $2 placeholders and returns a pyarrow.RecordBatchReader. It requires the two ADBC packages listed under Installation.

reader = client.sql_with_params(
    'SELECT trip_distance, fare_amount FROM taxi_trips WHERE trip_distance > $1 LIMIT 10',
    [5.0],
)

for batch in reader:
    print(batch.to_pandas())

Parameter values may be plain Python values, whose Arrow type is inferred, or (value, pyarrow_type) tuples to set the type explicitly. Pass [] for a query with no parameters; None raises ValueError.

Asynchronous queries

query(sql) and query_with_params(sql, params) submit a query for asynchronous execution instead of streaming results back. Both return a QueryJob handle used to wait for completion and fetch results, and both require http_url to be configured and the runtime to be running in distributed (scheduler) mode.

job = client.query('SELECT * FROM taxi_trips')
job.wait()

for row in job.results():
    print(row)

Parameters passed to query_with_params must be JSON-encodable values — the (value, pyarrow_type) tuple form is accepted only by sql_with_params.

Refreshing a dataset

refresh_dataset(dataset, refresh_opts=None) triggers a refresh of an accelerated dataset over the HTTP endpoint. RefreshOpts accepts refresh_sql, refresh_mode, and refresh_jitter_max.

from spicepy import Client, RefreshOpts

client.refresh_dataset('taxi_trips', RefreshOpts(refresh_mode='full'))

Contributing

Contribute to or file an issue with the spicepy library at: https://github.com/spiceai/spicepy