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:
- Connecting to TimescaleDB
- Creating a relational table
- Creating a hypertable
- Inserting data
- Executing a query
Before you start, make sure you have:
- Installed TimescaleDB. For more information, see the installation documentation.
- Installed the
psycopg2library. For more information, see the psycopg2 documentation.
- OptionalA Python virtual environment.
In this section, you create a connection to TimescaleDB using the
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.
Import the psycogpg2 library:import psycopg2
Locate your TimescaleDB credentials and use them to compose a connection string for
- host URL
- database name
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 followsCONNECTION = "dbname=tsdb user=tsdbadmin password=secret host=host.com port=5432 sslmode=require"
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.
mainfunction, 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 databasecursor.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
sensor_data which holds the measurements of those sensors. The
measurements contain the time, sensor_id, temperature reading, and CPU
percentage of the sensors.
Compose a string which contains the SQL statement to create a relational table. This example creates a table called
sensors, with columns
location:query_create_sensors_table = """CREATE TABLE sensors (id SERIAL PRIMARY KEY,type VARCHAR(50),location VARCHAR(50));"""
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 1cursor.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.
Create a string variable that contains the
CREATE TABLESQL statement for your hypertable. Notice how the hypertable has the compulsory time column:# create sensor data hypertablequery_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));"""
SELECTstatement that converts the
sensor_datatable 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_hypertabledocs:query_create_sensordata_hypertable = "SELECT create_hypertable('sensor_data', 'time');"
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 persistentconn.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.
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
INSERTSQL 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, sensor))except (Exception, psycopg2.Error) as error:print(error.pgerror)conn.commit()
OptionalAlternatively, you can pass variables to the
cursor.executefunction 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, sensor)cursor.execute(SQL, data)except (Exception, psycopg2.Error) as error:print(error.pgerror)conn.commit()
If you choose to use
pgcopy instead, install the
using pip, and then add this line to your list of
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
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
see the psycopg2 documentation.
Define the SQL query you'd like to run on the database. This example is a simple
SELECTstatement querying each row from the previously created
sensor_datatable.query = "SELECT * FROM sensor_data;"
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)
To access all resulting rows returned by your query, use one of
pyscopg2's results retrieval methods, such as
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()
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.
Found an issue on this page?Report an issue!