sqlachlemy multiple counts in a module

Question

I'm trying to get a count of rows in an sqlalchemy database, for each unique entry in a column, while simultaneously filtering for another two columns.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
db = SQLAlchemy(app)

class FPTP(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    candid = db.Column(db.String(40), index=True, unique=False)
    riding = db.Column(db.String(50), index=True, unique=False)
    election = db.Column(db.String(30), index=True, unique=False)

    def results(self, riding, election):
        result = self.query(func.count(self.candid), self.candid).\
                 filter(and_(self.election==election, self.riding==riding)).\
                 group_by(self.candid)
        return result

The documentation makes func.count an argument of query, and so does a tutorial, and some of the other answers here: count unique rows in sql-alchemy, SQLAlchemy func.count with filter.

But when I do that I get:TypeError: 'BaseQuery' object is not callable. And another answer here explained that query is an instance and not a class with it's own functions, which was helpful. But the documentation and the tutorial make func.count called by query.

I also see examples are in a "session": Group by & count function in sqlalchemy. I want this in a module, not in an interpreter. So building a shell, making a session, and binding an engine is not a solution I am looking for.

I see other answers here (like: Define name for column func.count in sqlalchemy), but that's about naming the columns and getting the count for a unique occurrence. I want to do it more pythonic than here: TypeError: 'BaseQuery' object is not callable Flask.

I want to call this function:

results = FPTP.results("Some Riding", "Election")

and get a count of all occurrences like what is given on page 21 of the documentation:

[(1, 'ed'), (1, 'fred'), (1, 'mary'), (1, 'wendy')]

after filtering by two other columns.

try something like http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#sqlalchemy.orm.session.Session.object_session — Shiva, Feb 26 '18 at 14:26
@Shiva, that uses a session. When I add to the database, I use `db.session.add(something)` and `db.session.commit()`. How is the information contained in your link different than what I have with `db = SQLAlchemy(app)`? Thanks. — David Collins, Feb 26 '18 at 14:57
... but `self.query.filter(something).all()` works. The error is actually stating that `query` is not callable. So query() gives the error. — David Collins, Feb 26 '18 at 15:29

sqlachlemy multiple counts in a module

0 Answers0