How to get SQLAlchemy to create a class from a view instead of a table?

Question

I am using flask-sqlalchemy and I want to create a class from a view instead of a db table. Is there an alternative to tablename? 'Car' was recently changed from a table to a view and now it's stuck sending a request.

class car(db.Model):
    __tablename__ = 'car'
    model = Column(Text, primary_key=True)
    brand = Column(Text, primary_key=True)
    condition = Column(Text, primary_key=True)
    year = Column(Integer)

Does this answer your question? [How to create an SQL View with SQLAlchemy?](https://stackoverflow.com/questions/9766940/how-to-create-an-sql-view-with-sqlalchemy) — rfkortekaas, Jul 09 '21 at 19:51

Gord Thompson · Answer 1 · 2021-07-09T23:42:37.950

SQLAlchemy does not have a particular problem with ORM objects based on views. For example, this works fine with SQL Server because SQL Server allows DML (INSERT, UPDATE, DELETE) on views:

# set up test environment
with engine.begin() as conn:
    conn.exec_driver_sql("DROP TABLE IF EXISTS car_table")
    conn.exec_driver_sql("CREATE TABLE car_table (id integer primary key, make varchar(50))")
    conn.exec_driver_sql("INSERT INTO car_table (id, make) VALUES (1, 'Audi'), (2, 'Buick')")
    conn.exec_driver_sql("DROP VIEW IF EXISTS car_view")
    conn.exec_driver_sql("CREATE VIEW car_view AS SELECT * FROM car_table WHERE id <> 2")

Base = sa.orm.declarative_base()


class Car(Base):
    __tablename__ = "car_view"
    id = Column(Integer, primary_key=True, autoincrement=False)
    make = Column(String(50), nullable=False)

    def __repr__(self):
        return f"<Car(id={self.id}, make='{self.make}')>"


with Session(engine) as session:
    print(session.execute(select(Car)).all())
    # [(<Car(id=1, make='Audi')>,)]
    # (note: the view excludes the row (object) where id == 2)

    session.add(Car(id=3, make="Chevrolet"))
    session.commit()
    print(session.execute(select(Car)).all())
    # [(<Car(id=1, make='Audi')>,), (<Car(id=3, make='Chevrolet')>,)]

However, if you really are using SQLite then you won't be able to add, update, or delete objects using a class based on a view because SQLite doesn't allow that:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) cannot modify car_view because it is a view  
[SQL: INSERT INTO car_view (id, make) VALUES (?, ?)]  
[parameters: (3, 'Chevrolet')]  
(Background on this error at: https://sqlalche.me/e/14/e3q8)

How to get SQLAlchemy to create a class from a view instead of a table?

1 Answers1