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 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/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/75efd358-c953-4d77-9bd2-09cef98d44f1%40www.fastmail.com. For more options, visit https://groups.google.com/d/optout.