%sql Configuration#

Query results are loaded as lists, so very large result sets may use up your system’s memory and/or hang your browser. There is no autolimit by default. However, autolimit (if set) limits the size of the result set (usually with a LIMIT clause in the SQL). displaylimit is similar, but the entire result set is still pulled into memory (for later analysis); only the screen display is truncated.

Setup#

%load_ext sql

There's a new jupysql version available (0.6.6), you're running 0.7.0.dev0. To upgrade: pip install jupysql --upgrade

%sql sqlite://

%%sql
CREATE TABLE languages (name, rating, change);
INSERT INTO languages VALUES ('Python', 14.44, 2.48);
INSERT INTO languages VALUES ('C', 13.13, 1.50);
INSERT INTO languages VALUES ('Java', 11.59, 0.40);
INSERT INTO languages VALUES ('C++', 10.00, 1.98);

*  sqlite://
Done.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.

[]

Options#

%config SqlMagic

SqlMagic(Magics, Configurable) options
------------------------------------
SqlMagic.autocommit=<Bool>
    Set autocommit mode
    Current: True
SqlMagic.autolimit=<Int>
    Automatically limit the size of the returned result sets
    Current: 0
SqlMagic.autopandas=<Bool>
    Return Pandas DataFrames instead of regular result sets
    Current: False
SqlMagic.autopolars=<Bool>
    Return Polars DataFrames instead of regular result sets
    Current: False
SqlMagic.column_local_vars=<Bool>
    Return data into local variables from column names
    Current: False
SqlMagic.displaycon=<Bool>
    Show connection string after execution
    Current: True
SqlMagic.displaylimit=<Int>
    Automatically limit the number of rows displayed (full result set is still
    stored)
    Current: None
SqlMagic.dsn_filename=<Unicode>
    Path to DSN file. When the first argument is of the form [section], a
    sqlalchemy connection string is formed from the matching section in the DSN
    file.
    Current: 'odbc.ini'
SqlMagic.feedback=<Bool>
    Print number of rows affected by DML
    Current: True
SqlMagic.short_errors=<Bool>
    Don't display the full traceback on SQL Programming Error
    Current: True
SqlMagic.style=<Unicode>
    Set the table printing style to any of prettytable's defined styles
    (currently DEFAULT, MSWORD_FRIENDLY, PLAIN_COLUMNS, RANDOM)
    Current: 'DEFAULT'

Note

If you have autopandas set to true, the displaylimit option will not apply. You can set the pandas display limit by using the pandas max_rows option as described in the pandas documentation.

Changing configuration#

%config SqlMagic.feedback = False

displaycon#

Default: True

Show connection string after execution.

%config SqlMagic.displaycon = True
%sql SELECT * FROM languages LIMIT 2

*  sqlite://
name rating change
Python 14.44 2.48
C 13.13 1.5 
%config SqlMagic.displaycon = False
%sql SELECT * FROM languages LIMIT 2
name rating change
Python 14.44 2.48
C 13.13 1.5

autolimit#

Default: 0 (no limit)

Automatically limit the size of the returned result sets (e.g., add a LIMIT at the end of the query).

%config SqlMagic.autolimit = 0
%sql SELECT * FROM languages
name rating change
Python 14.44 2.48
C 13.13 1.5
Java 11.59 0.4
C++ 10.0 1.98 
%config SqlMagic.autolimit = 1
%sql SELECT * FROM languages
name rating change
Python 14.44 2.48 
%config SqlMagic.autolimit = 0

displaylimit#

Default: None (no limit)

Automatically limit the number of rows displayed (full result set is still stored).

%config SqlMagic.displaylimit = None
%sql SELECT * FROM languages
name rating change
Python 14.44 2.48
C 13.13 1.5
Java 11.59 0.4
C++ 10.0 1.98 
%config SqlMagic.displaylimit = 1
res = %sql SELECT * FROM languages
res
name rating change
Python 14.44 2.48
4 rows, truncated to displaylimit of 1

One displayed, but all results fetched:

len(res)

4

autopandas#

Default: False

Return Pandas DataFrames instead of regular result sets.

%config SqlMagic.autopandas = False
res = %sql SELECT * FROM languages
type(res)

sql.run.ResultSet

%config SqlMagic.autopandas = True
df = %sql SELECT * FROM languages
type(df)

pandas.core.frame.DataFrame

autopolars#

Default: False

Return Polars DataFrames instead of regular result sets.

%config SqlMagic.autopolars = False
res = %sql SELECT * FROM languages
type(res)

pandas.core.frame.DataFrame

%config SqlMagic.autopolars = True
df = %sql SELECT * FROM languages
type(df)

Disabled 'autopandas' since 'autopolars' was enabled.

polars.dataframe.frame.DataFrame

feedback#

Default: True

Print number of rows affected by DML.

%config SqlMagic.feedback = True

%%sql
CREATE TABLE points (x, y);
INSERT INTO points VALUES (0, 0);
INSERT INTO points VALUES (1, 1);

Done.
1 rows affected.
1 rows affected.
shape: (0, 0)
%config SqlMagic.feedback = False

%%sql
CREATE TABLE more_points (x, y);
INSERT INTO more_points VALUES (0, 0);
INSERT INTO more_points VALUES (1, 1);
shape: (0, 0)