%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.
If you are concerned about query performance, please use the autolimit
config.
Setup#
%load_ext sql
%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);
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: 10
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: '/home/docs/.jupysql/connections.ini'
SqlMagic.feedback=<Int>
Verbosity level. 0=minimal, 1=normal, 2=all
Current: 1
SqlMagic.named_parameters=<Bool>
Allow named parameters in queries (i.e., 'SELECT * FROM foo WHERE bar =
:bar')
Current: False
SqlMagic.polars_dataframe_kwargs=<key-1>=<value-1>...
Polars DataFrame constructor keyword arguments(e.g. infer_schema_length,
nan_to_null, schema_overrides, etc)
Current: {}
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, SINGLE_BORDER,
DOUBLE_BORDER, MARKDOWN )
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 = 0
autocommit
#
Default: True
Commits each executed query to the database automatically.
Set to False
to disable this behavior.
This may be needed when commits are not supported by the database
(for example in sqlalchemy 1.x does not support commits)
%config SqlMagic.autocommit = False
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
autopandas
#
Default: False
Return Pandas DataFrames instead of regular result sets.
%config SqlMagic.autopandas = True
df = %sql SELECT * FROM languages
type(df)
pandas.core.frame.DataFrame
%config SqlMagic.autopandas = False
res = %sql SELECT * FROM languages
type(res)
sql.run.resultset.ResultSet
autopolars
#
Default: False
Return Polars DataFrames instead of regular result sets.
%config SqlMagic.autopolars = True
df = %sql SELECT * FROM languages
type(df)
polars.dataframe.frame.DataFrame
%config SqlMagic.autopolars = False
res = %sql SELECT * FROM languages
type(res)
sql.run.resultset.ResultSet
column_local_vars
#
Default: False
Returns data into local variable corresponding to column name.
To enable this behavior, set to True
.
%config SqlMagic.column_local_vars = True
%sql SELECT * FROM languages
You can now access columns returned through variables with the same name.
print(f"Name: {name}")
print(f"Rating: {rating}")
print(f"Change: {change}")
Name: ('Python', 'C', 'Java', 'C++')
Rating: (14.44, 13.13, 11.59, 10.0)
Change: (2.48, 1.5, 0.4, 1.98)
Note that column_local_vars
cannot be used when either of
autopandas
or autopolars
is enabled, and vice-versa.
%config SqlMagic.column_local_vars = False
displaycon
#
Default: True
Show connection string after execution.
%config SqlMagic.displaycon = False
%sql SELECT * FROM languages LIMIT 2
name | rating | change |
---|---|---|
Python | 14.44 | 2.48 |
C | 13.13 | 1.5 |
%config SqlMagic.displaycon = True
%sql SELECT * FROM languages LIMIT 2
name | rating | change |
---|---|---|
Python | 14.44 | 2.48 |
C | 13.13 | 1.5 |
displaylimit
#
Default: 10
Automatically limit the number of rows displayed (full result set is still stored).
(To display all rows: set to 0
or None
)
%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
dsn_filename
#
Changed in version 0.10.0: dsn_filename
default changed from odbc.ini
to ~/.jupysql/connections.ini
.
Default: ~/.jupysql/connections.ini
File to load connections configuration from. For an example, see: Using a connection file
feedback
#
Changed in version 0.10: feedback
takes values 0
, 1
, and 2
instead of True
/False
Default: 1
Control the quantity of messages displayed when performing certain operations. Each value enables the ones from previous values plus new ones:
0
: Minimal feedback1
: Normal feedback (default)Connection name when switching
Connection name when running a query
Number of rows afffected by DML (e.g.,
INSERT
,UPDATE
,DELETE
)
2
: All feedbackFooter to distinguish pandas/polars data frames from JupySQL’s result sets
named_parameters
#
New in version 0.9.
Default: False
If True, it enables named parameters :variable
. Learn more in the tutorial.
%config SqlMagic.named_parameters=True
rating = 12
%%sql
SELECT *
FROM languages
WHERE rating > :rating
name | rating | change |
---|---|---|
Python | 14.44 | 2.48 |
polars_dataframe_kwargs
#
Default: {}
Polars DataFrame constructor keyword arguments (e.g. infer_schema_length, nan_to_null, schema_overrides, etc)
# By default Polars will only look at the first 100 rows to infer schema
# Disable this limit by setting infer_schema_length to None
%config SqlMagic.polars_dataframe_kwargs = { "infer_schema_length": None}
# Create a table with 101 rows, last row has a string which will cause the
# column type to be inferred as a string (rather than crashing polars)
%sql CREATE TABLE points (x, y);
insert_stmt = ""
for _ in range(100):
insert_stmt += "INSERT INTO points VALUES (1, 2);"
%sql {{insert_stmt}}
%sql INSERT INTO points VALUES (1, "foo");
%sql SELECT * FROM points
x | y |
---|---|
1 | 2 |
To unset:
%config SqlMagic.polars_dataframe_kwargs = {}
short_errors
#
DEFAULT: True
Set the error description size.
If False
, displays entire traceback.
%config SqlMagic.short_errors = False
style
#
DEFAULT: DEFAULT
Set the table printing style to any of prettytable’s defined styles
%config SqlMagic.style = "MSWORD_FRIENDLY"
res = %sql SELECT * FROM languages LIMIT 2
print(res)
| name | rating | change |
| Python | 14.44 | 2.48 |
Truncated to displaylimit of 1.
%config SqlMagic.style = "SINGLE_BORDER"
res = %sql SELECT * FROM languages LIMIT 2
print(res)
┌────────┬────────┬────────┐
│ name │ rating │ change │
├────────┼────────┼────────┤
│ Python │ 14.44 │ 2.48 │
└────────┴────────┴────────┘
Truncated to displaylimit of 1.
Loading from pyproject.toml
#
New in version 0.9.
You can define configurations in a pyproject.toml
file and automatically load the configurations when you run %load_ext sql
. If the file is not found in the current or parent directories, default values will be used. A sample pyproject.toml
could look like this:
[tool.jupysql.SqlMagic]
feedback = true
autopandas = true
Note that pyproject.toml
is only for setting configurations. To store connection details, please use connections.ini
file.