SQL Alchemy Orm Returning a Single Column, How to Avoid Common Post Processing

Query for list of attribute instead of tuples in SQLAlchemy

When passing in ORM-instrumented descriptors such as a column, each result is a named tuple, even for just one column. You could use the column name in a list comprehension to 'flatten' the list (you can drop the .all() call, iteration retrieves the objects too):

result = [r.id for r in session.query(MyModel.id)]

or use the fact that it's a tuple when looping a for loop and unpack it to a single-element tuple of targets:

result = session.query(MyModel.id)
for id, in result:
    # do something with the id

The latter could also be used in a list comprehension:

[id for id, in session.query(MyModel.id)]

You don't really have any options to force the row results to be just the single id value.

SQL Alchemy ORM and Text of same query returns different results?

Thanks to univerio's comment above I went looking for how to specify DATE() in SQL Alchemy and got it working by changing it to the following:

under_10 = session.query(func.count(FacebookPost.id)).\
                         filter(FacebookPost.shares < 10, 
                                FacebookPost.account_id == '1234',
                                FacebookPost.article_id != None).\
                        filter(func.date(FacebookPost.created_time) > start,
                               func.date(FacebookPost.created_time) < end)

Unexcepted return format of filter function in sqlalchemy

This depends a little on whether User.username is unique. If you are only expecting the query to return a single row (or no row), then you can use Query.scalar().

Return the first element of the first result or None if no rows
present. If multiple rows are returned, raises MultipleResultsFound.

It would look like:

password = session.query(User.password).filter(User.username == 'asdef').scalar()
print(password)  # 1234

... i.e. not in a for loop.

However, if the query session.query(User.password).filter(User.username == 'asdef') can return more than one row (i.e. more than one user with the same username, and this is perhaps what you intend here, as you are accessing the query in a for loop) than .scalar() will fail as internally it uses Query.one() which raises MultipleResultsFound exception if a query returns more than one row. In that case you really just need to index out the password or get it using attribute lookup (the row proxy can do either):

for row in session.query(User.password).filter(User.username == 'asdef'):
    # both of these will print the same thing, but the second one
    # makes for nicer reading!
    print(row[0])
    print(row.password)

# output:
# 1234
# 1234

SQL Alchemy ORM Getter

It's actually quite easy to create a custom type like this when using the TypeDecorator:
http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html#sqlalchemy.types.TypeDecorator

However... if you don't have anything that specifically depends on json, I would recommend using the PickleType instead of your custom json type: http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/types.html#sqlalchemy.types.PickleType

Here's an example on how to make a json column work anyway:

class JsonType(types.TypeDecorator):
    impl = types.Unicode

    def process_bind_param(self, value, dialect):
        return json.dumps(value)

    def process_result_value(self, value, dialect):
        return json.loads(value)

---

Related Topics