Sqlalchemy setup for postgresql with timescaledb extension [duplicate]

Question

I was trying to hook up the sqlalchemy with my underlying postgresql, which uses the timescaledb extension. All queries work fine when I try them from the psql terminal client. But when I try to use python & sqlalchemy to do it, it keeps throwing me an error.

Here's the very basic code snippet that I try to test it with:

engine = create_engine('postgres://usr:pwd@localhost:5432/postgres', echo=True)
engine.execute('select 1;')

And it always shows the following the error message:

File "/home/usr/.local/share/virtualenvs/redbird-lRSbFM0t/lib/python3.6/site-packages/psycopg2/extras.py", line 917, in get_oids
""" % typarray)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not access file "timescaledb-0.9.0": No such file or directory

The connection to the db is fine, otherwise it won't know the db is using timescaledb.

Any one has any insights?

UPDATE: I try to use psycopg2 directly. It basically gives the same error. DB is connected successfully, but timescaledb-0.9.0 cannot be accessed.

Here's the code snippted

conn_string = "host='localhost' dbname='db' user='usr' password='pwd'"
print("Connecting to database\n ->%s " % (conn_string))

conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
print("Connected!\n")

cursor.execute("\dx")
records = cursor.fetchall()

Here's the exact same error message:

Connecting to database
Connected!

Traceback (most recent call last):
File "/home/usr/Workspace/somepath/web/model/model.py", line 21, in <module>
cursor.execute("\dx")
psycopg2.OperationalError: could not access file "timescaledb-0.9.0": No such file or directory

Answer 1

This seems very similar to my issue.

I guess you also updated to a new version of Timescale? The thing is: After each update of the timescale package you don't just have to make sure the library is preloaded (as the warning says on the command line) - you also have to upgrade each database that uses the extension manually via psql.

See my own answer to my issue for the steps.

--

This snipplet works for me:

#! /usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2

# Connect to an existing database.
conn = psycopg2.connect(dbname='my-db-name',
                        user='postgres',
                        password='super-secret',
                        host='localhost',
                        port='5432')

# Open a cursor to perform database operations.
cur = conn.cursor()

# Query the database and obtain data as Python objects.
cur.execute('SELECT * FROM my-table-name LIMIT 100 ;')

# Print results.
results = cur.fetchall()
for result in results:
    print(result)

# Close communication with the database.
cur.close()
conn.close()

Using the cursor to executue psql commands also doesn't work for me. I don't think it is supposed to. But what works reliably is doing SQL:

# Check if the database has the timescaledb extension installed.
# This is about the same as xecuting '\dx' on psql.
cur.execute('SELECT * from pg_extension;')