duckdb 的 python sdk 读取 csv 的时候，如何指定列的字段类型？

Question

─➤  pip show duckdb                     
Name: duckdb
Version: 1.1.1
Summary: DuckDB in-process database
Home-page: https://www.duckdb.org
Author: 
Author-email: 
License: MIT
Location: /home/pon/.local/share/virtualenvs/taisan_console-0970Xq3e/lib/python3.11/site-packages
Requires: 
Required-by: 

使用下面的代码读取一个 csv 文件，但是会报错

excel_file_path = 'dev/周度产出（9.16-9.22）.csv'
query = f"""
SELECT * 
FROM '{excel_file_path}'
"""
df = duckdb.query(query).df()

看起来是因为类型的问题

Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "/home/pon/code/work/vobile/vobile-it/taisan_console/dev/found_ma_from_csv_batch.py", line 21, in <module>
    df = duckdb.query(query).df()
         ^^^^^^^^^^^^^^^^^^^^^^^^
duckdb.duckdb.ConversionException: Conversion Error: CSV Error on Line: 25716
Original Line: 
2024/9/16-2024/9/22,BM-BQBH-2023-3,央视国际2023年新媒体视频版权监测服务协议,新浪新闻,https://k.sina.cn/article_2110705772_m7dced06c033016q3c.html,萧敬腾郁可唯把李清照的词唱成歌,微博电视,-,-,-,已下线,2024-09-19,系统,中央广播电视总台2024中秋晚会,系统,-
Error when converting column "监测日期". Could not convert string "-" to 'DATE'

Column 监测日期 is being converted as type DATE
This type was auto-detected from the CSV file.
Possible solutions:
* Override the type for this column manually by setting the type explicitly, e.g. types={'监测日期': 'VARCHAR'}
* Set the sample size to a larger value to enable the auto-detection to scan more values, e.g. sample_size=-1
* Use a COPY statement to automatically derive types from an existing table.

  file=dev/周度产出（9.16-9.22）.csv
  delimiter = , (Auto-Detected)
  quote = " (Auto-Detected)
  escape = " (Auto-Detected)
  new_line = \r\n (Auto-Detected)
  header = true (Auto-Detected)
  skip_rows = 0 (Auto-Detected)
  comment = \0 (Auto-Detected)
  date_format =  (Auto-Detected)
  timestamp_format =  (Auto-Detected)
  null_padding=0
  sample_size=20480
  ignore_errors=false
  all_varchar=0

所以如何在读取 csv 的时候指定类型？

---

问了一个 ai，答案都是不行的

chatgpt ❌

query = f"SELECT * FROM '{excel_file_path}' WITH (types={{'监测日期': 'VARCHAR'}})"
df = duckdb.query(query).df()

智谱清言 ❌

query = f"""
SELECT * 
FROM '{excel_file_path}' 
(监测日期 VARCHAR)
"""
df = duckdb.query(query).df()

豆包 ❌

import duckdb

# 指定列的类型映射
types = {'column_name': 'data_type'}

# 读取 CSV 文件并指定列类型
query = f"SELECT * FROM '{csv_file_path}' (types={types})"
df = duckdb.query(query).df()

Answer 1

问一堆AI为什么就是不敢看一眼官方文档？ https://duckdb.org/docs/data/csv/overview.html

你的绝大多数问题都可以阅读官方文档解决！

Answer 2

在 DuckDB 中，当你从 CSV 文件读取数据时，并希望指定列的数据类型，你需要使用 READ_CSV 函数或者通过 COPY 语句，并在这些语句中明确指定列的类型。直接使用 SELECT * FROM 'filename' 是不支持直接在查询中指定列类型的。

以下是一个使用 READ_CSV 函数的例子，该函数允许你指定列的类型：

import duckdb

# 连接到 DuckDB
conn = duckdb.connect()

# CSV 文件路径
excel_file_path = 'dev/周度产出（9.16-9.22）.csv'

# 指定列的类型
types = "监测日期 VARCHAR, 其他列名1 数据类型1, 其他列名2 数据类型2, ..."
# 注意：你需要替换 '其他列名1 数据类型1, 其他列名2 数据类型2, ...' 为你的 CSV 文件中实际的列名和对应的数据类型

# 使用 READ_CSV 函数读取 CSV 并指定类型
query = f"""
CREATE VIEW my_csv_view AS
SELECT *
FROM read_csv('{excel_file_path}', header=true, types='{types}');
"""

# 执行查询
conn.execute(query)

# 从视图中选择数据
df = conn.execute("SELECT * FROM my_csv_view").df()

# 关闭连接
conn.close()

# 现在 df 包含了你从 CSV 文件中读取的数据，并且列的类型已经按照你的指定进行了转换

注意：

1. 你需要将 types 字符串中的 '监测日期 VARCHAR, 其他列名1 数据类型1, 其他列名2 数据类型2, ...' 替换为你的 CSV 文件中实际的列名和对应的数据类型。
2. read_csv 函数的 header=true 参数假设你的 CSV 文件第一行是列名。如果不是，请将其设置为 false。
3. 使用 CREATE VIEW 是为了将读取的数据存储为一个视图，这样你就可以像查询普通表一样查询它。当然，你也可以直接将 read_csv 的结果赋值给一个 DataFrame，但这通常涉及到更复杂的 SQL 语句或 DuckDB 的 Python API 的其他用法。

确保你的 DuckDB 版本支持 read_csv 函数，因为这是一个相对较新的功能。如果你使用的是较旧的版本，可能需要更新 DuckDB 或使用其他方法来读取 CSV 并指定列类型。