ipython-sql

Warnings

• breaking ipython-sql is a 'Legacy project' with development shifted to JupySQL. For new projects or advanced features, consider using JupySQL, which is its maintained successor.
  Fix: Migrate to JupySQL for active development and new features. `pip install jupysql`.
• breaking Incompatible with SQLAlchemy 2.x. Users upgrading SQLAlchemy to version 2.0 or newer may encounter errors.
  Fix: Downgrade SQLAlchemy to version 1.4.x (e.g., `pip install sqlalchemy==1.4.46`) or migrate to JupySQL, which aims for SQLAlchemy 2.x compatibility.
• gotcha Database drivers (e.g., psycopg2, pymysql) are NOT installed with `ipython-sql` by default. You must install the appropriate driver separately for your chosen database.
  Fix: Install the necessary database driver: `pip install <driver_name>` (e.g., `pip install psycopg2-binary`, `pip install pymysql`). Some drivers can be installed via `ipython-sql` extras, e.g., `pip install 'ipython-sql[postgresql]'`.
• gotcha Common `ImportError` or `UsageError: Line magic function `%sql` not found` when running `%load_ext sql`. This often indicates that `ipython-sql` was installed in a different Python environment or Jupyter kernel than the one currently active.
  Fix: Ensure `ipython-sql` is installed in the *exact* Python environment/kernel that your Jupyter Notebook or IPython session is using. Use `!pip install ipython-sql` within a notebook cell or verify your `conda` or `venv` setup.
• gotcha Query results are loaded as lists into memory. Very large result sets can consume significant memory, potentially causing the notebook or browser to hang.
  Fix: Use `%config SqlMagic.autolimit = <INT>` to automatically limit results (e.g., `1000`). Note that `displaylimit` only truncates display, not memory usage. If `autopandas` is true, use Pandas `max_rows` instead.
• deprecated Older `prettytable` configuration (e.g., `c.SqlMagic.style`) may cause `KeyError: "DEFAULT"` due to deprecated options.
  Fix: Update `ipython-sql` to the latest version. Review and update notebook configuration files (`ipython_config.py`) to remove deprecated `SqlMagic.style` settings. The default display style is usually sufficient.
• gotcha Variable substitution: Using `:variable_name` creates bind parameters passed to the SQL engine (recommended for data). Using `$variable_name` or `{variable_name}` directly substitutes the value into the SQL string before execution (can be a SQL injection risk for untrusted input). Using `${variable_name}` (both) is not supported.
  Fix: Prefer bind parameters (`:variable_name`) for injecting data into queries to prevent SQL injection. Use direct substitution (`$variable_name` or `{variable_name}`) only for dynamic SQL constructs (e.g., table names) with trusted inputs.

Install

• pip install ipython-sql Install ipython-sql
• pip install ipython-sql [postgresql] Install with PostgreSQL support (requires psycopg2)
• pip install ipython-sql [mysql] Install with MySQL support (requires pymysql)
• pip install ipython-sql [duckdb] Install with DuckDB support (requires duckdb-engine)

Imports

• %load_ext sql

  %load_ext sql

  This is an IPython magic command, not a standard Python import. It loads the SQL extension into the notebook or IPython session.
• %sql

  %sql sqlite:///:memory:

  Used for single-line SQL queries or connecting to a database.
• %%sql

  %%sql
  SELECT * FROM my_table;

  Used for multi-line SQL queries.

Quickstart

This quickstart demonstrates how to load the `ipython-sql` extension, connect to an in-memory SQLite database, create a table, insert data, and perform a basic select query. It also shows how to use bind parameters for secure variable substitution in queries.

# Load the ipython-sql extension
%load_ext sql

# Connect to an in-memory SQLite database
%sql sqlite:///:memory:

# Create a table
%%sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

# Insert data into the table
%%sql
INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');

# Select data from the table
result = %sql SELECT * FROM users;
print(result)

# Demonstrate variable substitution (bind parameters)
user_id = 1
user_name = 'Charlie'

# Using bind parameters (:variable)
user_from_db = %sql SELECT * FROM users WHERE id = :user_id;
print(f"User with ID {user_id}: {user_from_db}")

# Using direct substitution ($variable) - use with caution for dynamic SQL
# (Note: %sql --persist is also available for pandas DataFrames)