CODEWITHSUNDEEP
pythonsqlsqlitesqlalchemyflask-sqlalchemy
rockets4all
rockets4all

Reputation: 876

get sqlalchemy to pull entire column

In regular sql I can pull an entire column via "select * from table", I can not find an equivalent in sqlalchemy.

I am trying to pull all the usernames from my table User. All I have found so far is models.User.query.all() and then performing a for loop to pull the columns I want, but I am lead to believe this is terrible practice as it pulls the entire table when I only want 1 column.

models.py

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(32), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    email = db.Column(db.String(120), index=True, unique=True)
    posts = db.relationship('Post', backref='author', lazy='dynamic')
    #mobilitys = db.relationship('Mobility', backref='username', lazy='dynamic')
    about_me = db.Column(db.String(140))
    last_seen = db.Column(db.DateTime)

    def __repr__(self):  # pragma: no cover
        return '<user> %r' % (self.username)

Terminal trying to do with_entities

>>> from app import db, models
>>> models.User.query.with_entities(User.username)
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
NameError: name 'User' is not defined
>>> models.User.username
<sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7fccb4f682f0>

terminal view for follow up questions:

jsnyder10@jsnyder10-VirtualBox:~$ cd Documents/45
jsnyder10@jsnyder10-VirtualBox:~/Documents/45$ source env/bin/activate
(env) jsnyder10@jsnyder10-VirtualBox:~/Documents/45$ flask/bin/python
Python 2.7.12 (default, Nov 19 2016, 06:48:10) 
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from models import User
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: No module named models
>>> from .models import User
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: Attempted relative import in non-package

2nd terminal view:

(env) jsnyder10@jsnyder10-VirtualBox:~/Documents/45$ flask/bin/python
Python 2.7.12 (default, Nov 19 2016, 06:48:10) 
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from app import db, models
>>> from models import User
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: No module named models
>>> from .models import User
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: Attempted relative import in non-package

Upvotes: 1

Views: 1282

Answers (1)

Tiny.D
Tiny.D

Reputation: 6556

You can try with with_entities() method to restrict which columns you'd like to return in the result. eg:

result = User.query.with_entities(User.username)

or with sqlalchmey session:

result = session.query(User.username)

Upvotes: 1

Related Questions