Querying a view in SQLAlchemy

Question

I want to know if SQLAlchemy has problems querying a view. If I query the view with normal SQL on the server like:

SELECT * FROM ViewMyTable WHERE index1 = '608_56_56';

I get a whole bunch of records. But with SQLAlchemy I get only the first one. But in the count is the correct number. I have no idea why.

This is my SQLAlchemy code.

myQuery = Session.query(ViewMyTable)
erg = myQuery.filter(ViewMyTable.index1 == index1.strip())

# Contains the correct number of all entries I found with that query.
totalCount = erg.count()
# Contains only the first entry I found with my query.
ergListe = erg.all()

Answer 1

if you've mapped ViewMyTable, the query will only return rows that have a fully non-NULL primary key. This behavior is specific to versions 0.5 and lower - on 0.6, if any of the columns have a non-NULL in the primary key, the row is turned into an instance. Specify the flag allow_null_pks=True to your mappers to ensure that partial primary keys still count :

mapper(ViewMyTable, myview, allow_null_pks=True)

If OTOH the rows returned have all nulls for the primary key, then SQLAlchemy cannot create an entity since it can't place it into the identity map. You can instead get at the individual columns by querying for them specifically:

for id, index in session.query(ViewMyTable.id, ViewMyTable.index):
    print id, index