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.
