### Setup Widgets for Complete Interactive Example

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/interactive.md

Initializes various ipywidgets, including `IntSlider`, `RadioButtons`, and `SelectMultiple`, to be used in a comprehensive interactive SQL query.

```python
body_mass_lower_bound = 3600
show_limit = (0, 50, 1)
sex_selection = widgets.RadioButtons(
    options=["MALE", "FEMALE"],
    description="Sex",
    disabled=False,
)
species_selections = widgets.SelectMultiple(
    options=["Adelie", "Chinstrap", "Gentoo"],
    value=["Adelie", "Chinstrap"],
    # rows=10,
    description="Species",
    disabled=False,
)
```

--------------------------------

### Install Dependencies for Development

Source: https://github.com/ploomber/jupysql/blob/master/doc/community/developer-guide.md

Set up the development environment by installing all necessary dependencies using pkgmt.

```sh
# create development environment (you can skip this if you already executed it)
pkgmt setup

# activate environment
conda activate jupysql
```

--------------------------------

### Start Microsoft SQL Server Instance with Docker

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mssql.ipynb

Launches a Microsoft SQL Server 2022 instance in a Docker container. Requires Docker to be installed and running. Ensure a strong password is set for the 'sa' user.

```python
%%bash
docker run -e "ACCEPT_EULA=Y" \
    -e "MSSQL_SA_PASSWORD=MyPassword!" \
    -p 1433:1433 \
    -d mcr.microsoft.com/mssql/server:2022-latest
```

--------------------------------

### Install JupySQL, chDB, and pyarrow

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/chdb.md

Install the necessary libraries for JupySQL and chDB integration. This command installs them quietly.

```python
%pip install jupysql chdb pyarrow --quiet
```

--------------------------------

### Install JupySQL, DuckDB, and Rich

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/json.md

Installs the necessary libraries for querying JSON with SQL. This is a prerequisite for the tutorial.

```ipython3
%pip install jupysql duckdb duckdb-engine rich --quiet
```

--------------------------------

### Install Required Packages

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mindsdb.ipynb

Installs PyMySQL, jupysql, and sklearn-evaluation. A kernel restart might be needed after installation.

```python
# Install required packages
%pip install --quiet PyMySQL jupysql sklearn-evaluation --upgrade
```

--------------------------------

### Install Dependencies

Source: https://github.com/ploomber/jupysql/blob/master/doc/tutorials/excel.md

Installs necessary libraries including jupysql, duckdb, pandas, and openpyxl. Use this command to set up your environment for the tutorial.

```ipython3
%pip install jupysql duckdb duckdb-engine pandas openpyxl --quiet
```

--------------------------------

### Install JupySQL and Dependencies

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/questdb.ipynb

Installs jupysql, pandas, pyarrow, and psycopg2-binary. A kernel restart might be needed after installation.

```python
%pip install jupysql pandas pyarrow --quiet
```

```python
%pip install psycopg2-binary --quiet
```

--------------------------------

### Start SQL Server Edge Instance with Docker

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mssql.ipynb

Launches a Microsoft Azure SQL Edge instance in a Docker container, suitable for development and testing. Requires Docker to be installed and running.

```python
%%bash
docker run -e "ACCEPT_EULA=1" -e "MSSQL_SA_PASSWORD=MyPassword!" \
    -e "MSSQL_PID=Developer" -e "MSSQL_USER=sa" \
    -p 1433:1433 -d --name=sql mcr.microsoft.com/azure-sql-edge
```

--------------------------------

### Install Libraries for Parquet Support

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/duckdb-native.md

Installs JupySQL, DuckDB, and PyArrow, which are required for working with Parquet files.

```ipython3
%pip install jupysql duckdb pyarrow --quiet
%load_ext sql
conn = duckdb.connect()
%sql conn --alias duckdb
```

--------------------------------

### Install JupySQL and Load SQL Extension

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/py-scripts.md

Installs necessary packages and loads the SQL extension. This should be run in a cell before other SQL operations.

```python
# %%
%pip install jupysql duckdb duckdb-engine --quiet
%load_ext sql
%sql duckdb://
```

--------------------------------

### Install and Load SQLite Scanner for DuckDB

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-profile.md

Installs and loads the 'sqlite_scanner' extension for DuckDB, enabling it to interact with SQLite databases. This is a setup step for using SQLite with DuckDB.

```sql
INSTALL 'sqlite_scanner';
LOAD 'sqlite_scanner';
```

--------------------------------

### Start Trino Docker Instance

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/trinodb.ipynb

Launches a Trino server instance using Docker. This command starts a Trino container and maps port 8080.

```bash
docker run -p 8080:8080 --name trino -d trinodb/trino
```

--------------------------------

### Install PostgreSQL Connector

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/postgres-connect.ipynb

Installs the psycopg2-binary package, a recommended PostgreSQL connector for JupySQL. Run this in a Jupyter notebook.

```python
%pip install psycopg2-binary --quiet
```

--------------------------------

### Install JupySQL

Source: https://github.com/ploomber/jupysql/blob/master/doc/jupyterlab/autocompletion.md

Install JupySQL quietly using pip. This is the first step to enable SQL autocompletion.

```bash
pip install jupysql --quiet
```

--------------------------------

### Install JupySQL

Source: https://github.com/ploomber/jupysql/blob/master/doc/tutorials/etl.md

Install the JupySQL package using pip. The --quiet flag suppresses verbose output during installation.

```python
!pip install jupysql --quiet
```

--------------------------------

### Start Spark Instance with Docker

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/spark.ipynb

Launches a Spark instance using the sparglim Docker image. This command starts a container that exposes Spark Connect and the Spark UI ports.

```bash
docker run -p 15002:15002 -p 4040:4040 -d --name spark wh1isper/sparglim-server
```

--------------------------------

### Setup jupysql and Load Data

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/interactive.md

Loads the SQL extension and sets up a DuckDB database connection. It also downloads the penguins dataset if it doesn't exist locally.

```python
%load_ext sql
import ipywidgets as widgets

from pathlib import Path
from urllib.request import urlretrieve

if not Path("penguins.csv").is_file():
    urlretrieve(
        "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv",
        "penguins.csv",
    )
%sql duckdb:///
```

