Query for List of Attribute Instead of Tuples in SQLalchemy

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.

Python - SqlAlchemy: convert lists of tuples to list of atomic values

Assuming the_roles is the list of tuples with one element (in your example you obtain it from database, but it doesn't matter for response object generating) like

>>>the_roles = [(0,), (1,), (2,), (3,), (4,), (5,), (6,), (7,), (8,), (9,)]

then we can generate response object using list comprehension and tuple unpacking

>>>response = [value for (value,) in the_roles]
>>>response
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]

Finally your get_user_role can be rewritten like

def get_user_roles(user_id):
    the_roles = db.session.query(Role.role).filter(Role.users.any(id=user_id)).all()
    return [value for (value,) in the_roles]

SQLAlchemy returns tuple not dictionary

This should work:
dict(zip(['id','username','email'],result)) (or you could use a dictionary comprehension if you're on Python 3.x).


Also, you don't need to call session.execute on a session.query object. You'll want to use the .one() method on it instead. This also obviates the need for the .limit(1) call hanging off the end of your query.

How to get a list from query in sqlalchemy

I use (in @classmethod - that's why cls instance here; I also have added 'session' to class properties=)

list(session.query(cls))

returns exactly the simple list of all DeclarativeBase's instances.

How to retrieve python list of SQLAlchemy result set?

the most succinct way to pull out a list of 1-element tuples into a list is:

result = [r[0] for r in result]

or:

result = [r for r, in result]

SQLAlchemy: How to get result in a list while querying single columns of table using all()?

Try following:

>>> units_ability = [(1L,), (2L,)] # units_abilities = (Session....all())
>>> [x for (x,) in units_ability]
[1L, 2L]

SQLAlchemy - Why I keep receiving the object model instead of the value of query, after .query().all()?

SQLAlchemy is the Python SQL toolkit and Object Relational Mapper(ORM) that gives application developers the full power and flexibility of SQL. You will have a object and from that object you can access data I guess. Maybe you can do the below to fetch data

for record in data:
   print(record.id, record.platform_name)

It is giving you the object of the record and you can access column data using member variables of that object.

Hope I understood your question.

---

Related Topics