exasol / sqlalchemy-exasol Goto Github PK

View Code? Open in Web Editor NEW

35.0 15.0 28.0 8.93 MB

SQLAlchemy dialect for EXASOL

Home Page: https://exasol.github.io/sqlalchemy-exasol/

License: Other

Python 100.00%

exasol python odbc sqlalchemy pyodbc turbodbc exasol-integration foundation-library

sqlalchemy-exasol's Introduction

SQLAlchemy Dialect for EXASOL DB

Getting Started with SQLAlchemy-Exasol

SQLAlchemy-Exasol supports multiple dialects, primarily differentiated by whether they are ODBC or Websocket based.

Choosing a Dialect

We recommend using the Websocket-based dialect due to its simplicity. ODBC-based dialects demand a thorough understanding of (Unix)ODBC, and the setup is considerably more complex.

Warning

The maintenance of Turbodbc support is currently paused, and it may be phased out in future versions. We are also planning to phase out the pyodbc support in the future.

System Requirements

Python
An Exasol DB (e.g. docker-db or a cloud instance)

Note

For ODBC-Based Dialects, additional libraries required for ODBC are necessary (for further details, checkout the developer guide).

Setting Up Your Python Project

Install SQLAlchemy-Exasol:

$ pip install sqlalchemy-exasol

Note

To use an ODBC-based dialect, you must specify it as an extra during installation.

pip install "sqlalchemy-exasol[pydobc]"
pip install "sqlalchemy-exasol[turbodbc]"

Using SQLAlchemy with EXASOL DB

Websocket based Dialect:

from sqlalchemy import create_engine
url = "exa+websocket://A_USER:[email protected]:1234/my_schema?CONNECTIONLCALL=en_US.UTF-8"
e = create_engine(url)
r = e.execute("select 42 from dual").fetchall()

Examples:

from sqlalchemy import create_engine

engine = create_engine("exa+websocket://sys:[email protected]:8888")
with engine.connect() as con:
    ...

from sqlalchemy import create_engine

# ATTENTION:
# In terms of security it is NEVER a good idea to turn of certificate validation!!
# In rare cases it may be handy for non-security related reasons.
# That said, if you are not a 100% sure about your scenario, stick with the
# secure defaults.
# In most cases, having a valid certificate and/or configuring the truststore(s)
# appropriately is the best/correct solution.
engine = create_engine("exa+websocket://sys:[email protected]:8888?SSLCertificate=SSL_VERIFY_NONE")
with engine.connect() as con:
    ...

Pyodbc (ODBC based Dialect):

from sqlalchemy import create_engine
url = "exa+pyodbc://A_USER:[email protected]:1234/my_schema?CONNECTIONLCALL=en_US.UTF-8&driver=EXAODBC"
e = create_engine(url)
r = e.execute("select 42 from dual").fetchall()

Turbodbc (ODBC based Dialect):

from sqlalchemy import create_engine
url = "exa+turbodbc://A_USER:[email protected]:1234/my_schema?CONNECTIONLCALL=en_US.UTF-8&driver=EXAODBC"
e = create_engine(url)
r = e.execute("select 42 from dual").fetchall()

Features

SELECT, INSERT, UPDATE, DELETE statements

General Notes

Schema name and parameters are optional for the host url
At least on Linux/Unix systems it has proven valuable to pass 'CONNECTIONLCALL=en_US.UTF-8' as a url parameter. This will make sure that the client process (Python) and the EXASOL driver (UTF-8 internal) know how to interpret code pages correctly.
Always use all lower-case identifiers for schema, table and column names. SQLAlchemy treats all lower-case identifiers as case-insensitive, the dialect takes care of transforming the identifier into a case-insensitive representation of the specific database (in case of EXASol this is upper-case as for Oracle)
As of Exasol client driver version 4.1.2 you can pass the flag 'INTTYPESINRESULTSIFPOSSIBLE=y' in the connection string (or configure it in your DSN). This will convert DECIMAL data types to Integer-like data types. Creating integers is a factor three faster in Python than creating Decimals.

Known Issues

Insert
- Insert multiple empty rows via prepared statements does not work in all cases

Development & Testing

See developer guide

sqlalchemy-exasol's People

Contributors

Stargazers

Watchers

sqlalchemy-exasol's Issues

Converting to Github Actions or Azure Pipelines.

I notice that the CI seems to having issues on Travis at the moment.
I think part of it might stem from the need for the tests to hit a server that is run externally to the tests.

Would you be open to running the tests in Github Actions or Azure Pipelines, since I think those services would allow us to start an Exasol docker container alongside the tests against which the tests could be run.

If that's something you'd be willing to consider, I'll look into figuring out how to get that working.

Bump exasol client driver version to 5.x

