librelist archives

« back to archive

Flask-SQLAlchemy: Select distinct()

Flask-SQLAlchemy: Select distinct()

From:
bruce bushby
Date:
2012-12-04 @ 10:14
Hi

Feel a bit stupid asking such a simple question...but I can't find any
flask-sqlalchemy example equivalent to:
"select distinct(brand) from tablename order by brand"
I have read: http://packages.python.org/Flask-SQLAlchemy/api.html
 ...perhaps I'm confused ....which is quite possible.

Do most people here use "flask-sqlalchemy" ...... or is it easier to stick
to building standard SQL statements and invoking them
via the "g" object?

Cheers
Bruce

Re: [flask] Flask-SQLAlchemy: Select distinct()

From:
Audrius Kažukauskas
Date:
2012-12-04 @ 10:44
On Tue, 2012-12-04 at 10:14:23 +0000, bruce bushby wrote:
> Feel a bit stupid asking such a simple question...but I can't find any
> flask-sqlalchemy example equivalent to:
> "select distinct(brand) from tablename order by brand"
> I have read: http://packages.python.org/Flask-SQLAlchemy/api.html
>  ...perhaps I'm confused ....which is quite possible.

Flask-SQLAlchemy is just a thin layer for SQLAlchemy, as is often the
case with other Flask extensions.  You should look at the docs of the
library the extension is written for.  For SQLAlchemy 0.7.x that would
be <http://docs.sqlalchemy.org/en/rel_0_7/>.

To answer your question, the query should look something like

  # I modified your SQL query to
  # "SELECT DISTINCT name FROM brand ORDER BY name"
  db.session.query(Brand.name.distinct()).order_by(Brand.name).all()

assuming that you have Brand class defined like this:

  class Brand(db.Model):
      id = db.Column(db.Integer, primary_key=True)
      name = db.Column(db.String(100))
      ...

> Do most people here use "flask-sqlalchemy" ...... or is it easier to stick
> to building standard SQL statements and invoking them
> via the "g" object?

Personally I like to use SQLAlchemy directly, but there are many people
here who use Flask-SQLAlchemy.  And there are some who prefer to write
pure SQL.  Choose whatever suits you best.

-- 
Audrius Kažukauskas
http://neutrino.lt/

Re: [flask] Flask-SQLAlchemy: Select distinct()

From:
bruce bushby
Date:
2012-12-04 @ 11:24
Hi Audrius

Thanks for the help and pointers!!

I have a blueprint called "main" and inside it a  dir called models where I
store mu models
app/main/models/__init__.py
app/main/models/ukvehicle.py



app/main/models/ukvehicle.py  contains:

from app import db
class ukvehicle(db.Model):

        __tablename__ = 'ukvehicle'

        id = db.Column(db.Integer, primary_key=True)
        brand = db.Column(db.String())
        model = db.Column(db.String())
        year = db.Column(db.String())
        trim = db.Column(db.String())
        fuel_delivery = db.Column(db.String())
        fuel_type = db.Column(db.String())
        doors = db.Column(db.String())
        style = db.Column(db.String())
        transmission = db.Column(db.String())
        power = db.Column(db.String())
        torque = db.Column(db.String())
        torque_rpm = db.Column(db.String())
        speed_mph = db.Column(db.String())
        tyres = db.Column(db.String())
        height_mm = db.Column(db.String())
        length_mm = db.Column(db.String())
        width_mm = db.Column(db.String())
        weight = db.Column(db.String())
        boot_ltr = db.Column(db.String())
        acceleration60 = db.Column(db.String())
        fuel_eco_urban = db.Column(db.String())
        fuel_eco_extra_urban = db.Column(db.String())
        fuel_eco_combined = db.Column(db.String())
        emissions = db.Column(db.String())
        igroup = db.Column(db.String())





app/main/views.py;
from app.main.models.ukvehicle import ukvehicle


However when I run;
results =
ukvehicle.query(ukvehicle.brand.distinct()).order_by(ukvehicle.brand).all()


I get:
 results =
ukvehicle.query(ukvehicle.brand.distinct()).order_by(ukvehicle.brand).all()
TypeError: 'BaseQuery' object is not callable



This query work 100%:
results = ukvehicle.query.filter_by(brand = mybrand)




any ideas?


Thanks





On Tue, Dec 4, 2012 at 10:44 AM, Audrius Kažukauskas <audrius@neutrino.lt>wrote:

> db.session.query(Brand.name.distinct()).order_by(Brand.name).all()
>

Re: [flask] Flask-SQLAlchemy: Select distinct()

From:
Simon Sapin
Date:
2012-12-04 @ 11:33
Le 04/12/2012 12:24, bruce bushby a écrit :
>
> However when I run;
> results =
> ukvehicle.query(ukvehicle.brand.distinct()).order_by(ukvehicle.brand).all()
>
>
> I get:
>   results =
> ukvehicle.query(ukvehicle.brand.distinct()).order_by(ukvehicle.brand).all()
> TypeError: 'BaseQuery' object is not callable
>

Try this:

     db.session.query(ukvehicle.brand.distinct()). …

The parameter(s) to db.session.query is the type of items you want in 
the results. Note that ukvehicle.query is a shortcut for 
db.session.query(ukvehicle), since that pattern is very common.

Cheers,
-- 
Simon Sapin

Re: [flask] Flask-SQLAlchemy: Select distinct()

From:
bruce bushby
Date:
2012-12-04 @ 12:34
Thank you Simon!!  using db.session...   did the trick   :)))



On Tue, Dec 4, 2012 at 11:33 AM, Simon Sapin <simon.sapin@exyr.org> wrote:

> Le 04/12/2012 12:24, bruce bushby a écrit :
> >
> > However when I run;
> > results =
> >
> ukvehicle.query(ukvehicle.brand.distinct()).order_by(ukvehicle.brand).all()
> >
> >
> > I get:
> >   results =
> >
> ukvehicle.query(ukvehicle.brand.distinct()).order_by(ukvehicle.brand).all()
> > TypeError: 'BaseQuery' object is not callable
> >
>
> Try this:
>
>      db.session.query(ukvehicle.brand.distinct()). …
>
> The parameter(s) to db.session.query is the type of items you want in
> the results. Note that ukvehicle.query is a shortcut for
> db.session.query(ukvehicle), since that pattern is very common.
>
> Cheers,
> --
> Simon Sapin
>