环境与基本使用

本页目的： 把 DuckDB 装起来并跑通基本用法 —— CLI、Python API、常用 SQL、扩展机制。全部基于本机实测（DuckDB v1.5.5 Variegata，macOS arm64）。

本页是 DuckDB 实战系列第 1 篇，下一篇见模拟数据。

1. 安装

DuckDB 有两个形态：CLI 二进制 与 Python 包。本机用 uv 管理，无需全局安装：

# Python API（临时环境，不污染项目依赖）
uv run --with duckdb python -c "import duckdb; print(duckdb.__version__)"   # 1.5.5

# CLI（新版 duckdb 包不再自带 CLI，需用 duckdb-cli 包）
uvx --from duckdb-cli duckdb --version   # v1.5.5 (Variegata)

⚠️ 坑： uvx duckdb 会报 Package "duckdb" does not provide any executables —— 新版 duckdb Python 包不再附带 CLI，要用 uvx --from duckdb-cli duckdb。

2. CLI 基本操作

CLI 支持 内存库（不指定文件）与 文件库（单文件持久化，就是普通文件，可拷走/备份）：

uvx --from duckdb-cli duckdb                    # 内存库
uvx --from duckdb-cli duckdb test.duckdb        # 文件库（不存在则创建）
uvx --from duckdb-cli duckdb test.duckdb -c "SQL"   # 非交互执行

交互模式常用元命令：

命令	作用
`.tables`	列出表
`.schema t`	查看建表语句
`.mode list` / `.mode markdown`	切换输出格式

示例：

-- 文件库：建表 + 写入 + 查询
CREATE TABLE users (id INTEGER, name VARCHAR, created DATE);
INSERT INTO users VALUES (1, 'Alice', '2026-01-01'), (2, 'Bob', '2026-02-14');
SELECT id, name, strftime(created, '%Y-%m') AS ym FROM users ORDER BY id;

3. Python API

import duckdb

# 1) 连接：内存库 vs 文件库
con_mem = duckdb.connect()  # 内存库
con_file = duckdb.connect("app.duckdb")  # 文件库

# 2) 默认连接：不显式 connect 也能直接查
duckdb.sql("SELECT 40 + 2").fetchone()  # (42,)

# 3) con.sql()（推荐，返回 relation）vs con.execute()（返回 cursor）
con_file.sql("SELECT 'a'").fetchone()  # ('a',)
con_file.execute("SELECT 'b'").fetchone()  # ('b',)

# 4) pandas / Arrow 互操作：直接查 DataFrame（零拷贝）
import pandas as pd

df = pd.DataFrame({"city": ["上海", "北京"], "pop": [2487, 2189]})
duckdb.sql("SELECT city FROM df WHERE pop > 2000").df()  # 结果取回 DataFrame
duckdb.sql("SELECT count(*) FROM df").arrow()  # 取回 Arrow RecordBatch

# 5) 结果展示
con_file.sql("SELECT ...").show()  # CLI 风格的表格输出

要点： DuckDB 是进程内嵌入式数据库，没有 server、没有端口、没有序列化开销。查询 pandas DataFrame、Arrow 表时直接读内存数据，不用导入导出。

4. 常用 SQL 特性

-- CTAS：建表并写入
CREATE TABLE sales AS
  SELECT * FROM (VALUES (1,'2026-01',100),(2,'2026-01',50)) t(id, ym, amt);

-- 窗口函数
SELECT id, ym, amt, sum(amt) OVER (PARTITION BY ym) AS ym_total FROM sales;

-- DESCRIBE 查看表结构
DESCRIBE sales;

-- COPY：与文件互导（CSV / Parquet）
COPY sales TO 'sales.parquet' (FORMAT PARQUET);
COPY sales FROM 'sales.csv'   (FORMAT CSV, HEADER);

-- EXPLAIN ANALYZE 查看执行计划与耗时
EXPLAIN ANALYZE SELECT ym, sum(amt) FROM sales GROUP BY ym;

5. 扩展机制

DuckDB 通过扩展提供更多功能，分两类：

扩展	安装方式	用途
`parquet` / `json`	核心扩展，开箱即用	Parquet / JSON 读写
`httpfs`	`INSTALL httpfs; LOAD httpfs;`	读取远程 HTTP(S)/S3 文件
`postgres_scanner`	`INSTALL postgres_scanner; LOAD postgres_scanner;`	连接 PostgreSQL

