PostgreSQL materialized view not refreshing from Python

Question

I created a materialized view in TimescaleDB (a PostgreSQL extension for time series data), and when I refresh it with the following code from PGAdmin, the latest data are added and all is well:

REFRESH MATERIALIZED VIEW CONCURRENTLY 
    public.time_series_mv
    WITH DATA;

When I run the exact same code in Python, with the same user (postgres), it indicates success (i.e. no errors occur) but the latest data have not actually been added to the materialized view...

I therefore tried creating a function to do the same thing:

CREATE OR REPLACE FUNCTION refresh_time_series_mv()
    RETURNS void
    SECURITY DEFINER
    AS $$
    BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY time_series_mv with data;
    RETURN;
    END;
$$ LANGUAGE plpgsql;

Then I run the function as follows, and again, it works from PGAdmin in my browser, but not when I run the same SQL from Python in my scheduling application.

select refresh_time_series_mv();

Here's my Python code:

import psycopg2

SQL1 = """
    REFRESH MATERIALIZED VIEW CONCURRENTLY 
    public.time_series_mv
    WITH DATA
"""

SQL2 = "select refresh_time_series_mv();"

host = os.getenv("HOST") 
port = int(os.getenv("PORT"))
dbname = os.getenv('DB')
user = os.getenv("USER")
password = os.getenv("PASS")

conn = psycopg2.connect(
    host=host, 
    port=port, 
    dbname=dbname, 
    user=user, 
    password=password, 
    connect_timeout=5
) 
cursor = conn.cursor()
cursor.execute(SQL1)
# cursor.execute(SQL2)
cursor.close()
conn.close()

The Python code runs successfully (apparently, to Python) whether I use "SQL1" or "SQL2", but again, the data are not refreshed in the materialized view...

I'm using the same username and password to connect to the database, whether in PGAdmin or in Python.

Any ideas? Thanks! -Sean

Answer 1

As @jjanes mentioned in a comment, I needed to add the following after cursor.execute(SQL):

conn.commit()

Problem solved. Either SQL statement I mentioned in my question would've worked if the conn.commit() had been there.