How to query a SQL View from flask-SQLAlchemy

Question

I have been given access to a database (MySQL, in case it matters) to access some data that I need, but I don't have direct access to the tables, the data is exposed through a SQL View.

I want to wrap this in a small API using flask. For the database connection I intend to use SQLAlchemy (just because it's the only databse wrapper that I know in python).

The thing is, I have read a lot of documentation, and dozens of stackoverflow posts and guides through the Internet, and I can't seem to find a way to query SQL Views from flask-SQLAlchemy.

In the documentation, they always use Models. I tried to define my View as a Model, but I get could not assemble any primary key columns for mapped table 'MySQLView'.

What I tried to do:

class MyView(db.Model):
    __tablename__ = "my_view_name_in_sql"
    db.Column("id", db.Integer, primary_key=True)
    db.Column("sample_id", db.String)
    {...etc}

Is there a way to do this, or maybe I should opt-out of using flask-SQLAlchemy and use something else instead?

c8999c 3f964f64 · Accepted Answer · 2021-10-05 07:33:40Z

2

There's a few answers here I think deal with your case, especially LeoRochael's answer who suggests using the https://pypi.org/project/sqlalchemy-views/ package. I suggest checking if you can - or cannot access the database's metdata, I think that could really help you avoid having to make models manually

db_engine = sqlalchemy_package.create_engine("mysql+...")
db_metadata = db.MetaData(bind=db_engine)

you can then either try making a Table directly, or use the metadata to make views like in the answer I linked.

your_table = db.Table("my_view_name_in_sql", db_metadata, autoload=True)
view_definition = your_table.select()
your_view = CreateView(your_table, view_definition, or_replace=True)

edited Oct 5, 2021 at 7:33

answered Oct 5, 2021 at 7:22

c8999c 3f964f64

1,6491 gold badge18 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brian · Accepted Answer · 2023-05-16 16:48:50Z

2

I don't have experience with SQLAlchemy Flask but this is what worked for me in SQLAlchemy. I used the __mapper_args__ attribute to define the private key for a view in my database.

db_metadata = MetaData()
Base = automap_base(metadata=db_metadata)

class MyView(Base):
    __table__ = Table("View_Name", db_metadata, autoload_with=engine, schema="My_Schema")
    __mapper_args__ = {'primary_key': [__table__.c['Primary_Column_Name']]}

Base.prepare()

edited May 16, 2023 at 16:48

answered May 16, 2023 at 16:40

Brian

414 bronze badges

Comments

manosk · Accepted Answer · 2024-10-06 00:47:21Z

0

Managed to do this through the following code:

query = text('select * from "view_name"')
result = connection.execute(query)
inserted_rows = result.mappings().all()

3 pointers here to keep in mind:

Wrap query statement with sqlalchemy's text method
"view_name" should be wrapped by double quotes
Result from query is of CursorResult type and contains a mappings method to retrieve data

answered Oct 6, 2024 at 0:47

manosk

843 silver badges6 bronze badges

Collectives™ on Stack Overflow

How to query a SQL View from flask-SQLAlchemy

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related