There are a few ways to do this in sqlalchemy, but I would probably handle 
this with a custom compiled function.  

there are also custom columns types 

here's an example of making a python function that generates sql to extract 
the year & week from a timestamp field. i use this for SELECTS, but it 
could also be used on inserts.

explaining the below:

* year_week becomes the python function that would be used in your python 
* year_week__default is the default sql compilation for it
* year_week__postgresql and year_week__sqllite are database specific 
compilers for the year_week function. they are registered to the 
`year_week` class by the @compiles decorator

    class year_week(expression.FunctionElement):
        type = sqlalchemy.types.String()
        name = 'year_week'

    def year_week__default(element, compiler, **kw):
        # select extract(week from timestamp_field) from table_a;
        week_num = sqlalchemy.sql.expression.extract('WEEK', 
        args = list(element.clauses)
        return "concat(extract(year from %s), '.', extract(week from %s)) " 
% (

    @compiles(year_week, 'postgresql')
    def year_week__postgresql(element, compiler, **kw):
        # select to_char(timestamp_field, 'YYYY.WW')  from table_a;
        week_num = sqlalchemy.func.to_char(mytable.timestamp_field, 
        args = list(element.clauses)
        return "to_char(%s, 'YYYY.WW')" % (

    @compiles(year_week, 'sqlite')
    def year_week__sqlite(element, compiler, **kw):
        # strftime('%Y.%W', cast(mytable.timestamp_field) as text)
        week_num = sqlalchemy.func.strftime('%Y.%W',
        args = list(element.clauses)
        return "strftime('%%Y.%%W', %s)" % (


SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See for a full description.
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
To post to this group, send email to
Visit this group at
For more options, visit

Reply via email to