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);
Connecting to 'sqlite://'
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
Running query in 'sqlite://'
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
Running query in 'sqlite://'
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)
Running query in 'sqlite://'
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
Running query in 'sqlite://'
Success! Persisted df to the database.
%sql SELECT * FROM df;
Running query in 'sqlite://'
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
Running query in 'sqlite://'
first_name last_name year_of_death
0 William Shakespeare 1616

Use --persist-replace#

%sql --persist-replace df
Running query in 'sqlite://'
Success! Persisted df to the database.

df table is overridden#

%sql SELECT * FROM df;
Running query in 'sqlite://'
index first_name last_name year_of_death
0 0 William Shakespeare 1616

--persist in schema#

A schema can also be specified when persisting a dataframe.

%%sql duckdb://
CREATE SCHEMA IF NOT EXISTS schema1;
CREATE TABLE numbers (num INTEGER);
INSERT INTO numbers VALUES (1);
INSERT INTO numbers VALUES (2);
Connecting and switching to connection 'duckdb://'
Success 
results = %sql SELECT * FROM numbers;
Running query in 'duckdb://'
%sql --persist schema1.results
Running query in 'duckdb://'
Success! Persisted results to the database.