title: How can I write my own aggregate functions with sqlalchemy?
body: <p>How can I write my own aggregate functions with SQLAlchemy? As an easy example I would like to use numpy to calculate the variance. With sqlite it would look like this:</p>
  
  <pre><code>import sqlite3 as sqlite
  import numpy as np
  
  class self_written_SQLvar(object):
    def __init__(self):
      import numpy as np
      self.values = []
    def step(self, value):
      self.values.append(value)
    def finalize(self):
      return np.array(self.values).var()
  
  cxn = sqlite.connect(':memory:')
  cur = cxn.cursor()
  cxn.create_aggregate("self_written_SQLvar", 1, self_written_SQLvar)
  # Now - how to use it:
  cur.execute("CREATE TABLE 'mytable' ('numbers' INTEGER)")
  cur.execute("INSERT INTO 'mytable' VALUES (1)") 
  cur.execute("INSERT INTO 'mytable' VALUES (2)") 
  cur.execute("INSERT INTO 'mytable' VALUES (3)") 
  cur.execute("INSERT INTO 'mytable' VALUES (4)")
  a = cur.execute("SELECT avg(numbers), self_written_SQLvar(numbers) FROM mytable")
  print a.fetchall()
  &gt;&gt;&gt; [(2.5, 1.25)]
  </code></pre>