loss of precision when retrieving DATETIME2 column from MSSQL

[peter bell]

I am new to sqlalchemy and I am trying to retrieve results from a table containing a DATETIME2 column in a SQL Server database.  A SQL Server DATETIME2 column includes a seven-digit number from 0 to 9999999 that represents the fractional seconds.

When I retrieve the results of the table into sqlalchemy (version 1.3.5), the DATETIME2 column seems to be mapped to a python datetime object (which only has a precision of 6 digits)

Is there anyway I can avoid this loss of precision ??

Here's a simple test case to demonstrate - In my SQL Server database :

drop table if exists t

create table t (id int, created datetime2 default sysutcdatetime());

insert into t (id) values (1)

If I select from my table in SQL Server, the fractional seconds has 7 digits :

2019-07-18 09:37:05.2347191

Here's my python code using sqlalchemy version 1.3.5 :

import urllib

from sqlalchemy import *

from sqlalchemy.dialects.mssql import \

    BIGINT, BINARY, BIT, CHAR, DATE, DATETIME, DATETIME2, \

    DATETIMEOFFSET, DECIMAL, FLOAT, IMAGE, INTEGER, MONEY, \

    NCHAR, NTEXT, NUMERIC, NVARCHAR, REAL, SMALLDATETIME, \

    SMALLINT, SMALLMONEY, SQL_VARIANT, TEXT, TIME, \

    TIMESTAMP, TINYINT, UNIQUEIDENTIFIER, VARBINARY, VARCHAR

params = 'DRIVER={SQL Server Native Client 11.0};' \

         'SERVER=MyDbServer;' \

         'PORT=1433;' \

         'DATABASE=MyDb;' \

         'UID=MyUser;' \

         'PWD=MyPwd;'

params = urllib.parse.quote_plus(params)

# engine to the source database

engine = create_engine('mssql+pyodbc:///?odbc_connect=%s' % params)

# select from t

stmt = text("SELECT * FROM t")

# specify return data type of columns

stmt = stmt.columns(created=DATETIME2)

# connection object

conn = engine.connect()

# run stmt

result = conn.execute(stmt)

# print results

for row in result:

    print(row)

The results in the following out (the last digit of the datetime2 column is lost) :

(1, datetime.datetime(2019, 7, 18, 9, 37, 5, 234719))

[Simon King]

I've never used SQL Server or ODBC, but I wonder if this is a pyodbc issue:

https://github.com/mkleehammer/pyodbc/issues/235

Do you have the same problem if you use pyodbc directly, rather than SQLAlchemy?

Simon

> --
> 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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d21f62a3-6327-42f6-b2a0-b698032ca859%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[peter bell]

I think the issue is more fundamental than that.

Based on the output in my test program, the mssql DATETIME2 column is being mapped to the Python datetime data type.  

