It seems that you would like to retrieve this value as a string so that you
can have precision that's not supported by Python datetime
Yes. If a table contains DATETIME2 columns, I would like to return all
those columns as a string.
I was able to achieve that by applying your StringDate class explicitly to
the 'created' column using table reflection :
t = Table('t', meta, Column('created', StringDate),autoload=True,
autoload_with=engine)
Is there a way to apply such a transformation to all DATETIME2 columns by
default, without explicitly naming them, as above ?
br
Peter
On Thursday, 18 July 2019 18:30:44 UTC+3, Mike Bayer wrote:
>
>
>
> On Thu, Jul 18, 2019, at 7:56 AM, peter bell wrote:
>
> You are correct - it seems the issue is in pyodbc
>
>
> but the pyodbc issue was fixed over a year ago. It seems that you
> would like to retrieve this value as a string so that you can have
> precision that's not supported by Python datetime, so that is not what the
> pyodbc issue addresses.
>
> For a canonical solution that won't break if pymssql ever changes this,
> you should use CAST:
>
> stmt = text("SELECT CAST(datecol AS VARCHAR) FROM t")
> stmt = stmt.columns(created=String)
>
> if you want to work with the SQL expression language you can make this
> cast automatic using column_expression documented at
> https://docs.sqlalchemy.org/en/13/core/custom_types.html#applying-sql-level-bind-result-processing
>
> from sqlalchemy import TypeDecorator, String
>
> class StringDate(TypeDecorator):
> impl = DATETIME2
>
> def column_expression(self, col):
> return cast(col, String)
>
>
>
>
>
>
> 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]> 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].
> > 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/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] <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/1303368e-d156-437c-9264-ff2aeab1ee5c%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/1303368e-d156-437c-9264-ff2aeab1ee5c%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
> 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/ce91c6c9-f95f-44aa-aece-0d215784a1ed%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
