SQL Alchemy / flask_sqlalchemy - Get all column's values by an input column name for a specific-CodePudding

I'm using flask-SQL Alchemy and I'm trying to build a function to get all values for an input column name for an input table.

Example:

I have the following model:

class Image(db.Model):
    id = db.Column(db.Integer, unique=True, primary_key=True)
    path = db.Column(db.String(256), nullable=False, unique=True)

Let's say that the table was filled with 5 rows:

Image(id=1, path="c:\1.bmp")
Image(id=2, path="c:\2.bmp")
Image(id=5, path="c:\5.bmp")
Image(id=6, path="c:\6.bmp")

I would like to build a function that will receive the Table & Column name and will return the whole values of this column in the input table.

Desired fucntion signiture and output

result = get_all_vals(Image, "id")
print(result)
>> [1,2,5,6]

result = get_all_vals(Image, "path")
print(result)
>> ["c:\1.bmp","c:\2.bmp","c:\5.bmp","c:\6.bmp"]

I know that using with_entities() I can do something like this, but it doesn't support binding the colmn's name as parameter (since I must use the '.' operator)

q = session.query(Image)
subq = q.with_entities(Image.id)

CodePudding user response：

From what you provided, there are several possibilities, such as:

# Using load_only
q = session.query(Image).options(load_only("id"))
# > 'SELECT image.id AS image_id \nFROM image'
   
# Query will still return models instances
results = [getattr(row, "id") for row in q.all()]
    
# Using with_entities
q = session.query(Image).with_entities(getattr(Image, "id"))
# > 'SELECT image.id AS image_id \nFROM image'

# Query will return list of tuples, so flatten them
results = [value for (value,) in q.all()]

Note : if all your values are different, as shown in your exemples, you might want to consider using a distinct :

from sqlalchemy.sql import func

q = session.query(func.distinct(getattr(Image, "id")))
results = [value for (value,) in q.all()]