Based on the documentation (https://docs.python.org/3/library/datetime.html), that data type can only hold fractional seconds to microsecond precision (6 digits)

class datetime.datetime

A combination of a date and a time. Attributes: year, month, day, hour, minute, second, microsecond, and tzinfo.

So, is there anyway in SQLAlchemy to map the results of a query to a data type other than Python's datetime.datetime ?

regards

Peter

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlal...@googlegroups.com.

[Simon King]

Right, but I don't know if the conversion from number to datetime is
being done by SQLAlchemy or pyodbc. If it's pyodbc, then you'll need
to find the fix there, rather than in SQLAlchemy.

Simon

> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3ee27ff2-db56-4d77-ab2e-9ff82d5f60b8%40googlegroups.com.

[peter bell]

You are correct - it seems the issue is in pyodbc

I installed pymssql and used that when creating the sqlalchemy engine object.

The DATETIME2 column is now mapped to a string (which has all 7 digits preserved)

Thanks for your help,

br

Peter

So this :

from sqlalchemy import *

URL = "mssql+pymssql://MyUser:MyPwd@MyServer/Mydb"

# engine to the source database

engine = create_engine(URL)

# select from t

stmt = text("SELECT * FROM t")

# connection object

conn = engine.connect()

# run stmt

result = conn.execute(stmt)

# print results

for row in result:

    print(row)

Produces this :

(1, '2019-07-18 09:37:05.2347191')

[Mike Bayer]

On Thu, Jul 18, 2019, at 7:56 AM, peter bell wrote:

You are correct - it seems the issue is in pyodbc

but the pyodbc issue was fixed over a year ago.     It seems that you would like to retrieve this value as a string so that you can have precision that's not supported by Python datetime, so that is not what the pyodbc issue addresses.

For a canonical solution that won't break if pymssql ever changes this, you should use CAST:

stmt = text("SELECT CAST(datecol AS VARCHAR) FROM t")

stmt = stmt.columns(created=String)

if you want to work with the SQL expression language you can make this cast automatic using column_expression documented at https://docs.sqlalchemy.org/en/13/core/custom_types.html#applying-sql-level-bind-result-processing

from sqlalchemy import TypeDecorator, String

class StringDate(TypeDecorator):

    impl = DATETIME2

    def column_expression(self, col):

        return cast(col, String)

  

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1303368e-d156-437c-9264-ff2aeab1ee5c%40googlegroups.com.

[peter bell]

It seems that you would like to retrieve this value as a string so that you can have precision that's not supported by Python datetime

Yes. If a table contains DATETIME2 columns, I would like to return all those columns as a string. 

I was able to achieve that by applying your StringDate class explicitly to the 'created' column using table reflection :

t = Table('t', meta, Column('created', StringDate),autoload=True, autoload_with=engine)

Is there a way to apply such a transformation to all DATETIME2 columns by default, without explicitly naming them, as above ?

br

Peter

[Mike Bayer]

On Thu, Jul 18, 2019, at 1:27 PM, peter bell wrote:
>> It seems that you would like to retrieve this value as a string so that you can have precision that's not supported by Python datetime
>
> Yes. If a table contains DATETIME2 columns, I would like to return all those columns as a string.
>
> I was able to achieve that by applying your StringDate class explicitly to the 'created' column using table reflection :
>
> t = Table('t', meta, Column('created', StringDate),autoload=True, autoload_with=engine)
>
> Is there a way to apply such a transformation to all DATETIME2 columns by default, without explicitly naming them, as above ?

there are mulitple contexts in which you may be concerned with when you say "by default". if the autoload case is the one you want to address, then you can use the column_reflect event:

https://docs.sqlalchemy.org/en/13/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect

from sqlalchemy.schema import Table
from sqlalchemy import event

def listen_for_reflect(inspector, table, column_info):
"receive a column_reflect event"
if isinstance(column_info['type'], DATETIME2):
column_info['type'] = StringDatetime

event.listen(
Table,
'column_reflect',
listen_for_reflect)

>>> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/1303368e-d156-437c-9264-ff2aeab1ee5c%40googlegroups.com <https://groups.google.com/d/msgid/sqlalchemy/1303368e-d156-437c-9264-ff2aeab1ee5c%40googlegroups.com?utm_medium=email&utm_source=footer>.

>>> For more options, visit https://groups.google.com/d/optout.
>>
>

> --
> 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+...@googlegroups.com.

> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ce91c6c9-f95f-44aa-aece-0d215784a1ed%40googlegroups.com <https://groups.google.com/d/msgid/sqlalchemy/ce91c6c9-f95f-44aa-aece-0d215784a1ed%40googlegroups.com?utm_medium=email&utm_source=footer>.

[peter bell]

A belated thank you for your response.

This worked fine for individual tables but I got an unexpected result (at least, unexpected to me) when using this approach with the union or union_all functions.

The TypeDecorator was only applied to the first table in the union / union_all.  I'm sure I can workaround this (but just thought I'd let you know)

Example code below.

Regards,

Peter

from sqlalchemy import (create_engine, TypeDecorator, String, Integer, event, MetaData, cast)

from sqlalchemy.dialects.mssql import DATETIME2

from sqlalchemy.schema import (Table, Column)

# TypeDecorator to cast DATETIME2 columns to String

class StringDate(TypeDecorator):

    impl = DATETIME2

    def column_expression(self, col):

        return cast(col, String)

# event listener to apply StringDate on Table reflection

def listen_for_reflect (inspector, table, column_info):

    "receive a column reflect event"

    if isinstance(column_info['type'],DATETIME2):

        column_info['type'] = StringDate

event.listen(Table,'column_reflect',listen_for_reflect)

engine = create_engine("some_db_url")

# create test tables (to mimic mssql temporal tables)

meta = MetaData()

Table('t', meta,

    Column('Id', Integer, primary_key=True),

    Column('SysVerStart', DATETIME2),

    Column('SysVerEnd', DATETIME2)

    )

Table('t_history', meta,

    Column('Id', Integer),

    Column('SysVerStart', DATETIME2),

    Column('SysVerEnd', DATETIME2)

    )

# create tables in our database

meta.create_all(engine)

# generate select statements using table reflection

meta.clear()

t = Table('t', meta, autoload=True, autoload_with=engine)

t_history = Table('t_history', meta, autoload=True, autoload_with=engine)

print('****************** StringDate TypeDecorator applied as expected to t :')

print(t.select())

print('****************** StringDate TypeDecorator applied as expected to t_history :')

print(t_history.select())

print('****************** StringDate TypeDecorator only applied to the first table in a union_all :')

print(t.select().union_all(t_history.select()))

print('****************** StringDate TypeDecorator only applied to the first table in a union :')

print(t_history.select().union(t.select()))

[Mike Bayer]

this would be an entirely new SQLAlchemy issue unrelated to reflection which is that TypeEngine.column_expression is not applied to subsequent SELECTs in a UNION even those SELECTs are at the top level of the query.

https://github.com/sqlalchemy/sqlalchemy/issues/4787 is added.

Workaround is to SELECT from your SELECT, which is what the ORM does in any case but w/ Core you need to add this:

print(t.select().union_all(t_history.select()).select())

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/543122f2-e282-44f4-8050-cf71b350721f%40googlegroups.com.