executing a raw sql query from sqlalchemy on postgresql

Question

I have a raw sql query which is:

select distinct(user_id) from details_table where event_id in (29,10) and user_id in (7,11,24,45) and epoch_timestamp >= 1433116800 and epoch_timestamp <= 1506816000;

which in psql returns:

 user_id 
---------
       7
      24
(2 rows)

Now when i run this raw sql query via sqlalchemy I'm getting a sqlalchemy.engine.result.ResultProxy object in response and not the result as above. The code i'm using right now is as follows:

from flask import current_app
sql_query = text(select distinct(user_id) from details_table where event_id in (29,10) and user_id in (7,24) and epoch_timestamp >= 1433116800 and epoch_timestamp <= 1506816000;)

filtering_users = db.get_engine(current_app, bind='<my_binding>')\
                    .execute(sql_query)
print(type(filtering_users))
# <class 'sqlalchemy.engine.result.ResultProxy'>
print(filtering_users)
# <sqlalchemy.engine.result.ResultProxy object at 0x7fde74469550>

I used the reference from here but unlike the solution there I'm getting a ResultProxy object.

What am I doing wrong here? My end goal is to get the list of users returned from executing the raw sql-query, stored into a list.

Solution

As explained is the SQLAlchemy documentation, the .execute() method returns only a proxy on which you'll have to iterate (or apply any aggregation method) to view the actual result of the query. Apparently, in your case, what you want is the .fetchall() method.

If you try something like this:

from sqlalchemy import create_engine

engine = create_engine('/path/to/your/db...')
connection = engine.connect()

my_query = 'SELECT * FROM my_table'
results = connection.execute(my_query).fetchall()

the results variable will be a list of all the items that the query fetches.

Hope this helps!