flask-sqlalchemy icon indicating copy to clipboard operation
flask-sqlalchemy copied to clipboard

Provide documentation for using reflect

Open cowgill opened this issue 5 years ago • 6 comments

I've searched high and low trying to find a flask-sqlalchemy reflect example that actually works.

There seem to be others with the same issue (via stack overflow) who are frustrated. I'm sure you're tired of having to troubleshoot for users as well. :-(

I'd be happy to write up the doc if you could provide a working example (using an app factory and without).

Here's what I've come up with thus far only to be hit with this error:

sqlalchemy.exc.ArgumentError: Mapper Mapper|Feed|autofeeds could not assemble any primary key columns for mapped table 'autofeeds

app.py

from flask import Flask
from src import views
from src.extensions import db

def create_app(config_name):
    app = Flask(__name__)
    app.config.from_object(config_name)

    register_extensions(app)
    register_blueprints(app)
    return app

def register_extensions(app):
    db.init_app(app)
    db.reflect(app=app)
    db.Model.metadata.reflect(db.engine)
    return None

src/extensions.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

src/models.py

from .extensions import db

class Feed(db.Model):
    __tablename__ = 'autofeeds'

cowgill avatar Mar 21 '19 17:03 cowgill

Soooo...what are you looking for here? You want Feed to have it's columns implicitly populated using SQLAlchemy's reflect functionality?'

In that case, I believe AutoMap is what you want and specifically automap_base.

I've not tried this code, but it might be as simple as:

AutoMapModel = automap_base(db.Model)
class Feed(AutoMapModel):
    __tablename__ = 'autofeeds'

AutoMapModel.prepare(db.engine, reflect=True)

Let me know. If it works or you can give me the version that works, I don't see any reason why we can't add it to the docs.

rsyring avatar Apr 18 '19 15:04 rsyring

We generally don't add docs for things that are documented by SQLAlchemy unless they have different behavior. Is there something specific to Flask-SQLAlchemy here? We do provide db.reflect(), which is a shortcut for calling db.Model.metadata.reflect() for each bind, but there's no added behavior.

davidism avatar Apr 18 '19 15:04 davidism

I think the thing that is causing confusion, and may be worth documenting, is that unlike traditional SA, we "hide" the declarative_base() work in SQLAlchemy.make_declarative_base().

With pure SA, you'd swap your usage of declarative_base() with automap_base(). But since we "hide" that from the user, it's not exactly clear how you'd use FSA with automap.

rsyring avatar Apr 18 '19 15:04 rsyring

We already have some documentation on the metaclass: http://flask-sqlalchemy.pocoo.org/2.3/customizing/#model-metaclass. We also document make_declarative_base as part of the public API to override: http://flask-sqlalchemy.pocoo.org/2.3/api/#flask_sqlalchemy.SQLAlchemy.make_declarative_base. Based on the SQLAlchemy docs: https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/table_config.html#using-reflection-with-declarative, I think the only thing wrong with the code in the op is that it needs to set __table__ to a reflected table.

davidism avatar Apr 18 '19 15:04 davidism

I have a similar question but specifically around how to define a view binding. I have looked at the docs here: https://docs.sqlalchemy.org/en/13/core/reflection.html#reflecting-views and I have come up with the following:

api/extensions/database.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()  # pylint: disable=invalid-name

def init_app(app):
    """Initialize relational database extension"""
    db.init_app(app)
    db.create_all(app=app)
    db.reflect(app=app)

api/models.py

from api.extensions.database import db

def current_reporting_entities():
     return db.Table(
         "vw_CurrentReportingEntityAssignment", 
         db.metadata,
         schema="finance",
         autoload=True,
         autoload_with=db.engine
    )

class OfficeType(db.Model):
    __tablename__ = 'OfficeType'
    __table_args__ = {'schema': 'finance'}

    OfficeTypeID = db.Column(db.Integer, primary_key=True)
    OfficeTypeDesc = db.Column(db.Unicode(200), nullable=False)
    OfficeTypeCode = db.Column(db.Integer, nullable=False)

<other models>

To use it have todo something like:

session = db.create_scoped_session()
session.connection().execute(select([current_reporting_entities()])).fetchall()

There must be a better way that's more ORM like but I couldn't find any good examples out there..

jamescw avatar Oct 13 '20 11:10 jamescw

New user here wondering about the same issue. Want to query data in existing database. After

app = Flask(__name__)
app.config.from_mapping(SQLALCHEMY_DATABASE_URI='PATH TO EXISTING DB')
sql_alchemy = SQLAlchemy()
sql_alchemy.init_app(app)
sql_alchemy.reflect(app)

I can see the tables:

with app.app_context():
        print(sql_alchemy.get_tables_for_bind())

But how to start query the tables?

aaronzhangSema4 avatar Nov 16 '20 17:11 aaronzhangSema4

Fixed in #1087.

davidism avatar Sep 18 '22 17:09 davidism