Capture output of SQL command with Ipython/Jupyter Notebook

ipython-sql.md

The indirection operator captures the output of any SQL query into a Python variable. In the example below the variable output will contain the result of the query. The query used here is irrelevant and only used for illustrating the usage.

%%sql output << 
SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull, i.indisprimary
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'users'::regclass

Source

The variable output has the type sql.run.ResultSet and has builtin methods to convert it into a Pandas Dataframe or dump it as a CSV