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 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/d21f62a3-6327-42f6-b2a0-b698032ca859%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.