Skip to content

SQLAlchemy Notes

Treat a view as a table in SQLAlchemy and allow use with ORM for queries.

engine = create_engine(config('DB_URI'))
meta = MetaData()
meta.reflect(bind=engine, schema='public', views=True)
base = automap_base(metadata=meta)
base.prepare()

def get_table(table_name):
    try:
        result = base.classes[table_name]
    except KeyError as err:
        result = meta.tables[table_name]
    return result

CHILD_VIEW = 'public.child_view'


class ChildView(base):
    __table__ = get_table(CHILD_VIEW)
    __mapper_args__ = {
        'primary_key': [__table__.c.account_uuid]
    }

base.prepare()

Automap

  • Use to automatically get table details from the database. This is if the table is being managed separately from sqlalchemy.

Notes

Examples

  • Order By - returns the latest item with the site_uuid.
    return self.query.filter(self._table.site_uuid == uuid).order_by(
      self._table.updated_at.desc()
      ).first()