With the release of DB version 5.x the client driver should be bumped to the newest version. In this context, check if the renaming of the client driver so file can be removed.

Dialect does not autocommit for TRUNCATE Table Statement

The Exasolution database supports the TRUNCATE Table Statement, but this data changing operation is not properly handled (i.e. no commit is executed) when executed with autocommit=True (default in create_engine).

from sqlalchemy import *
dsn = "exa+pyodbc://sys:exasol@localhost/"
db = create_engine(dsn)

con = db.connect()
con.execute("create table test.test(id integer, val varchar(50))")
con.execute("insert into test.test values(1, 'foo');")

con = db.connect()
assert con.execute("select * from test.test").fetchall() ==  [(1L, 'foo')]

con = db.connect()
con.execute("truncate table test.test")

con = db.connect()
assert con.execute("select * from test.test").fetchall() == []  ### Fails

See:
http://docs.sqlalchemy.org/en/latest/core/connections.html#understanding-autocommit

Fix:
Overwrite https://github.com/zzzeek/sqlalchemy/blob/b9d0e2d5c55f68193fae001bb553a51018ab8ed6/lib/sqlalchemy/engine/default.py#L780 in dialect.

Add support for sqlalchemy >= 1.0.0

SQLAlchemy has released version 1.0.0 http://www.sqlalchemy.org/blog/2015/04/16/sqlalchemy-1.0.0-released/

Test Suite needs to be fixed/adapted https://travis-ci.org/blue-yonder/sqlalchemy_exasol/builds/58815635

Pull request for SQLAlchemy to add millisecond precision to test suite

The SQLAlchemy test suite has test cases for timestamps with precision up to seconds and up to microseconds. EXASOL supports milliseconds.
Ask on SQLA mailing list if extending the test suite will be accepted. If yes, add a pull request.

Support for DISTRIBUTE BY

Support DISTRIBUTE BY for tables to control distribution of table records within the cluster.

Add coveralls support

Add support for coveralls (https://coveralls.io/docs) and show coverage status in README.md

Sqlalchemy Failure on OSX with python 3.6

Sqlalchemy fails to excecute a simple statement on osx with turbodbc

e = create_engine("..")
r = e.execute(u"select 42 from dual").fetchall()
print(r)

  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1122, in _do_get
    return self._pool.get(wait, self._timeout)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/util/queue.py", line 145, in get
    raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "test_sqla.py", line 4, in <module>
    r = e.execute(u"select 42 from dual").fetchall()
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2062, in execute
    connection = self.contextual_connect(close_with_result=True)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2111, in contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2146, in _wrap_pool_connect
    return fn()
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/pool.py", line 387, in connect
    return _ConnectionFairy._checkout(self)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/pool.py", line 766, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/pool.py", line 516, in checkout
    rec = pool._do_get()
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1138, in _do_get
    self._dec_overflow()
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1135, in _do_get
    return self._create_connection()
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/pool.py", line 333, in _create_connection
    return _ConnectionRecord(self)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/pool.py", line 461, in __init__
    self.__connect(first_connect_check=True)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/pool.py", line 661, in __connect
    exec_once(self.connection, self)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/event/attr.py", line 246, in exec_once
    self(*args, **kw)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/event/attr.py", line 256, in __call__
    fn(*args, **kw)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 1331, in go
    return once_fn(*arg, **kw)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 181, in first_connect
    dialect.initialize(c)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 245, in initialize
    self._get_server_version_info(connection)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy_exasol/turbodbc.py", line 103, in _get_server_version_info
    result = connection.execute(query).fetchone()[0].split('.')
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 939, in execute
    return self._execute_text(object, multiparams, params)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1097, in _execute_text
    statement, parameters
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1188, in _execute_context
    context)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1404, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1181, in _execute_context
    context)
  File "/opt/python/py3/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute
    cursor.execute(statement, parameters)
  File "/opt/python/py3/lib/python3.6/site-packages/turbodbc/exceptions.py", line 49, in wrapper
    return f(*args, **kwds)
  File "/opt/python/py3/lib/python3.6/site-packages/turbodbc/cursor.py", line 90, in execute
    self.impl.prepare(sql)
TypeError: prepare(): incompatible function arguments. The following argument types are supported:
    1. (self: turbodbc_intern.Cursor, arg0: str) -> None

Invoked with: <turbodbc_intern.Cursor object at 0x10c6862d0>, b"select PARAM_VALUE from SYS.EXA_METADATA where PARAM_NAME = 'databaseProductVersion'"

This is due to https://github.com/blue-yonder/sqlalchemy_exasol/blob/master/sqlalchemy_exasol/base.py#L421

