Retrieve query results as dict in SQLAlchemy

by

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'}

More Related Contents:

  1. How to build a flask application around an already existing database?
  2. How to execute raw SQL in Flask-SQLAlchemy app
  3. jsonify a SQLAlchemy result set in Flask [duplicate]
  4. Flask SQLAlchemy query, specify column names
  5. SQLAlchemy ORM conversion to pandas DataFrame
  6. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  7. How to disable SQLAlchemy caching?
  8. memory-efficient built-in SqlAlchemy iterator/generator?
  9. Writing to MySQL database with pandas using SQLAlchemy, to_sql
  10. AttributeError: can’t set attribute when connecting to sqlite database with flask-sqlalchemy
  11. Case Insensitive Flask-SQLAlchemy Query
  12. How to update SQLAlchemy row entry?
  13. SQLAlchemy ManyToMany secondary table with additional fields
  14. Bulk insert with SQLAlchemy ORM
  15. Can SQLAlchemy be used with Google Cloud SQL?
  16. Updates to JSON field don’t persist to DB
  17. ImportError: No module named MySQLdb
  18. Flask sqlalchemy many-to-many insert data
  19. How to delete a record by id in Flask-SQLAlchemy
  20. Get inserted key before commit session
  21. Querying with function on Flask-SQLAlchemy model gives BaseQuery object is not callable error
  22. AttributeError: ‘int’ object has no attribute ‘_sa_instance_state’
  23. SQLAlchemy – performing a bulk upsert (if exists, update, else insert) in postgresql
  24. flake8 complains on boolean comparison “==” in filter clause
  25. flask-sqlalchemy or sqlalchemy
  26. Flask-SQLAlchemy Constructor
  27. Reverse engineer SQLAlchemy declarative class definition from existing MySQL database?
  28. Using SQLAlchemy models in and out of Flask
  29. SQLAlchemy ordering by count on a many to many relationship
  30. flask sqlalchemy query with keyword as variable

Leave a Comment