--------------------------------

### Install mysqlclient using Conda

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mysql.ipynb

Installs the mysqlclient package, which is a prerequisite for connecting to MySQL from Python. It's recommended to use conda for installation to ensure all dependencies are met.

```python
%conda install mysqlclient -c conda-forge --quiet
```

--------------------------------

### Start MariaDB Docker Container

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mariadb.ipynb

Launches a MariaDB Docker container for testing purposes. Sets up user, password, and database environment variables.

```bash
docker run --detach --name mariadb \
    --env MARIADB_USER=user \
    --env MARIADB_PASSWORD=password \
    --env MARIADB_ROOT_PASSWORD=password \
    --env MARIADB_DATABASE=db \
    -p 3306:3306 mariadb:latest
```

--------------------------------

### Start Oracle Docker Instance

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/oracle.ipynb

Launches a local Oracle Database instance using the gvenzl/oracle-free Docker image. It configures the instance with specified passwords and ports.

```bash
docker run --name oracle \
  -e ORACLE_PASSWORD=ploomber_app_admin_password \
  -e APP_USER=ploomber_app \
  -e APP_USER_PASSWORD=ploomber_app_password \
  -p 1521:1521 -d gvenzl/oracle-free
```

--------------------------------

### Install pgspecial (version < 2)

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/postgres-install.md

Install the pgspecial library, ensuring to use version 1.x. This version is recommended for informative error messages, as version 2.x uses psycopg3.

```sh
conda install "pgspecial<2" -c conda-forge
```

--------------------------------

### Prepare SQLAlchemy Connection for Transpilation Example

Source: https://github.com/ploomber/jupysql/blob/master/doc/community/developer-guide.md

Initializes a SQLAlchemy connection using SQLite for demonstrating SQL transpilation.

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

conn = SQLAlchemyConnection(engine=create_engine(url="sqlite://"))
```

--------------------------------

### Install jupyterlab-execute-time

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/benchmarking-time.md

Install the jupyterlab-execute-time plugin using pip. This is the first step to enable cell runtime benchmarking.

```sh
pip install jupyterlab_execute_time
```

--------------------------------

### Install JupySQL using pip

Source: https://github.com/ploomber/jupysql/blob/master/README.md

Use this command to install JupySQL via pip. Ensure you have pip installed.

```bash
pip install jupysql
```

--------------------------------

### Install Redshift Connector

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/redshift.ipynb

Installs the 'redshift-connector' package quietly. This is a prerequisite for establishing a native connection to Redshift.

```python
%pip install redshift-connector --quiet
```

--------------------------------

### Install psycopg2 using pip

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/postgres-install.md

Use this command to install the psycopg2 Python library via pip. This is the simplest method for installation.

```sh
pip install psycopg2-binary
```

--------------------------------

### Install SQLAlchemy Trino Connector

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/trinodb.ipynb

Installs the SQLAlchemy connector for Trino. This is necessary for establishing a connection between SQLAlchemy and Trino.

```python
%pip install sqlalchemy-trino --quiet
```

--------------------------------

### Install JupySQL and Dependencies

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/postgres-connect.ipynb

Installs necessary Python packages for JupySQL and data handling. Run this in a Jupyter notebook.

```python
%pip install jupysql pandas pyarrow --quiet
```

--------------------------------

### Install JupySQL and Matplotlib

Source: https://github.com/ploomber/jupysql/blob/master/doc/compose.md

Installs the necessary packages for using JupySQL and data visualization. Run this command in your terminal or a notebook cell.

```bash
pip install jupysql matplotlib
```

--------------------------------

### Start PostgreSQL Docker Container

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/postgres-connect.ipynb

Launches a PostgreSQL instance using Docker, creating a database named 'db' with user 'user' and password 'password'. This command may take 1-2 minutes to complete.

```bash
docker run --name postgres -e POSTGRES_DB=db \
  -e POSTGRES_USER=user \
  -e POSTGRES_PASSWORD=password \
  -p 5432:5432 -d postgres
```

--------------------------------

### Install ipywidgets for %sqlcmd connect

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-connect.md

Install the ipywidgets package to enable the graphical interface for the %sqlcmd connect command. This is a prerequisite for using the widget.

```sh
pip install ipywidgets --upgrade
```

--------------------------------

### Install clickhouse-sqlalchemy

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/clickhouse.ipynb

Install the necessary Python package for ClickHouse integration with SQLAlchemy. This is a prerequisite for connecting.

```python
%pip install clickhouse-sqlalchemy --quiet
```

--------------------------------

### Install oracledb Package

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/oracle.ipynb

Installs the necessary Python package for Oracle database connectivity. This should be run before proceeding.

```python
%pip install oracledb --quiet
```

--------------------------------

### Start Connection from INI File Section

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-sql.md

Establishes a database connection using details from a specified section within the DSN configuration file. This allows for managing multiple connection profiles.

```python
from pathlib import Path

_ = Path("connections.ini").write_text(
    """
[mydb]
drivername = duckdb
"""
)
```

```python
%sql --section mydb
```

--------------------------------

### Install JupySQL and Trino Packages

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/trinodb.ipynb

Installs the required Python packages for JupySQL and Trino integration. Use this command at the beginning of your notebook.

```python
%pip install jupysql trino pandas pyarrow --quiet
```

--------------------------------

### Install JupySQL and PySpark Packages

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/spark.ipynb

Installs necessary Python packages for JupySQL and PySpark integration. Ensure you restart the kernel after installation if prompted.

```python
%pip install jupysql pyspark==3.4.1 arrow pyarrow==12.0.1 pandas grpcio-status --quiet
```

--------------------------------

### Start ClickHouse Docker Container

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/clickhouse.ipynb

Launches a ClickHouse server instance using the official Docker image. Configure database name, user, and password via environment variables. Exposes port 9000 for connections.

```bash
docker run --detach --name clickhouse \
    -e CLICKHOUSE_DB=my_database \
    -e CLICKHOUSE_USER=username \
    -e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
    -e CLICKHOUSE_PASSWORD=password \
    -p 9000:9000/tcp clickhouse/clickhouse-server
