Retrieve query results as dict in SQLAlchemy

Question

I am using flask SQLAlchemy and I have the following code to get users from database with raw SQL query from a MySQL database:

connection = engine.raw_connection()
cursor = connection.cursor()
cursor.execute("SELECT * from User where id=0")
results = cursor.fetchall()

results variable is a tuple and I want it to be of type dict(). Is there a way to achieve this?

when I was using pymysql to build the db connection I was able to do

cursor = connection.cursor(pymysql.cursors.DictCursor)

Is there something similar in SQLAlchemy?

Note: The reason I want to do this change is to get rid of using pymysql in my code, and only use SQLAlcehmy features, i.e. I do not want to have ´´´import pymysql´´´ in my code anywhere.

Does this answer your question? [Return SQLAlchemy results as dicts instead of lists](https://stackoverflow.com/questions/31624530/return-sqlalchemy-results-as-dicts-instead-of-lists) — Gord Thompson, Nov 01 '19 at 11:54
No I have tried to apply this solution it does not work as I mentioned in my question I am using engine.raw_connection which returns tuples not named tuples same applies to your comment IIja I got this error message AttributeError: 'tuple' object has no attribute '_asdict' — M.Alsioufi, Nov 01 '19 at 12:44
I think the old title of my question was better describing my need — M.Alsioufi, Nov 01 '19 at 12:53
@malsioufi Right, entirely missed the fact that you're using raw connections, which invalidates my previous comment. I also agree that the old title was more fitting. But a good question then is: why are you using raw connections? — Ilja Everilä, Nov 02 '19 at 13:50
What I am doing is updating my application from using pymysql to use SQLAlchemy. In many places in the code ```cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute(some_statment) ``` is used so I think if there is a way to keep this behavior as it is for now and just update the connection object (conn) from being a pymysql object to SQLAlchemy one. In other words, I want to keep this part ```python cursor = connection.cursor() cursor.execute("SELECT * from User where id=0") results = cursor.fetchall()``` — M.Alsioufi, Nov 04 '19 at 08:56
out of curiosity - any share-worthy reason you prefer not to use pymysql? — Zubo, May 03 '21 at 21:23

Gord Thompson · Accepted Answer · 2021-04-05T13:16:18.330

results is a tuple and I want it to be of type dict()

Updated answer for SQLAlchemy 1.4:

Version 1.4 has deprecated the old engine.execute() pattern and changed the way .execute() operates internally. .execute() now returns a CursorResult object with a .mappings() method:

import sqlalchemy as sa

# …

with engine.begin() as conn:
    qry = sa.text("SELECT FirstName, LastName FROM clients WHERE ID < 3")
    resultset = conn.execute(qry)
    results_as_dict = resultset.mappings().all()
    pprint(results_as_dict)
    """
    [{'FirstName': 'Gord', 'LastName': 'Thompson'}, 
     {'FirstName': 'Bob', 'LastName': 'Loblaw'}]
    """

(Previous answer for SQLAlchemy 1.3)

SQLAlchemy already does this for you if you use engine.execute instead of raw_connection(). With engine.execute, fetchone will return a SQLAlchemy Row object and fetchall will return a list of Row objects. Row objects can be accessed by key, just like a dict:

sql = "SELECT FirstName, LastName FROM clients WHERE ID = 1"
result = engine.execute(sql).fetchone()
print(type(result))  # <class 'sqlalchemy.engine.result.Row'>
print(result['FirstName'])  # Gord

If you need a true dict object then you can just convert it:

my_dict = dict(result)
print(my_dict)  # {'FirstName': 'Gord', 'LastName': 'Thompson'}

What I am actually doing is updating my application from using pymysql to use SQLAlchemy. and in many places in the code ```python cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute(some_statment) ``` is used so I thought if there is a way to keep this behavior as it is for now and just update the connection object (conn) from being a pymysql object to SQLAlchemy one. That's why I did not want to solve the issue with engine.execute(), because this requires me to do a lot of changes in my code which I do not want to do right now — M.Alsioufi, Nov 04 '19 at 08:50
This works just for the top-level select fields, but not for aggregators, e.g. `func.array_agg` - how can you make it work for those too? — user2340939, Feb 23 '23 at 14:04

score 2 · Answer 2 · answered Jun 24 '20 at 10:18

you can use sqlalchemy cursor and cursor's description.

def rows_as_dicts(cursor):
    """convert tuple result to dict with cursor"""
    col_names = [i[0] for i in cursor.description]
    return [dict(zip(col_names, row)) for row in cursor]


db = SQLAlchemy(app)
# get cursor
cursor = db.session.execute(sql).cursor
# tuple result to dict
result = rows_as_dicts(cursor)

score 1 · Answer 3 · answered Nov 04 '19 at 14:40

1

If raw_connection() is returning a PyMySQL Connection object then you can continue to use DictCursor like so:

engine = create_engine("mysql+pymysql://root:whatever@localhost:3307/mydb")
connection = engine.raw_connection()
cursor = connection.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT 1 AS foo, 'two' AS bar")
result = cursor.fetchall()
print(result)  # [{'foo': 1, 'bar': 'two'}]

answered Nov 04 '19 at 14:40

Gord Thompson

116,920
32
215
418

I think there is something missing in my question( I will add it now) that I want to get rid of using pymysql anywhere in the code since SQLAlchemy is using it under the hood. But it seems to be impossible to do so right now. – M.Alsioufi Nov 05 '19 at 07:26

score -1 · Answer 4 · answered Nov 01 '19 at 16:07

-1

I would personally use pandas:

import pandas as pd
connection = engine.raw_connection()
df = pd.read_sql_query('SELECT * from User where id=0' , connection)
mydict = df.to_dict()

answered Nov 01 '19 at 16:07

s5s

11,159
21
74
121

What I am actually doing is updating my application from using pymysql to use SQLAlchemy. and in many places in the code ```python cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute(some_statment) ``` is used so I thought if there is a way to keep this behavior as it is for now and just update the connection object (conn) from being a pymysql object to SQLAlchemy one. That's why I did not want to solve the issue with engine.execute(), because this requires me to do a lot of changes in my code which I do not want to do right now – M.Alsioufi Nov 04 '19 at 08:54

Retrieve query results as dict in SQLAlchemy

4 Answers4

Linked

Related