SQLALchemy - cannot reflect a SQL Server DB running on Amazon RDS

Question

My code is simple:

app = Flask(__name__)
app.config.from_object('config')

db = SQLAlchemy(app)
db.metadata.reflect()

And it throws no errors. However, when I inspect the metadata after this reflection, it returns an empty immutabledict object.

The parameters in my connection string is 100% correct and the code works with non-RDS databases.

It seems to happen to others as well but I can't find a solution.

Also, I have tried to limit the reflection to specific tables using the "only" parameter in the metadata.reflect function, and this is the error I get:

sqlalchemy.exc.InvalidRequestError: Could not reflect: requested table(s) not available in mssql+pyodbc://{connection_string}: (users)

Community · Accepted Answer · 2017-05-23 12:17:41Z

1

I've fixed it. The reflect() method of the SQLAlchemy class has a parameter named 'schema'. Setting this parameter, to "dbo" in my case, solved it.

I am using Flask-SQLAlchemy, which does not have the said parameter in its reflect() method. You can follow this post to gain access to that parameter and others, such as 'only'.

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Apr 19, 2017 at 17:54

Matt-Heun Hong

3963 silver badges13 bronze badges

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

Comments

t_warsop · Accepted Answer · 2019-03-20 16:59:37Z

0

This error occurs when reflect is called without the schema name provided. For example, this will cause the error to happen:

metadata.reflect(only = [tableName])

It needs to be updated to use the schema of the table you are trying to reflect over like this:

metadata.reflect(schema=schemaName, only = [tableName])

answered Mar 20, 2019 at 16:59

t_warsop

1,3203 gold badges27 silver badges39 bronze badges

Comments

Saurav Pratihar · Accepted Answer · 2020-05-06 19:44:43Z

0

You have to set schema='dbo' in parameter for reflect.

db.Model.metadata.reflect(bind=engine, schema='dbo', only=['User'])

and then create model of your table:

class User(db.Model):
    __table__ = Base.metadata.tables['dbo.User']

and to access data from that table:

answered May 6, 2020 at 19:44

Saurav Pratihar

312 bronze badges

Collectives™ on Stack Overflow

SQLALchemy - cannot reflect a SQL Server DB running on Amazon RDS

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