The mentioned ticket in the code does not exist. The only ticket I found was (https://www.exasol.com/support/browse/EXASOL-1703).

Setting the the values to

    supports_unicode_statements = True
    supports_unicode_binds = True

fixes the problem and has no side effects to the other platforms (e.g. Linux)

Compiling INSERT/DELETE statements containing WITH clauses

If you have an insert statement containing a common table expression the compiling to string will fail. In a very simplified way:

from sqlalchemy import MetaData, Table, Column, Integer, select
metadata = MetaData()

table_A = Table("table_A", metadata,
              Column("int", Integer))
table_B = Table("table_B", metadata,
              Column("int", Integer))
table_C = Table("table_C", metadata,
              Column("int", Integer))

cte = select(
    columns=[table_A.c.int],
    from_obj=table_A).cte("cte")
query = select(
    columns=[table_B.c.int],
    from_obj=table_B.join(cte, onclause=table_B.c.int == cte.c.int))
insert = table_C.insert().from_select(['int'], query)

If you print the insert statement you will end up with

WITH cte AS
(SELECT "table_A".int AS int
FROM "table_A")
INSERT INTO "table_C" (int) SELECT "table_B".int
FROM "table_B" JOIN cte ON "table_B".int = cte.int

But the EXASOL dialect will complain (syntax error, unexpected INSERT_, expecting SELECT_ or TABLE_ or VALUES_ or '(') because the expected syntax would be the WITH statement before the SELECT directly:

INSERT INTO "table_C" (int)
WITH cte AS
(SELECT "table_A".int AS int
FROM "table_A")
SELECT "table_B".int
FROM "table_B" JOIN cte ON "table_B".int = cte.int

Avoid DB locks when performing metadata queries on SYS tables

The default locking behavior of Exasol is to create table locks.

When running queries with a high degree of concurrent connections on the same tables, deadlock situations can occur. This can be observed, e.g. when using BI tools that excessively query metadata tables.

To avoid deadlocks with pure read queries, select statements on SYS tables should be prepended with the DB hint /*snapshot execution*/. Starting with Exasol 6.2.5 the DB will execute these statements in a snapshot isolation mode, effectively avoiding creation of locks.

The idea for implementation is to prepend select statements on SYS tables with the pseudo comment /*snapshot execution*/.

Things to clarify & explore before implementation:

details on the snapshot execution feature documented here https://www.exasol.com/support/browse/EXASOL-2646
what is the DB version of the test DB?
should the pseudo comment only be generated for DB versions >= 6.2.5 or even for older versions (that will ignore this comment)?
how can this be tested given that the deadlock situations only occur in situations of excessive concurrency? (e.g. are they ways to inspect the query plan to check if snapshot isolation is active)
which type of queries should be covered (e.g. only SELECT)?
how should queries be treated that query/merge other tables besides tables residing in SYS?
what are potential side-effects on the SQLA test suite that heavily uses metadata queries to test the effects of DDL statements, e.g. are the changes to the data catalog visible when running in snapshot isolation?
potential places for implementation
- the pre_exec() method could be a potential place for the injection: https://github.com/blue-yonder/sqlalchemy_exasol/blob/master/sqlalchemy_exasol/base.py#L336
- the reflection methods of the EXADialect class (e.g. manually insert the pseudo comment to all select statements) https://github.com/blue-yonder/sqlalchemy_exasol/blob/master/sqlalchemy_exasol/base.py#L375
consider using the prefix_with() method available on all selectables https://docs.sqlalchemy.org/en/13/core/selectable.html?highlight=prefix#sqlalchemy.sql.expression.Select.prefix_with

Release a 3.0.0 version that drops Python 2.7 support

Redo dialect layout

Improve project layout to match SQLAlchemy recommendation:
https://github.com/zzzeek/sqlalchemy/blob/master/README.dialects.rst

No possibility to retrieve SQL statement from Merge object?

Hi there,

is there a possibility to retrieve the SQL statement generated from a Merge object? We generate the Merge object like so:

merge_statement = Merge(
    target_table=table_1,
    source_expr=table_2,
    on=table_1.id == table_2.id)

Where in object merge_statement can I retrieve the SQL code?

Regards
Michael

Exception attempting to detect unicode

I've an issue with unicode sending queries to Exasol. Running the following on Mac OS:

#!/usr/bin/python
# -*- coding: <utf-8> -*-
import sqlalchemy
from sqlalchemy import create_engine
e = create_engine('exa+pyodbc://EXA_TEST')

r = e.execute("select 3")
for row in r:
  print row

Result in:

/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/default.py:298: SAWarning: Exception attempting to detect unicode returns: ProgrammingError('(pyodbc.ProgrammingError) (\'42000\', "[42000] [EXASOL][EXASolution driver]syntax error, unexpected end_of_input, expecting assignment_operator or \':\' [line 1, column 1] (Session: 1526508375593279549) (-1) (SQLExecDirectW)")',)
  "detect unicode returns: %r" % de)
