Loading Flask Models from Postgres

Question

I've got a Flask application set up using a postgres db and sqlalchemy. Unfortunately this is not the flask-sqlalchemy extension. While I am able to get the app to work with regular sqlalchemy, the extension does not seem to like the schemas we have set up. I'm hoping to take advantage of some of the features the extension has like pagination.

Here is my question...

I have a postgres URL which I can use with the flask-sqlalchemy extension, but I'm not sure what to do about the models. Do I just retype out all of the postgres scheama as a model for each table?

Right now I have a couple models which looks like:

class Account(Base):
 users = relationship("User")
class User(Base):
 account = relationship("Account", backref="user")

This works fine. There are additional columns which aren't defined in the models but I am able to access (such as name, email, etc for the User model). Do I need to declare all of these in the model to use the flask-sqlalchemy extension?

Thanks for any help.

Answer 1

this variation of sqlacodegen model code generator for SQLAlchemy

gives and option for flask model generation "--flask"

https://github.com/ksindi/flask-sqlacodegen

Answer 2

Try using SQLACodeGen. Just point it at your database, and it'll generate all the SQLA models for you, then you can tweak it as required— it's a big time saver.

SQLAlchemy can also reflect existing tables if you prefer the discovery route at runtime.

Here's a quick example for using SQLACodeGen:

sqlacodegen postgresql:///example_database --tables car

Outputs:

# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata
class Car(Base):
 __tablename__ = 'car'
 id = Column(Integer, primary_key=True)
 price = Column(Integer)
 color_id = Column(ForeignKey('color.id'))
 color = relationship(u'Color')
class Color(Base):
 __tablename__ = 'color'
 id = Column(Integer, primary_key=True)
 name = Column(String(50))

Answer 3

I figured out a solution. My main problem was that I wanted to use the paginate and other methods given by the flask-SQLAlchemy. I initialized my database very similarly to this Declarative section.

The line of interest was:

Base.query = db_session.query_property()

It turns out you can give that constructor another implementation which it will make calls to. So I imported flask.ext.sqlalchemy.BaseQuery and gave it that.

The line now looks like:

Base.query = db_session.query_property(BaseQuery)

I'm able to use all of the flask-sqlalchemy query features I wanted! Figure'd I'd post this here in case anybody else comes across the problem.