%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 |
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.
%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);