Traceback (most recent call last):
  File "sqlalchemy_example.py", line 15, in <module>
    r = e.execute("select 3")
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1990, in execute
    connection = self.contextual_connect(close_with_result=True)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2039, in contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2078, in _wrap_pool_connect
    e, dialect, self)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1405, in _handle_dbapi_exception_noconnection
    exc_info
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 200, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2074, in _wrap_pool_connect
    return fn()
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.py", line 376, in connect
    return _ConnectionFairy._checkout(self)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.py", line 713, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.py", line 480, in checkout
    rec = pool._do_get()
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1060, in _do_get
    self._dec_overflow()
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1057, in _do_get
    return self._create_connection()
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.py", line 323, in _create_connection
    return _ConnectionRecord(self)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/pool.py", line 454, in __init__
    exec_once(self.connection, self)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 246, in exec_once
    self(*args, **kw)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/event/attr.py", line 256, in __call__
    fn(*args, **kw)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 1319, in go
    return once_fn(*arg, **kw)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 165, in first_connect
    dialect.initialize(c)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/connectors/pyodbc.py", line 154, in initialize
    super(PyODBCConnector, self).initialize(connection)
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 256, in initialize
    self._check_unicode_description(connection):
  File "/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 343, in _check_unicode_description
    ]).compile(dialect=self)
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [EXASOL][EXASolution driver]syntax error, unexpected end_of_input, expecting assignment_operator or ':' [line 1, column 1] (Session: 1526508375593279549) (-1) (SQLExecDirectW)")

However, it does work the next time I run execute, e.g.:

#!/usr/bin/python
# -*- coding: <utf-8> -*-
import sqlalchemy
from sqlalchemy import create_engine
e = create_engine('exa+pyodbc://EXA_TEST')

# Temporary fix to unicode problem, works after running query the first time
# todo: investigate further into this
try:
    e.execute("select 1")
except sqlalchemy.exc.ProgrammingError:
    pass
###

r = e.execute("select 3")
for row in r:
  print row

/Users/myusername/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/default.py:298: SAWarning: Exception attempting to detect unicode returns: ProgrammingError('(pyodbc.ProgrammingError) (\'42000\', "[42000] [EXASOL][EXASolution driver]syntax error, unexpected end_of_input, expecting assignment_operator or \':\' [line 1, column 1] (Session: 1526508898697595965) (-1) (SQLExecDirectW)")',)
  "detect unicode returns: %r" % de)
(3,)

~/.odbc.ini:

[EXA_TEST]:
DRIVER=/FILEPATH/libexaodbc-io352fw.dylib
EXAHOST=localhost:8563
EXASCHEMA=PUBLIC
EXAUID=USERNAME
EXAPWD=PASSWORD

How to solve this unicode issue without having to use try-except?

Thanks!

'EXAODBC' driver must be defined although using DSN with driver specified

Hi
if I use a DSN (with driver specified) then I still need EXAODBC driver defined (in odbcinst.ini)

engine = sqlalchemy.create_engine('exa+turbodbc://@exasolution-uo2214lv2_64')
then query results in

DatabaseError: (turbodbc.exceptions.DatabaseError) ODBC error
state: 01000
native error code: 0
message: [unixODBC][Driver Manager]Can't open lib 'EXAODBC' : file not found

As soon as I define an odbcinst.ini with EXAODBC driver, problem goes away.

Error thrown when trying to insert a row where the PK is not explicitly defined as autoincrement=False and does not have a column_identity value

This has been bugging me for a while, but I only decided to look into this today.

I'm trying to insert a row into a table where the PK is a date. In my original schema, I had not defined autoincrement=False against this PK.

As a result, in get_lastrowid() in base.py, the first check autoincrement_pk_columns includes the PK column, so the return None is not executed at this stage. So, the function then goes on to find the column_identity column from exa_all_columns, which comes back Null.

As a result, the int(...) call is calling Null, and the exception is thrown.

The quick fix is to add a check of result[0] is None, and if so either throw a warning or error so that it's clear what the issue is, or ignore it and return None.

    session.commit()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 921, in commit
    self.transaction.commit()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 461, in commit
    self._prepare_impl()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 441, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 2192, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 2312, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 2276, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/unitofwork.py", line 389, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/unitofwork.py", line 548, in execute
    uow
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
    mapper, table, insert)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/persistence.py", line 799, in _emit_insert_statements
    execute(statement, multiparams)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1202, in _execute_context
    result = context._setup_crud_result_proxy()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 896, in _setup_crud_result_proxy
    self._setup_ins_pk_from_lastrowid()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 932, in _setup_ins_pk_from_lastrowid
    lastrowid = self.get_lastrowid()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy_exasol/base.py", line 327, in get_lastrowid
    return int(result[0]) - 1
