new to python new to python - 1 year ago 58
Python Question

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:

[email protected]:~$ cd Documents/45
[email protected]:~/Documents/45$ source env/bin/activate
(env) [email protected]:~/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) [email protected]:~/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

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download