pythonmysqlsqlalchemyflask-sqlalchemy

Retrieve query results as dict in SQLAlchemy

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.

Solution

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

    SQLAlchemy 1.4 and 2.0:

    engine.execute() 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'}