On Thu, Jul 18, 2019, at 1:27 PM, peter bell wrote:
>> 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 ?
there are mulitple contexts in which you may be concerned with when you say "by
default". if the autoload case is the one you want to address, then you can use
the column_reflect event:
https://docs.sqlalchemy.org/en/13/core/events.html?highlight=column_reflect#sqlalchemy.events.DDLEvents.column_reflect
from sqlalchemy.schema import Table
from sqlalchemy import event
def listen_for_reflect(inspector, table, column_info):
"receive a column_reflect event"
if isinstance(column_info['type'], DATETIME2):
column_info['type'] = StringDatetime
event.listen(
Table,
'column_reflect',
listen_for_reflect)
>
> 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].
>>> 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/ce91c6c9-f95f-44aa-aece-0d215784a1ed%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/ce91c6c9-f95f-44aa-aece-0d215784a1ed%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/e0bc9c44-55d0-461f-b6fe-befa10bc888c%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