```

--------------------------------

### Install PostgreSQL Driver (psycopg2) with Pip

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/db-drivers.md

Install the psycopg2-binary package using pip for PostgreSQL connectivity if conda is not available. Restart the kernel after installation.

```shell
# run this in your notebook
%pip install psycopg2-binary --quiet
```

--------------------------------

### Install JupySQL, DuckDB, and Engine

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/duckdb.md

Installs necessary packages for DuckDB integration with JupySQL. Run this in your Jupyter environment.

```ipython3
%pip install jupysql duckdb duckdb-engine --quiet
%load_ext sql
%sql duckdb://
```

--------------------------------

### Install Matplotlib

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/plot-legacy.md

Installs the matplotlib library, which is required for plotting. Use this command in your notebook environment.

```python
%pip install matplotlib --quiet
```

--------------------------------

### Install Libraries for Large Dataset Plotting

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/duckdb-native.md

Installs JupySQL, DuckDB, and PyArrow, which are necessary for efficient plotting of large datasets using DuckDB.

```ipython3
%pip install jupysql duckdb pyarrow --quiet
```

--------------------------------

### Install JupySQL and DuckDB

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/duckdb-native.md

Installs the necessary libraries for using JupySQL with DuckDB. Use this command in your Jupyter environment.

```ipython3
%pip install jupysql duckdb --quiet
```

--------------------------------

### Install Matplotlib

Source: https://github.com/ploomber/jupysql/blob/master/doc/user-guide/ggplot.md

The ggplot API requires matplotlib for plotting. Install it using pip.

```bash
pip install matplotlib
```

--------------------------------

### Install JupySQL for UDF Registration

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto.md

Installs JupySQL, a prerequisite for registering user-defined functions (UDFs) with SQLite.

```ipython3
%pip install jupysql --quiet
```

--------------------------------

### Start MySQL Docker Container

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mysql.ipynb

Launches a MySQL Docker container with a specified database, user, and password. This is useful for setting up a testing environment quickly. The container is exposed on the default MySQL port (3306).

```bash
docker run --name mysql -e MYSQL_DATABASE=db \
  -e MYSQL_USER=user \
  -e MYSQL_PASSWORD=password \
  -e MYSQL_ROOT_PASSWORD=password \
  -p 3306:3306 -d mysql
```

--------------------------------

### Start QuestDB Docker Instance

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/questdb.ipynb

Runs a QuestDB Docker container in detached mode, exposing necessary ports for database access and management. This command may take 1-2 minutes to complete.

```bash
docker run --detach --name questdb_ \
    -p 9000:9000 -p 9009:9009 -p 8812:8812 -p 9003:9003 questdb/questdb:7.1
```

--------------------------------

### Establish QuestDB Connection

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/questdb.ipynb

Creates a database connection to QuestDB using psycopg2 with specified credentials and host information. Ensure all necessary packages are installed.

```python
import psycopg2 as pg

engine = pg.connect(
    "dbname='qdb' user='admin' host='127.0.0.1' port='8812' password='quest'"
)
```

--------------------------------

### Install JupySQL, DuckDB, and DuckDB Engine

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto.md

Installs the necessary packages for querying CSV files with SQL using JupySQL and DuckDB.

```ipython3
%pip install jupysql duckdb duckdb-engine --quiet
```

--------------------------------

### Create and Populate Table

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/csv.md

Sets up an SQLite database in memory and creates a 'writer' table with sample data. This prepares the database for querying.

```python
%%sql sqlite://
CREATE TABLE writer (first_name, last_name, year_of_death);
INSERT INTO writer VALUES ('William', 'Shakespeare', 1616);
INSERT INTO writer VALUES ('Bertold', 'Brecht', 1956);
```

--------------------------------

### Install pyodbc Package

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mssql.ipynb

Installs the pyodbc library, which is necessary for connecting to SQL Server from Python. Use the specified version if encountering issues on macOS with Apple Silicon.

```sh
pip install pyodbc
```

```sh
pip install pyodbc==4.0.34
```

--------------------------------

### Install ipywidgets

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/interactive.md

Install ipywidgets, which is required for the `--interact` functionality in jupysql. This enables the creation of UI controls for query parameters.

```bash
pip install ipywidgets
```

--------------------------------

### Install JupySQL and Redshift Packages

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/redshift.ipynb

Install the necessary packages for JupySQL, SQLAlchemy, and Redshift. This command also ensures you are using SQLAlchemy version 1.x.

```python
%pip install jupysql sqlalchemy-redshift redshift-connector "sqlalchemy<2" --quiet
```

--------------------------------

### Install JupySQL

Source: https://github.com/ploomber/jupysql/blob/master/doc/user-guide/table_explorer.ipynb

Installs or upgrades the JupySQL package. This command might require a kernel restart to apply changes.

```python
%pip install jupysql --upgrade --quiet
```

--------------------------------

### Create and Populate Table

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/configuration.md

Defines a 'languages' table and inserts sample data using SQL commands within a Jupyter cell. This is a common setup for demonstrating SQL queries.

```python
%%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);
```

--------------------------------

### Install Snowflake SQLAlchemy

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/snowflake.ipynb

Installs the necessary package for Snowflake integration with SQLAlchemy. Ensure SQLAlchemy version is compatible.

```python
%pip install --upgrade snowflake-sqlalchemy 'sqlalchemy<2' --quiet
```

--------------------------------

### Connect to a SQLite Database

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-sql.md

Establishes a connection to a SQLite database file. This is the basic way to start using JupySQL.

```python
%sql sqlite:///db_one.db
```

--------------------------------

### Parameterized Query Example

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/snowflake.ipynb

Demonstrates how to use JupySQL's variable expansion feature to dynamically set query parameters for LIMIT and column selection.

```python
dynamic_limit = 5
dynamic_column = "island, sex"
```

```python
%sql SELECT {{dynamic_column}} FROM penguins LIMIT {{dynamic_limit}}
```

--------------------------------

### Install pandas and pyarrow

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mysql.ipynb

Installs the pandas and pyarrow libraries, which are necessary for data manipulation and reading parquet files, respectively. These are often used together for efficient data handling in Python.

```python
%pip install pandas pyarrow --quiet
```

--------------------------------

### Install Packages for Plotting Large Datasets

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/duckdb.md

Installs JupySQL, DuckDB, DuckDB-Engine, and PyArrow, which are required for efficient plotting of large datasets with DuckDB.

```ipython3
%pip install jupysql duckdb duckdb-engine pyarrow --quiet
```

--------------------------------

### Get Batch Predictions from MindsDB Model

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mindsdb.ipynb

Retrieve multiple predictions from a MindsDB model by joining with a data source. This example fetches customer details and their predicted churn status, limiting the output to 5 rows.

```python
%%sql
SELECT t.customerID, t.Contract, t.MonthlyCharges, m.Churn
FROM files.churn AS t
JOIN mindsdb.customer_churn_predictor AS m
LIMIT 5;
```

--------------------------------

### Install JupySQL, DuckDB, Engine, and PyArrow

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/duckdb.md

Installs necessary packages for DuckDB integration with JupySQL, including PyArrow for Parquet support. Run this in your Jupyter environment.

```ipython3
%pip install jupysql duckdb duckdb-engine pyarrow --quiet
%load_ext sql
%sql duckdb://
```

--------------------------------

### Install psycopg2 using conda

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/postgres-install.md

Install the psycopg2 Python library using conda for a more reliable installation, especially within conda environments. It installs from the conda-forge channel.

```sh
conda install psycopg2 -c conda-forge
```

--------------------------------

### Execute Short SQL Query

Source: https://github.com/ploomber/jupysql/blob/master/doc/quick-start.md

Run a short SQL query using the %sql line magic. This example selects the first 3 rows from the penguins.csv file.

```ipython3
%sql SELECT * FROM penguins.csv LIMIT 3
```

--------------------------------

### Initialize IPython Session for Testing

Source: https://github.com/ploomber/jupysql/blob/master/doc/community/developer-guide.md

Set up an IPython testing session and register JupySQL magics.

```python
from sql._testing import TestingShell
from sql.magic import SqlMagic

