%sql Configuration

`%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.

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

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)

`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

`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

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 `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.

%sql Configuration

Contents

%sql Configuration#

Setup#

Options#

Changing configuration#

autocommit#

autolimit#

autopandas#

autopolars#

column_local_vars#

displaycon#

displaylimit#

dsn_filename#

feedback#

named_parameters#

polars_dataframe_kwargs#

short_errors#

style#

Loading from pyproject.toml#

`%sql` Configuration#

`autocommit`#

`autolimit`#

`autopandas`#

`autopolars`#

`column_local_vars`#

`displaycon`#

`displaylimit`#

`dsn_filename`#

`feedback`#

`named_parameters`#

`polars_dataframe_kwargs`#

`short_errors`#

`style`#

Loading from `pyproject.toml`#