Error using SqlSoup with database views

Question

I would like to use SqlSoup with an existing database containing views. Access to the table is smooth, but access to viewing results in "PKNotFoundError: table" [viewname] "does not have a defined primary key ..."

Do you conclude correctly that SqlSoup does not work with database views (at least by default)? I could not find anything directly relevant on the Google, SO, or SqlAlchemy mailing list. If you were faced with this, what would you do if you want to access unrestored views? I am new to SQLAlchemy and SQLSoup.

Here is an example:

from sqlalchemy.ext.sqlsoup import SqlSoup u = SqlSoup('postgresql+psycopg2:// [e-mail] :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 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 () hint. Here is a complete solution that you can try verbatim (the database is publicly available):

 from sqlalchemy.ext.sqlsoup import SqlSoup from sqlalchemy import Table u = SqlSoup('postgresql+psycopg2:// [e-mail] :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 reference 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 will need to pass the columns, which will be considered part of the primary key to the base converter, using sqlsoup.map_to (), but unfortunately, there is currently no simple interface for this, since you need a Table object, as well as for receiving columns. So until this interface is improved, now it will look like this:

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

u.map_to ("pseqaliases", selectable = t, mapper_args = {"primary_key": [tccol1, tccol2]})

This is just the "primary_key" argument for 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 got a PK error. However, the following method 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) 

Answer 3

Something like this (untested):

 from sqlalchemy import sql from sqlalchemy.ext.sqlsoup import SqlSoup u = SqlSoup('postgresql+psycopg2:// [e-mail] :5432/unison') pseq_id = sql.column('pseq_id') aliases = u.bind.execute(sql.select([pseq_id, '*'], from_obj=['pseqalias'])\ .where(pseq_id==76)).fetchall() 

Of course, you can only do:

 aliases = u.bind.execute('select * from pseqalias where pseq_id=76').fetchall() 

... but in this case you lose the possibility of reuse.