SQLAlchemy Core滑雪计时字符串转数值的优化及复用咨询

Hey there! Let's break down practical solutions for your two questions about converting time strings with SQLAlchemy Core:

1. A More Concise & Robust Conversion Method #

Your current code works for single-digit minutes, but it'll fail if you ever have times like 10:23.45 (two-digit minutes). A cleaner, more flexible approach uses SQL's split_part function to split the string at the colon, which handles any number of minutes digits:

from sqlalchemy import select, cast, func, Numeric

stmt = select([
    cast(func.split_part(result.c.timetot, ':', 1), Numeric) * 60 +
    cast(func.split_part(result.c.timetot, ':', 2), Numeric(6, 2))
])

If you're using a database that supports interval types (like PostgreSQL), you can make this even shorter by converting the string to an interval and extracting total seconds directly:

stmt = select([func.extract('epoch', func.cast(result.c.timetot, 'interval'))])

This returns a float representing total seconds automatically, no manual arithmetic needed. Just note this is database-specific—it won't work with all SQL databases (like MySQL, which handles intervals differently).

2. Reusable Function to Avoid Code Duplication #

Absolutely! You can wrap the conversion logic in a simple Python function that takes a SQLAlchemy column as input, then reuse it across all your time columns. This keeps your code DRY (Don't Repeat Yourself):

from sqlalchemy import cast, func, Numeric

def time_str_to_seconds(column):
    """Convert a 'MM:SS.ss' string column to total seconds as a float."""
    minutes = cast(func.split_part(column, ':', 1), Numeric)
    seconds = cast(func.split_part(column, ':', 2), Numeric(6, 2))
    return minutes * 60 + seconds

# Now use it for all your time columns:
stmt = select([
    time_str_to_seconds(result.c.timetot),
    time_str_to_seconds(result.c.race_time1),
    time_str_to_seconds(result.c.race_time2)
])

If you want to go a step further and define a proper SQL-level function (so the logic runs entirely in the database), you can use SQLAlchemy's GenericFunction to register a custom function, but the Python wrapper above is usually simpler for this use case.

内容的提问来源于stack exchange，提问作者Ichta