%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://
Connecting to '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);
Running query in 'sqlite://'
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: 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.lazy_execution=<Bool>
    Whether to evaluate using ResultSet which will cause the plan to execute or
    just return a lazily executed plan allowing validating schemas, without
    expensive compute.Currently only supported for Spark Connection.
    Current: False
SqlMagic.named_parameters=<Parameters>
    Allow named parameters in queries (i.e., 'SELECT * FROM foo WHERE bar =
    :bar')
    Current: 'warn'
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
displaylimit: Value None will be treated as 0 (no limit)
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
Truncated to displaylimit of 1.

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 feedback

  • 1: 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 feedback

    • Footer to distinguish pandas/polars data frames from JupySQL’s result sets

lazy_execution#

New in version 0.10.7: This option only works when connecting to Spark

Default: False

Return lazy relation to dataset rather than executing through JupySql.

%config SqlMagic.lazy_execution = True
df = %sql SELECT * FROM languages
%config SqlMagic.lazy_execution = False
res = %sql SELECT * FROM languages

named_parameters#

Changed in version 0.10.9.

Default: warn

If warn, a warning will be raised when named parameters are included in the statement. If enabled, the statement will be executed with named parameters enabled. If disabled, the statement will be executed with named parameters disabled.

Important

The disabled feature makes use of SQLAlchemy’s exec_driver_sql() instead of execute() to execute SQL statements without the use of bound parameters. This operation doesn’t include other SQL compilation steps which could affect the behavior of your program. If you encounter problems, please open an issue on Slack or Github.

Learn more in the tutorial.

Named parameters can be declared with :variable.

%config SqlMagic.named_parameters="enabled"
rating = 12
%%sql
SELECT *
FROM languages
WHERE rating > :rating
name rating change
Python 14.44 2.48
Truncated to displaylimit of 1.

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
Truncated to displaylimit of 1.

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 a file#

New in version 0.9.

Changed in version 0.10.3: Look for ~/.jupysql/config if pyproject.toml doesn’t exist.

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, jupysql then looks for configurations in ~/.jupysql/config. If no configuration file is found, default values will be used. A sample configuration file could look like this:

[tool.jupysql.SqlMagic]
feedback = true
autopandas = true

Note that these files are only for setting configurations. To store connection details, please use connections.ini file.