On Mon, Jul 29, 2019, at 1:49 PM, peter bell wrote:
> 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)
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())
>
> # 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].
>> > 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/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
>
> <https://groups.google.com/d/msgid/sqlalchemy/543122f2-e282-44f4-8050-cf71b350721f%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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/d335a631-1e0e-440d-8c00-07775afaf1a6%40www.fastmail.com.
