title: filtering on a range of values in a db column with sqlalchemy orm
body: <p>I have a postgresql database and in one particular table, with many rows.  One column in this table, called data, is a float array, REAL[], and gets filled with an array of ~4500 elements.  I want to access this table through some query via SQLAlchemy and the ORM. </p>
  
  <p>How do I select all rows in the table where a subset of this column satisfies some condition, e.g.contains a range of values?  Like I want to select all rows where the data contains values >= 10, or values between >=10 and &lt;=20.  </p>
  
  <p>Can I do this with a straight session query like</p>
  
  <pre><code>rows = session.query(Table).filter(Table.data.(some conditional)).all()
  </code></pre>
  
  <p>where my conditional is something like "VALUES >= 10 and VALUES &lt;= 20"?</p>
  
  <p>Or do I need to define some special methods, or setup, when I'm defining my SQLAlchemy table class.  For example, I have my table set up as </p>
  
  <pre><code>class Table(Base):
      __tablename__ = 'table'
      __table_args__ = {'autoload' : True, 'schema' : 'testdb', 'extend_existing':True}
  
      data = deferred(Column(ARRAY(Float)))
  
      def __repr__(self):
          return '&lt;Table (pk={0})&gt;'.format(self.pk)       
  </code></pre>
  
  <p>Ideally I'd like to set it up so I can just do simple filtering in my session.query calls.  Is this possible?  I'm not super familiar with the ORM, so maybe it is?   </p>
  
  <p>I've had a look at the ARRAY Comparator sqlalchemy docs but those only seem to work on exact values.  My data is precise to 6 sigfigs, and I don't know the exact values ahead of time. </p>
  
  <p>What's the best way to do this?  Thanks.    </p>
  
  <p>EDIT:</p>
  
  <p>Based on the below comment, here is the code I'm using in attempting to select all rows (out of 1000) that have data (from 1 column) >= 1.0.  There should be 537 rows.          </p>
  
  <pre><code>rows =  session.query(datadb.Table).filter(datadb.Table.data.any(1.0,operator=operators.le)).all()
  </code></pre>
  
  <p>This gives the correct subset number.  len(rows) = 537.  However, I don't understand the logic of with this operator, where to select data >=1.0 , I use the le operator? Also, along those same lines, there should be 234 rows that have data between the values >=1.0 and &lt;1.0, but this statement fails to give the correct subset..</p>
  
  <pre><code>rows = session.query(datadb.Table).filter(datadb.Table.data.any(1.0,operator=operators.le)).filter(datadb.Table.data.any(1.2,operator=operators.ge)).all()
  </code></pre>
  
  <p><strong>* EDIT 2 *</strong></p>
  
  <p>Here's an example of my database Table with a few rows.  pk is an integer, and data is a real[].</p>
  
  <pre><code>db datadb
  schema Table 
  pk      data
  0       [0.0,0.0,0.5,0.3,1.3,1.9,0.3,0.0,0.0]
  1       [0.1,0.0,1.0,0.7,1.1,1.5,1.2,0.3,1.4]
  2       [0.0,0.6,0.4,0.3,1.6,1.7,0.4,1.3,0.0]
  3       [0.0,0.1,0.2,0.4,1.0,1.1,1.2,0.9,0.0]
  4       [0.0,0.0,0.5,0.3,0.2,0.1,0.7,0.3,0.1]
  </code></pre>
  
  <p>I have 5 rows, 4 of them have data with values >= 1.0, while just 2 have values in the range >= 1.0 and &lt;= 1.2.  The query I would do to grab the rows is in the first case </p>
  
  <pre><code>rows = session.query(datadb.Table).filter(datadb.Table.data.any(1.0,operator=operators.le)).all()
  </code></pre>
  
  <p>This should return the 4 rows, at pk=0,1,2,3. This query does what I expect. The second case</p>
  
  <pre><code>rows = session.query(datadb.Table).filter(datadb.Table.data.any(1.0,operator=operators.le)).filter(datadb.Table.data.any(1.2,operator=operators.ge)).all()
  </code></pre>
  
  <p>and should return the 2 rows at pk=1,3.  However this query just returns the 4 rows from the first query. For the second query, I also tried</p>
  
  <pre><code>rows = session.query(datadb.Table).filter(datadb.Table.data.any(1.0,operator=operators.le),datadb.Table.data.any(1.2,operator=operators.ge)).all()
  </code></pre>
  
  <p>which also didn't work.  </p>