Pandas#

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

Load sample data#

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#

New in version 0.7.0: We are using SQLAlchemy 2.x to support this feature. If you are using Python 3.7, please upgrade to Python 3.8+. Alternatively, you might use Python 3.7 and downgrade to SQlAlchemy 1.x

--persist#

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

--persist-replace#

The --persist-replace performs the similar functionality with --persist, but it will drop the existing table before inserting the new table

Declare the dataframe again#

df = %sql SELECT * FROM writer LIMIT 1
df
*  sqlite://
Done.
first_name last_name year_of_death
0 William Shakespeare 1616

Use --persist-replace#

%sql --persist-replace df
*  sqlite://
'Persisted df'

df table is overridden#

%sql SELECT * FROM df;
*  sqlite://
Done.
index first_name last_name year_of_death
0 0 William Shakespeare 1616