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 
(http://docs.sqlalchemy.org/en/latest/core/custom_types.html#rounding-numerics)

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 
code.
* 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'


    @compiles(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', 
mytable.timestamp_field)
        """
        args = list(element.clauses)
        return "concat(extract(year from %s), '.', extract(week from %s)) " 
% (
            compiler.process(args[0]),
            compiler.process(args[0]),
        )


    @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, 
'YYYY.WW')
        """
        args = list(element.clauses)
        return "to_char(%s, 'YYYY.WW')" % (
            compiler.process(args[0]),
        )


    @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',
                                            sqlalchemy.cast(TABLE.COLUMN,
                                                            
sqlalchemy.Unicode
                                                            )
                                            )
        """
        args = list(element.clauses)
        return "strftime('%%Y.%%W', %s)" % (
            compiler.process(args[0]),
        )


 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Reply via email to