Help using SqlSoup with database views

[Reece Hart]

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://PUB...@unison-db.org:5432/unison')
        seq = u.pseq.filter(u.pseq.pseq_id==76).all() # okay
        aliases = u.pseqalias.filter(u.pseqalias.pseq_id==76).all()
        PKNotFoundError: table 'pseqalias' does not have a primary key defined...
        
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'

This question is also posted at http://stackoverflow.com/q/6286704/342839

Thanks,
Reece

[Michael Bayer]

On Jun 9, 2011, at 12:41 AM, Reece Hart wrote:

> 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://PUB...@unison-db.org:5432/unison')
> seq = u.pseq.filter(u.pseq.pseq_id==76).all() # okay
> aliases = u.pseqalias.filter(u.pseqalias.pseq_id==76).all()
> PKNotFoundError: table 'pseqalias' does not have a primary key defined...

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.

[Reece]

Thanks, Michael.

I'm getting further, but still have an error that I've not been able to resolve.

from sqlalchemy.ext.sqlsoup import SqlSoup

from sqlalchemy import Table

u = SqlSoup('postgresql+psycopg2://PUB...@unison-db.org:5432/unison')

t = Table("pseqalias", u._metadata, autoload=True, schema='unison')

u.map_to('pseqalias', schema='unison', selectable=t, mapper_args={"primary_key":[t.c.pannotation_id]})

For which I get:

File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/sqlsoup.py", line 558, in __getattr__

    return self.entity(attr)

File "/usr/lib/python2.7/dist-packages/sqlalchemy/ext/sqlsoup.py", line 547, in entity

    table = Table(attr, self._metadata, autoload=True, autoload_with=self.bind, schema=schema or self.schema)

File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 209, in __new__

    table._init(name, metadata, *args, **kw)

File "/usr/lib/python2.7/dist-packages/sqlalchemy/schema.py", line 257, in _init

    include_columns=include_columns)

File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1866, in reflecttable

    self.dialect.reflecttable(conn, table, include_columns)

File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/default.py", line 228, in reflecttable

    return insp.reflecttable(table, include_columns)

File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/reflection.py", line 376, in reflecttable

    for col_d in self.get_columns(table_name, schema, **tblkw):

File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/reflection.py", line 223, in get_columns

    **kw)

File "<string>", line 1, in <lambda>

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/reflection.py", line 40, in cache

    ret = fn(self, con, *args, **kw)

File "/usr/lib/python2.7/dist-packages/sqlalchemy/dialects/postgresql/base.py", line 962, in get_columns

    info_cache=kw.get('info_cache'))

File "<string>", line 1, in <lambda>

  File "/usr/lib/python2.7/dist-packages/sqlalchemy/engine/reflection.py", line 40, in cache

    ret = fn(self, con, *args, **kw)

File "/usr/lib/python2.7/dist-packages/sqlalchemy/dialects/postgresql/base.py", line 876, in get_table_oid

    raise exc.NoSuchTableError(table_name)

sqlalchemy.exc.NoSuchTableError: map_to

This is SqlAlchemy 0.6.4. I'll try 0.7.1 shortly. I'd appreciate for any idea you have.

This database makes heavy use of views. I'd be very interested in any suggestions you have for adding support for reflection of views. 

Thanks,

Reece

[Michael Bayer]

map_to() was added in 0.6.6

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/1zGXKGrrIhYJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Randy Syring]

FWIW, 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)

[Michael Bayer]

On Aug 4, 2011, at 1:08 PM, Randy Syring wrote:

> FWIW, 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)

yeah SqlSoup is really kind of up in the attic for me, if you'd like to propose some patches to make it work more nicely for things like that we are open.

>
> On Jun 10, 8:18 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> On Jun 9, 2011, at 12:41 AM, Reece Hart wrote:
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>> 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://PUB...@unison-db.org:5432/unison')
>>> seq = u.pseq.filter(u.pseq.pseq_id==76).all() # okay
>>> aliases = u.pseqalias.filter(u.pseqalias.pseq_id==76).all()
>>> PKNotFoundError: table 'pseqalias' does not have a primary key defined...
>>
>> 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 athttp://www.sqlalchemy.org/docs/orm/mapper_config.htmlnear the top.
>

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.