TopolearnMost people use DuckDB from Python scripts and notebooks, so this is the everyday interface.
pip install duckdb gives you everything; there are no other dependencies. Two entry points:
duckdb.sql(query)runs on a shared default in-memory database. Handy in notebooks.con = duckdb.connect("file.duckdb")(orconnect()for in-memory) gives you an explicit connection. Use this in real programs and close it withcon.close()or awithblock.
sql() returns a lazy relation: nothing runs until you print it or fetch. fetchall() returns a list of tuples, fetchone() a single tuple, and .show() prints a table. Relations can be chained or queried again, which makes building a query step by step natural.
con.execute(query, params) runs a statement immediately and supports prepared parameters with ? or $name placeholders. Always pass values this way rather than formatting them into the SQL string; it avoids injection and quoting bugs. executemany runs one statement for many parameter rows, but for bulk loading it is far faster to insert from a DataFrame or a file.
A connection is not meant to be shared by several threads at once; give each thread its own con.cursor().
Example
import duckdb
with duckdb.connect() as con: # in-memory database
con.execute("CREATE TABLE users (id INT, name TEXT, age INT)")
con.executemany("INSERT INTO users VALUES (?, ?, ?)",
[(1, "Ann", 34), (2, "Bob", 19), (3, "Cy", 52)])
rows = con.execute("SELECT name FROM users WHERE age > ? ORDER BY name",
[30]).fetchall()
print(rows) # [('Ann',), ('Cy',)]
rel = con.sql("SELECT avg(age) AS avg_age FROM users")
print(rel.fetchone()) # (35.0,)Remember
duckdb.sqluses a default in-memory database;duckdb.connectgives an explicit one- Relations are lazy until fetched or shown
- Pass values with
?or$nameparameters, never string formatting - Use one cursor per thread
Watch out for
- Building SQL with f-strings from user input, which breaks on quotes and invites injection.