flask-sqlalchemy
flask-sqlalchemy copied to clipboard
Provide documentation for using reflect
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'
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.
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.
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.
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.
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..
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?
Fixed in #1087.