TypeError: int() argument must be a string, a bytes-like object or a number, not 'NoneType'

No Constraint Violation Raised in Prepared Statements

If you insert data with prepared statements you don't get constraint violations

The error occurred with the normal sqlalchemy expression language insert like:

con.execute(bar_table.insert(), values)

Here is a stripped down example

from sqlalchemy import *
from sqlalchemy.exc import DBAPIError

dsn = ""
schema = ""

engine = create_engine(dsn)
con = engine.connect()
con.execute("open schema %s" %schema)
con.execute("""DROP TABLE IF EXISTS BAR CASCADE CONSTRAINTS""")
con.execute("""CREATE TABLE BAR(
               BAR_ID    DECIMAL(18,0) IDENTITY NOT NULL ENABLE);""")
con.execute("""ALTER TABLE BAR ADD CONSTRAINT test_bar_constraint_primary PRIMARY KEY(BAR_ID) ENABLE;""")


con.execute("insert into bar values(?)", (1, ))
print con.execute("select * from bar").fetchall()

# Normal Insert Constraint Violation raised 
try:
    con.execute("insert into bar values(1)")
except DBAPIError as e:
    print "Expected Error%s" %e

# Prepared Statement No Constraint violation raised
r = con.execute("insert into bar values(?)", (1, ))

# Cannot call r.cursor.commit(), because cursor has been closed automatically
#r.cursor.commit()

print con.execute("select * from bar").fetchall()

For Comparision here is a plain pyodbc example

import pyodbc

dsn = ""
schema = ""

con = pyodbc.connect(dsn)
con.execute("open schema %s" %schema)

con.execute("""DROP TABLE IF EXISTS BAR CASCADE CONSTRAINTS""")
con.execute("""CREATE TABLE BAR(
               BAR_ID  DECIMAL(18,0) IDENTITY NOT NULL ENABLE);""")
con.execute("""ALTER TABLE BAR ADD CONSTRAINT test_bar_constraint_primary PRIMARY KEY(BAR_ID) ENABLE;""")

con.execute("insert into bar values(1)")

# No constraint violation 
con.execute("insert into bar values(?)", (1,))
print con.execute("select * from bar").fetchall()

# Force Commit to get ExaSolution driver  constraint violation
cursor = con.execute("insert into bar values(?)", (1,))
cursor.commit()

Release a 2.1.0 version removing Travis CI and using odbc metadata

I'm currently working on a new release to remove Travis CI completely and replace it with Github Actions. Additionally, this version will contain the new ODBC medatata requested by @tkilias in #90

no coverage uploaded to coveralls

Apparently, the upload of new coverage data to coveralls is currently broken. Needs investigation.

Waiting for new SQLA Release

Build of this dialect fails until two pull requests are included:
zzzeek/sqlalchemy#95
zzzeek/sqlalchemy#96

Fix SQLAlchemy 0.9.x compatibility

Build with SQLAlchemy 0.9.x fails. Go through the change log and migration guide to determine what is necessary to support 0.8.x and 0.9.x in parallel:

http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html
http://docs.sqlalchemy.org/en/latest/changelog/changelog_09.html

add reflection support for the distribute by constraint

from sqlalchemy import create_engine, MetaData, Table, Integer, Column
from sqlalchemy_exasol.constraints import DistributeByConstraint
from sqlalchemy.schema import CreateTable

engine = create_engine("exa+pyodbc://user:pw@host/test")

md = MetaData(bind=engine, schema="test")

t = Table('t', md,
           Column('a', Integer),
           Column('b', Integer),
           Column('c', Integer),
           DistributeByConstraint('a', 'b')
        )

print CreateTable(t).compile(engine)
print t.constraints
md.create_all()

md2 = MetaData(bind=engine)
md2.reflect(schema="test")

reflected_table = md2.tables["test.t"]
print CreateTable(reflected_table).compile(engine)
print reflected_table.constraints

results in

CREATE TABLE t (
    a INTEGER,
    b INTEGER,
    c INTEGER,
    DISTRIBUTE BY a,b
)

set([PrimaryKeyConstraint(), DistributeByConstraint(Column('a', Integer(), table=<t>), Column('b', Integer(), table=<t>))])

CREATE TABLE test.t (
    a INTEGER,
    b INTEGER,
    c INTEGER
)

set([PrimaryKeyConstraint()])

Exasol Dialect should not execute update statements with executemanny

