DOCSEARCHbyAlgolia
Submit Docs

JupySQL Documentation

repository·master·Indexed 21 days ago

https://github.com/ploomber/jupysql

A SQL client for Jupyter/IPython and a fork of ipython-sql that enables running SQL queries via %sql and %%sql magic commands. It features Pandas and Polars integration, native DuckDB support, SQL composition, and specialized magic commands for plotting (%sqlplot), connection management (%sqlcmd connect), table profiling (%sqlcmd profile), and query snippet management.

Tokens
52.2K
Snippets
287
Records
304
Agent score
72%

What's inside JupySQL

  1. Overview of JupySQL features

    master

    JupySQL is a full-featured SQL client for Jupyter designed to improve the SQL workflow in notebooks. Key capabilities include:

    • Pandas integration: Seamlessly work with Pandas DataFrames.
    • SQL composition: Simplify complex queries and avoid hard-to-debug Common Table Expressions (CTEs).
    • Efficient plotting: Plot massive datasets without exhausting system memory.
    • DuckDB integration: Native support for DuckDB.

  2. Explore new features in JupySQL

    master

    JupySQL introduces several features not present in the original ipython-sql:

    • Plotting: A dedicated module for efficiently plotting massive datasets without memory exhaustion.
    • Query Composition: Ability to break queries into multiple cells using Common Table Expressions (CTEs).
    • Database Exploration: Use %sqlcmd tables to list tables and %sqlcmd columns --table/-t <table_name> to explore columns within a specific table.
    • Polars Integration: Easily convert query results to polars.DataFrame. You can automate this by setting %config SqlMagic.autopolars to return Polars DataFrames by default instead of standard result sets.
    # Automatically return Polars DataFrames
%config SqlMagic.autopolars = True

# Explore database schema
%sqlcmd tables
%sqlcmd columns --table my_table

  3. Using raw_execute vs execute for queries

    master

    JupySQL connections (both SQLAlchemyConnection and DBAPIConnection) provide two primary methods for running SQL. Choosing the right one is critical for security and correctness:

    1. raw_execute(query, with_=None):

      • Use for: User-submitted queries.
      • Does not perform SQL transpilation.
      • Supports the with_ parameter to allow the use of saved snippets (CTEs).

    2. execute(query, with_=None):

      • Use for: Internal queries only (queries defined within the JupySQL codebase).
      • Performs transpilation to ensure the query is compatible with the target database dialect.
      • Warning: Do not use this for strings received directly from a user, as transpilation is not perfect and can fail.
    # For user input: ALWAYS use raw_execute
conn.raw_execute("SELECT * FROM user_table")

# For internal library queries: use execute
conn.execute("SELECT internal_logic_query")

# To support snippets/CTEs:
conn.raw_execute("SELECT * FROM my_snippet", with_=["my_snippet"])

  4. Transpile SQL across dialects using SQLGlot

    master

    To maintain a single codebase for multiple database dialects, use sqlglot to build generic SQL constructs and then use SQLAlchemyConnection._transpile_query to convert them to the current connection's dialect.

    Approach 1: General SQL Clause

    Use sqlglot to build an expression, then pass the resulting SQL string to _transpile_query.

    Approach 2: Dialect-Specific Source

    If you have a complex query written in a specific dialect (e.g., DuckDB), use sqlglot.parse_one(query, read='dialect_name').sql() to generate a standard SQL string, which can then be transpiled to other dialects via _transpile_query.

    from sqlglot import select, condition
from sql.connection import SQLAlchemyConnection
from sqlalchemy import create_engine

# 1. Setup connection
conn = SQLAlchemyConnection(engine=create_engine(url="sqlite://"))

# 2. Create generic SQL using sqlglot
where = condition("x=1").and_("y=1")
general_sql = select("*").from_("y").where(where).sql()

# 3. Transpile to the connection's dialect
transpiled = conn._transpile_query(general_sql)

  5. Parameterize SQL arguments using Jinja templates

    master

    JupySQL supports variable expansion in arguments using Jinja-style syntax: {{variable}}. This allows you to define Python variables and dynamically inject them into SQL magic commands, such as --save flags, --table arguments, or --column arguments. This pattern is useful for creating reusable snippets and parameterized workflows.

    # Define a Python variable
table_name = "my_table"

