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].
> 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
>
> <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/75efd358-c953-4d77-9bd2-09cef98d44f1%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
