Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

querying and selecting specific column in SQLAlchemy

Tags:

sql

sqlalchemy

How do I select specific columns from a query. For example, just the User name and size of a photo from:

class User(Base):
    __tablename__ = 'user'
    user_id =      Column(String,   primary_key = True, unique = True)
    real_name =    Column(String,   nullable = True)

class Photo(Base):
    __tablename__ = 'photo'
    url =        Column(String,        primary_key = True, unique = True)
    size =       Column(Integer,       nullable = False)
    ts_taken =   Column(DateTime,      nullable = True)
    user_id =    Column(String,        ForeignKey('user.user_id'))
    user = relationship(User, backref='photos')

I can use:

s.query(Photo).join(User.photo).all()

or maybe, if I want all the photos of a specfic user:

s.query(Photo).join(User.photo).filter(User.user_id == '1234').all()

But how do I make it return just the User.real_name and the size?

like image 962
eran Avatar asked Jun 03 '14 09:06

eran


1 Answers

Specify what you want returned in .query(). The first mapped column indicates the base table to query from.

session.query(User.real_name, Photo.size).join(User.photos).all()

This will get you a list of tuples like [('name', 12)] for every photo.

Please consider searching the documentation briefly. It's author is very thorough.

like image 190
davidism Avatar answered Sep 28 '22 00:09

davidism