SqlSoup - Mapping against a database view will fail because no PK will be found

Hi,

I'm using SqlSoup with Postgres and mapping agains a database view will fail while mapping is attempted, because no PK is defined on the view.

  File "C:\Python25\lib\site-packages\sqlalchemy\ext\sqlsoup.py", line 546, in entity
    raise PKNotFoundError('table %r does not have a primary key defined [%s](columns:)' % (attr, ','.join(table.c.keys())))
sqlalchemy.ext.sqlsoup.PKNotFoundError: table 'view01' does not have a primary key defined [col1,col2,col3](columns:)

If you believe this would be a useful feature please see below the workaround (possible solution) I'm using:

sqlalchemy.ext.sqlsoup

from sqlalchemy.schema import PrimaryKeyConstraint

sqlalchemy.ext.sqlsoup.SqlSoup.entity() - line 540

current version

    def entity(self, attr):
        try:
            t = self._cache[attr](attr)
        except KeyError:
            table = Table(attr, self._metadata, autoload=True, schema=self.schema)
            if not table.primary_key.columns:
                raise PKNotFoundError('table %r does not have a primary key defined [%s](columns:)' % (attr, ','.join(table.c.keys())))
            if table.columns:
                t = class_for_table(table)
            else:
                t = None
            self._cache[attr](attr) = t
        return t

my modified version

    def entity(self, attr, ***args**):
        try:
            t = self._cache[attr](attr)
        except KeyError:

            **if not args:            
                table = Table(attr, self._metadata, autoload=True, schema=self.schema)
            else:
                table = Table(attr, self._metadata, 
                              PrimaryKeyConstraint(*args),
                              autoload=True, schema=self.schema)           **

            if not table.primary_key.columns:
                raise PKNotFoundError('table %r does not have a primary key defined [%s](columns:)' % (attr, ','.join(table.c.keys())))
            if table.columns:
                t = class_for_table(table)
            else:
                t = None
            self._cache[attr](attr) = t
        return t

how to use it

#consider all columns part of the PK so no filtering will be made by SA

db.entity('view01', 'col1', 'col2', 'col3') == self.db.view01

# or pick your colums (SA will filter results by these columns)

db.entity('view01', 'col1', 'col2') == self.db.view01

# this will now work

view01 = db.view01.get('100', '1000')
view01 = db.view01.filter_by(**kwargs).all()

# and so on ...

Best regards.

Comments (5)