Use Python and the Flight SQL library to query data

Use Python and the Flight SQL library to query data stored in an InfluxDB Cloud Serverless bucket.

• Get started using Python to query InfluxDB
• Create a Python virtual environment
  • Install Python
  • Create a project virtual environment
  • Install Anaconda
• Query InfluxDB using Flight SQL
  • Install the Flight SQL Python Library
  • Create a query client
  • Execute a query
  • Retrieve data for Flight SQL query results

Get started using Python to query InfluxDB

This guide follows the recommended practice of using Python virtual environments. If you don’t want to use virtual environments and you have Python installed, continue to Query InfluxDB using Flight SQL.

Create a Python virtual environment

Python virtual environments keep the Python interpreter and dependencies for your project self-contained and isolated from other projects.

To install Python and create a virtual environment, choose one of the following options:

• Python venv: The venv module comes standard in Python as of version 3.5.

• Anaconda® Distribution: A Python/R data science distribution that provides Python and the conda package and environment manager.

  venv Anaconda

  Install Python

  1. Follow the Python installation instructions to install a recent version of the Python programming language for your system.

  2. Check that you can run python and pip commands. pip is a package manager included in most Python distributions.

     In your terminal, enter the following commands:

     python --version
     

     pip --version
     

     Depending on your system, you may need to use version-specific commands–for example.

     python3 --version
     

     pip3 --version
     

     If neither pip nor pip<PYTHON_VERSION> works, follow one of the Pypa.io Pip installation methods for your system.

  Create a project virtual environment

  1. Create a directory for your Python project and change to the new directory–for example:

     mkdir ./PROJECT_DIRECTORY && cd $_
     

  2. Use the Python venv module to create a virtual environment–for example:

     python -m venv envs/virtualenv-1
     

     venv creates the new virtual environment directory in your project.

  3. To activate the new virtual environment in your terminal, run the source command and pass the file path of the virtual environment activate script:

     source envs/VIRTUAL_ENVIRONMENT_NAME/bin/activate
     

     For example:

     source envs/virtualenv-1/bin/activate
     

  Install Anaconda

  1. Follow the Anaconda installation instructions for your system.

  2. Check that you can run the conda command:

     conda
     

  3. Use conda to create a virtual environment–for example:

     conda create --prefix envs/virtualenv-1 
     

     conda creates a virtual environment in a directory named ./envs/virtualenv-1.

  4. To activate the new virtual environment, use the conda activate command and pass the directory path of the virtual environment:

     conda activate envs/VIRTUAL_ENVIRONMENT_NAME
     

     For example:

     conda activate ./envs/virtualenv-1
     

When a virtual environment is activated, the name displays at the beginning of your terminal command line–for example:

(virtualenv-1) $ PROJECT_DIRECTORY

Query InfluxDB using Flight SQL

1. Install the Flight SQL Python Library
2. Create a query client
3. Execute a query

Install the Flight SQL Python Library

The flightsql-dbapi Flight SQL library for Python provides a DB API 2 interface and SQLAlchemy dialect for Flight SQL. Installing flightsql-dbapi also installs the pyarrow library that you’ll use for working with Arrow data.

In your terminal, use pip to install flightsql-dbapi:

pip install flightsql-dbapi

With flightsql-dbapi and pyarrow installed, you’re ready to query and analyze data stored in an InfluxDB bucket.

Create a query client

The following example shows how to use Python with flightsql-dbapi and the DB API 2 interface to instantiate a Flight SQL client configured for an InfluxDB bucket.

1. In your editor, copy and paste the following sample code to a new file–for example, query-example.py:

   # query-example.py
   
   from flightsql import FlightSQLClient
   
   # Instantiate a FlightSQLClient configured for your bucket
   client = FlightSQLClient(host='cloud2.influxdata.com',
                           token='INFLUX_READ_WRITE_TOKEN',
                           metadata={'database': 'BUCKET_NAME'},
                           features={'metadata-reflection': 'true'})
   

2. Replace the following configuration values:

   • INFLUX_READ_WRITE_TOKEN: An InfluxDB token with read permission to the bucket.
   • BUCKET_NAME: The name of the InfluxDB bucket to query.

Execute a query

To execute an SQL query, call the query client’s execute(query) method and pass the query as a string.

Syntax

execute(query: str, call_options: Optional[FlightSQLCallOptions] = None)

Example

# query-example.py

from flightsql import FlightSQLClient

client = FlightSQLClient(host='cloud2.influxdata.com',
    token='INFLUX_READ_WRITE_TOKEN',
    metadata={'database': 'BUCKET_NAME'},
    features={'metadata-reflection': 'true'})

# Execute the query
info = client.execute("SELECT * FROM home")

Replace the following:

• INFLUX_READ_WRITE_TOKEN: An InfluxDB token with read permission to the bucket.
• BUCKET_NAME: The name of the InfluxDB bucket to query.

The response contains a flight.FlightInfo object that contains metadata and an endpoints: [...] list. Each endpoint contains the following:

• A list of addresses where you can retrieve the data.
• A ticket value that identifies the data to retrieve.

Next, use the ticket to retrieve data for Flight SQL query results

Retrieve data for Flight SQL query results

To retrieve Arrow data for a query result, call the client’s do_get(ticket) method.

Syntax

 do_get(ticket, call_options: Optional[FlightSQLCallOptions] = None)

Example

The following sample shows how to use Python with flightsql-dbapi and pyarrow to query InfluxDB and retrieve data.

# query-example.py

from flightsql import FlightSQLClient

# Instantiate a FlightSQLClient configured for a bucket
client = FlightSQLClient(host='cloud2.influxdata.com',
    token='INFLUX_READ_WRITE_TOKEN',
    metadata={'database': 'BUCKET_NAME'},
    features={'metadata-reflection': 'true'})

# Execute the query to retrieve FlightInfo
info = client.execute("SELECT * FROM home")

# Extract the token for retrieving data
ticket = info.endpoints[0].ticket

# Use the ticket to request the Arrow data stream.
# Return a FlightStreamReader for streaming the results.
reader = client.do_get(ticket)

# Read all data to a pyarrow.Table
table = reader.read_all()

do_get(ticket) returns a pyarrow.flight.FlightStreamReader for streaming Arrow record batches.

To read data from the stream, call one of the following FlightStreamReader methods:

• read_all(): Read all record batches as a pyarrow.Table.
• read_chunk(): Read the next RecordBatch and metadata.
• read_pandas(): Read all record batches and convert them to a pandas.DataFrame.

Next, learn how to use Python tools to work with time series data:

• Use PyArrow
• Use pandas