ip_session = TestingShell()
ip_session.register_magics(SqlMagic)
```

--------------------------------

### Install JupySQL and DuckDB Engine

Source: https://github.com/ploomber/jupysql/blob/master/doc/quick-start.md

Install JupySQL and the DuckDB engine using pip. This command is intended to be run in a terminal.

```sh
pip install jupysql duckdb-engine
```

--------------------------------

### Setup Table for Parametrized Plotting

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-plot.md

Creates and populates a table named 'penguins' in schema 's1' for use with parametrized SQLPlot commands.

```ipython3
%%sql 
DROP TABLE IF EXISTS penguins;
CREATE SCHEMA IF NOT EXISTS s1;
CREATE TABLE s1.penguins (
    species VARCHAR(255),
    island VARCHAR(255),
    bill_length_mm DECIMAL(5, 2),
    bill_depth_mm DECIMAL(5, 2),
    flipper_length_mm DECIMAL(5, 2),
    body_mass_g INTEGER,
    sex VARCHAR(255)
);
COPY s1.penguins FROM 'penguins.csv' WITH (FORMAT CSV, HEADER TRUE);
```

--------------------------------

### Connect to PostgreSQL Database

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/postgres-connect.ipynb

Establish a connection to your PostgreSQL database using a connection string. Ensure necessary packages are installed.

```python
%sql postgresql://user:password@localhost/db
```

--------------------------------

### Install JupySQL and DuckDB Engine in Jupyter

Source: https://github.com/ploomber/jupysql/blob/master/doc/quick-start.md

Install JupySQL and the DuckDB engine within a Jupyter notebook environment using the %pip magic command. The --quiet flag suppresses output.

```ipython3
%pip install jupysql duckdb-engine --quiet
```

--------------------------------

### Connect with Custom Connection Arguments (SQLite)

Source: https://github.com/ploomber/jupysql/blob/master/doc/connecting.md

Provide custom arguments to the connection URL using `--connection_arguments`. This example sets a timeout for SQLite.

```python
%load_ext sql
```

```python
%sql --connection_arguments '{"timeout":10}' sqlite://
```

--------------------------------

### Install JupySQL using Conda

Source: https://github.com/ploomber/jupysql/blob/master/README.md

Use this command to install JupySQL via Conda Forge. This is an alternative installation method.

```bash
conda install jupysql -c conda-forge
```

--------------------------------

### Check Running Docker Containers

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mssql.ipynb

Lists all currently running Docker containers. Use this to verify if the SQL Server instance has started successfully.

```python
%%bash
docker ps
```

--------------------------------

### Install ODBC Driver for SQL Server on Mac

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mssql.ipynb

Installs the Microsoft ODBC driver for SQL Server on macOS using Homebrew. Ensure you have Homebrew installed first.

```sh
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18
```

--------------------------------

### Install DuckDB Driver

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/db-drivers.md

Install the duckdb-engine package using pip for DuckDB connectivity.

```shell
%pip install duckdb-engine --quiet
```

--------------------------------

### Execute PostgreSQL Meta-Command

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/postgres-connect.ipynb

Use JupySQL's magic command to execute psql meta-commands like \dt. Ensure PGSpecial is installed.

```python
%sql \dt
```

--------------------------------

### Create SQLAlchemy Engine and Connect

Source: https://github.com/ploomber/jupysql/blob/master/doc/connecting.md

Use `create_engine` to establish a connection to a database. Ensure the `sql` extension is loaded.

```python
engine = create_engine(db_url)
```

```python
%load_ext sql
```

```python
%sql engine
```

--------------------------------

### Connect to DuckDB and Load Extension

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/duckdb-native.md

Establishes a connection to DuckDB and loads the JupySQL extension for use in the notebook. This sets up the SQL magic commands.

```ipython3
import duckdb

%load_ext sql
conn = duckdb.connect()
%sql conn --alias duckdb
```

--------------------------------

### Download Sample SQLite Database

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/duckdb-native.md

Downloads a sample SQLite database file named 'my.db' if it does not already exist. This database is used for demonstrating SQLite integration.

```ipython3
import urllib.request
from pathlib import Path

# download sample database
if not Path("my.db").is_file():
    url = "https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite"  # noqa
    urllib.request.urlretrieve(url, "my.db")
```

--------------------------------

### Query Parquet File over HTTP

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/chdb.md

Run a SQL query on a Parquet file hosted via HTTP. This example aggregates data, counts distinct users, and limits results.

```sql
SELECT
    RegionID, SUM(AdvEngineID), COUNT(*) AS c, AVG(ResolutionWidth), COUNT(DISTINCT UserID)
FROM url('https://datasets.clickhouse.com/hits_compatible/athena_partitioned/hits_0.parquet')
-- query on s3 --
--  FROM s3('xxxx')
GROUP BY
    RegionID
ORDER BY c
DESC LIMIT 10
```

--------------------------------

### Create and Populate SQLite Database

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-profile.md

Creates an SQLite database file 'a.db' and a table 'my_numbers' within it, inserting sample data. This prepares a SQLite database for attachment.

```python
import sqlite3

with sqlite3.connect("a.db") as conn:
    conn.execute("CREATE TABLE my_numbers (number FLOAT)")
    conn.execute("INSERT INTO my_numbers VALUES (1)")
    conn.execute("INSERT INTO my_numbers VALUES (2)")
    conn.execute("INSERT INTO my_numbers VALUES (3)")
```

--------------------------------

### Create 'people' Table

Source: https://github.com/ploomber/jupysql/blob/master/doc/user-guide/tables-columns.md

Creates a sample table named 'people' with text column 'name' and integer column 'birth_year'. This is used for demonstrating table and column listing.

```ipython3
%%sql
CREATE TABLE people (name TEXT, birth_year INT)
```

--------------------------------

### Create Widgets for Facet Wrap Example

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/ggplot-interact.md

Initializes an integer slider for bin count, a dropdown for colormap selection, and a toggle button to control the visibility of the legend. These widgets will dynamically adjust the faceted histogram.

```python
b = widgets.IntSlider(
    value=5,
    min=1,
    max=10,
    step=1,
    description="Bin:",
    orientation="horizontal",
)
cmap = widgets.Dropdown(
    options=["viridis", "plasma", "inferno", "magma", "cividis"],
    value="plasma",
    description="Colormaps:",
    disabled=False,
)
show_legend = widgets.ToggleButton(
    value=False,
    description="Show legend",
    disabled=False,
    button_style="",  # 'success', 'info', 'warning', 'danger' or ''
    tooltip="Is show legend",
)
```

--------------------------------

### GitHub Actions CI Workflow

Source: https://github.com/ploomber/jupysql/blob/master/doc/tutorials/etl.md

A sample GitHub Actions workflow file (ci.yml) to automate the execution of a Jupyter notebook. It checks out code, sets up Python, installs dependencies, runs the notebook using ploomber-engine, and uploads the transformed data as an artifact.

```yaml
name: CI

on:
  push:
  pull_request:
  schedule:
    - cron: '0 0 4 * *'

# These permissions are needed to interact with GitHub's OIDC Token endpoint.
permissions:
  id-token: write
  contents: read

jobs:
  report:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v3
    - name: Set up Python ${{ matrix.python-version }}
      uses: conda-incubator/setup-miniconda@v2
      with:
        python-version: '3.10'
        miniconda-version: latest
        activate-environment: conda-env
        channels: conda-forge, defaults


    - name: Run notebook
      env:
        PLOOMBER_STATS_ENABLED: false
        PYTHON_VERSION: '3.10'
      shell: bash -l {0}
      run: |
        eval "$(conda shell.bash hook)"
        
        # pip install -r requirements.txt
        pip install jupysql pandas ploomber-engine --quiet
        ploomber-engine --log-output posthog.ipynb report.ipynb

    - uses: actions/upload-artifact@v3
      if: always()
      with:
        name: Transformed_data
        path: transformed_data.csv
```

--------------------------------

### Parameterizing Connection Aliases

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-sql.md

Demonstrates using variable substitution for connection aliases. This allows dynamic creation and closing of database connections.

```python
alias = "db-four"
```

```python
%sql sqlite:///db_four.db --alias {{alias}}
```

```python
%sql --close {{alias}}
```

--------------------------------

### Get Column Information Using Variable Expansion

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-tables-columns.md

Demonstrates variable expansion with %sqlcmd columns. The 'schema' and 'table' variables are dynamically substituted to query column information for 's1.t1'.

```python
table = "t1"
schema = "s1"
%sqlcmd columns -s {{schema}} -t {{table}}
```

--------------------------------

### Create and Populate user_activity Table

Source: https://github.com/ploomber/jupysql/blob/master/doc/tutorials/product-analytics.md

Creates a 'user_activity' table and inserts sample data for user interactions. This table is used for product analytics calculations.

```sql
CREATE TABLE user_activity (
  user_id INT NOT NULL,
 date DATE NOT NULL,
  activity_count INT NOT NULL,
  PRIMARY KEY (user_id, date)
);
INSERT INTO user_activity (user_id, date, activity_count)
VALUES
  (1, '2021-01-01', 5),
  (1, '2021-02-01', 3),
  (1, '2021-03-01', 2),
  (2, '2021-01-01', 10),
  (3, '2021-02-01', 1),
  (3, '2021-03-01', 0),
  (4, '2021-02-01', 6),
  (5, '2021-01-01', 4),
  (5, '2021-02-01', 5),
  (5, '2021-03-01', 6),
  (6, '2021-03-01', 7),
  (7, '2021-03-01', 10);
```

--------------------------------

### Execute SQL with Named Parameters

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/configuration.md

Demonstrates executing a SQL query using a named parameter `:rating`. Ensure `named_parameters` is configured appropriately.

```ipython3
rating = 12

%%sql
SELECT *
FROM languages
WHERE rating > :rating
```

--------------------------------

### Create and Populate SQLite Databases

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto.md

Initializes two SQLite databases, 'one.db' and 'two.db', and populates them with sample data using pandas DataFrames. This sets up the environment for subsequent SQL operations.

```python
from sqlalchemy import create_engine
import pandas as pd

engine_one = create_engine("sqlite:///one.db")
pd.DataFrame({"x": range(5)}).to_sql("one", engine_one)

engine_two = create_engine("sqlite:///two.db")
_ = pd.DataFrame({"x": range(5)}).to_sql("two", engine_two)
```

--------------------------------

### Install PostgreSQL Driver (psycopg2) with Conda

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/db-drivers.md

Install the psycopg2 package using conda for PostgreSQL connectivity. This is the recommended method.

```shell
# run this in your notebook
%conda install psycopg2 -c conda-forge --yes --quiet
```

--------------------------------

### List All Tables in the Environment

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-tables-columns.md

Uses the %sqlcmd tables magic command to list all available table names in the current database environment.

```python
%sqlcmd tables
```

--------------------------------

### Connect to DuckDB using Section

Source: https://github.com/ploomber/jupysql/blob/master/doc/user-guide/connection-file.md

Establishes a connection to a database defined in the 'duck' section of the 'connections.ini' file. The `--section` flag specifies which connection to use.

```python
%sql --section duck
```

--------------------------------

### Initialize DuckDB Connection

Source: https://github.com/ploomber/jupysql/blob/master/doc/compose.md

Establishes a connection to a DuckDB database. This is a prerequisite for executing SQL queries.

```ipython3
%sql duckdb://
```

--------------------------------

### Create 'coordinates' Table

Source: https://github.com/ploomber/jupysql/blob/master/doc/user-guide/tables-columns.md

Creates a sample table named 'coordinates' with integer columns 'x' and 'y'. This is used for demonstrating table and column listing.

```ipython3
%%sql
CREATE TABLE coordinates (x INT, y INT)
```

--------------------------------

### Prepare SQLite Connection

Source: https://github.com/ploomber/jupysql/blob/master/doc/community/developer-guide.md

Establishes a database connection using SQLite. Requires SQLAlchemy library.

```python
from sql.connection import SQLAlchemyConnection
from sqlalchemy import create_engine

conn = SQLAlchemyConnection(engine=create_engine(url="sqlite://"))
```

--------------------------------

### Load SQL Extension and Connect to SQLite

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/testing-columns.md

Loads the SQL extension and establishes a connection to an in-memory SQLite database. This is a prerequisite for running SQL commands.

```python
%load_ext sql
%sql sqlite://
```

--------------------------------

### Create Diamonds Table

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/ggplot-interact.md

Loads the downloaded diamonds.csv file into a SQL table named 'diamonds' using the %%sql magic command. This makes the data queryable.

```python
%%sql
CREATE TABLE diamonds AS SELECT * FROM diamonds.csv
```

--------------------------------

### Test if Column Values are Greater Than a Limit (Failing Example)

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/testing-columns.md

Tests if all values in the 'year_of_death' column are greater than 1700. This example is expected to raise an exception, indicating a failed test case.

```python
%sqlcmd test --table writer --column year_of_death --greater 1700
```

--------------------------------

### Create and Populate Second SQLite Database

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-profile.md

Creates a second SQLite database file 'b.db' and a table 'my_numbers' with different sample data. This prepares another SQLite database for attachment.

```python
import sqlite3

with sqlite3.connect("b.db") as conn:
    conn.execute("CREATE TABLE my_numbers (number FLOAT)")
    conn.execute("INSERT INTO my_numbers VALUES (11)")
    conn.execute("INSERT INTO my_numbers VALUES (22)")
    conn.execute("INSERT INTO my_numbers VALUES (33)")
```

--------------------------------

### Download Sample Parquet File

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/chdb.md

Downloads a sample Parquet file from a specified URL to the local filesystem for querying.

```python
from urllib.request import urlretrieve

_ = urlretrieve(
    "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2021-01.parquet",
    "yellow_tripdata_2021-01.parquet",
)
```

--------------------------------

### Get Single Prediction from MindsDB Model

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mindsdb.ipynb

Obtain a single prediction from a trained MindsDB model. This snippet demonstrates how to query for specific customer attributes and get the predicted churn status, confidence, and explanation.

```python
%%sql
SELECT Churn, Churn_confidence, Churn_explain
FROM mindsdb.customer_churn_predictor
WHERE SeniorCitizen=0
AND Partner='Yes'
AND Dependents='No'
AND tenure=1
AND PhoneService='No'
AND MultipleLines='No phone service'
AND InternetService='DSL';
```

--------------------------------

### Test Column Values within a Range (Exclusive - Failing Example)

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/testing-columns.md

Tests if all values in the 'year_of_death' column are strictly greater than 1800 and strictly less than 1900. This example is expected to fail, returning rows that do not meet the criteria.

```python
%sqlcmd test --table writer --column year_of_death --greater 1800 --less-than 1900
```

--------------------------------

### Connect to a Database

Source: https://github.com/ploomber/jupysql/blob/master/doc/tutorials/etl.md

Connect to a database using a connection string. Replace dialect, username, password, host, port, and database with your specific details.

```python
%sql dialect://username:password@host:port/database
```

--------------------------------

### Import pyodbc Library

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mssql.ipynb

Imports the pyodbc library to check for successful installation and availability in the Python environment.

```python
import pyodbc
```

--------------------------------

### Render and Print Full Query

Source: https://github.com/ploomber/jupysql/blob/master/doc/compose.md

Uses the '%sqlcmd snippets' magic to render the full SQL query represented by the 'top_artist' snippet and prints it to the console.

```python
final = %sqlcmd snippets top_artist
print(final)
```

--------------------------------

### Import Necessary Libraries

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/ggplot-interact.md

Imports core components for ggplot, ipywidgets, and interact functionality. Ensure these libraries are installed.

```python
from sql.ggplot import ggplot, aes, geom_histogram, facet_wrap
import ipywidgets as widgets
from ipywidgets import interact
```

--------------------------------

### Programmatic Execution of JupySQL Script

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/py-scripts.md

Demonstrates the command-line steps to convert a Python script with JupySQL magics into a notebook and then execute it using ploomber-engine.

```bash
pip install ploomber-engine
jupytext sql-analysis.py --to ipynb
ploomber-engine sql-analysis.ipynb output.ipynb
```

--------------------------------

### Transpile General SQL Clause

Source: https://github.com/ploomber/jupysql/blob/master/doc/community/developer-guide.md

Transpiles a general SQL clause to the dialect of the currently connected database (SQLite in this example).

```python
print("Transpiled result: ")
conn._transpile_query(general_sql)
```

--------------------------------

### Capture Docker Container IDs

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/postgres-connect.ipynb

Capture the output of a command to a variable. This example filters for PostgreSQL containers and captures their IDs.

```python
%%capture out
! docker container ls --filter ancestor=postgres --quiet
```

--------------------------------

### Connect to Raw DuckDB

Source: https://github.com/ploomber/jupysql/blob/master/doc/tutorials/duckdb-native-sqlalchemy.md

Establishes a native connection to DuckDB.

```python
import duckdb

conn = duckdb.connect()
```

--------------------------------

### Download SQLite Database

Source: https://github.com/ploomber/jupysql/blob/master/doc/compose.md

Downloads the Chinook SQLite database file if it does not already exist in the current directory. This is used for the example data.

```python
import urllib.request
from pathlib import Path

if not Path("my.db").is_file():
    url = "https://raw.githubusercontent.com/lerocha/chinook-database/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlite"  # noqa
    urllib.request.urlretrieve(url, "my.db")
```

--------------------------------

### Create DuckDB Engine with Connection Arguments

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/duckdb.md

Initialize a DuckDB in-memory engine, specifying connection arguments like 'preload_extensions'.

```python
from sqlalchemy import create_engine

some_engine = create_engine(
    "duckdb:///:memory:",
    connect_args={
        "preload_extensions": [],
    },
)
```

--------------------------------

### Download Penguins Dataset

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-tables-columns.md

Downloads the penguins.csv dataset from a GitHub repository if it does not already exist locally. This dataset is used for subsequent examples.

```python
from pathlib import Path
from urllib.request import urlretrieve

if not Path("penguins.csv").is_file():
    urlretrieve(
        "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv",
        "penguins.csv",
    )
```

--------------------------------

### Prepare DuckDB Connection

Source: https://github.com/ploomber/jupysql/blob/master/doc/community/developer-guide.md

Establishes a database connection using DuckDB. Requires SQLAlchemy and sqlglot libraries.

```python
from sql.connection import SQLAlchemyConnection
from sqlalchemy import create_engine
import sqlglot

conn = SQLAlchemyConnection(engine=create_engine(url="duckdb://"))
```

--------------------------------

### Pie Chart with Labels and X Columns

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-plot.md

Creates a pie chart using two specified columns: one for labels and one for the size of the pie slices (x). Requires a pre-saved query or table with aggregated data.

```ipython3
%%sql --save add_col --no-execute
SELECT species, count(species) as cnt
FROM penguins.csv
group by species
```

```ipython3
%sqlplot pie --table add_col --column species cnt
```

--------------------------------

### Load Excel Data with Pandas

Source: https://github.com/ploomber/jupysql/blob/master/doc/tutorials/excel.md

Reads an Excel file from a URL into a pandas DataFrame. Ensure pandas and openpyxl are installed before running this code.

```python
import pandas as pd

df = pd.read_excel("https://go.microsoft.com/fwlink/?LinkID=521962")
```

--------------------------------

### Execute Raw SQL with SQLAlchemyConnection

Source: https://github.com/ploomber/jupysql/blob/master/doc/community/developer-guide.md

Demonstrates executing raw SQL queries, including CREATE, INSERT, and SELECT, using a SQLAlchemy connection. Use raw_execute for user-submitted queries as it bypasses transpilation.

```python
conn_sqlalchemy.raw_execute("CREATE TABLE foo (bar INT);")
conn_sqlalchemy.raw_execute("INSERT INTO foo VALUES (42), (43), (44), (45);")
results = conn_sqlalchemy.raw_execute("SELECT * FROM foo")
print("one: ", results.fetchone())
print("many: ", results.fetchmany(size=1))
print("all: ", results.fetchall())
```

--------------------------------

### Initialize DuckDB Connection and Pandas DataFrame

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/duckdb-native.md

Set up a DuckDB connection and create a sample Pandas DataFrame. This is the initial step for querying DataFrames with JupySQL.

```python
import pandas as pd
import duckdb

conn = duckdb.connect()
df = pd.DataFrame({"x": range(10)})
```

--------------------------------

### Preview Data with SQL

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-plot.md

Selects and displays the first 3 rows from the 'penguins.csv' table using SQL. This is a basic data inspection step.

```python
%%sql
SELECT * FROM "penguins.csv" LIMIT 3
```

--------------------------------

### Read CSV into Pandas DataFrame

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/csv.md

Reads the generated 'writer.csv' file into a Pandas DataFrame for further inspection or manipulation. Requires the pandas library to be installed.

```python
import pandas as pd

df = pd.read_csv("writer.csv")
df
```

--------------------------------

### Create and Attach a New Database

Source: https://github.com/ploomber/jupysql/blob/master/doc/user-guide/tables-columns.md

Creates a new SQLite database file 'my.db' and attaches it as a schema named 'some_schema'. This allows querying tables in different databases.

```python
from sqlalchemy import create_engine
from sql.connection import SQLAlchemyConnection

conn = SQLAlchemyConnection(engine=create_engine("sqlite:///my.db"))
conn.execute("CREATE TABLE numbers (n FLOAT)")
```

```ipython3
%%sql
ATTACH DATABASE 'my.db' AS some_schema
```

--------------------------------

### Get Length of Fetched Results

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/configuration.md

Demonstrates retrieving the total number of rows fetched into the result set, even when `displaylimit` truncates the display.

```python
len(res)
```

--------------------------------

### Import HTML Display Utilities

Source: https://github.com/ploomber/jupysql/blob/master/doc/community/developer-guide.md

Import functions for displaying rich HTML messages, including links, using `message_html` and `Link` from `sql.display`.

```python
from sql.display import message_html, Link
```

--------------------------------

### Dynamic Column Selection and Limit

Source: https://github.com/ploomber/jupysql/blob/master/doc/user-guide/template.md

Demonstrates using Jinja variables for dynamic column selection and setting the LIMIT clause in a SQL query.

```python
dynamic_limit = 5
dynamic_column = "island, sex"

