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