Timescale Code Quick Start Guides are designed to help you integrate Timescale into your own programs. They use your favorite programming language to explain how to connect to a Timescale database, create and manage hypertables, and ingest and query data.

This quick start guide walks you through:

Before you start, make sure you have:

In this section, you create a connection to TimescaleDB using the psycopg2 library. This library is one of the most popular PostgreSQL libraries for Python. It allows you to execute raw SQL queries efficiently and safely, and prevents common attacks such as SQL injection.

  1. Import the psycogpg2 library:

    import psycopg2
  2. Locate your TimescaleDB credentials and use them to compose a connection string for psycopg2.

    You'll need:

    • password
    • username
    • host URL
    • port
    • database name
  3. Compose your connection string variable as a libpq connection string, using this format:

    CONNECTION = "postgres://username:password@host:port/dbname"

    If you're using a hosted version of TimescaleDB, or generally require an SSL connection, use this version instead:

    CONNECTION = "postgres://username:password@host:port/dbname?sslmode=require"

    Alternatively you can specify each parameter in the connection string as follows

    CONNECTION = "dbname=tsdb user=tsdbadmin password=secret host=host.com port=5432 sslmode=require"
    Warning

    This method of composing a connection string is for test or development purposes only. For production, use environment variables for sensitive details like your password, hostname, and port number.

  4. Use the psycopg2 connect function to create a new database session and create a new cursor object to interact with the database.

    In your main function, add these lines:

    CONNECTION = "postgres://username:password@host:port/dbname"
    with psycopg2.connect(CONNECTION) as conn:
    cursor = conn.cursor()
    # use the cursor to interact with your database
    # cursor.execute("SELECT * FROM table")

    Alternatively, you can create a connection object and pass the object around as needed, like opening a cursor to perform database operations:

    CONNECTION = "postgres://username:password@host:port/dbname"
    conn = psycopg2.connect(CONNECTION)
    cursor = conn.cursor()
    # use the cursor to interact with your database
    cursor.execute("SELECT 'hello world'")
    print(cursor.fetchone())

In this section, you create a table called sensors which holds the ID, type, and location of your fictional sensors. Additionally, you create a hypertable called sensor_data which holds the measurements of those sensors. The measurements contain the time, sensor_id, temperature reading, and CPU percentage of the sensors.

  1. Compose a string which contains the SQL statement to create a relational table. This example creates a table called sensors, with columns id, type and location:

    query_create_sensors_table = """CREATE TABLE sensors (
    id SERIAL PRIMARY KEY,
    type VARCHAR(50),
    location VARCHAR(50)
    );
    """
  2. Open a cursor, execute the query you created in the previous step, and commit the query to make the changes persistent. Afterward, close the cursor to clean up:

    cursor = conn.cursor()
    # see definition in Step 1
    cursor.execute(query_create_sensors_table)
    conn.commit()
    cursor.close()

When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable.

  1. Create a string variable that contains the CREATE TABLE SQL statement for your hypertable. Notice how the hypertable has the compulsory time column:

    # create sensor data hypertable
    query_create_sensordata_table = """CREATE TABLE sensor_data (
    time TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER,
    temperature DOUBLE PRECISION,
    cpu DOUBLE PRECISION,
    FOREIGN KEY (sensor_id) REFERENCES sensors (id)
    );
    """
  2. Formulate a SELECT statement that converts the sensor_data table to a hypertable. You must specify the table name to convert to a hypertable, and the name of the time column as the two arguments. For more information, see the create_hypertable docs:

    query_create_sensordata_hypertable = "SELECT create_hypertable('sensor_data', by_range('time'));"
    Note

    The by_range dimension builder is an addition to TimescaleDB 2.13.

  3. Open a cursor with the connection, execute the statements from the previous steps, commit your changes, and close the cursor:

    cursor = conn.cursor()
    cursor.execute(query_create_sensordata_table)
    cursor.execute(query_create_sensordata_hypertable)
    # commit changes to the database to make changes persistent
    conn.commit()
    cursor.close()

You can insert data into your hypertables in several different ways. In this section, you can use psycopg2 with prepared statements, or you can use pgcopy for a faster insert.

  1. This example inserts a list of tuples, or relational data, called sensors, into the relational table named sensors. Open a cursor with a connection to the database, use prepared statements to formulate the INSERT SQL statement, and then execute that statement:

    sensors = [('a', 'floor'), ('a', 'ceiling'), ('b', 'floor'), ('b', 'ceiling')]
    cursor = conn.cursor()
    for sensor in sensors:
    try:
    cursor.execute("INSERT INTO sensors (type, location) VALUES (%s, %s);",
    (sensor[0], sensor[1]))
    except (Exception, psycopg2.Error) as error:
    print(error.pgerror)
    conn.commit()
  2. OptionalAlternatively, you can pass variables to the cursor.execute function and separate the formulation of the SQL statement, SQL, from the data being passed with it into the prepared statement, data:

    SQL = "INSERT INTO sensors (type, location) VALUES (%s, %s);"
    sensors = [('a', 'floor'), ('a', 'ceiling'), ('b', 'floor'), ('b', 'ceiling')]
    cursor = conn.cursor()
    for sensor in sensors:
    try:
    data = (sensor[0], sensor[1])
    cursor.execute(SQL, data)
    except (Exception, psycopg2.Error) as error:
    print(error.pgerror)
    conn.commit()

If you choose to use pgcopy instead, install the pgcopy package using pip, and then add this line to your list of import statements:

from pgcopy import CopyManager

This section covers how to execute queries against your database.

The first procedure shows a simple SELECT * query. For more complex queries, you can use prepared statements to ensure queries are executed safely against the database.

For more information about properly using placeholders in psycopg2, see the basic module usage document. For more information about how to execute more complex queries in psycopg2, see the psycopg2 documentation.

  1. Define the SQL query you'd like to run on the database. This example is a simple SELECT statement querying each row from the previously created sensor_data table.

    query = "SELECT * FROM sensor_data;"
  2. Open a cursor from the existing database connection, conn, and then execute the query you defined:

    cursor = conn.cursor()
    query = "SELECT * FROM sensor_data;"
    cursor.execute(query)
  3. To access all resulting rows returned by your query, use one of pyscopg2's results retrieval methods, such as fetchall() or fetchmany(). This example prints the results of the query, row by row. Note that the result of fetchall() is a list of tuples, so you can handle them accordingly:

    cursor = conn.cursor()
    query = "SELECT * FROM sensor_data;"
    cursor.execute(query)
    for row in cursor.fetchall():
    print(row)
    cursor.close()
  4. OptionalIf you want a list of dictionaries instead, you can define the cursor using DictCursor:

    cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    Using this cursor, cursor.fetchall() returns a list of dictionary-like objects.

For more complex queries, you can use prepared statements to ensure queries are executed safely against the database.

Keywords

Found an issue on this page?

Report an issue!