CODEWITHSUNDEEP
sql-serversqlalchemyflask-sqlalchemyamazon-rds
Matt-Heun Hong
Matt-Heun Hong

Reputation: 406

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

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)

Upvotes: 1

Views: 1956

Answers (3)

Saurav Pratihar
Saurav Pratihar

Reputation: 31

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:

Upvotes: 0

t_warsop
t_warsop

Reputation: 1270

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])

Upvotes: 0

Matt-Heun Hong
Matt-Heun Hong

Reputation: 406

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'.

Upvotes: 1

Related Questions