I know I need to use the MetaData object, in SQLAlchemy, but I am not sure how to use it with a class,
db = SQLAlchemy(app)
meta =db.Metadata()
class orders(db.model):
pass
How do I pass the meta object to the class so that it will auto generate table schema?
Well you can use SQLAlchemy's autoload feature but I still haven't figured out how to use that from flask-sqlalchemy. Here's a tutorial if you want to read about it anyway: SQLAlchemy Connecting to pre-existing databases. The best solution I found for the time being is to use sqlautocode to generate the SQLAlchemy models from the existing tables in your database. I know it would be preferable if SQLAlchemy would handle that automatically but I can't find a way to do it from Flask.
Here's how to use it:
sqlautocode mysql://<dbuser>:<pass>@localhost:3306/<dbname> -o alchemy_models.py
This will generate the Models and place them in the alchemy_models.py file. I hope this helps
You can use sqlacodegen to generate the classes needed for sqlalchemy.
pip install sqlacodegen
sqlacodegen postgresql+psycopg2://username:password@host/database --outfile models.py
I ran into an issue with the Base class and the query attribute. The error I received was:
AttributeError: type object 'PaymentType' has no attribute 'query'
I was able to make the sqlacodegen classes work by using a scoped_session.
session = scoped_session(sessionmaker(autocommit=False,autoflush=False,bind=engine))
Base.query = session.query_property()
print(PaymentType.query.all())
The db.Model.metadata.reflect(...) function is also super useful here; you don't have to define the model at all (it's just inferred from the existing database schema).
See stay_hungry's answer here for a specific implementation
