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 <[email protected]> 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. 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/CAFHwexc6N_SfMKMMXVdwgVpC7gR%3DR0wDB4icO13%2B-WrZ4m56zQ%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
