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 ? 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 <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/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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@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. For more options, visit https://groups.google.com/d/optout.