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 <[email protected]>
> 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 <[email protected]>
> 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 [email protected].
> >>>> >> > To post to this group, send email to [email protected].
> >>>> >> > 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 [email protected].
> >>>> > To post to this group, send email to [email protected].
> >>>> > 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 [email protected].
> >>> To post to this group, send email to [email protected].
> >>> 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 [email protected] <javascript:>.
> > To post to this group, send email to [email protected]
> <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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/543122f2-e282-44f4-8050-cf71b350721f%40googlegroups.com.
