Error using SqlSoup with database views

Question

I'd like to use SqlSoup with an existing database that contains views. Accessing a table goes swimmingly, but accessing a view results in "PKNotFoundError: table '[viewname]' does not have a primary key defined..."

Do I correctly infer that SqlSoup does not work with database views (by default, at least)? I've been unable to find anything directly relevant on Google, SO, or the SqlAlchemy mailing list. If you were faced with this, how would you proceed if you wanted to access non-updatable views? I'm new to SQLAlchemy and SQLSoup.

Here's a specific example:

from sqlalchemy.ext.sqlsoup import SqlSoup
u = SqlSoup('postgresql+psycopg2://[email protected]:5432/unison')
seq = u.pseq.filter(u.pseq.pseq_id==76).all() # okay
aliases = u.pseqalias.filter(u.pseqalias.pseq_id==76).all()

This is a public database. You can run the equivalent queries using psql:

psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pseq where pseq_id=76'
psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pseqalias where pseq_id=76'

Answer 1

Thanks to Randy for the map() tip. Here's a complete solution that you may try verbatim (the database is publicly available):

from sqlalchemy.ext.sqlsoup import SqlSoup
from sqlalchemy import Table
u = SqlSoup('postgresql+psycopg2://[email protected]:5432/unison')
pa_t = Table("palias", u._metadata, autoload=True, schema='unison')
pa = u.map(pa_t,primary_key=[pa_t.c.pannotation_id])
pa.slice(0,20).all()

This is with Python 2.7.1, Alchemy 0.7.2.

For references, see:

• http://www.sqlalchemy.org/docs/core/schema.html?highlight=table#sqlalchemy.schema.Table
• http://www.sqlalchemy.org/docs/orm/extensions/sqlsoup.html?highlight=map#sqlalchemy.ext.sqlsoup.SqlSoup.map

Answer 2

From Michael Bayer:

You would need to pass the columns to be considered part of the primary key to the underlying mapper, using sqlsoup.map_to(), but unfortunately there is not a simple interface for that at the moment, since you need the Table object as well to get at the columns. So until this interface could be improved, for now it would look like:

metadata = u._metadata t = Table("pseqaliases", metadata, autoload=True)

u.map_to("pseqaliases", selectable=t, mapper_args={"primary_key":[t.c.col1, t.c.col2]})

This is just the "primary_key" argument to mapper, there are some examples at http://www.sqlalchemy.org/docs/orm/mapper_config.html near the top.

http://groups.google.com/group/sqlalchemy/browse_thread/thread/fc1e8d079e10bac8

I tried the map_to() method but still received the PK error. The following method, however, worked fine:

ss = SqlSoup(db.engine)
meta = ss._metadata
tbl_vrmf = sa.Table("vRMF", meta, autoload=True)
vrmf_pks = [tbl_vrmf.c.dateId, tbl_vrmf.c.ident, tbl_vrmf.c.mnum]
vrmf = ss.map(tbl_vrmf, primary_key=vrmf_pks)