Online DuckDB Compiler

DuckDB is an SQL database engine that runs inside the Python process, so there is no server to install or connect to. It is designed for analysis: aggregations, joins and window functions over tables of data. It can query CSV and Parquet files and pandas DataFrames directly, without loading them into a table first. People use it to explore data files with SQL and to convert data between formats. This page is an online DuckDB compiler: the code runs in your browser, so you can try it without installing anything. Run the example first, then paste any snippet below into a new cell to try it.

What the example does

urlretrieve() downloads tips.csv, a table of restaurant bills and tips from the seaborn-data repository on GitHub, to a temporary file and returns its path. duckdb.sql() runs the query and returns a relation, which appears below the code as a table. read_csv() takes the column names from the header and detects each column's type: total_bill and tip become double, size becomes int64, and smoker, which holds Yes and No, becomes boolean. LIMIT 5 keeps the first five rows.

Query a pandas DataFrame with SQL

DuckDB finds a pandas DataFrame by its variable name, so you can use it in FROM like a table. .df() turns the result back into a DataFrame:

import duckdb
import pandas as pd

orders = pd.DataFrame({
    "customer": ["Ana", "Ben", "Ana", "Chloe", "Ben", "Ana"],
    "amount": [25.0, 12.5, 40.0, 8.0, 30.0, 15.5],
})

result = duckdb.sql("""
    SELECT customer, COUNT(*) AS orders, SUM(amount) AS total
    FROM orders
    GROUP BY customer
    ORDER BY total DESC
""")
print(result)
print(type(result.df()))   # back to a pandas DataFrame

Query a CSV file

A file name in single quotes works as a table name. DESCRIBE shows the types DuckDB detected. Here date becomes a DATE, so date functions such as month() work on it directly:

import duckdb

with open("sales.csv", "w") as f:
    f.write("date,region,units,price\n"
            "2024-01-05,North,12,2.5\n"
            "2024-01-19,South,7,2.5\n"
            "2024-02-02,North,3,4.0\n"
            "2024-02-14,East,9,4.0\n"
            "2024-03-01,South,14,4.0\n")

print(duckdb.sql("DESCRIBE SELECT * FROM 'sales.csv'"))
print(duckdb.sql("""
    SELECT month(date) AS month, SUM(units * price) AS revenue
    FROM 'sales.csv'
    GROUP BY month
    ORDER BY month
"""))

Create tables and join them

duckdb.connect() opens a database file and creates it if it doesn't exist. Its tables stay in the file after close(). ? placeholders pass values into a query, and fetchall() returns rows as a list of tuples:

import duckdb

con = duckdb.connect("shop.duckdb")
con.execute("CREATE OR REPLACE TABLE products (id INTEGER, name VARCHAR, price DOUBLE)")
con.execute("CREATE OR REPLACE TABLE orders (product_id INTEGER, qty INTEGER)")
con.executemany("INSERT INTO products VALUES (?, ?, ?)",
                [(1, "Pen", 1.2), (2, "Notebook", 3.5), (3, "Backpack", 25.0)])
con.executemany("INSERT INTO orders VALUES (?, ?)", [(1, 10), (2, 4), (1, 5), (3, 1)])

print(con.sql("""
    SELECT p.name, COUNT(*) AS orders, SUM(o.qty * p.price) AS revenue
    FROM orders o JOIN products p ON p.id = o.product_id
    GROUP BY p.name
    ORDER BY revenue DESC
"""))
print(con.execute("SELECT name FROM products WHERE price > ?", [2]).fetchall())
con.close()

CREATE OR REPLACE TABLE lets you run the cell again without an error.

Export results to Parquet or CSV

COPY ... TO writes a query result to a file, and write_csv() does the same for a relation. This uses sales.csv from the CSV snippet above:

import duckdb

duckdb.sql("""
    COPY (SELECT region, SUM(units * price) AS revenue
          FROM 'sales.csv' GROUP BY region ORDER BY region)
    TO 'revenue.parquet' (FORMAT parquet)
""")
print(duckdb.sql("SELECT * FROM 'revenue.parquet'"))

duckdb.sql("SELECT * FROM 'sales.csv' WHERE region = 'North'").write_csv("north.csv")
print(open("north.csv").read())

Good to know

• duckdb.sql() uses one in-memory database that every cell shares. A table created in one cell can be queried in the next, but it is not saved to a file. Use duckdb.connect("name.duckdb") for tables you want to keep.
• Text values go in single quotes. Double quotes name a column, so WHERE region = "North" raises a BinderException: DuckDB looks for a column called North and does not find one.
• This build (DuckDB 1.1.2) runs on one thread, and the parquet, json and icu extensions are already loaded.