SQLAlchemy dialect support more SQL datatypes

[dungdm93]

This is a meta issue that tracks the support of SQL datatypes after #81 merged.

• [x] time & timestamp with precision (TIME(3) & TIMESTAMP(6)) https://github.com/trinodb/trino-python-client/pull/181
• [ ] INTERVAL types
• [ ] un-named ROW (e.g ROW(BIGINT, DOUBLE))
• [ ] IPADDRESS
• [ ] UUID
• [ ] HyperLogLog types
• [ ] QDigest
• [ ] TDigest

[MichaelTiemannOSC]

TIMESTAMP(6) definitely needs to be supported for pd.to_sql to work with datetype.datetime types (e.g. dtype='datetime64[ns]'). Test case:

# The variable `engine` is a SQLAlchemy engine connected to a Trino/Iceberg database with a `sandbox` schema available for table creation by the test user.
# The variable `connection ` is the engine's connection interface

import math
from datetime import datetime
drop_table = engine.execute(f"drop table if exists sandbox.test_timezone6")
drop_table.fetchall()
test_df = pd.DataFrame(
    {"A": [4.5], "B'C": [math.nan], None: [math.inf], "D": [-math.inf], "E": [datetime(2022, 1, 1)], ":F": [1.0]}
).convert_dtypes()
assert (test_df.dtypes == ['Float64', 'Int64', 'Float64', 'Float64', 'datetime64[ns]', 'Int64']).all()
xdf.to_sql("test_timezone6",
    con=connection,
    schema="sandbox",
    if_exists="append",
    index=False,
    method=osc.TrinoBatchInsert(batch_size=5, verbose=True),
)

This presently fails because Pandas converts the datatime64 data type to TIMESTAMP, which gets converted by SQLAlchemy/DBZPI to TIMESTAMP(3) instead of TIMESTAMP(6).

[erikerlandson]

@MichaelTiemannOSC does #181 resolve this?

[MichaelTiemannOSC]

There are changes to the correct functions in the code, so will test as soon as I clear out some other nuisances that are impeding my daily tech work. I'm optimistic the answer is yes...