Querying for all results in SQLAlchemy 0.7.10

Question

I would like to retrieve all results of a query. My code looks like:

engine = sqlalchemy.create_engine(...) Base = declarative_base() class Something(Base): ... Session = sessionmaker(bind=engine) session = Session() query = session.query(Something).filter(...).all()

I see query.count() == 3000, but when I try results = query.all(), I get a single row only (i.e. len(results) == 1).

My understanding is that query.all() executes the query and returns a list of all results. Am I missing something here? What are some possible reasons the counted number of rows would be greater than the number of rows returned?

Edit (some more details):

Taking the raw SQL (e.g. looking at str(query) and making parameter substitutions) and passing it into session.execute seems to work just fine:

# this gives 3000 rows for row in session.execute(raw_sql, {'param1': val1, 'param2': val2}): print(row)

Solution

Turns out the problem was that my primary key was not unique. Lesson learned: be very careful when setting up your class! In my case, I had:

class Something(Base): row_id_1 = Column(Integer, primary_key=True) row_id_2 = Column(Integer) # PROBLEM! Need to specify primary_key=True value = Column(Float)

This caused problems because the primary key was the combination (row_id_1, row_id_2).

Can you post the full "Something" class definition, along with a couple of the records that are being returned from the execute()? — Mark Hildreth
– Mark Hildreth, Commented Oct 16, 2013 at 18:21
Aha, actually your suggestion helped me resolve the issue when I attempted to produce a toy example of my class. The problem was that my primary key was not unique. This is okay for count(), but apparently SQL Alchemy will only return the first instance of the primary key if it is not unique. I wasn't expecting this behavior. Thanks! — Max
– Max, Commented Oct 16, 2013 at 18:32
No problem, I figured that was the problem. I've written up a slightly more involved answer for those who happen upon this question in the future. — Mark Hildreth
– Mark Hildreth, Commented Oct 16, 2013 at 18:48

Mark Hildreth · Accepted Answer · 2013-10-16 20:14:58Z

Your issue is related to the fact that your primary key is not unique. Assume your mapping was...

class Something(Base): __tablename__ = 'things' id = Column(Integer, primary_key=True) name = Column(String(50))

And you have the following data in your database...

| ID| Name | | 1 | Larry | | 1 | Moe | | 1 | Curly |

If you did...

SELECT * FROM names

You'd get three results. That's what the execute function in SQLAlchemy does.

However, the ORM goes one step beyond. When using the ORM for querying the "things" table, the results are turned into Something objects. Also, they tracked by the SQLAlchemy using an Identity Map. After converting the first record ("Larry") into an object, it looks at the second record ("Moe", also an ID of 1). Because the ID of 1 is exactly the same, it assumes that the record represents the same object as the first record. Therefore, it doesn't instantiate a new Something object, and just moves on to the next record.

From the docs:

In the most general sense, the Session establishes all conversations with the database and represents a “holding zone” for all the objects which you’ve loaded or associated with it during its lifespan. It provides the entrypoint to acquire a Query object, which sends queries to the database using the Session object’s current database connection, populating result rows into objects that are then stored in the Session, inside a structure called the Identity Map - a data structure that maintains unique copies of each object, where “unique” means “only one object with a particular primary key”.

The "Identity Map" is the reason you are seeing this behavior. When you say that the primary key is the "ID" field, SQLAlchemy will hold you to this promise, and if it sees that multiple records are returned with the same ID, it assumes that they are all the same object.

Making sure your primary key is unique is the solution.

Collectives™ on Stack Overflow

Querying for all results in SQLAlchemy 0.7.10

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related