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.

edthrn · Accepted Answer · 2018-03-30 00:06:05Z

11

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!

answered Mar 30, 2018 at 0:06

edthrn

1,19214 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

executing a raw sql query from sqlalchemy on postgresql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related