JdbcLoad

atoti_jdbc.JdbcLoad(
    query: str,
    url: Annotated[str, AfterValidator(normalize_jdbc_url)],
    *,
    driver: Annotated[str | None, AfterValidator(_validate_driver)] = None,
    parameters: FrozenSequence[Constant] = (),
)

>>> from atoti_jdbc import JdbcLoad
>>> url = f"h2:{database_path};USER=root;PASSWORD=pass"
>>> jdbc_load = JdbcLoad("SELECT * FROM MYTABLE", url=url)

>>> data_types = session.tables.infer_data_types(jdbc_load)
>>> data_types
{'ID': 'int', 'CITY': 'String', 'MY_VALUE': 'long'}

>>> table = session.create_table("Cities", data_types=data_types, keys={"ID"})

>>> table.load(jdbc_load)
>>> table.head().sort_index()
        CITY  MY_VALUE
ID
1      Paris       100
2     London        80
3   New York        90
4     Berlin        70
5    Jakarta        75

>>> table.drop()
>>> table.load(
...     JdbcLoad(
...         "SELECT * FROM MYTABLE WHERE City IN (?, ?)",
...         parameters=["Paris", "New York"],
...         url=url,
...     )
... )
>>> table.head().sort_index()
        CITY  MY_VALUE
ID
1      Paris       100
3   New York        90

>>> import sqlite3
>>> query = "SELECT * FROM MYTABLE"
>>> url = f"sqlite:{database_path}"
>>> with sqlite3.connect(database_path) as connection:
...     pd.read_sql(query, connection).set_index("ID")
        CITY    MY_VALUE
ID
1      Paris         100
2     London          80
3   New York  wrong type
4     Berlin          70
5    Jakarta          75

• "fail" raises an error on the first invalid row and interrupts the load:
  >>> table.drop()
  >>> table += (0, "Rome", 42)
  >>> table.head()
      CITY  MY_VALUE
  ID
  0   Rome        42
  >>> jdbc_load = JdbcLoad(query, url=url)
  >>> with session.tables.data_transaction():  
  ...     table.load(jdbc_load, error_handling="fail")
  Traceback (most recent call last):
      ...
  py4j.protocol.Py4JError: ...Caused by: java.lang.ClassCastException: class java.lang.String cannot be cast to class java.lang.Number...
  >>> table.head()
      CITY  MY_VALUE
  ID
  0   Rome        42
  >>> exception_message = "Caused by: java.lang.ClassCastException: class java.lang.String cannot be cast to class java.lang.Number"
  >>> _count_transaction_exceptions(exception_message) > 0
  True
  
• "warn" skips the invalid rows and raises a Python warning:
  >>> from warnings import catch_warnings, simplefilter
  >>> table.drop()
  >>> with (
  ...     session.tables.data_transaction(),
  ...     catch_warnings(record=True) as warnings,
  ... ):
  ...     simplefilter("always")
  ...     table.load(jdbc_load, error_handling="warn")
  >>> [warning.message for warning in warnings]
  [UserWarning("Some errors occurred while loading data into t['Cities'].")]
  >>> table.head().sort_index()
         CITY  MY_VALUE
  ID
  1     Paris       100
  2    London        80
  4    Berlin        70
  5   Jakarta        75
  >>> _count_transaction_exceptions(exception_message) > 0
  True
  
• "log" skips the invalid rows and logs the errors:
  >>> table.drop()
  >>> with session.tables.data_transaction():
  ...     table.load(jdbc_load, error_handling="log")
  >>> table.head().sort_index()
         CITY  MY_VALUE
  ID
  1     Paris       100
  2    London        80
  4    Berlin        70
  5   Jakarta        75
  >>> _count_transaction_exceptions(exception_message) > 0
  True
  
• "skip" skips the invalid rows without logging:
  >>> table.drop()
  >>> with session.tables.data_transaction():
  ...     table.load(jdbc_load, error_handling="skip")
  >>> table.head().sort_index()
         CITY  MY_VALUE
  ID
  1     Paris       100
  2    London        80
  4    Berlin        70
  5   Jakarta        75
  >>> _count_transaction_exceptions(exception_message)
  0
  

​

Attributes

​

parameters

​

driver

​

url

• "h2:/home/user/database/file/path;USER=username;PASSWORD=passwd"
• "postgresql://postgresql.db.server:5430/example?user=username&password=passwd"

​

query