Python script for importing .csv to_sql with Pandas fails unless I DROP TRIGGER for updating materialized viewb

Python script for importing .csv to_sql with Pandas fails unless I DROP TRIGGER for updating materialized viewb

I have a Postgres database with a table with a materialized view that automatically updates every time changes are made to the table via the following user-defined function and trigger:

I have a Postgres database with a table with a materialized view that automatically updates every time changes are made to the table via the following user-defined function and trigger:

create function refresh_matview_dohscrapemat()
returns trigger language plpgsql
as $$
begin
    refresh materialized view dohscrape;
    return null;
end $$;
create trigger refresh_matview_dohscrapemat
after insert or update or delete or truncate
on dohscrape for each statement
execute procedure refresh_matview_dohscrapemat();

I have a python script that uses pandas to automatically import .csv files into this table that only works if I DROP the TRIGGER for updating the materialized view before running it.

If I try to run the script without dropping the trigger I get the following error:

sqlalchemy.exc.NotSupportedError: (psycopg2.NotSupportedError) "dohscrape" is not a materialized view CONTEXT: SQL statement "refresh materialized view dohscrape" PL/pgSQL function refresh_matview_dohscrapemat() line 3 at SQL statement [SQL: 'INSERT INTO dohscrape (filename, content) VALUES (%(filename)s, %(content)s)'] [parameters: ({'filename':...(Background on this error at: http://sqlalche.me/e/tw8g)

It says "dohscrape" is not a materialized view, which is correct because that's the name of the table not the materialized view. The materialized view is named "dohscrapemat".

The link in the error message points to the following info:

NotSupportedError Exception raised in case a method or database API was used which is not supported by the database, e.g. requesting a .rollback() on a connection that does not support transaction or has transactions turned off.
This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.

But I don't see how this would relate to an error caused by/related to a trigger for updating a materialized view, which I assume is the root issue because dropping it resolves the error.

Once I let the script upload to the table and recreate the trigger it all works fine, but I'd like to be able to run this script without having to drop and recreate the trigger.

Why is the trigger for refreshing the materialized view causing an import error? And why is pandas/sqlalchemy/psycopg2 confuse my table with its materialized view?

The snippet of code in the python script that uploads to the db is:

for files in source:
    if files.endswith(".csv"):
        df = pd.read_csv(os.path.join(sourcepath,files))
        df.to_sql(name='dohscrape',con=dbconn,if_exists='append',index=False)

I'm using Python 3.7 and Postgres 11.

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Top Python Development Companies | Hire Python Developers

After analyzing clients and market requirements, TopDevelopers has come up with the list of the best Python service providers. These top-rated Python developers are widely appreciated for their professionalism in handling diverse projects. When...

Using PostgreSQL Database with Python

In this article we will see how to connect to PostgreSQL from Python Script and perform queries.

Python GUI Programming Projects using Tkinter and Python 3

Python GUI Programming Projects using Tkinter and Python 3