In EXAExecutionContext the setting executemany is set to True. Therefore update statements are also executed via executemanny(..).
While executing the update statenment works, the result.rowcount field is always set to -1 which breaks alembic > 0.7 https://bitbucket.org/zzzeek/alembic/src/a192c345fcfcf4d5dd817f898ec883d7fa3b0ac4/alembic/migration.py?at=master#cl-459

from sqlalchemy import *
dsn = "exa+pyodbc://scott:tiger@host:8563/"
engine = create_engine(dsn, echo=True)

md = MetaData(bind=engine)
md.reflect(schema="test")
t = md.tables.get("test.alembic_version")
update_statement = update(t).values(version_num = 'dc0a2e2ac10')
r = engine.execute(update_statement)
print "Rowcount %s" %r.rowcount

2015-04-29 20:09:04,992 INFO sqlalchemy.engine.base.Engine (u'TEST',)
2015-04-29 20:09:05,208 INFO sqlalchemy.engine.base.Engine UPDATE test.alembic_version SET version_num=?
2015-04-29 20:09:05,208 INFO sqlalchemy.engine.base.Engine (('dc0a2e2ac10',),)
2015-04-29 20:09:05,300 INFO sqlalchemy.engine.base.Engine COMMIT
Rowcount -1

FAIL: test.test_suite.DateTimeMicrosecondsTest.test_round_trip

Date round trip from SQLA test suite fails with:

AssertionError: (datetime.datetime(2012, 10, 15, 12, 57, 18),) != (datetime.datetime(2012, 10, 15, 12, 57, 18, 396),)

Add travis build for python 3.4

Once feature travis-ci/travis-ci#1366 is available in the Travis CI UI, restrict concurrent builds to '1' and enable 3.4 build in .travis.yml.

Initial Update

The bot created this issue to inform you that pyup.io has been set up on this repo.
Once you have closed it, the bot will open pull requests for updates as soon as they are available.

FAIL: test.test_suite.InsertBehaviorTest.test_insert_from_select

Insert from select test case from SQLA test suite fails with:

AssertionError: [(u'data1',), (u'data2',), (u'data3',)] != [('data1',), ('data2',), ('data2',), ('data3',), ('data3',)]

Does not work with Debian 9 Stretch

Module does not work on Debian 9. Trying to do a select on a table. Python2 or 3 does not make any difference. It works with Debian 8.

Traceback (most recent call last):
File "test.py", line 5, in
connection = engine.connect()
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2102, in connect
return self._connection_cls(self, **kwargs)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 90, in init
if connection is not None else engine.raw_connection()
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2188, in raw_connection
self.pool.unique_connection, _connection)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
e, dialect, self)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
exc_info
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
raise value.with_traceback(tb)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
return fn()
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 345, in unique_connection
return _ConnectionFairy._checkout(self)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 782, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 532, in checkout
rec = pool._do_get()
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1186, in _do_get
self._dec_overflow()
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 66, in exit
compat.reraise(exc_type, exc_value, exc_tb)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
raise value
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1183, in _do_get
return self._create_connection()
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
return _ConnectionRecord(self)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 477, in init
self.__connect(first_connect_check=True)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/pool.py", line 667, in __connect
connection = pool._invoke_creator(self)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py", line 105, in connect
return dialect.connect(*cargs, **cparams)
File "/home/user/.local/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 410, in connect
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY000', 'The driver did not supply an error!') (Background on this error at: http://sqlalche.me/e/dbapi)

Support turbodbc

Currently only python 2.7 is supported by turbodbc and therefore for sqlalchemy-exasol, too.

Humble request: Since turbodbc now supports Python 3, can sqlalchemy-exasol extend the turbodbc support to Python 3, too?

Restore coverage/coveralls in sqlalchemy_exasol

As part of Release 2.1.0, we have removed all usage of coverage and coveralls. This is a ticket to keep track of it and restore both tools for future releases

Remove workaround for missing prepared delete support in EXASOL

Once supported by EXASOL, the workaround in base.py for prepared delete must be removed.

Depends on
[open] https://www.exasol.com/support/browse/EXASOL-899
[resolved in 4.2] https://www.exasol.com/support/browse/EXASOL-875

Default: CONNECTIONLCALL=en_US.UTF-8

Using 'CONNECTIONLCALL=en_US.UTF-8' as query parameter for connection URLs is a reliable way of making sure that Python process and EXASOL driver agree on a common encoding (at least on Linux/Unix).

Add this as documentation or even default to have less pain with running into conversion errors.

Reflection supports no doc and default attributes for columns

I used following code to reflect a table:

table = sqla.Table('M_PREDICTIONS', sqla.MetaData(), autoload=True, autoload_with=engine, schema=mining_schema)

