Using DB-API 2.0#
PyExasol provides a PEP 249-compatible DB-API 2.0 adapter in the exasol.driver.websocket.dbapi2
module. The adapter does not strictly follow the specification. This page
introduces the adapter, explains the reasons for its intentional differences,
and describes alternative drivers when they are a better fit for your application.
The SQLAlchemy-Exasol dialect uses this adapter to connect SQLAlchemy applications to Exasol.
Alternatives#
Exasol WebSocket Driver#
The pyexasol package includes this DB-API 2.0 adapter in the
exasol.driver.websocket.dbapi2 module. However, using pyexasol
directly will generally yield better performance when utilizing Exasol in an
OLAP manner, which is likely the typical use case.
In specific scenarios, the DB-API 2.0 interface can be advantageous or even necessary, particularly when integrating with database-agnostic frameworks.
TurboODBC#
TurboODBC offers an alternative ODBC-based, DBAPI2-compatible driver, which supports the Exasol database.
Pyodbc#
Pyodbc provides an ODBC-based, DBAPI2-compatible driver. For further details, please refer to the wiki.
Rationale#
PEP 249 was originally created for general purpose OLTP row store databases running on a single server: SQLite, MySQL, PostgreSQL, MSSQL, Oracle, etc.
It does not work very well for OLAP columnar databases (like Exasol) running on multiple servers because it was never designed for this purpose. Despite both OLTP DBMS and OLAP DBMS using SQL for communication, the foundation and usage patterns are completely different.
When people use DB-API 2.0 drivers, they tend to skip manuals and automatically apply OLTP usage patterns without even realizing how much they lose in terms of performance and efficiency.
A good example is TurboODBC. Very few know that it is possible to fetch data as NumPy arrays and as Apache Arrow.
Minor intentional incompatibilities with DB-API 2.0 force users to look through the manual and to learn about Best Practices of getting the job done.
Usage Notes#
from exasol.driver.websocket.dbapi2 import connect
connection = connect(dsn='', username='sys', password='exasol', schema='TEST')
with connection.cursor() as cursor:
cursor.execute("SELECT 1;")
See the Using DB-API 2.0 example for a longer usage example.
Fetching Data#
The WebSocket DBAPI converts fetched values with the fetch_mapper connection
option. The Python types you receive are determined by the default mapper;
applications can replace it with a custom mapper.
Default Types#
The default fetch_mapper is
pyexasol.data_types.convert_websocket_to_python(). It converts values to
Python types according to the result column’s WebSocket metadata. To align more
closely with PEP 249, it returns values as Python types whenever possible. Some
values were already returned as Python types before version 3.0; version 3.0
made this behavior more consistent.
Note
NULLvalues are returned asNonefor all types.Nanosecond precision in
TIMESTAMPvalues is truncated to Python’s microsecond precision.
Exasol type |
Version < 3.0 |
Version >= 3.0 |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Custom Mapper#
Instead of the default mapper described above, applications can provide a custom
mapper through the fetch_mapper connection option. The mapper receives two
positional arguments: the raw scalar value and the column’s WebSocket dataType
metadata. See the WebSocket API data type definition. Use the metadata’s
type value to determine how to convert the value.
from pyexasol.data_types import WebSocketDataType
def fetch_mapper(value, data_type):
if data_type["type"] == WebSocketDataType.Decimal.value:
return float(value)
return value
Note
When using a custom fetch_mapper, the application is responsible for
supporting the session date/time formats required by that mapper. The DBAPI
only validates the session formats when the default mapper is active.
Inserting Data#
The WebSocket DBAPI converts and validates the parameter values passed to
executemany with the insert_mapper connection option. The Python types
accepted per column type are determined by the default mapper; applications can
replace it with a custom mapper.
Default Types#
The default insert_mapper is
pyexasol.data_types.convert_python_to_websocket(). It converts each value
according to the target column’s WebSocket metadata. Before anything is sent to
the database, executemany checks every row and raises:
a
ProgrammingErrorif a row has a different number of values than the statement has columns. The error gives the row number and both counts.a
DataErrorif a value’s Python type is not accepted for its column type. The error gives the row number, the column location, the column type and the allowed types.
Both errors are raised for the first invalid entry only, so other invalid entries are not reported. If you get one of these errors, check your data directly to make sure that all rows have the expected number of values and that each value has a type accepted for its column.
Note
Noneis accepted for all types.Only the types are validated. Precision, scale and length are not checked and remain the responsibility of the database.
A naive
datetime.datetimeis assumed to be exactly the time the database should store and is inserted in ISO format. A timezone-awaredatetime.datetimefor aDATE,TIMESTAMPorTIMESTAMP WITH LOCAL TIME ZONEcolumn raises aDataError, because these columns have no timezone and the offset cannot be sent. Convert the value explicitly first, e.g. withvalue.astimezone(datetime.UTC).replace(tzinfo=None)for aTIMESTAMP.Values bound to a
VARCHARcolumn must bestr. Before version 4.0, any value was silently converted withstr(), which can hide a mismatch between the values and the columns they are inserted into. Convert values explicitly withstr(...)where a string is desired.
Exasol type |
Version < 4.0 |
Version >= 4.0 |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
any value accepted by |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
naive |
|
|
naive |
|
any value (converted with |
|
Custom Mapper#
Instead of the default mapper described above, applications can provide a custom
mapper through the insert_mapper connection option. The mapper receives two
positional arguments: the Python parameter value and the column’s WebSocket
dataType metadata, and returns the value to send. See the
WebSocket API data type definition. Use the metadata’s type value to
determine how to convert the value.
from pyexasol.data_types import WebSocketDataType
def insert_mapper(value, data_type):
if data_type["type"] == WebSocketDataType.String.value:
return str(value)
return value
The mapper’s output must still be a type accepted by
validate_value_for_websocket for the column (e.g. float for DOUBLE,
str for VARCHAR), otherwise a DataError is raised. This validation
gives more specific type errors than the database would.
Note
When using a custom insert_mapper, executemany no longer validates
the session’s date and timestamp formats. execute still validates them
unless a custom fetch_mapper is also provided, so both mappers must be
custom for the formats to be changeable. The application is then responsible
for ensuring that the custom mappers support the session’s formats.
Session Date/Time Formats#
The default mappers depend on the session’s NLS_DATE_FORMAT and
NLS_TIMESTAMP_FORMAT, which are listed in EXA_PARAMETERS. The WebSocket
DBAPI therefore validates both values, but only when the relevant default mapper
is active:
Before executing a user statement, if the default
fetch_mapperis active.Before calling
executemany, if the defaultinsert_mapperis active.
The DBAPI currently supports only the exact formats listed below. Format-model matching is case-sensitive.
The supported values are represented by the following definitions in the WebSocket-to-Python conversion module:
SUPPORTED_DATE_FORMATS: Final[tuple[str, ...]] = ("YYYY-MM-DD",)
SUPPORTED_TIMESTAMP_FORMATS: Final[tuple[str, ...]] = (
"YYYY-MM-DD HH24:MI:SS",
"YYYY-MM-DD HH24:MI:SS.FF1",
"YYYY-MM-DD HH24:MI:SS.FF2",
"YYYY-MM-DD HH24:MI:SS.FF3",
"YYYY-MM-DD HH24:MI:SS.FF4",
"YYYY-MM-DD HH24:MI:SS.FF5",
"YYYY-MM-DD HH24:MI:SS.FF6",
"YYYY-MM-DD HH24:MI:SS.FF7",
"YYYY-MM-DD HH24:MI:SS.FF8",
"YYYY-MM-DD HH24:MI:SS.FF9",
)
Statements containing ALTER SESSION are allowed so applications can change
these settings. If either format is unsupported, execution raises one
InterfaceError containing all invalid parameters, their supported formats,
and an example ALTER SESSION statement showing where to insert a supported
format.
Exasol Specific Problems with DB-API 2.0#
Default
autocommit=offprevents indexes from being stored permanently on disk forSELECTstatements;Default
autocommit=offmay hold transaction for a long time (e.g., opened connection in an IPython notebook);Python object creation and destruction overhead is very significant when you process large amounts of data;
Functions
fetchmany()andexecutemany()have significant additional overhead related to JSON serialization;Exasol WebSocket protocol provides more information about columns than is normally available in
.descriptionproperty ofcursor;
We also wanted to discourage:
“Drop-in” replacements of other Exasol drivers without reading the manual;
Usage of OLTP-oriented ORM (e.g., SQLAlchemy, Django);
Unlike common OLTP databases, each OLAP database is very unique. It is important to understand implementation details and features of the specific database and to build applications around those features. Generalization of any kind and the “copy-paste” approach may lead to abysmal performance in trivial cases.
Ideas for Migration#
Find cursor() calls:
cur = C.cursor()
cur.execute('SELECT * FROM table')
data = cur.fetchall()
Replace with:
st = C.execute('SELECT * FROM table')
data = st.fetchall()
Find .description
columns = list(map(str.lower, next(zip(*cur.description))))
Replace with:
columns = st.column_names()
Find all reads into pandas:
cur.execute('SELECT * FROM table')
pandas.DataFrame(cur.fetchall(), columns=columns)
Replace with:
C.export_to_pandas('SELECT * FROM table')