db.session.execute(sql, params) does not return object

Question

I'm using JinjaSQL to create query templates so that I can have cleaner and more efficient code. I initially started building my API with Flask-SqlAlchemy. I've gone from this hideous thing:

      query = text('SELECT photos.*, profile.* FROM photos '
                     'INNER JOIN profile on profile.user_id = photos.user_id '
                     'WHERE photos.user_id != {} AND profile.dob BETWEEN {} and {} '
                     'AND profile.gender = {} '
                     'AND NOT EXISTS (SELECT * FROM ratings '
                     'WHERE ratings.photo_name = photos.photo_name '
                     'AND ratings.rating_user_id = {}) '
                     'ORDER BY random() LIMIT 1'.format(current_user, high_date, low_date, gender, current_user))

        query_all_genders = text('SELECT photos.*, profile.* FROM photos '
                                 'INNER JOIN profile on profile.user_id = photos.user_id '
                                 'WHERE photos.user_id != {} AND profile.dob BETWEEN {} and {} '
                                 'AND NOT EXISTS (SELECT * FROM ratings '
                                 'WHERE ratings.photo_name = photos.photo_name '
                                 'AND ratings.rating_user_id = {}) '
                                 'ORDER BY random() LIMIT 1'.format(current_user, high_date, low_date, current_user))

        query_all_ages = text('SELECT photos.*, profile.* FROM photos '
                              'INNER JOIN profile on profile.user_id = photos.user_id '
                              'WHERE photos.user_id != {} '
                              'AND profile.gender = {} '
                              'AND NOT EXISTS (SELECT * FROM ratings '
                              'WHERE ratings.photo_name = photos.photo_name '
                              'AND ratings.rating_user_id = {}) '
                              'ORDER BY random() LIMIT 1'.format(current_user, gender, current_user))

        query_all = text('SELECT photos.*, profile.* FROM photos '
                         'INNER JOIN profile on profile.user_id = photos.user_id '
                         'WHERE photos.user_id != {} '
                         'AND NOT EXISTS (SELECT * FROM ratings '
                         'WHERE ratings.photo_name = photos.photo_name '
                         'AND ratings.rating_user_id = {}) '
                         'ORDER BY random() LIMIT 1'.format(current_user, current_user))

        if flag == 1:
            resultproxy = db.session.execute(query_all_genders)
        elif flag == 2:
            resultproxy = db.session.execute(query_all_ages)
        elif flag == 3:
            resultproxy = db.session.execute(query_all)
        else:
            resultproxy = db.session.execute(query)

to:

# create query template
        template = """
            SELECT photos.*, profile.*, geolocation.* 
            FROM photos
            INNER JOIN profile on profile.user_id = photos.user_id
            WHERE photos.user_id != :user_id AND profile.dob BETWEEN :high and :low 
            {% if gender %}
            AND profile.gender = :gender
            {% endif %}
            AND NOT EXISTS (SELECT * FROM ratings WHERE ratings.photo_name = photos.photo_name AND ratings.rating_user_id = :user_id)
                            ORDER BY random() LIMIT 1;
        """
user_json = request.get_json()
query, bind_params = j.prepare_query(template, user_json)

The problem is with this line here:

resultproxy = db.session.execute(text(query), user_json)  # needed to execute the query with sqlalchemy

When I run

        for row in resultproxy:
            print(row)

it works just fine, but returns the values from the columns but not the column names themselves as key:value pairs.

If I use the ugly version of the code, it returns an object like it should.

query = text('SELECT *....') # ugly version
resultproxy = db.session.execute(query)
for rowproxy in resultproxy:
            # returns an array like [(key0, value0), (key1, value1)]
            for column, value in rowproxy.items():
                # create the dictionary

I'm not really sure what I'm doing wrong here. If there's a better way to do this, then I'm happy to rework the entire thing. I do like the jinjasql approach though, and would prefer to continue using it.

score 4 · Accepted Answer · answered Feb 29 '20 at 07:01

4

The RowProxy instances that you are receiving from db.session.execute is a dict like object, so you can just cast it to dict with dict(rowproxy). But this should be unnecessary as RowProxy object itself will support all operations that a dictionary would. You are printing rowproxy in the terminal with print, in which case it uses the __repr__ method for that class, which formats the rowproxy object to look like a tuple of the row data.

answered Feb 29 '20 at 07:01

syfluqs

658
5
12

When I use the `RowProxy` iteration as with the old code it returns a key error when I try to access it as above. I printed in the terminal to see what the outputs were. Strangely enough, I was cleaning up the formatting today and adding comments and somehow it now works. All I can think of was something was wrong with the structure/indents. With that said, your comment helped me learn a few important things that I missed during my debug process. Thank you for that! – maestro416 Feb 29 '20 at 17:54

db.session.execute(sql, params) does not return object

1 Answers1