Iterating over the columns of table revealed that the doc attribute is always empty and I think this should correspond to the Comment in Exasol. Also the default attribute for the column is always empty. This looks like the reflection is not fully implemented.

Use schema_translate_map in get_lastrowid

Hi,

we have an issue when we use autoincrement PKs in combination with schema_translate_map set in the engine.
For example:

metadata = MetaData()
my_table = Table(
    "my_table",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(1000), nullable=False),
    schema="my_schema"
)

engine = create_engine(
        conn_string,
        execution_options={"schema_translate_map": {"my_schema": "my_tenant_schema"}},
    )
conn = engine.connect()
conn.execute(my_table.insert().values(name="foo))

throws this error:

/caches/pypoetry/virtualenvs/all-KwnZMQER-py3.6/lib/python3.6/site-packages/sqlalchemy/engine/default.py:1273: in _setup_ins_pk_from_lastrowid
    lastrowid = self.get_lastrowid()
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy_exasol.base.EXAExecutionContext object at 0x7f695ec97eb8>

    def get_lastrowid(self):
        columns = self.compiled.sql_compiler.statement.table.columns
        autoinc_pk_columns = \
            [c.name for c in columns if c.autoincrement and c.primary_key]
        if len(autoinc_pk_columns) == 0:
            return None
        elif len(autoinc_pk_columns) > 1:
            util.warn("Table with more than one autoincrement, primary key" \
                      " Column!")
            raise Exception
        else:
            id_col = self.dialect.denormalize_name(autoinc_pk_columns[0])
    
            table = self.compiled.sql_compiler.statement.table.name
            table = self.dialect.denormalize_name(table)
    
            sql_stmnt = "SELECT column_identity from SYS.EXA_ALL_COLUMNS " \
                        "WHERE column_object_type = 'TABLE' and column_table " \
                        "= ? AND column_name = ?"
    
            schema = self.compiled.sql_compiler.statement.table.schema
            if schema is not None:
                schema = self.dialect.denormalize_name(schema)
                sql_stmnt += " AND column_schema = ?"
            cursor = self.create_cursor()
            if schema:
                cursor.execute(sql_stmnt, (table, id_col, schema))
            else:
                cursor.execute(sql_stmnt, (table, id_col))
            result = cursor.fetchone()
            cursor.close()
>           return int(result[0]) - 1
E           TypeError: 'NoneType' object is not subscriptable

/caches/pypoetry/virtualenvs/all-KwnZMQER-py3.6/lib/python3.6/site-packages/sqlalchemy_exasol/base.py:337: TypeError

in version 2.1.

For our particular case, the error can be circumvented by using the schema in the schema_translate_map in the error throwing get_lastrowid function:

            if schema is not None:
                schema = self.compiled.sql_compiler.schema_translate_map.map_[schema] if schema in self.compiled.sql_compiler.schema_translate_map.map_ else schema # <--- explicitely use the translation map here 
                schema = self.dialect.denormalize_name(schema)
                sql_stmnt += " AND column_schema = ?"

test ExceptionTest_exasol_pyodbc::()::test_integrity_error fails

The test case ExceptionTest_exasol_pyodbc::()::test_integrity_error (added with SQLA 1.0.5 fails). The test expects an exception upon insert with a duplicate key. However, the exception is not raised.

link to test case:
https://github.com/zzzeek/sqlalchemy/blob/0766c80b9c02fdbad3203835ab850ad690f4c03b/lib/sqlalchemy/testing/suite/test_dialect.py#L28

Unable to reflect views

According to
http://docs.sqlalchemy.org/en/latest/core/reflection.html#reflecting-views
sqlalchemy should be able to reflect views, as if they were tables in the database.

However the exasol dialect does not support the reflection of views.
In particular it already claims, that views that are present on the database do not exist. You may reproduce this undesirable behavior like:

import sqlalchemy
uri = "exa+pyodbc://my_dsn"
schema = "my_schema"
engine = sqlalchemy.create_engine(uri)
meta_data = sqlalchemy.MetaData(bind=engine, schema=schema)
view_name = "my_view"
view = sqlalchemy.Table(view_name, meta_data)
assert(view.exists()) # Fires for every view

ERROR: test suite for <class 'test.test_suite.TextTest'>

Text test from SQLA test suite fails with:

NotSupportedError: (NotSupportedError) ('0A000', '[0A000] [EXASOL][EXASolution driver]Feature not supported: user-defined type (TEXT) line 4, column 16 (SQLExecDirectW)') u'\nCREATE TABLE text_table (\n\tid INTEGER IDENTITY NOT NULL, \n\ttext_data TEXT, \n\tPRIMARY KEY (id)\n)\n\n' [()]

Check for server version

Check for server version in pyodbc.py does a round-trip to the database. Retrieving the patch version from the driver was not working in pre 4.2.5 versions.

Need to check with wich client/server version of EXASOL this issue is supported and limit the round-trip to versions that require it.

Move repository

Move repository to blue-yonder ogranization. Requires re-encrypting the credentials for DB and pypi.

Provide type hints to pyodbc/unixodbc/db driver for parameters in SQL statements

SQLAlchemy does not call the 'setinputsizes' function prior executing statements. This is usually not an issue can cause unwanted effects when a lot of parameters are transmitted. EXASOL reserves up to 2.000.000 bytes per parameter if no type information is provided by the caller. This causes statements with more than 266 to fail.
Current workaround is to avoid prepared statements when an SQL statement has more than x parameters. The sane solution is to use the type information available in SQLAlchemy to call setinputsizes prior execution (pre_exec method). This is only done in the Oracle dialect:

https://github.com/zzzeek/sqlalchemy/blob/be3c185fd48c2abcc5d9f54dd0c415e15c33184f/lib/sqlalchemy/dialects/oracle/cx_oracle.py#L462

TODO:

Have a test case that fails
Implement something similar to Oracle in the pre_exec method of the EXA dialect.

test_bound_in_scalar fails with turbodbc

On branch sqla1_2 the test case 'test_bound_in_scalar' fails with with turbodbc active:

https://travis-ci.org/blue-yonder/sqlalchemy_exasol?utm_source=email&utm_medium=notification

Interestingly, this test is already some months old (https://github.com/zzzeek/sqlalchemy/blob/2efd89d02941ab4267d6e2842963fd38b1539f6c/lib/sqlalchemy/testing/suite/test_select.py#L344) but it only popped up now with the change to 1.2.0b3.

Not sure if it is limited to turbodbc, or if it is just a race condition. From my perspective the test for equality assumes a certain ordering of the result, but this ordering is not guaranteed by the select statement. might be a bug in the test itself.

Add Merge support

Add a class that compiles MERGE statements (http://en.wikipedia.org/wiki/Merge_(SQL))

Remove DEFAULT_DRIVER_NAME

Remove all traces to DEFAULT_DRIVER_NAME. Avoids confusion by non-standard defaults, creates need for all users to inject driver name in a meaningful name.

See discussion around: #57

Improve CI and CI/CD pipelines

Had a conversation with Frank Fischer and he suggested a couple changes for the project:

Remove manual stuff like git clone and pip install and replace them with public github actions like checkout@v2 already used
Cache all installation of requirements
Use a pre-commit hook to remove trailing spaces and alert about other formatting issues
Figure out way to pull newest versions of public actions
Figure out way to restrict tagging by contributors so not anyone can publish to PyPi

Connection failure : ODBC Driver Manager not found Error

Hello, I am struggling to connect to an exasol database using sqlalchemy even though the Exasol ODBC driver 6.2.7 is already installed. I am also setting the driver name under EXASolution Driver in since this is how it exists in the ODBC Data Source Administrator, but this outputted a similar error to the one below. Can you please let me know if I am making an error in the connection string? Or how do I make sure that the ODBC driver is correctly installed?

This is the connection string I am using:

from sqlalchemy import create_engine

engine = create_engine("exa+pyodbc://{username}:{password}@{host}:
{port}/CONNECTIONLCALL=en_US.UTF- 8&driver=EXASolution Driver")

engine.connect()

Error :

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: http://sqlalche.me/e/13/rvf5)

Thank you for your help!

Error on Default Value

Generating default values does not work correctly for non-PK columns.

Required [EXAODBC] section in odbcinst.ini

It seems that sqlalchemy_exasol assumes an [EXAODBC] section in the odbcinst.ini and does not interpret the DRIVER=value from odbc.ini like pyodbc does when a DSN is specified. Could this be changed?

Websocket based SQLA Dialect (Beta Version)

I wonder if it would be possible to add a pyexasol backend as an alternative to odbc?

Support reflection of table / columns comments with SQLAlchemy 1.2

http://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#support-for-sql-comments-on-table-column-includes-ddl-reflection

This should be relatively straightforward as the EXASOL dialect mirrors the ORACLE dialect. Comments on different DB objects are supported and can be set inline
CREATE TABLE foo ( ... ) COMMENT is 'a comment';
or as individual SQL
COMMENT ON TABLE foo IS 'a table';

Update Reserved Keywords for Exasol 5.x

Exasol 5.x has some new reserved keywords.

Update https://github.com/blue-yonder/sqlalchemy_exasol/blob/master/sqlalchemy_exasol/base.py#L61

and add a integration test wich checks the RESERVED_WORDS against the db query

select * from EXA_SQL_KEYWORDS;