SqlAlchemy query create view

148 Views Asked by At

This is my sql query for create view in Postgresql. But I need to query it from SqlAlchemy

CREATE OR REPLACE VIEW public.my_view
     AS
     SELECT m.id AS mt_dump_model_csv_id,
        m.geom,
        m."time",
        r.id AS rf_data_type_id,
        r.code,
        d.id AS dt_dump_model_data_id,
        d.fk_data_type,
        d.fk_dum_model_csv,
        d.value
       FROM mt_dump_model_csv m
         JOIN dt_dump_model_data d ON m.id = d.fk_dum_model_csv
         JOIN rf_data_type r ON d.fk_data_type = r.id
      ORDER BY m."time";

I also tried the below format of query. But it didn't work.

metadata = MetaData()
view = Table('my_view', metadata)
definition = text("SELECT m.id AS mt_dump_model_csv_id,m.geom,m.time,r.id AS rf_data_type_id,r.code,d.id AS dt_dump_model_data_id,d.fk_data_type,d.fk_dum_model_csv,d.value FROM mt_dump_model_csv m JOIN dt_dump_model_data d ON m.id = d.fk_dum_model_csv JOIN rf_data_type r ON d.fk_data_type = r.id ORDER BY m.time;")

create_view = CreateView(view, definition, or_replace=True)
engine.execute(create_view)

I got the following error:

column r.id does not exist
LINE 2: ...model_csv JOIN rf_data_type r ON d.fk_data_type = r.id ORDER...
                                                             ^
HINT:  Perhaps you meant to reference the column "m.id" or the column "d.id".
0

There are 0 best solutions below