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 |