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

user_10551444 0
Viewed 328

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?

sqlalchemy python
2 Answers

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
user_995940

First error message is the fact that query and filter without join (our any other joins) cannot give you columns from both tables. You need to either put both tables into session query, or join those two tables in order to gather column values from different tables, so this code:

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

needs to look more like this:

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

or like this:

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

Another thing is that the data will be structured differently so in the first example, you will need two for loops:

for taskuser in all_taskuser:
   for task in taskuser.task:
      # to reference to id_type_task : task.id_type_task

and in the second example your result is the tuple, so for loop should look like this

for taskuser, task in all_taskuser:
    # to reference to id_type_task : task.id_type_task

NOTE: I haven't check all these examples, so there may be errors, but concepts are there. For more info, please refer yourself to this page: https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_orm_working_with_joins.htm

user_43448860
Related