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.

python postgresql

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

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

What is new features in Javascript ES2020 ECMAScript 2020

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Random Password Generator Online

HTML Color Picker online | HEX Color Picker | RGB Color Picker

Basic Data Types in Python | Python Web Development For Beginners

In the programming world, Data types play an important role. Each Variable is stored in different data types and responsible for various functions. Python had two different objects, and They are mutable and immutable objects.

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...

Python Hello World Program - Create & Run Your First Python Program in PyCharm

Python Hello World Program - Your first step towards Python world. Learn how to create the Hello World Python program in PyCharm.

Standard Data Types in Python - Python Tutorial - Python Training

This video on 'Standard Data Types in Python' will help you establish a foothold on Python by helping you learn basic concepts.

Python Libraries You Must Learn in 2020-Learn Python-Python Training

This LIVE session on 'Python Libraries' will help you understand the topmost trending Python libraries toy must learn.