Sql select as json

Many times when querying a database in python I end up re-learning how to get the data to json. This is a good way to remember.

  with conn:
    cur = conn.cursor()
    cur.execute("SELECT substring(gday,1,4) AS year, count(*) AS count FROM games GROUP BY year ORDER BY year DESC;")

    # data=cur.fetchall()

    # instead of  above fetchall I do this...
    columns = [column[0] for column in cur.description]
    data = [dict(zip(columns, row)) for row in cur.fetchall()]

    json_data = json.dumps(data, indent=4)