How to have the possibility to call name of columns in db.session.query with 2 tables in Flask Python?

I am developing a web application with Flask, Python, SQLAlchemy, and Mysql.

I have 2 tables:

TaskUser:
- id
- id_task (foreign key of id column of table Task)
- message


Task
- id
- id_type_task

I need to extract all the tasksusers (from TaskUser) where the id_task is in a specific list of Task ids. For example, all the taskusers where id_task is in (1,2,3,4,5)

Once I get the result, I do some stuff and use some conditions.

When I make this request :

all_tasksuser=TaskUser.query.filter(TaskUser.id_task==Task.id)  
    .filter(TaskUser.id_task.in_(list_all_tasks),Task.id_type_task).all()

for item in all_tasksuser:
    item.message="something"
    if item.id_type_task == 2:
        #do some stuff
    if item.id_task == 7 or item.id_task == 7:
        #do some stuff

I get this output error:

if item.id_type_task == 2:
AttributeError: 'TaskUser' object has no attribute 'id_type_task'

It is normal as my SQLAlchemy request is calling only one table. I can’t access to columns of table Task.

BUT I CAN call the columns of TaskUser by their names (see item.id_task).

So I change my SQLAlchemy to this:

all_tasksuser=db_mysql.session.query(TaskUser,Task.id,Task.id_type_task).filter(TaskUser.id_task==Task.id)  
    .filter(TaskUser.id_task.in_(list_all_tasks),Task.id_type_task).all()

This time I include the table Task in my query BUT I CAN’T call the columns by their names. I should use the [index] of columns. I get this kind of error message:

AttributeError: 'result' object has no attribute 'message'

The problem is I have many more columns (around 40) on both tables. It is too complicated to handle data with index numbers of columns.

I need to have a list of rows with data from 2 different tables and I need to be able to call the data by column name in a loop.

Do you think it is possible?

Answer

The key point leading to the confusion is the fact that when you perform a query for a mapped class like TaskUser, the sqlalchemy will return instances of that class. For example:

q1 = TaskUser.query.filter(...).all()  # returns a list of [TaskUser]
q2 = db_mysql.session.query(TaskUser).filter(...).all()  # ditto

However, if you specify only specific columns, you will receive just a (special) list of tuples:

q3 = db_mysql.session.query(TaskUser.col1, TaskUser.col2, ...)...

If you switch your mindset to completely use the ORM paradigm, you will work mostly with objects. In your specific example, the workflow could be similar to below, assuming you have relationships defined on your models:

# model
class Task(...):
    id = ...
    id_type_task = ...


class TaskUser(...):
    id = ...
    id_task = Column(ForeignKey(Task.id))
    message = ...

    task = relationship(Task, backref="task_users")
    
# query
all_tasksuser = TaskUser.query ...

# work
for item in all_tasksuser:
    item.message = "something"
    if item.task.id_type_task == 2:  # <- CHANGED to navigate the relationship ...
        #do some stuff
    if item.task.id_task == 7 or item.task.id_task == 7:  # <- CHANGED
        #do some stuff