Pandas integration

If you have installed pandas, you can use a result set’s .DataFrame() method.

Let’s create some sample data:

%load_ext sql

%%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);

Done.
1 rows affected.
1 rows affected.

[]

Convert to pandas.DataFrame

Query the sample data and convert to pandas.DataFrame:

result = %sql SELECT * FROM writer WHERE year_of_death > 1900

*  sqlite://
Done.

df = result.DataFrame()
type(df)

pandas.core.frame.DataFrame

df

	first_name	last_name	year_of_death
0	Bertold	Brecht	1956

Or using the cell magic:

%%sql result <<
SELECT * FROM writer WHERE year_of_death > 1900

*  sqlite://
Done.

result.DataFrame()

	first_name	last_name	year_of_death
0	Bertold	Brecht	1956

Convert automatically

%config SqlMagic.autopandas = True
df = %sql SELECT * FROM writer
type(df)

*  sqlite://
Done.

pandas.core.frame.DataFrame

df

	first_name	last_name	year_of_death
0	William	Shakespeare	1616
1	Bertold	Brecht	1956

Uploading a dataframe to the database

The --persist argument, with the name of a DataFrame object in memory, will create a table name in the database from the named DataFrame. Or use --append to add rows to an existing table by that name.

%sql --persist df

*  sqlite://

'Persisted df'

%sql SELECT * FROM df;

*  sqlite://
Done.

	index	first_name	last_name	year_of_death
0	0	William	Shakespeare	1616
1	1	Bertold	Brecht	1956