%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
Assign an alias to the connection (added 0.5.2):
%sql sqlite:///db_two.db --alias db-two
%sql sqlite:///db_three.db --alias db-three
To make all subsequent queries to use certain connection, pass the connection name:
%sql db-two
%sql db-three
You can inspect which is the current active connection:
%sql --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
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
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
%sql --connections
current | url | alias |
---|---|---|
sqlite:///db_three.db | db-three | |
* | duckdb:// | mydb |
Create table#
%sql sqlite://
import pandas as pd
my_data = pd.DataFrame({"x": range(3), "y": range(3)})
%sql --persist my_data
%sql SELECT * FROM my_data
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
%sql SELECT * FROM my_chars
char |
---|
a |
b |
c |
Append to table#
my_data = pd.DataFrame({"x": range(3, 6), "y": range(3, 6)})
%sql --append my_data
%sql SELECT * FROM my_data
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
%sql SELECT * FROM my_data
x | y |
---|---|
0 | 0 |
1 | 1 |
2 | 2 |
Query#
%sql SELECT * FROM my_data LIMIT 2
x | y |
---|---|
0 | 0 |
1 | 1 |
%%sql
SELECT * FROM my_data LIMIT 2
x | y |
---|---|
0 | 0 |
1 | 1 |
Programmatic SQL queries#
QUERY = """
SELECT *
FROM my_data
LIMIT 3
"""
%sql {{QUERY}}
x | y |
---|---|
0 | 0 |
1 | 1 |
2 | 2 |
Templated SQL queries#
target = 1
%%sql
SELECT *
FROM my_data
WHERE x = {{target}}
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
%%sql
SELECT x, y
FROM larger_than_one
WHERE y < 5
x | y |
---|---|
2 | 2 |
Convert result to pandas.DataFrame
#
result = %sql SELECT * FROM my_data
df = result.DataFrame()
print(type(df))
df.head()
<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")
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
x | y |
---|---|
0 | 0 |
1 | 1 |
2 | 2 |
Parameterizing arguments#
JupySQL supports variable expansion of arguments in the form of {{variable}}
. This allows the user to specify arguments with placeholders that can be replaced by variables dynamically.
Let’s see an example of creating a connection using an alias and closing the same through variable substitution.
alias = "db-four"
%sql sqlite:///db_four.db --alias {{alias}}
%sql --close {{alias}}