1

How do I compare a Python list to a SQLAlchemy collection?

I get the following error message

InvalidRequestError: Can't compare a collection to an object or collection; use contains() to test for membership

from this line

gr = AGroup.query.filter_by(users=sorted(members)).first()

Members is a list of AUser objects.

I want to check if any group exists with the same users as the members list.

However, since users is an sqlalchemy collection, this is not working.

Thank you. :)

Basic Class Structure

AGroup
- users

AUser
- name
GangstaGraham
  • 8,865
  • 12
  • 42
  • 60
  • how would you do it with a SQL query? – zzzeek Mar 08 '13 at 20:51
  • Have you tried what it says? "use contains() to test for membership" – MGP Mar 08 '13 at 20:58
  • Yeah, but that does not suit my needs. I dont want it to just contain the members and then maybe some other users. I want it to contain ONLY the members - which contains() does not check for. So hence, this did not work for me. – GangstaGraham Mar 08 '13 at 21:02
  • @zzzeek I ended up using just a little SQLAlchemy mixed in with Python (see my solution below and upvote if you like it), if you know of a better way to do this please let me know. – GangstaGraham Mar 09 '13 at 16:46
  • comparing a list in SQL is not that easy. Here's an SO answer that talks about it: http://stackoverflow.com/questions/11017678/sql-server-compare-results-of-two-queries-that-should-be-identical – zzzeek Mar 12 '13 at 01:35
  • @zzzeek I already solved this issue in my answer below, however do you think my method is more or less efficient than the approach given in your link? Thanks. – GangstaGraham Mar 12 '13 at 03:53
  • depends on how much data we're dealing with. if it scales how you need, i'd certainly stick with loading it in memory, but if i needed it to scale arbitrarily, i'd try to get a "compare lists in SQL" recipe to work, it would be somewhat complicated. – zzzeek Mar 12 '13 at 15:58

1 Answers1

-3

I solved the problem with the following code.

    groups = AGroup.query.all()
    for g in groups:
        if sorted(list(g.users)) == members:
            doSomething()
GangstaGraham
  • 8,865
  • 12
  • 42
  • 60