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())) On Thursday, 18 July 2019 21:42:16 UTC+3, Mike Bayer wrote: > > > > 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) > > > > > > > > br > > Peter > > > > > > On Thursday, 18 July 2019 18:30:44 UTC+3, Mike Bayer wrote: > >> > >> > >> 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) > >> > >> > >> > >> > >> > >>> > >>> 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') > >>> > >>> On Thursday, 18 July 2019 13:58:13 UTC+3, Simon King wrote: > >>>> 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 > >>>> > >>>> On Thu, Jul 18, 2019 at 11:43 AM peter bell <peterb...@gmail.com> > wrote: > >>>> > > >>>> > > >>>> > 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 > >>>> > > >>>> > > >>>> > > >>>> > > >>>> > > >>>> > > >>>> > On Thursday, 18 July 2019 13:02:16 UTC+3, Simon King wrote: > >>>> >> > >>>> >> 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 > >>>> >> > >>>> >> On Thu, Jul 18, 2019 at 10:44 AM peter bell <peterb...@gmail.com> > wrote: > >>>> >> > > >>>> >> > > >>>> >> > 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)) > >>>> >> > > >>>> >> > > >>>> >> > -- > >>>> >> > 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 sqlal...@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. > >>>> > > >>>> > -- > >>>> > 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 sqlal...@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. > > > >>>> > 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 sqlal...@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 > > < > 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 sqlal...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > 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>. > > > > 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/543122f2-e282-44f4-8050-cf71b350721f%40googlegroups.com.