liveBook · Manning

Locale module

As explained in DuckDB in Action

The locale module is Python's standard-library module for handling locale-dependent cultural conventions such as numeric and date formatting.

The locale module handles regional numeric conventions when European decimal strings use commas instead of periods. This matters when DuckDB ingests those values as VARCHARs, blocking numerical analysis. section 6.4

Convert locale-formatted values in DuckDB

Define convert_locale to set the de_DE locale and apply locale.atof, then register it with DuckDB. Querying Coastline and Pop. Density converts values such as 0,00, 48,0, and 124,6 into 0.0, 48.0, and 124.6. ALTER TABLE can persist the converted DOUBLE values. section 6.4

con.sql("""
SELECT "Coastline (coast/area ratio)" AS coastline,
        convert_locale(coastline) as cleanCoastline,
        "Pop. Density (per sq. mi.)" as popDen,
        convert_locale(popDen) as cleanPopDen
FROM population
LIMIT 5
""")
┌───────────┬────────────────┬─────────┬─────────────┐
│ coastline │ cleanCoastline │ popDen  │ cleanPopDen │
│  varchar  │     double     │ varchar │   double    │
├───────────┼────────────────┼─────────┼─────────────┤
│ 0,00      │            0.0 │ 48,0    │        48.0 │
│ 1,26      │           1.26 │ 124,6   │       124.6 │
│ 0,04      │           0.04 │ 13,8    │        13.8 │
│ 58,29     │          58.29 │ 290,4   │       290.4 │
│ 0,00      │            0.0 │ 152,1   │       152.1 │
└───────────┴────────────────┴─────────┴─────────────┘

Inspect the original VARCHAR values beside their locale-aware DOUBLE conversions. section 6.4

How DuckDB in Action covers it

How deep it goes
Demonstrates locale-aware conversion, DuckDB registration, inspection, and ALTER TABLE persistence, then leaves additional columns as an exercise.
Examples
Uses European-formatted Coastline and Pop. Density fields, including values such as 0,00 and 124,6.
What it assumes
Assumes basic Python and DuckDB usage while demonstrating how to bridge Python functions with SQL queries.
What you can do after
Apply it via Python and DuckDB to convert locale-formatted strings and persist them as numeric columns.
How it connects
Connects Python, DuckDB, SQL, user-defined functions, and numeric type conversion through data cleaning.
Cover of DuckDB in Action
liveBookmanning.com
Read DuckDB in Action on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.