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 <https://docs.python.org/3/library/datetime.html#datetime.datetime.year>, month <https://docs.python.org/3/library/datetime.html#datetime.datetime.month>, day <https://docs.python.org/3/library/datetime.html#datetime.datetime.day> , hour <https://docs.python.org/3/library/datetime.html#datetime.datetime.hour>, minute <https://docs.python.org/3/library/datetime.html#datetime.datetime.minute>, second <https://docs.python.org/3/library/datetime.html#datetime.datetime.second>, microsecond <https://docs.python.org/3/library/datetime.html#datetime.datetime.microsecond>, and tzinfo <https://docs.python.org/3/library/datetime.html#datetime.datetime.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 > <javascript:>> 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 <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/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 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/3ee27ff2-db56-4d77-ab2e-9ff82d5f60b8%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
