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.

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

5
    # data=cur.fetchall()
6

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

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