This quick start guide is designed to get the Python developer up and running with TimescaleDB as their database. In this tutorial, you'll learn how to:
Before you start, make sure you have:
psycopg2library installed, which you can install with pip.
Locate your TimescaleDB credentials. You need them to compose a connection string for
You'll need the following credentials:
Compose your connection string variable as a libpq connection string, using the following format:
CONNECTION = "postgres://username:[email protected]:port/dbname"
If you're using a hosted version of TimescaleDB, or generally require an SSL connection, use this version instead:
CONNECTION = "postgres://username:[email protected]: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"
main function, add the following lines:
CONNECTION = "postgres://username:[email protected]:port/dbname" def main(): 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:[email protected]:port/dbname" def main(): conn = psycopg2.connect(CONNECTION) cursor = conn.cursor() # use the cursor to interact with your database cursor.execute("SELECT 'hello world'") print(cursor.fetchone())
Congratulations, you've successfully connected to TimescaleDB using Python.
First, compose a string which contains the SQL statement that you would use to create
a relational table. In the example below, we create a table called
query_create_sensors_table = "CREATE TABLE sensors (id SERIAL PRIMARY KEY, type VARCHAR(50), location VARCHAR(50));"
Next, we execute the
CREATE TABLE statement by opening a cursor, executing the
query from Step 1 and committing the query we executed in order to make the changes persistent.
Afterward, we close the cursor to clean up:
cursor = conn.cursor() # see definition in Step 1 cursor.execute(query_create_sensors_table) conn.commit() cursor.close()
Congratulations, you've successfully created a relational table in TimescaleDB using Python.
In TimescaleDB, the primary point of interaction with your data is a hypertable. It provides an abstraction of a single continuous table across all space and time intervals. You can can query it via standard SQL.
Virtually all user interactions with TimescaleDB are with hypertables. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks can and should all be executed on the hypertable.
A hypertable is defined by a standard schema with column names and types, with at least one column specifying a time value. Learn more about using hypertables in the API documentation.
First, create a string variable which houses 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) );"""
Next, formulate a
SELECT statement that converts the
sensor_data table to a hypertable. Note that you must specify
the table name which you wish to convert to a hypertable and its time column name as the two arguments, as mandated by
query_create_sensordata_hypertable = "SELECT create_hypertable('sensor_data', 'time');"
Now bring it all together by opening a cursor with our connection, executing the statements from step 1 and step 2 and committing your changes and closing 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()
Congratulations, you've successfully created a hypertable in your Timescale database using Python!
Here's a typical pattern you'd use to insert data into a table. In the
example below, insert a list of tuples (relational data) called
sensors, into the
relational table named
First, we open a cursor with our connection to the database, then using prepared
statements formulate our
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, sensor)) except (Exception, psycopg2.Error) as error: print(error.pgerror) conn.commit()
A cleaner way to pass variables to the
cursor.execute function is to separate the formulation of our SQL
SQL, from the data being passed with it into the prepared statement,
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()
Congratulations, you've successfully inserted data into TimescaleDB using Python.
psycopg2 by itself may be sufficient for you to insert rows into your
hypertable, if you need quicker performance, you can use
pgcopy. To do this, install
pgcopy using pip and
then add this line to your list of
from pgcopy import CopyManager
First we generate random sensor data using the
generate_series function provided by PostgreSQL.
In the example query below, you will insert a total of 480 rows of data (4 readings, every 5 minutes, for 24 hours).
In your application, this would be the query that saves your time-series data into the hypertable.
# for sensors with ids 1-4 for id in range(1, 4, 1): data = (id,) # create random data simulate_query = """SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time, %s as sensor_id, random()*100 AS temperature, random() AS cpu """ cursor.execute(simulate_query, data) values = cursor.fetchall()
Then we define the column names of the table we want to insert data into. In this
case, we're using the
sensor_data hypertable that we created in the
"Generate a Hypertable" section above. This hypertable consists of the columns
cpu. We define these column names
in a list of strings called
cols = ['time', 'sensor_id', 'temperature', 'cpu']
Lastly we create an instance of the
mgr, and pass our
connection variable, hypertable name, and list of column names. Then we use
copy function of the CopyManager to insert the data into the database
mgr = CopyManager(conn, 'sensor_data', cols) mgr.copy(values)
Finally, commit to persist changes:
Full sample code to insert data into TimescaleDB using
pgcopy, using the example of sensor data from four sensors:
# insert using pgcopy def fast_insert(conn): cursor = conn.cursor() # for sensors with ids 1-4 for id in range(1, 4, 1): data = (id,) # create random data simulate_query = """SELECT generate_series(now() - interval '24 hour', now(), interval '5 minute') AS time, %s as sensor_id, random()*100 AS temperature, random() AS cpu """ cursor.execute(simulate_query, data) values = cursor.fetchall() # column names of the table you're inserting into cols = ['time', 'sensor_id', 'temperature', 'cpu'] # create copy manager with the target table and insert mgr = CopyManager(conn, 'sensor_data', cols) mgr.copy(values) # commit after all sensor data is inserted # could also commit after each sensor insert is done conn.commit()
You can also check if the insertion worked:
cursor.execute("SELECT * FROM sensor_data LIMIT 5;") print(cursor.fetchall())
Congratulations, you've successfully inserted time-series data into TimescaleDB
using Python and the
First, define the SQL query you'd like to run on the database. The example below
is a simple
SELECT statement querying each row from the previously created
query = "SELECT * FROM sensor_data;"
Next, open a cursor from our existing database connection,
and then execute the query you defined in Step 1:
cursor = conn.cursor() query = "SELECT * FROM sensor_data;" cursor.execute(query)
To access all resulting rows returned by your query, use
pyscopg2's results retrieval methods,
fetchmany(). In the example below, we're simply
printing the results of our query, row by row. Note that the result of
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()
If you want a list of dictionaries instead, you can define the cursor using
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
Using this cursor,
cursor.fetchall() will return a list of dictionary-like objects.
For more complex queries than a simple
SELECT *, we can use prepared statements
to ensure our queries are executed safely against the database. We write our
query using placeholders as shown in the sample code below. For more information about properly using placeholders
in psycopg2, see the basic module usage document.
# query with placeholders cursor = conn.cursor() query = """ SELECT time_bucket('5 minutes', time) AS five_min, avg(cpu) FROM sensor_data JOIN sensors ON sensors.id = sensor_data.sensor_id WHERE sensors.location = %s AND sensors.type = %s GROUP BY five_min ORDER BY five_min DESC; """ data = (location, sensor_type) cursor.execute(query, data) results = cursor.fetchall()
Congratulations, you've successfully executed a query on TimescaleDB using Python! For more information on how to execute more complex queries, see the psycopg2 documentation
Now that you're able to connect, read, and write to a TimescaleDB instance from your Python application, and generate the scaffolding necessary to build a new application from an existing TimescaleDB instance, be sure to check out these advanced TimescaleDB tutorials: