%sql/%%sql#

Note

You can view the documentation and command line arguments by running %sql?

-l / --connections List all active connections (example)

-x / --close <session-name/alias> Close named connection (example)

-c / --creator <creator-function> Specify creator function for new connection (example)

-s / --section <section-name> Section of dsn_file to be used for generating a connection string (example)

-p / --persist Create a table name in the database from the named DataFrame (example)

--append Like --persist, but appends to the table if it already exists (example)

--persist-replace Like --persist, but it will drop the existing table before inserting the new table (example)

-a / --connection_arguments <"{connection arguments}"> Specify dictionary of connection arguments to pass to SQL driver

-f / --file <path> Run SQL from file at this path (example)

New in version 0.4.2.

-n / --no-index Do not persist data frame’s index (used with -p/--persist) (example)

New in version 0.4.3.

-S / --save <name> Save this query for later use (example)

-w / --with <name> Use a previously saved query (used after -S/--save) (example)

New in version 0.5.2.

-A / --alias <alias> Assign an alias when establishing a connection (example)

Initialization#

%load_ext sql

Connect to database#

%sql sqlite:///db_one.db
Connecting to 'sqlite:///db_one.db'

Assign an alias to the connection (added 0.5.2):

%sql sqlite:///db_two.db --alias db-two
Connecting and switching to connection 'db-two'
%sql sqlite:///db_three.db --alias db-three
Connecting and switching to connection 'db-three'

To make all subsequent queries to use certain connection, pass the connection name:

%sql db-two
Switching to connection 'db-two'
%sql db-three
Switching to connection 'db-three'

You can inspect which is the current active connection:

%sql --connections
Active connections:
current url alias
* sqlite:///db_three.db db-three
sqlite:///db_two.db db-two
sqlite:///db_one.db sqlite:///db_one.db

For more details on managing connections, see Switch connections.

List connections#

%sql --connections
Active connections:
current url alias
* sqlite:///db_three.db db-three
sqlite:///db_two.db db-two
sqlite:///db_one.db sqlite:///db_one.db

Close connection#

%sql --close sqlite:///db_one.db

Or pass an alias (added in 0.5.2):

%sql --close db-two

Specify creator function#

import os
import sqlite3

# Set environment variable $DATABASE_URL
os.environ["DATABASE_URL"] = "sqlite:///"

# Define a function that returns a DBAPI connection


def creator():
    return sqlite3.connect("")

%sql --creator creator
Running query in 'db-three'

Start a connection from .ini file#

Changed in version 0.10.0: dsn_filename default changed from odbc.ini to ~/.jupysql/connections.ini.

Use --section to start a connection from the dsn_filename. To learn more, see: Using a connection file

By default, JupySQL reads connections from ~/.jupysql/connections.ini, but you can set it to a different value:

%config SqlMagic.dsn_filename

'/home/docs/.jupysql/connections.ini'

%config SqlMagic.dsn_filename = "connections.ini"

%config SqlMagic.dsn_filename

'connections.ini'

from pathlib import Path

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

%sql --section mydb
Connecting and switching to connection 'mydb'
%sql --connections
Active connections:
current url alias
sqlite:///db_three.db db-three
* duckdb:// mydb

Create table#

%sql sqlite://
Connecting and switching to connection 'sqlite://'
import pandas as pd

my_data = pd.DataFrame({"x": range(3), "y": range(3)})

%sql --persist my_data
Running query in 'sqlite://'
Success! Persisted my_data to the database.
%sql SELECT * FROM my_data
Running query in 'sqlite://'
index x y
0 0 0
1 1 1
2 2 2

Create table without DataFrame index#

my_chars = pd.DataFrame({"char": ["a", "b", "c"]})
my_chars
char
0 a
1 b
2 c 
%sql --persist my_chars --no-index
Running query in 'sqlite://'
Success! Persisted my_chars to the database.
%sql SELECT * FROM my_chars
Running query in 'sqlite://'
char
a
b
c

Append to table#

my_data = pd.DataFrame({"x": range(3, 6), "y": range(3, 6)})

%sql --append my_data
Running query in 'sqlite://'
Success! Persisted my_data to the database.
%sql SELECT * FROM my_data
Running query in 'sqlite://'
index x y
0 0 0
1 1 1
2 2 2
0 3 3
1 4 4
2 5 5

Persist replace to table#

my_data = pd.DataFrame({"x": range(3), "y": range(3)})

%sql --persist-replace my_data --no-index
Running query in 'sqlite://'
Success! Persisted my_data to the database.
%sql SELECT * FROM my_data
Running query in 'sqlite://'
x y
0 0
1 1
2 2

Query#

%sql SELECT * FROM my_data LIMIT 2
Running query in 'sqlite://'
x y
0 0
1 1 
%%sql
SELECT * FROM my_data LIMIT 2
Running query in 'sqlite://'
x y
0 0
1 1

Programmatic SQL queries#

QUERY = """
SELECT *
FROM my_data
LIMIT 3
"""

%sql {{QUERY}}
Running query in 'sqlite://'
x y
0 0
1 1
2 2

Templated SQL queries#

target = 1

%%sql
SELECT *
FROM my_data
WHERE x = {{target}}
Running query in 'sqlite://'
x y
1 1

Important: Ensure you sanitize the input parameters; as malicious parameters will be able to run arbitrary SQL queries.

For more information, visit Parameterizing SQL queries section.

Compose large queries#

%%sql --save larger_than_one --no-execute
SELECT x, y
FROM my_data
WHERE x > 1
Running query in 'sqlite://'
Skipping execution...
%%sql
SELECT x, y
FROM larger_than_one
WHERE y < 5
Generating CTE with stored snippets: 'larger_than_one'
Running query in 'sqlite://'
x y
2 2

Convert result to pandas.DataFrame#

result = %sql SELECT * FROM my_data
df = result.DataFrame()
print(type(df))
df.head()
Running query in 'sqlite://'
<class 'pandas.core.frame.DataFrame'>
x y
0 0 0
1 1 1
2 2 2

Store as CSV#

result = %sql SELECT * FROM my_data
result.csv(filename="my_data.csv")
Running query in 'sqlite://'
CSV results

Run query from file#

from pathlib import Path

# generate sql file
Path("my-query.sql").write_text(
    """
SELECT *
FROM my_data
LIMIT 3
"""
)

31

%sql --file my-query.sql
Running query in 'sqlite://'
x y
0 0
1 1
2 2