# Use it in a JupySQL command via {{variable}}
%%sql --save {{table_name}}
SELECT * FROM data.csv

  6. How the JupySQL ggplot API works

    master

    The ggplot API follows the grammar of graphics principles but is optimized for SQL. Instead of passing a local DataFrame, you provide a SQL table name via the table parameter. This allows JupySQL to plot larger-than-memory datasets by querying the database directly.

    Core Components:

    • ggplot(table=..., mapping=...): Initializes the plot with a SQL table reference and aesthetic mappings.
    • aes(): Defines mappings between SQL columns and visual properties (e.g., x, color, fill).
    • Geoms: Geometric objects that define the type of plot (e.g., geom_boxplot, geom_histogram).
    • Facets: Layout tools to split plots into multiple panels (e.g., facet_wrap).

    Basic Template:

    (ggplot(table='sql_table_name', mapping=aes(x='column')) + geom_func() + facet_func())
    (ggplot(table='sql_table_name', mapping=aes(x='table_column_name'))
    +
    geom_func() # geom_histogram or geom_boxplot (required)
    +
    facet_func() # facet_wrap (optional)
)

  7. Understand the difference between Line Magics and Cell Magics

    master

    JupySQL uses IPython Magics to execute SQL commands. There are two types:

    1. Line Magics: Denoted by a single % prefix. They operate on a single line of input. Example: %sql SELECT * FROM table.
    2. Cell Magics: Denoted by a double %% prefix. They operate on the entire content of a code cell (multiple lines). Example: %%sql SELECT * FROM table.

    To use JupySQL, you first load the extension using %load_ext sql.

    # Load the extension
%load_ext sql 

# Example of a Line Magic
%sql SELECT * FROM my_table

# Example of a Cell Magic
%%sql
SELECT *
FROM my_table
WHERE id = 1

  8. Manage query result limits with autolimit and displaylimit

    master

    To prevent memory issues or browser hangs with large datasets, use these two distinct limiting mechanisms:

    1. autolimit: Limits the actual size of the result set by appending a LIMIT clause to your SQL query. This is recommended for performance.
    2. displaylimit: Truncates the visual display in the notebook, but the entire result set is still fetched into memory for analysis.

    Note: If autopandas is enabled, displaylimit is ignored; use pandas' max_rows instead.

    # Limit actual data fetched
%config SqlMagic.autolimit = 1

# Limit only visual display (full data still in memory)
%config SqlMagic.displaylimit = 1

  9. Understand JupySQL argument parsing and SQL comments

    master

    JupySQL supports ---delimited options (e.g., --persist). Because -- is also the standard syntax for SQL comments, the parser follows these rules:

    • Unsupported arguments: If you pass an argument that JupySQL does not recognize (e.g., %sql --lutefisk), it is treated as a standard SQL comment and will not raise an error.
    • First-line comments: If a SQL statement starts with a comment that looks like a JupySQL argument (e.g., %sql --persist is great!), the parser will attempt to interpret it as a JupySQL argument. To avoid this, move the comment to the second line of the cell.

  10. Efficiently plot large datasets with %sqlplot

    master

    JupySQL's plotting module allows you to visualize massive datasets by performing computations (aggregation and summarization) directly within the SQL engine (e.g., Snowflake, BigQuery, DuckDB) rather than loading all data into local memory via pandas.

    This is useful for:

    1. Remote Tables: Aggregating data in a warehouse so only summary statistics are fetched over the network.
    2. Local Files: Using an embedded engine like DuckDB to process larger-than-memory .csv or .parquet files.

    Prerequisites:

    • matplotlib must be installed: pip install matplotlib.
    • For DuckDB examples, duckdb-engine is required: pip install duckdb-engine.
    • The %sqlplot magic was introduced in version 0.5.2.
    # Setup requirements
# pip install matplotlib duckdb-engine

%load_ext sql
%sql duckdb://

  11. Use CTEs and save query snippets in JupySQL

    master

    JupySQL allows you to break complex queries into multiple cells by saving snippets as Common Table Expressions (CTEs).

    1. Define a snippet (use --save and --no-execute to prevent running the snippet immediately):
    %%sql --save many_passengers --no-execute
SELECT *
FROM taxi
WHERE passenger_count > 3
AND trip_distance < 18.93
    1. Reference the snippet in a subsequent cell using --with:
    %%sql --save trip_stats --with many_passengers
SELECT MIN(trip_distance), AVG(trip_distance), MAX(trip_distance)
FROM many_passengers
    1. Inspect the generated query:
    query = %sqlcmd snippets trip_stats
print(query)
    %%sql --save many_passengers --no-execute
SELECT *
FROM taxi
WHERE passenger_count > 3
AND trip_distance < 18.93

%%sql --save trip_stats --with many_passengers
SELECT MIN(trip_distance), AVG(trip_distance), MAX(trip_distance)
FROM many_passengers

  12. Use variable expansion in snippets

    master

    JupySQL supports dynamic variable expansion in the form of {{variable}}. This allows you to use Python variables to define snippet names or other arguments dynamically.

    snippet_name = "gentoo"

# Saving a snippet using a variable for the name
%%sql --save {{snippet_name}}
SELECT * FROM penguins.csv where species == 'Gentoo'

# Retrieving a snippet using a variable
%sqlcmd snippets {{snippet_name}}

# Deleting a snippet using a variable
%sqlcmd snippets -d {{snippet_name}}