Database cheatsheet

DuckDB 2 Cheatsheet

Query CSV, Parquet and DataFrames with SQL from Python or the duckdb command line, without running a server.

From Python

Install DuckDB — Install the Python package. DuckDB runs inside your process, with no server.
Run a query — Run SQL and fetch the rows as Python tuples.
Query a CSV file — Read a CSV directly in SQL. DuckDB detects types and headers automatically.
Query Parquet files — Read one file or many with a glob pattern.
Query a pandas DataFrame — Use a DataFrame variable as a table in SQL, with no copying step.
Get the result as a DataFrame — Convert a result to pandas, Polars or Arrow. The Polars and Arrow conversions need pyarrow installed.
Use a database file — Connect to a file so tables persist between runs.
Pass parameters safely — Use placeholders instead of building SQL from strings.

Create a table from a file — Load a CSV into a table in one statement.
Inspect a table — Show column names and types.
Summarize every column — Get min, max, quartiles, unique counts and null percentages for each column.
Group by all non-aggregated columns — Skip listing the grouping columns by hand.
Filter on a window function — QUALIFY filters rows after window functions are calculated, without a subquery.
Select all columns except some — Leave out columns without listing the rest.
Pivot rows into columns — Turn category values into columns.
Expand a list into rows — Turn each list element into its own row.
Take a random sample — Return about 10% of the rows.

Write query results to Parquet — Export a result as a compressed Parquet file.
Write query results to CSV — Export a result as CSV with a header.
Open a database in the CLI — Start the interactive shell on a database file. Omit the file for an in-memory database.
Run a query from the shell — Run one statement and exit, which suits scripts.
Change the output format — Print results as markdown, CSV or JSON from the CLI.
Query files over HTTP or S3 — Load the httpfs extension, then read remote files like local ones.

Elasticsearch 9 cheatsheet — curl requests for indexes, documents, searches, bulk loads and cluster health. Security is on by default, so the examples authenticate.
MongoDB 8 cheatsheet — mongosh shell commands for documents, queries, aggregation, indexes, backups and administration.
MySQL 8.4 cheatsheet — mysql client, backup and maintenance commands for MySQL 8.4.
PostgreSQL 18 cheatsheet — psql, backup and maintenance commands for PostgreSQL 18.
Redis 8 cheatsheet — redis-cli and core commands for keys, data structures and server administration.
SQLite 3 cheatsheet — sqlite3 shell dot-commands, pragmas, import and export, and the SQL you use most.