Pandas

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

Pandas

Contents