Reflect a PostgreSQL view in Python’s SQLAlchemy

Alexander Hultnér
Oct 23, 2017 · 3 min read

I ran into this problem a while ago, where I had a simple view created by joining two tables with a 1:1 relationship in PostgreSQL but SQLAlchemy didn’t like my view.


Why’s that? you might ask. Well here’s the thing, to the best of my knowledge there’s as of this date no way to natively reflect a view in SQLAlchemy. Instead one must use the Table() construct to initiate the view, and herein lies the foundational problem, see table’s in SQLAlchemy requires a primary key to use as a hash while views in PostgreSQL can’t. Clearly a point of conflict.

A problematic domain model — sample code attached


id serial PRIMARY KEY,
order_Index INTEGER,
label TEXT,
key VARCHAR(255)

Templates in them self are not interesting for this obstacle and are thus left out, however I left the relation in Template_Field for illustrative purposes.

CREATE TABLE IF NOT EXISTS quick_bits.Template_Field(
template integer REFERENCES quick_bits.Template(id),
field integer PRIMARY KEY REFERENCES quick_bits.Field(id),


CREATE OR REPLACE VIEW quick_bits.template_field_view AS
FROM quick_bits.template_field as template_field
INNER JOIN quick_bits.field as field
ON = template_field.field

Problems start surfacing

# ...

def init_table(name):
return Table(name, meta, autoload=True, schema=config.DB_SCHEMA)

class Field(Base):
__table__ = init_table('field')

class TemplateField(Base):
__table__ = init_table('template_field')

class TemplateFieldView(Base):
__table__ = init_table('template_field_view')

Now let’s see what happens if we try to read data using this model.

class TemplateFieldView(Base):
File "/Users/hultner/Development/quick_bit_test/venv/lib/python3.6/site-packages/sqlalchemy/ext/declarative/", line 64, in __init__
_as_declarative(cls, classname, cls.__dict__)
File "/Users/hultner/Development/quick_bit_test/venv/lib/python3.6/site-packages/sqlalchemy/ext/declarative/", line 88, in _as_declarative
_MapperConfig.setup_mapping(cls, classname, dict_)
File "/Users/hultner/Development/quick_bit_test/venv/lib/python3.6/site-packages/sqlalchemy/ext/declarative/", line 103, in setup_mapping
cfg_cls(cls_, classname, dict_)
File "/Users/hultner/Development/quick_bit_test/venv/lib/python3.6/site-packages/sqlalchemy/ext/declarative/", line 135, in __init__
File "/Users/hultner/Development/quick_bit_test/venv/lib/python3.6/site-packages/sqlalchemy/ext/declarative/", line 138, in _early_mapping
File "/Users/hultner/Development/quick_bit_test/venv/lib/python3.6/site-packages/sqlalchemy/ext/declarative/", line 534, in map
File "<string>", line 2, in mapper
File "/Users/hultner/Development/quick_bit_test/venv/lib/python3.6/site-packages/sqlalchemy/orm/", line 677, in __init__
File "/Users/hultner/Development/quick_bit_test/venv/lib/python3.6/site-packages/sqlalchemy/orm/", line 1277, in _configure_pks
(self, self.mapped_table.description))
sqlalchemy.exc.ArgumentError: Mapper Mapper|TemplateFieldView|template_field_view could not assemble any primary key columns for mapped table 'template_field_view'

The following error does seem daunting but we can quickly see that the problem lies in mapping a view without primary keys as a table which requires said key.

First workaround attempt

My first approach were something along these lines.

class TemplateFieldView(Base):
__table__ = init_table('template_field_view')
id = Column(Integer, primary_key=True)

Let’s try it out.

File "/Users/hultner/Development/quick_bit_test/venv/lib/python3.6/site-packages/sqlalchemy/ext/declarative/", line 131, in __init__
File "/Users/hultner/Development/quick_bit_test/venv/lib/python3.6/site-packages/sqlalchemy/ext/declarative/", line 403, in _setup_table
"specifying __table__" % c.key
sqlalchemy.exc.ArgumentError: Can't add additional column 'id' when specifying __table__

Some progress but no cigar. We can now see that the problem is no longer the primary key but rather that we can’t specify new columns like this in a reflected tables. This is because of the inner workings of SQLAlchemy have already decided on the table structure and doesn’t want to mutate this representation.

The solution

So now I have the following code.

class TemplateFieldView(Base):
__table__ = init_table('template_field_view')
__mapper_args__ = {

So let’s try it out.

>>> get_template_field_from_view(54)
Field(id='54', template='3', order_index='1', label='Name', key='name_data', type='FIRST_NAME')>


There you have it. If anyone got a nicer solution I would love to hear it, until then I hope this is helpful to you!

Originally published at on October 23, 2017.


Product Development in the connected world

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store