INSTALL postgres_scanner; LOAD postgres_scanner;   -- 需要网络下载扩展
LOAD parquet; LOAD json;                            -- 核心扩展直接 LOAD

-- 查看扩展状态
SELECT extension_name, installed, loaded
FROM duckdb_extensions()
WHERE extension_name IN ('parquet','json','httpfs','postgres_scanner');

坑： 1.5.5 里 LOAD httpfs 会报 Extension not found. Install it first —— 非核心扩展必须先 INSTALL（联网下载）再 LOAD；INSTALL 后扩展文件缓存在 ~/.duckdb/extensions/。

6. 参考链接

资源	链接
DuckDB 文档	https://duckdb.org/docs/
Python API	https://duckdb.org/docs/stable/clients/python/overview
扩展列表	https://duckdb.org/docs/stable/extensions/overview

→ 下一站：模拟数据

Backlinks (2)

flowchart LR
  n0["AI"]
  n1["Database"]
  n2["Dev Tools"]
  n3["Emoji"]
  n4["Frontend"]
  n5["Game Dev"]
  n6["Collection"]
  n7["Languages"]
  n8["Maps"]
  n9["Media"]
  n10["Monitor"]
  n11["Plans"]
  n12["对象存储基本用法（Bucket / Object / 常用操作）"]
  n13["挂载 Bucket 为本地文件系统（FUSE Mount）"]
  n14["对象存储签名 URL（Signed URL）原理与实战"]
  n15["对象存储供应商对比（S3 / R2 / OSS / Supabase / MinIO）"]
  n16["Prototypes"]
  n17["Research"]
  n18["Better Auth 源码阅读指南"]
  n19["DuckDB 环境与基本使用"]
  n20["DuckDB 实战研究"]
  n21["DuckDB 模拟数据"]
  n22["PostgreSQL 数据用 DuckDB 加速查询"]
  n23["HK 角色阅读"]
  n24["HK 台词阅读"]
  n25["Hollow Knight 英语主题"]
  n26["HK 物品阅读"]
  n27["HK 地点阅读"]
  n28["HK 世界观 / Lore 阅读"]
  n29["HK 英语学习素材清单"]
  n30["英语学习 Dashboard"]
  n31["English Scraps Archive"]
  n32["English Scraps 使用指南"]
  n33["Jellyfin 源码阅读指南"]
  n34["Lux 资料整理"]
  n35["Nest Commander 学习资料"]
  n36["NestJS 源码阅读指南"]
  n37["Protomaps 自建底图研究"]
  n38["自制 PMTiles 地图（最简单例子）"]
  n39["MapLibre 集成 Protomaps"]
  n40["PMTiles 格式与工具链"]
  n41["上海地区底图项目"]
  n42["Redash 源码阅读指南"]
  n43["Rust 学习计划"]
  n44["shadcn/ui 源码阅读指南"]
  n45["TRIP 项目核心原理与代码阅读指南"]
  n1 --> n20
  n6 --> n0
  n6 --> n1
  n6 --> n2
  n6 --> n3
  n6 --> n4
  n6 --> n5
  n6 --> n7
  n6 --> n8
  n6 --> n9
  n6 --> n10
  n6 --> n11
  n6 --> n17
  n12 --> n14
  n12 --> n15
  n12 --> n16
  n13 --> n12
  n13 --> n14
  n13 --> n15
  n14 --> n16
  n15 --> n12
  n15 --> n14
  n15 --> n16
  n16 --> n12
  n16 --> n13
  n16 --> n14
  n16 --> n15
  n16 --> n37
  n17 --> n18
  n17 --> n20
  n17 --> n30
  n17 --> n33
  n17 --> n34
  n17 --> n35
  n17 --> n36
  n17 --> n37
  n17 --> n42
  n17 --> n43
  n17 --> n44
  n17 --> n45
  n19 --> n21
  n20 --> n1
  n20 --> n17
  n20 --> n19
  n20 --> n21
  n20 --> n22
  n21 --> n19
  n21 --> n22
  n22 --> n20
  n22 --> n21
  n25 --> n23
  n25 --> n24
  n25 --> n26
  n25 --> n27
  n25 --> n28
  n25 --> n29
  n25 --> n32
  n29 --> n24
  n30 --> n25
  n30 --> n31
  n30 --> n32
  n32 --> n30
  n32 --> n31
  n37 --> n8
  n37 --> n38
  n37 --> n39
  n37 --> n40
  n37 --> n41
  n38 --> n40
  n39 --> n16
  n39 --> n41
  n40 --> n38
  n40 --> n41
  n41 --> n39
  n41 --> n40
  click n0 "../../../../collection/ai/" "AI"
  click n1 "../../../../collection/database/" "Database"
  click n2 "../../../../collection/dev-tools/" "Dev Tools"
  click n3 "../../../../collection/emoji/" "Emoji"
  click n4 "../../../../collection/frontend/" "Frontend"
  click n5 "../../../../collection/game-dev/" "Game Dev"
  click n6 "../../../../collection/" "Collection"
  click n7 "../../../../collection/languages/" "Languages"
  click n8 "../../../../collection/maps/" "Maps"
  click n9 "../../../../collection/media/" "Media"
  click n10 "../../../../collection/monitor/" "Monitor"
  click n11 "../../../../collection/scraps/plans/" "Plans"
  click n12 "../../../../knowledge/infrastructure/cloud/object-storage/basic-usage/" "对象存储基本用法（Bucket / Object / 常用操作）"
  click n13 "../../../../knowledge/infrastructure/cloud/object-storage/mount-bucket/" "挂载 Bucket 为本地文件系统（FUSE Mount）"
  click n14 "../../../../knowledge/infrastructure/cloud/object-storage/signed-url/" "对象存储签名 URL（Signed URL）原理与实战"
  click n15 "../../../../knowledge/infrastructure/cloud/object-storage/vendors-comparison/" "对象存储供应商对比（S3 / R2 / OSS / Supabase / MinIO）"
  click n16 "../../../../prototypes/" "Prototypes"
  click n17 "../../../" "Research"
  click n18 "../../better-auth/" "Better Auth 源码阅读指南"
  click n19 "./" "DuckDB 环境与基本使用"
  click n20 "../" "DuckDB 实战研究"
  click n21 "../mock-data/" "DuckDB 模拟数据"
  click n22 "../postgresql-acceleration/" "PostgreSQL 数据用 DuckDB 加速查询"
  click n23 "../../english/hollow-knight/characters/" "HK 角色阅读"
  click n24 "../../english/hollow-knight/dialogues/" "HK 台词阅读"
  click n25 "../../english/hollow-knight/" "Hollow Knight 英语主题"
  click n26 "../../english/hollow-knight/items/" "HK 物品阅读"
  click n27 "../../english/hollow-knight/locations/" "HK 地点阅读"
  click n28 "../../english/hollow-knight/lore/" "HK 世界观 / Lore 阅读"
  click n29 "../../english/hollow-knight/resources/" "HK 英语学习素材清单"
  click n30 "../../english/" "英语学习 Dashboard"
  click n31 "../../english/scraps/archive/" "English Scraps Archive"
  click n32 "../../english/scraps/" "English Scraps 使用指南"
  click n33 "../../jellyfin/" "Jellyfin 源码阅读指南"
  click n34 "../../lux/" "Lux 资料整理"
  click n35 "../../nest-commander/" "Nest Commander 学习资料"
  click n36 "../../nestjs/" "NestJS 源码阅读指南"
  click n37 "../../protomaps/" "Protomaps 自建底图研究"
  click n38 "../../protomaps/make-own-map/" "自制 PMTiles 地图（最简单例子）"
  click n39 "../../protomaps/maplibre/" "MapLibre 集成 Protomaps"
  click n40 "../../protomaps/pmtiles/" "PMTiles 格式与工具链"
  click n41 "../../protomaps/shanghai-map/" "上海地区底图项目"
  click n42 "../../redash/" "Redash 源码阅读指南"
  click n43 "../../rust/" "Rust 学习计划"
  click n44 "../../shadcn-ui/" "shadcn/ui 源码阅读指南"
  click n45 "../../trip/" "TRIP 项目核心原理与代码阅读指南"

Links (1)

DuckDB 模拟数据