5

In SQLAlchemy, ModelName.query.filter_by(field_name=value).count() returns correct record count, but ModelName.query.filter_by(field_name=value).all() only returns a single record. Doing a db.session.execute("SELECT * FROM table_name WHERE field_name = 'value'") works fine. Has anybody faced a similar problem? Does anyone have any idea what could possibly be wrong. Any pointers will help.

Information that might be helpful
I am using MS SQL Server 2008 R2 and accessing it using FreeTDS/pyodbc. I do not control the database and can not change it.

Thanks in advance.

Mir Nazim
  • 636
  • 1
  • 8
  • 20
  • 3
    try turning `SQLALCHEMY_ECHO` to `True` so you can see what SQL statements are being fired, then have a look to see if the `.all()` query is doing something unexpected. – Doobeh Jun 13 '13 at 13:48
  • 7
    one thing to keep in mind is that if a query returns more than one row with the same primary key, a query against the full entity class will de-duplicate those rows. But count() will show the actual number of rows that would be returned regardless of primary key. – zzzeek Jun 17 '13 at 05:00
  • Can you try one of the other drivers like pymssql (see list under http://docs.sqlalchemy.org/en/rel_0_8/dialects/mssql.html) and see if the problem persists? I remember pyodbc was showing some quirks when I tried it with SQL Server. But it's been some time and I only used it directly, not through SQLAlchemy. That's why I switched to ceODBC which was much more reliable and faster. But unfortunately not supported by SQLAlchemy. Still I would try out one of the other options you have. – Felix Zumstein Sep 10 '13 at 20:05
  • @MirNazim: Without showing us the data you are querying on and the SQLAlchemy mapping (the SQL schema as well as the debug logs obtained through SQLALCHEMY_ECHO as Doobeh mentioned would also be useful), it will be very difficult for someone to answer this question aside from just guessing. – Mark Hildreth Sep 11 '13 at 17:05
  • I could not find the reason this was happening, and ended up using raw SQL queries in the case where it did not work(I was on a deadline). Queries output by `SQLALCHEMY_ECHO=True` worked perfectly fine when used as raw SQL. Also I cannot share the data or the logs. Data belongs to government and our contract clearly states not data what so ever(including logs, schema and even dummy test data we made up ourselves ) must not move outside the building we were working in. – Mir Nazim Sep 13 '13 at 07:45

2 Answers2

1

I think I may have fallen in the same problem. My query do multiple Joins, than the raw result can bring back multiple rows of the same primary key, each row is counted by .count(), however when you call .all() a distinct is applied on the primary key and only unique rows are fetched, than the number of records on .all() list is different from .count().

This should be equal:

query.distinct().count() == query.all()

Best regards

Gustavo Gonçalves
  • 463
  • 6
  • 10
-2

The function all() return a list and count() return a number...

Thomas Ayoub
  • 29,063
  • 15
  • 95
  • 142