CODEWITHSUNDEEP
pythonsqlalchemy
Charles Duffy
Charles Duffy

Reputation: 295373

sqlalchemy: Making schema reflection find/use a custom type for all instances

I'm trying to override the SQLAlchemy DATETIME2 type (from MS SQL Server) to discard extra digits of subsecond precision if necessary to coerce to a native Python datetime type, as described in Replacing the Bind Result Processing of Existing Types section of the docs, like so:

import re
import sqlalchemy.dialects as sa_dialects

class TruncatingPrecisionDATETIME2(sa_dialects.mssql.base.DATETIME2):
    __visit_name__ = 'DATETIME2'
    _reg = re.compile(r"(\d+):(\d+):(\d+)(?:\.(\d{0,6})\d*)?")

    def process_result_value(self, value, dialect):
        if isinstance(value, util.string_types):
            return datetime.time(*[
                int(x or 0)
                for x in self._reg.match(value).groups()])
        else:
            return value

    def adapt(self, impltype):
        return TruncatingPrecisionDATETIME2()

However, when I call meta.reflect(), and inspect the type of one a column of type DATETIME2 in the database, it's an instance of sqlalchemy.dialects.mssql.base.DATETIME2 rather than TruncatingPrecisionDATETIME2. What do I need to do to change this globally for all DATETIME2 instances?

Upvotes: 3

Views: 676

Answers (1)

Dologan
Dologan

Reputation: 4774

This is probably unofficial as hell, and I'm not sure it would even work on MSSQL as opposed to PostgreSQL (as this changelog might seem to suggest), but by replacing the 'datetime2' item on the sqlalchemy.dialects.mssql.base.ischema_names dictionary by your custom subclass, you may be able to override the SQLAlchemy type used for all reflections of that type, if the official, recommended way of modifying result processing fails.

Upvotes: 1

Related Questions