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 <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. 
>> > 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>.
>> >  
>> > 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
>  
> <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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d335a631-1e0e-440d-8c00-07775afaf1a6%40www.fastmail.com.

Reply via email to