SQLAlchemy Notes

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

• Reflect a PostgreSQL view in Python’s SQLAlchemy
• Ref: How do I map a table that has no primary key
• How to reflect views for the ORM

  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

• Delete with session. ORM Delete

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

---

Last update: April 13, 2020 15:25:03