librelist archives

« back to archive

proper way to query once

proper way to query once

From:
Cord Macleod
Date:
2013-01-24 @ 08:02
Given the following tables, I'm curious as to how to grab a list of user
locations with the description, created date lat, lgn and address.  My
current code is rather inefficient and I'm sure there is a way to do this
with a join.  Any thoughts?

Models:
class Userlocs(db.Model):
    __tablename__ = 'userlocs'
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'),
        primary_key=True)
    location_id = db.Column(db.Integer, db.ForeignKey('location.id'),
primary_key=True)
    description = db.Column(db.String(128))
    created_time = db.Column(db.DateTime)
    db.Index('user_id_index', 'user_id')
    db.Index('location_id_index', 'location_id')
    db.UniqueConstraint('user_id', 'location_id', name='user_location')

    def __init__(self, description, created_time):
        self.description = description
        self.created_time = created_time

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(128), unique=True)
    email = db.Column(db.String(128), unique=True)
    password = db.Column(db.String(128))
    locations = db.relationship('Userlocs', backref=db.backref('user'))

    def __init__(self, username, email, password):
        self.username = username
        self.email = email
        self.password = password

    def __repr__(self):
        return '<User %r>' % self.username

class Location(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    address = db.Column(db.String(128), unique=True)
    lat = db.Column(db.String(32))
    lng = db.Column(db.String(32))

    def __init__(self, address, lat, lng):
        self.address = address
        self.lat = lat
        self.lng = lng

    def __repr__(self):
        return '<Address: %r>' % self.address

Query:

@app.route('/get/<user_id>/locations', methods = ['GET'])
def apt_get_locations(user_id):
    try:
        location_list = []
        user = uber_db.User.query.filter_by(id=user_id).one()
        locations = user.locations
        for location in locations:
            loc = uber_db.Location.query.filter_by(location_id=location.id
).one()
            loc_list = [loc.address, loc.lat, loc.lng,
location.description, location.created_time]
            location_list.append(loc_list)
        data = []
        for i in location_list:
            data.append[{'address':location_list[i][0]],
'lat':location_list[i][1], 'lng':location_list[i][2],
'description':location_list[i][3], 'created_time':location_list[i][4]}]
        return jsonify(data)

Re: [flask] proper way to query once

From:
Trey Long
Date:
2013-01-24 @ 14:23
Seem mostly like a SqlAlchemy issue, which is what it looks like you're using.

Looping over a query is always a bad idea if avoidable and it does look 
like a join would help. The SqlAlchemy docs are quite good.
http://docs.sqlalchemy.org/ru/latest/orm/tutorial.html#querying-with-joins

http://docs.sqlalchemy.org/ru/latest/orm/query.html#sqlalchemy.orm.query.Query.join


On Jan 24, 2013, at 3:02 AM, Cord Macleod <cordmacleod@gmail.com> wrote:

> Given the following tables, I'm curious as to how to grab a list of user
locations with the description, created date lat, lgn and address.  My 
current code is rather inefficient and I'm sure there is a way to do this 
with a join.  Any thoughts?
> 
> Models:
> class Userlocs(db.Model):
>     __tablename__ = 'userlocs'
>     user_id = db.Column(db.Integer, db.ForeignKey('user.id'), 
>         primary_key=True)
>     location_id = db.Column(db.Integer, db.ForeignKey('location.id'), 
primary_key=True)
>     description = db.Column(db.String(128))
>     created_time = db.Column(db.DateTime)
>     db.Index('user_id_index', 'user_id')
>     db.Index('location_id_index', 'location_id')
>     db.UniqueConstraint('user_id', 'location_id', name='user_location')
> 
>     def __init__(self, description, created_time):
>         self.description = description
>         self.created_time = created_time
> 
> class User(db.Model):
>     id = db.Column(db.Integer, primary_key=True)
>     username = db.Column(db.String(128), unique=True)
>     email = db.Column(db.String(128), unique=True)
>     password = db.Column(db.String(128))
>     locations = db.relationship('Userlocs', backref=db.backref('user'))
> 
>     def __init__(self, username, email, password):
>         self.username = username
>         self.email = email
>         self.password = password
> 
>     def __repr__(self):
>         return '<User %r>' % self.username
> 
> class Location(db.Model):
>     id = db.Column(db.Integer, primary_key=True)
>     address = db.Column(db.String(128), unique=True)
>     lat = db.Column(db.String(32))
>     lng = db.Column(db.String(32))
> 
>     def __init__(self, address, lat, lng):
>         self.address = address
>         self.lat = lat
>         self.lng = lng
> 
>     def __repr__(self):
>         return '<Address: %r>' % self.address
> 
> Query:
> 
> @app.route('/get/<user_id>/locations', methods = ['GET'])
> def apt_get_locations(user_id):
>     try:
>         location_list = []
>         user = uber_db.User.query.filter_by(id=user_id).one()
>         locations = user.locations
>         for location in locations:
>             loc = 
uber_db.Location.query.filter_by(location_id=location.id).one()
>             loc_list = [loc.address, loc.lat, loc.lng, 
location.description, location.created_time]
>             location_list.append(loc_list)
>         data = []
>         for i in location_list:
>             data.append[{'address':location_list[i][0]], 
'lat':location_list[i][1], 'lng':location_list[i][2], 
'description':location_list[i][3], 'created_time':location_list[i][4]}]
>         return jsonify(data)