```

```sql
%sql SELECT {{dynamic_column}} FROM penguins.csv LIMIT {{dynamic_limit}}
```

--------------------------------

### Connect to Second DuckDB Instance

Source: https://github.com/ploomber/jupysql/blob/master/doc/user-guide/connection-file.md

Establishes a connection to the database defined in the 'second_duck' section of the 'connections.ini' file. This demonstrates managing multiple distinct connections.

```python
%sql --section second_duck
```

--------------------------------

### Get Default Connection Filename

Source: https://github.com/ploomber/jupysql/blob/master/doc/user-guide/connection-file.md

Retrieves the current filename JupySQL uses to store and read connection configurations. By default, it's set to '~/.jupysql/connections.ini'.

```python
%config SqlMagic.dsn_filename
```

--------------------------------

### Retrieve Results as Iterators

Source: https://github.com/ploomber/jupysql/blob/master/doc/intro.md

Get query results as an iterator of dictionaries using `result.dicts()` or as a single dictionary with scalar values per key using `result.dict()`.

```python
result.dicts()
```

```python
result.dict()
```

--------------------------------

### Enable Autopandas and Get DataFrame Type

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/configuration.md

Enables returning query results as Pandas DataFrames. The type of the returned object is checked to confirm it's a DataFrame.

```python
%config SqlMagic.autopandas = True
df = %sql SELECT * FROM languages
type(df)
```

--------------------------------

### Get Column Information for a Table

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-tables-columns.md

Uses the %sqlcmd columns -t penguins command to retrieve and display the column names and data types for the 'penguins' table.

```python
%sqlcmd columns -t penguins
```

--------------------------------

### List Tables Using Schema Variable Expansion

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-tables-columns.md

Demonstrates variable expansion with %sqlcmd tables. The 'schema' variable is dynamically substituted into the command to list tables in schema 's1'.

```python
schema = "s1"
%sqlcmd tables -s {{schema}}
```

--------------------------------

### Run All Integration Tests

Source: https://github.com/ploomber/jupysql/blob/master/doc/community/developer-guide.md

Execute all integration tests. Ensure Docker is running beforehand.

```sh
pytest src/tests/integration
```

--------------------------------

### List Available ODBC Drivers

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/mssql.ipynb

Retrieves and displays a list of all installed ODBC drivers recognized by pyodbc. This helps verify if the SQL Server driver is correctly detected.

```python
pyodbc.drivers()
```

--------------------------------

### Connect to chDB and load JupySQL extension

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/chdb.md

Establish a connection to chDB and load the JupySQL extension, aliasing it as 'chdb' for subsequent SQL commands.

```python
from chdb import dbapi

conn = dbapi.connect()

%load_ext sql
%sql conn --alias chdb
```

--------------------------------

### Configure Polars DataFrame Constructor Arguments

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/configuration.md

Set `polars_dataframe_kwargs` to customize the Polars DataFrame constructor. For example, setting `infer_schema_length` to `None` disables schema inference limits.

```ipython3
# 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
```

--------------------------------

### Test for No Nulls in a Column

Source: https://github.com/ploomber/jupysql/blob/master/doc/howto/testing-columns.md

Demonstrates the usage of the 'no-nulls' comparator argument for testing if a column contains any null values. This is a conceptual example as the specific code is not provided in the source.

```python
# Example for --no-nulls (conceptual, not directly from source)
# %sqlcmd test --table your_table --column your_column --no-nulls
```

--------------------------------

### Connect using DBAPI 2.0 (DuckDB)

Source: https://github.com/ploomber/jupysql/blob/master/doc/connecting.md

Connect using a DBAPI 2.0 compliant connection object. This example uses DuckDB's native `connect` method.

```python
import duckdb

conn = duckdb.connect()
```

```python
%sql conn
```

```python
import urllib

urllib.request.urlretrieve(
    "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv",
    "penguins.csv",
)
```

```python
%sql select * from penguins.csv limit 3
```

--------------------------------

### Enable Autopolars and Get DataFrame Type

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/configuration.md

Enables returning query results as Polars DataFrames. The type of the returned object is checked to confirm it's a Polars DataFrame.

```python
%config SqlMagic.autopolars = True
df = %sql SELECT * FROM languages
type(df)
```

--------------------------------

### Attach SQLite Database to DuckDB

Source: https://github.com/ploomber/jupysql/blob/master/doc/integrations/duckdb-native.md

Installs and loads the 'sqlite_scanner' extension, then attaches the 'my.db' SQLite file to the DuckDB connection. This makes tables within the SQLite database queryable.

```ipython3
%%sql
INSTALL 'sqlite_scanner';
LOAD 'sqlite_scanner';
CALL sqlite_attach('my.db');
```

--------------------------------

### Handle Non-existent Table Query (Magic)

Source: https://github.com/ploomber/jupysql/blob/master/doc/community/developer-guide.md

Demonstrates how JupySQL hides Python tracebacks when a SQL query fails due to a non-existent table when using magics.

```python
%sql SELECT * FROM not_a_table
```

--------------------------------

### Transform Data: Filter Data

Source: https://github.com/ploomber/jupysql/blob/master/doc/tutorials/etl.md

Filter rows based on a condition using Pandas or JupySQL. The JupySQL example uses %%sql df << to assign the result to the 'df' variable.

```python
# Filter data
filtered_data = data[data['sales'] > 1000]  # Pandas
```

```sql
%%sql df << 
SELECT * FROM data 
WHERE sales > 1000;  # JupySQL
```

--------------------------------

### Parametrize Table Name for Exploration

Source: https://github.com/ploomber/jupysql/blob/master/doc/user-guide/table_explorer.ipynb

Demonstrates how to use variable expansion with JupySQL's `explore` command. This allows dynamic selection of tables by using placeholders like `{{variable_name}}`.

```python
table_name = "yellow_tripdata_2021.parquet"
%sqlcmd explore --table {{table_name}}
```

--------------------------------

### Load JupySQL Extension and Connect to DuckDB

Source: https://github.com/ploomber/jupysql/blob/master/doc/api/magic-tables-columns.md

Loads the SQL extension and establishes a connection to a DuckDB database. This is a prerequisite for executing SQL commands.

```python
%load_ext sql
%sql duckdb://
```