SqlAlchemy not returning all rows when querying table object, but returns all rows when I query table object column

Question

Update - Solution Below

I am extremely new to SqlAlchemy so please excuse if this is an obvious problem. When I query the Table object I only get one result (the first in the database, there are 600+ with my filter). When I query by a column on the table it returns all the data I expect. What I am doing incorrectly?

Only returns 1 result should be hundreds

for row in edb_alchemy.session.query(FtSite).filter(FtSite.serial_si == 200134444):
        print(row.s_sequence)

Result looks like: 1

Returns all results

for row in edb_alchemy.session.query(FtSite.s_sequence).filter(FtSite.serial_si == 200134444):
        print(row)

Result looks like: (1,) (2,) (3,) (4,)...

Returns 1 result for FtSite table and all results for column

for row in edb_alchemy.session.query(FtSite, FtSite.s_sequence).filter(FtSite.serial_si == 200134444):
        print(row.FtSite.s_sequence, row.s_sequence)

Result looks like (1, 1), (1, 2), (1, 3), (1,4)....

The SQL that SQLAlchemy says its using is

"SELECT ft_site.serial_si AS ft_site_serial_si, ft_site.partition_id AS ft_site_partition_id, ft_site.s_sequence AS ft_site_s_sequence, ft_site.value AS ft_site_value" + \
    " FROM ft_site" + \
    " WHERE ft_site.serial_si = 200134444"

Which works fine as I'd expect when just using SQL query outside of SQLAlchemy.

---

Update

Thank you Ilja in the comments. For some reason I thought this table had an id primary key. It does not, I'm just a consumer of this db and should have been more observant. You were correct. This table has no unique key and lists MUL under FtSite.serial_si.

This is what the table actually looks like.

+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| p_id       | int(10)  | NO   |     | NULL    |                |
| serial_si  | int(10)  | NO   | MUL | NULL    |                |
| s_sequence | int(10)  | NO   |     | NULL    |                |
| value      | double   | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

My original table description was

class FtSite(Base):
    __tablename__ = "ft_site"

    id = Column(INTEGER, primary_key=True)
    serial_si = Column(INTEGER)
    partition_id = Column(INTEGER)
    s_sequence = Column(INTEGER)
    value = Column(DOUBLE)

I changed this to have a composite key in SQLAlchemy (s_sequence, serial_si) is unique even if it's not defined in the database. Is this the best way to handle this in SQLAlchemy? It's now returning expected results.

class FtSite(Base):
    __tablename__ = "ft_site"

    serial_si = Column(INTEGER, primary_key=True)
    partition_id = Column(INTEGER)
    s_sequence = Column(INTEGER, primary_key=True)
    value = Column(DOUBLE)

Answer 1

I ran into a similar situation where SQLAlchemy query object's .all() doesn't return all the rows in the table (always missing some) but .count() call does give the correct count. After digging into it a bit more, I realized that the model declaration deviated from the actual table schema in that database. First, the database has a single primary key column in the schema but the model declaration has a composition primary key (in reverse case like yours), also I missed a 3-column unique constraint where as the table schema has it.

What happened there in my case was that whenever SQL Alchemy query the database it did get all the rows behind the scene but due to the incorrect composition primary key in my model declaration has prevented some rows loading into SQLAlchemy's session (primary keys by definition uniquely indentify the objects and it won't load two objects with the same primary key in session as a result, hence it tosses out those composition columns that have same values even in the database they do have different PKs.)

In conclusion, double check model declaration with database schema to make sure they are in sync is the first response from this kind of issues.