{"id":6384,"library":"ipython-sql","title":"ipython-sql","description":"ipython-sql provides SQL magic commands (`%sql` and `%%sql`) to seamlessly integrate SQL queries and database interactions directly within IPython and Jupyter Notebook environments. It leverages SQLAlchemy for database connections and supports various RDBMS. The current version is 0.5.0. Development has largely shifted to its successor, JupySQL, making ipython-sql a legacy project primarily in maintenance mode.","status":"maintenance","version":"0.5.0","language":"en","source_language":"en","source_url":"https://github.com/catherinedevlin/ipython-sql","tags":["ipython","jupyter","sql","database","orm","magic","data-science","data-analysis"],"install":[{"cmd":"pip install ipython-sql","lang":"bash","label":"Install ipython-sql"},{"cmd":"pip install ipython-sql [postgresql]","lang":"bash","label":"Install with PostgreSQL support (requires psycopg2)"},{"cmd":"pip install ipython-sql [mysql]","lang":"bash","label":"Install with MySQL support (requires pymysql)"},{"cmd":"pip install ipython-sql [duckdb]","lang":"bash","label":"Install with DuckDB support (requires duckdb-engine)"}],"dependencies":[{"reason":"Used for database connection strings and ORM capabilities.","package":"SQLAlchemy","optional":false},{"reason":"Provides the magic command functionality for Jupyter/IPython notebooks.","package":"IPython","optional":false},{"reason":"Required for connecting to a specific database type (e.g., psycopg2 for PostgreSQL, pymysql for MySQL).","package":"database-specific driver","optional":true},{"reason":"Enables returning query results as DataFrames and persisting DataFrames to the database.","package":"pandas","optional":true}],"imports":[{"note":"This is an IPython magic command, not a standard Python import. It loads the SQL extension into the notebook or IPython session.","symbol":"%load_ext sql","correct":"%load_ext sql"},{"note":"Used for single-line SQL queries or connecting to a database.","symbol":"%sql","correct":"%sql sqlite:///:memory:"},{"note":"Used for multi-line SQL queries.","symbol":"%%sql","correct":"%%sql\nSELECT * FROM my_table;"}],"quickstart":{"code":"# Load the ipython-sql extension\n%load_ext sql\n\n# Connect to an in-memory SQLite database\n%sql sqlite:///:memory:\n\n# Create a table\n%%sql\nCREATE TABLE users (\n    id INTEGER PRIMARY KEY,\n    name VARCHAR(50),\n    email VARCHAR(100)\n);\n\n# Insert data into the table\n%%sql\nINSERT INTO users (name, email) VALUES\n    ('Alice', 'alice@example.com'),\n    ('Bob', 'bob@example.com');\n\n# Select data from the table\nresult = %sql SELECT * FROM users;\nprint(result)\n\n# Demonstrate variable substitution (bind parameters)\nuser_id = 1\nuser_name = 'Charlie'\n\n# Using bind parameters (:variable)\nuser_from_db = %sql SELECT * FROM users WHERE id = :user_id;\nprint(f\"User with ID {user_id}: {user_from_db}\")\n\n# Using direct substitution ($variable) - use with caution for dynamic SQL\n# (Note: %sql --persist is also available for pandas DataFrames)\n","lang":"python","description":"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."},"warnings":[{"fix":"Migrate to JupySQL for active development and new features. `pip install jupysql`.","message":"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.","severity":"breaking","affected_versions":"All versions post-JupySQL emergence"},{"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.","message":"Incompatible with SQLAlchemy 2.x. Users upgrading SQLAlchemy to version 2.0 or newer may encounter errors.","severity":"breaking","affected_versions":"0.5.0 and older with SQLAlchemy 2.x"},{"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]'`.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"Query results are loaded as lists into memory. Very large result sets can consume significant memory, potentially causing the notebook or browser to hang.","severity":"gotcha","affected_versions":"All versions"},{"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.","message":"Older `prettytable` configuration (e.g., `c.SqlMagic.style`) may cause `KeyError: \"DEFAULT\"` due to deprecated options.","severity":"deprecated","affected_versions":"Versions older than 0.5.0, or specific configurations with `prettytable`."},{"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.","message":"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.","severity":"gotcha","affected_versions":"All versions"}],"env_vars":null,"last_verified":"2026-04-15T00:00:00.000Z","next_check":"2026-07-14T00:00:00.000Z"}