Export to CSV

Result sets come with a .csv(filename=None) method. This generates comma-separated text either as a return value (if filename is not specified) or in a file of the given name.

%load_ext sql

[JupySQL] Initializing providers...
[JupySQL] JUPYSQL_CNPG_ENABLED=NOT SET
[JupySQL] JUPYSQL_CNPG_NAMESPACE=NOT SET
[CNPG] Provider __init__ called, enabled=False
[JupySQL] CNPG provider registered, enabled=True
[JupySQL] CNPG discovered 0 database(s)

%%sql sqlite://
CREATE TABLE writer (first_name, last_name, year_of_death);
INSERT INTO writer VALUES ('William', 'Shakespeare', 1616);
INSERT INTO writer VALUES ('Bertold', 'Brecht', 1956);

Connecting to 'sqlite://'

1 rows affected.

1 rows affected.

result = %sql SELECT * FROM writer
result.csv(filename="writer.csv")

Running query in 'sqlite://'

CSV results

import pandas as pd

df = pd.read_csv("writer.csv")
df

	first_name	last_name	year_of_death
0	William	Shakespeare	1616
1	Bertold	Brecht	1956