Re: [flask] proper way to query once

From:
Audrius Kažukauskas
Date:
2013-01-24 @ 12:41
On Thu, 2013-01-24 at 00:02:18 -0800, Cord Macleod wrote:
> Given the following tables, I'm curious as to how to grab a list of user
> locations with the description, created date lat, lgn and address.  My
> current code is rather inefficient and I'm sure there is a way to do this
> with a join.  Any thoughts?

If I understand your example correctly, User/Location is a many-to-many
relationship.  If so, then I would define it using association table:

  userloc = db.Table('userlocs',
      db.Column('user_id', db.Integer, primary_key=True,
                db.ForeignKey('user.id')),
      db.Column('location_id', db.Integer, primary_key=True,
                db.ForeignKey('location.id')),
  )

  class User(db.Model):
      # ...
      locations = db.relationship('Location', secondary=userloc,
                                  backref='users')

Then locations the user is associated with would be queried like this:

  uber_db.Location.query.join(User.locations).filter(User.id == user_id)

I also noticed that you are keeping additional data besides FKs in your
association object (Userlocs).  This is allowed[0], but it's better to
use associationproxy extension[1].

[0] 
http://docs.sqlalchemy.org/en/rel_0_8/orm/relationships.html#association-object
[1] 
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/associationproxy.html#simplifying-association-objects

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

Re: [flask] proper way to query once

From:
Cord Macleod
Date:
2013-01-24 @ 20:05
Inline.

On Thu, Jan 24, 2013 at 4:41 AM, Audrius Kažukauskas <audrius@neutrino.lt>wrote:
>
>
> If I understand your example correctly, User/Location is a many-to-many
> relationship.  If so, then I would define it using association table:
>
>   userloc = db.Table('userlocs',
>       db.Column('user_id', db.Integer, primary_key=True,
>                 db.ForeignKey('user.id')),
>       db.Column('location_id', db.Integer, primary_key=True,
>                 db.ForeignKey('location.id')),
>   )
>
>   class User(db.Model):
>       # ...
>       locations = db.relationship('Location', secondary=userloc,
>                                   backref='users')
>
> Then locations the user is associated with would be queried like this:
>
>   uber_db.Location.query.join(User.locations).filter(User.id == user_id)
>

Yes, that's what I needed.  Excellent.  Two follow upl questions.  That
particular query only gives me back the addresses, when I call
locations.all().  Do I need to put all of the information I want in the
__repr__ method in the locations table?  Also how would I go about grabbing
the description and created_time in the Userlocs table associated with each
address?

Re: [flask] proper way to query once

From:
Cord Macleod
Date:
2013-01-24 @ 20:05
Inline.

On Thu, Jan 24, 2013 at 4:41 AM, Audrius Kažukauskas <audrius@neutrino.lt>wrote:
>
>
> If I understand your example correctly, User/Location is a many-to-many
> relationship.  If so, then I would define it using association table:
>
>   userloc = db.Table('userlocs',
>       db.Column('user_id', db.Integer, primary_key=True,
>                 db.ForeignKey('user.id')),
>       db.Column('location_id', db.Integer, primary_key=True,
>                 db.ForeignKey('location.id')),
>   )
>
>   class User(db.Model):
>       # ...
>       locations = db.relationship('Location', secondary=userloc,
>                                   backref='users')
>
> Then locations the user is associated with would be queried like this:
>
>   uber_db.Location.query.join(User.locations).filter(User.id == user_id)
>

Yes, that's what I needed.  Excellent.  Two follow upl questions.  That
particular query only gives me back the addresses, when I call
locations.all().  Do I need to put all of the information I want in the
__repr__ method in the locations table?  Also how would I go about grabbing
the description and created_time in the Userlocs table associated with each
address?

Re: [flask] proper way to query once

From:
Audrius Kažukauskas
Date:
2013-01-24 @ 21:11
On Thu, 2013-01-24 at 12:05:53 -0800, Cord Macleod wrote:
> > Then locations the user is associated with would be queried like this:
> >
> >   uber_db.Location.query.join(User.locations).filter(User.id == user_id)
> 
> Yes, that's what I needed.  Excellent.  Two follow upl questions.  That
> particular query only gives me back the addresses, when I call
> locations.all().  Do I need to put all of the information I want in the
> __repr__ method in the locations table?  Also how would I go about grabbing
> the description and created_time in the Userlocs table associated with each
> address?

Well, if you're sticking to association object approach, then I think
that something along the lines of

  uber_db.session.query(
      Location.address,
      Location.lat,
      Location.lng,
      Userlocs.description,
      Userlocs.created_time
  ).join(Userlocs, User).filter(User.id == user_id).all()

should work.  You'll get a list of tuples when running this query.

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