You are correct - it seems the issue is in pyodbc
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 <[email protected]
> <javascript:>> 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 <[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] <javascript:>.
> > To post to this group, send email to [email protected]
> <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/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 [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/1303368e-d156-437c-9264-ff2aeab1ee5c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
