connector-x: Reading date'9999-12-31' from Oracle leads to 'out of range DateTime' error
What language are you using?
Python
What version are you using?
3.9.13
What database are you using?
Oracle
What dataframe are you using?
polars
Can you describe your bug?
If reading date’9999-12-31’ from Oracle I get the error: ‘out of range DateTime’
What are the steps to reproduce the behavior?
Described here in details: https://github.com/pola-rs/polars/issues/16768
Database setup if the error only happens on specific data or data type
create table my_test nologging pctfree 0 as
select
'aaa' prod,
date'2000-01-01' start_date,
date'9999-12-31' end_date
from dual
Example query / code
import polars as pl
uri = f"oracle://{user}:{password}@{host}:{port}/{service_name}"
v_sql = "select * from my_test"
df = pl.read_database_uri(
v_sql, uri, engine="connectorx"
)
What is the error?
thread ‘<unnamed>’ panicked at /__w/connector-x/connector-x/connectorx/src/destinations/arrow2/arrow_assoc.rs:312:36: out of range DateTime
PanicException Traceback (most recent call last) /opt/conda/envs/Python-3.9-Premium/lib/python3.9/site-packages/polars/io/database/_utils.py in _read_sql_connectorx(query, connection_uri, partition_on, partition_range, partition_num, protocol, schema_overrides) 53 try: —> 54 tbl = cx.read_sql( 55 conn=connection_uri,
/opt/conda/envs/Python-3.9-Premium/lib/python3.9/site-packages/connectorx/init.py in read_sql(conn, query, return_type, protocol, partition_on, partition_range, partition_num, index_col) 385 –> 386 result = _read_sql( 387 conn,
PanicException: out of range DateTime
The above exception was the direct cause of the following exception:
PanicException Traceback (most recent call last) /tmp/1000780000/ipykernel_12937/202598540.py in <module> 4 v_sql = “select * from my_test” ----> 5 df = pl.read_database_uri( 6 v_sql, uri, engine=“connectorx”,
/opt/conda/envs/Python-3.9-Premium/lib/python3.9/site-packages/polars/io/database/functions.py in read_database_uri(query, uri, partition_on, partition_range, partition_num, protocol, engine, schema_overrides, execute_options)
417 msg = “the ‘connectorx’ engine does not support use of execute_options”
418 raise ValueError(msg)
–> 419 return _read_sql_connectorx(
420 query,
421 connection_uri=uri,
/opt/conda/envs/Python-3.9-Premium/lib/python3.9/site-packages/polars/io/database/_utils.py in _read_sql_connectorx(query, connection_uri, partition_on, partition_range, partition_num, protocol, schema_overrides) 64 # basic sanitisation of /user:pass/ credentials exposed in connectorx errs 65 errmsg = re.sub(“😕/[^:]+:[^:]+@”, “😕/:@”, str(err)) —> 66 raise type(err)(errmsg) from err 67 68 return from_arrow(tbl, schema_overrides=schema_overrides) # type: ignore[return-value]
PanicException: out of range DateTime
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Reactions: 1
- Comments: 16 (9 by maintainers)
Commits related to this issue
- adding microsecond timestamp type (postgres->arrow2), related to #644 #634 — committed to sfu-db/connector-x by wangxiaoying 2 years ago
- Merge pull request #646 from sfu-db/microsec Adding microsecond timestamp type, fix #644 #634 — committed to sfu-db/connector-x by wangxiaoying 2 years ago
- fix #644 — committed to sfu-db/connector-x by wangxiaoying 2 years ago
- fix #644 — committed to sfu-db/connector-x by wangxiaoying 2 years ago
Hi, same error with a MySQL db despite having version 0.4.0 installed. The date is ‘0001-01-01’, weird but correct as far as I know.
Someone in another issue wrote this: “This is a connectorx issue as it reads timestamps in nanoseconds (which it shouldn’t, as few databases actually support nanosecond precision - the majority top-out at microseconds).”
datetime.datetime(9999, 12, 31) is a valid date in python. How / when will connectorx support it?
Yeah, 0.3.4a2 solved the problem! Thx!
The data here is from a real database. Python version 3.11.8 return_type=
arrow2protocol=binarySending this to
read_sqlfails inconnectorx==0.3.3, but is successful inconnectorx==0.3.2This is the expected output.
Sending this to
read_sqlworks fine with both versions.