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.