Re: [sqlalchemy] Regression for sub-second precision when inserting timestamps into Oracle database

[Mike Bayer]

On Mon, Jan 8, 2018 at 12:55 PM, Stefan Schwarzer
 wrote:
> Hello,
>
> I've run into a problem that's present in SQLAlchemy 1.2, but not 1.1.15
> when run against an Oracle 11.2 database using cx_Oracle 6.1.

OK, I had no idea oracle had any fractional seconds support for the
TIMESTAMP variety and this has never been tested.   issue is at
https://bitbucket.org/zzzeek/sqlalchemy/issues/4157/restore-setinputsizes-for
which also adds test support in the associated gerrit.

>
> When creating the table `TIMESTAMP_TEST` with
>
> CREATE TABLE TIMESTAMP_TEST (
>   TS TIMESTAMP(6)
> )
>
> and running the following code
>
> import datetime
> import sys
>
> import sqlalchemy as sa
>
>
> def test(user, password, host, port, database_name):
> # Setup
> metadata = sa.MetaData()
> connect_string = "oracle+cx_oracle://{}:{}@{}:{}/{}".format(user,
> password, host, port, database_name)
> engine = sa.create_engine(connect_string)
> timestamp_table = sa.Table("timestamp_test", metadata, sa.Column("ts",
> sa.TIMESTAMP(timezone=False)))
> # Insert
> insert = timestamp_table.insert().values(ts=datetime.datetime.now())
> print("insert:", insert)
> print("insert params:", insert.compile().params)
> with engine.connect() as connection:
> connection.execute(insert)
>
>
> # Get database connection details.
> ...
> test(user, password, host, port, database_name)
>
> fractional seconds aren't present in the inserted timestamp when the code
> runs with SQLAlchemy 1.2. The fractional seconds are present when the code
> runs against SQLAlchemy 1.1.15. In both cases the version of cx_Oracle is
> 6.1. The behavior is the same when using `sa.dialects.oracle.TIMESTAMP`
> instead of `sa.TIMESTAMP`.
>
> Possibly this is to do with the changes described here:
>
> https://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#dialect-improvements-and-changes-oracle
>
> The documentation says that the `cursor.setinputsizes` calls were removed.
>
> http://www.oracle.com/technetwork/articles/dsl/prez-python-timesanddates-093014.html
>
> states that `cursor.setinputsizes` must be used to be able to insert
> timestamps with sub-second precision into an Oracle database database with
> cx_Oracle. Now, this article is pretty old, so I don't know how relevant
> this still is.
>
> Related tickets are possibly
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/304/oracle-timestamp-with-sub-second
> https://bitbucket.org/zzzeek/sqlalchemy/issues/604/oracle-sub-second-timestamp-handling
>
> "Related" in the sense that they describe or refer to what presumably must
> be done to get the sub-second precision upon insertion. Then again, these
> tickets are old, but I wanted to mention them in case they help.
>
> Best regards,
> Stefan
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Regression for sub-second precision when inserting timestamps into Oracle database

[Stefan Schwarzer]

Hello,

I've run into a problem that's present in SQLAlchemy 1.2, but not 1.1.15 
when run against an Oracle 11.2 database using cx_Oracle 6.1.

When creating the table `TIMESTAMP_TEST` with

CREATE TABLE TIMESTAMP_TEST (
  TS TIMESTAMP(6)
)

and running the following code

import datetime
import sys

import sqlalchemy as sa


def test(user, password, host, port, database_name):
# Setup
metadata = sa.MetaData()
connect_string = "oracle+cx_oracle://{}:{}@{}:{}/{}".format(user, 
password, host, port, database_name)
engine = sa.create_engine(connect_string)
timestamp_table = sa.Table("timestamp_test", metadata, sa.Column("ts", 
sa.TIMESTAMP(timezone=False)))
# Insert
insert = timestamp_table.insert().values(ts=datetime.datetime.now())
print("insert:", insert)
print("insert params:", insert.compile().params)
with engine.connect() as connection:
connection.execute(insert)


# Get database connection details.
...
test(user, password, host, port, database_name)

fractional seconds *aren't* present in the inserted timestamp when the code 
runs with SQLAlchemy 1.2. The fractional seconds *are* present when the 
code runs against SQLAlchemy 1.1.15. In both cases the version of cx_Oracle 
is 6.1. The behavior is the same when using `sa.dialects.oracle.TIMESTAMP` 
instead of `sa.TIMESTAMP`.

Possibly this is to do with the changes described here:

https://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#dialect-improvements-and-changes-oracle

The documentation says that the `cursor.setinputsizes` calls were removed.

http://www.oracle.com/technetwork/articles/dsl/prez-python-timesanddates-093014.html

states that `cursor.setinputsizes` must be used to be able to insert 
timestamps with sub-second precision into an Oracle database database with 
cx_Oracle. Now, this article is pretty old, so I don't know how relevant 
this still is.

Related tickets are possibly

https://bitbucket.org/zzzeek/sqlalchemy/issues/304/oracle-timestamp-with-sub-second
https://bitbucket.org/zzzeek/sqlalchemy/issues/604/oracle-sub-second-timestamp-handling

"Related" in the sense that they describe or refer to what presumably must 
be done to get the sub-second precision upon insertion. Then again, these 
tickets are old, but I wanted to mention them in case they help.

Best regards,
Stefan

-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.