Retrieve query results as dict in SQLAlchemy

Question

I'm using Flask SQLAlchemy and I have the following code to get users from the database via a raw SQL query from the MySQL database:

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

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

When I use pymysql to build the database connection, I am able to do

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

Is there something similar in SQLAlchemy?

NOTE: The reason I want to make this change is to get rid of using pymysql in my code and just use the SQLAlcehmy functionality, i.e. I don't want to have "import pymysql" anywhere in my code.

Answer 1

You can use sqlalchemy cursors and cursor descriptions.

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)

Answer 2

Updated answer for SQLAlchemy 1.4:

Version 1.4 has deprecated the old engine.execute() mode and changed the way .execute() operates internally. .execute() now returns a CursorResult object with the .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)

If you use engine.execute instead of raw_connection(), SQLAlchemy already does this for you. Using engine.execute, fetchone will return a SQLAlchemy Row object, fetchall will return a list Row Object. Row objects can be accessed by key, just like 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 real dict object, then you can just convert it:

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