class EXADialect_pyodbcTest(fixtures.TestBase):
def setup(self):
self.dialect = EXADialect_pyodbc()
def assert_parsed(self, dsn, expected_connector, expected_args):
url = sa_url.make_url(dsn)
connector, args = self.dialect.create_connect_args(url)
eq_(connector, expected_connector)
eq_(args, expected_args)
def test_create_connect_args(self):
self.assert_parsed("exa+pyodbc://scott:[email protected]:1234/my_schema",
['DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'],
{})
def test_create_connect_args_trusted(self):
self.assert_parsed("exa+pyodbc://192.168.1.2..8:1234/my_schema",
['DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;Trusted_Connection=Yes;INTTYPESINRESULTSIFPOSSIBLE=y'],
{})
def test_create_connect_args_autotranslate(self):
self.assert_parsed("exa+pyodbc://scott:[email protected]:1234/my_schema?odbc_autotranslate=Yes",
['DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;AutoTranslate=Yes;INTTYPESINRESULTSIFPOSSIBLE=y'],
{})
def test_create_connect_args_with_param(self):
self.assert_parsed("exa+pyodbc://scott:[email protected]:1234/my_schema?autocommit=true",
['DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'],
{'AUTOCOMMIT': True})
def test_create_connect_args_with_param_multiple(self):
self.assert_parsed("exa+pyodbc://scott:[email protected]:1234/my_schema?autocommit=true&ansi=false&unicode_results=false",
['DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'],
{'AUTOCOMMIT': True, 'ANSI': False, 'UNICODE_RESULTS': False})
def test_create_connect_args_with_unknown_params(self):
self.assert_parsed("exa+pyodbc://scott:[email protected]:1234/my_schema?clientname=test&querytimeout=10",
['DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y;clientname=test;querytimeout=10'],
{})
def raw_sql(query):
"""
Converts an SQLAlchemy object to a single raw SQL string. Note
that this function currently only supports Exasol queries!
:param query: An SQLAlchemy query object
:returns: A string of raw SQL
:rtype: string
"""
dialect = EXADialect_pyodbc()
class LiteralCompiler(base.EXACompiler):
def visit_bindparam(self,
bindparam,
within_columns_clause=False,
literal_binds=False,
**kwargs):
return super(LiteralCompiler, self).render_literal_bindparam(
bindparam,
within_columns_clause=within_columns_clause,
literal_binds=literal_binds,
**kwargs)
def render_literal_value(self, value, type_):
if value is None:
return 'NULL'
elif isinstance(value, six.binary_type):
return u"'{value}'".format(value=value.decode('utf-8'))
elif isinstance(value, six.text_type):
return u"'{value}'".format(value=value)
elif type(value) is datetime.date:
return "to_date('{value}', 'YYYY-MM-DD')"\
.format(value=value.strftime('%Y-%m-%d'))
elif type(value) is datetime.datetime:
return u"to_timestamp('{value}', 'YYYY-MM-DD HH24:MI:SS.FF6')"\
.format(value=value.strftime('%Y-%m-%d %H:%M:%S.%f'))
else:
return u"{}".format(value)
compiler = LiteralCompiler(dialect, query)
return compiler.string
def setup(self):
self.dialect = EXADialect_pyodbc()
self.dialect.dbapi = pyodbc
class EXADialect_pyodbcTest(fixtures.TestBase):
def setup(self):
self.dialect = EXADialect_pyodbc()
self.dialect.dbapi = pyodbc
def assert_parsed(self, dsn, expected_connector, expected_args):
url = sa_url.make_url(dsn)
connector, args = self.dialect.create_connect_args(url)
assert connector == expected_connector
assert args == expected_args
def test_create_connect_args(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema?driver=EXAODBC",
[
"DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y"
";SQLSTATEMAPPINGACTIVE=y"
";SQLSTATEMAPPINGS=42X91:23000,27002:23000"
],
{},
)
def test_create_connect_args_with_driver(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema?driver=FOOBAR",
[
"DRIVER={FOOBAR};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y"
";SQLSTATEMAPPINGACTIVE=y"
";SQLSTATEMAPPINGS=42X91:23000,27002:23000"
],
{},
)
def test_create_connect_args_dsn(self):
self.assert_parsed(
"exa+pyodbc://scott:tiger@exa_test",
[
"DSN=exa_test;EXAHOST=;EXASCHEMA=;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y"
";SQLSTATEMAPPINGACTIVE=y"
";SQLSTATEMAPPINGS=42X91:23000,27002:23000"
],
{},
)
def test_create_connect_args_trusted(self):
self.assert_parsed(
"exa+pyodbc://192.168.1.2..8:1234/my_schema",
[
"DRIVER={None};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;Trusted_Connection=Yes;INTTYPESINRESULTSIFPOSSIBLE=y"
";SQLSTATEMAPPINGACTIVE=y"
";SQLSTATEMAPPINGS=42X91:23000,27002:23000"
],
{},
)
def test_create_connect_args_autotranslate(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema?odbc_autotranslate=Yes",
[
"DRIVER={None};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;AutoTranslate=Yes;INTTYPESINRESULTSIFPOSSIBLE=y"
";SQLSTATEMAPPINGACTIVE=y"
";SQLSTATEMAPPINGS=42X91:23000,27002:23000"
],
{},
)
def test_create_connect_args_with_param(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema?autocommit=true",
[
"DRIVER={None};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y"
";SQLSTATEMAPPINGACTIVE=y"
";SQLSTATEMAPPINGS=42X91:23000,27002:23000"
],
{"AUTOCOMMIT": True},
)
def test_create_connect_args_with_param_multiple(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema?autocommit=true&ansi=false&unicode_results=false",
[
"DRIVER={None};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y"
";SQLSTATEMAPPINGACTIVE=y"
";SQLSTATEMAPPINGS=42X91:23000,27002:23000"
],
{
"AUTOCOMMIT": True,
"ANSI": False,
"UNICODE_RESULTS": False
},
)
def test_create_connect_args_with_unknown_params(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema?clientname=test&querytimeout=10",
[
"DRIVER={None};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y"
";SQLSTATEMAPPINGACTIVE=y"
";SQLSTATEMAPPINGS=42X91:23000,27002:23000"
";clientname=test;querytimeout=10"
],
{},
)
def test_is_disconnect(self):
connection = Mock(spec=_ConnectionFairy)
cursor = Mock(spec=pyodbc.Cursor)
errors = [
pyodbc.Error(
"HY000",
"[HY000] [EXASOL][EXASolution driver]Connection lost in socket read attempt. Operation timed out (-1) (SQLExecDirectW)",
),
pyodbc.Error(
"HY000",
"[HY000] [EXASOL][EXASolution driver]Socket closed by peer."),
]
for error in errors:
status = self.dialect.is_disconnect(error, connection, cursor)
assert status
def setup(self):
self.dialect = EXADialect_pyodbc()
def setup(self):
self.dialect = EXADialect_pyodbc()
self.dialect.dbapi = pyodbc
class EXADialect_pyodbcTest(fixtures.TestBase):
__skip_if__ = (lambda: testing.db.dialect.driver != 'pyodbc',)
def setup(self):
self.dialect = EXADialect_pyodbc()
self.dialect.dbapi = pyodbc
def assert_parsed(self, dsn, expected_connector, expected_args):
url = sa_url.make_url(dsn)
connector, args = self.dialect.create_connect_args(url)
eq_(connector, expected_connector)
eq_(args, expected_args)
def test_create_connect_args(self):
self.assert_parsed("exa+pyodbc://scott:[email protected]:1234/my_schema?driver=EXAODBC",
['DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'],
{})
def test_create_connect_args_with_driver(self):
self.assert_parsed("exa+pyodbc://scott:[email protected]:1234/my_schema?driver=FOOBAR",
['DRIVER={FOOBAR};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'],
{})
def test_create_connect_args_dsn(self):
self.assert_parsed("exa+pyodbc://scott:tiger@exa_test",
['DSN=exa_test;EXAHOST=;EXASCHEMA=;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'],
{})
def test_create_connect_args_trusted(self):
self.assert_parsed("exa+pyodbc://192.168.1.2..8:1234/my_schema",
['DRIVER={None};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;Trusted_Connection=Yes;INTTYPESINRESULTSIFPOSSIBLE=y'],
{})
def test_create_connect_args_autotranslate(self):
self.assert_parsed("exa+pyodbc://scott:[email protected]:1234/my_schema?odbc_autotranslate=Yes",
['DRIVER={None};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;AutoTranslate=Yes;INTTYPESINRESULTSIFPOSSIBLE=y'],
{})
def test_create_connect_args_with_param(self):
self.assert_parsed("exa+pyodbc://scott:[email protected]:1234/my_schema?autocommit=true",
['DRIVER={None};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'],
{'AUTOCOMMIT': True})
def test_create_connect_args_with_param_multiple(self):
self.assert_parsed("exa+pyodbc://scott:[email protected]:1234/my_schema?autocommit=true&ansi=false&unicode_results=false",
['DRIVER={None};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'],
{'AUTOCOMMIT': True, 'ANSI': False, 'UNICODE_RESULTS': False})
def test_create_connect_args_with_unknown_params(self):
self.assert_parsed("exa+pyodbc://scott:[email protected]:1234/my_schema?clientname=test&querytimeout=10",
['DRIVER={None};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y;clientname=test;querytimeout=10'],
{})
def test_is_disconnect(self):
connection = Mock(spec=_ConnectionFairy)
cursor = Mock(spec=pyodbc.Cursor)
errors = [
pyodbc.Error(
'HY000',
'[HY000] [EXASOL][EXASolution driver]Connection lost in socket read attempt. Operation timed out (-1) (SQLExecDirectW)'
),
pyodbc.Error(
'HY000',
'[HY000] [EXASOL][EXASolution driver]Socket closed by peer.'
),
]
for error in errors:
status = self.dialect.is_disconnect(error, connection, cursor)
eq_(status, True)
class EXADialect_pyodbcTest(fixtures.TestBase):
__skip_if__ = (lambda: testing.db.dialect.driver != 'pyodbc', )
def setup(self):
self.dialect = EXADialect_pyodbc()
self.dialect.dbapi = pyodbc
def assert_parsed(self, dsn, expected_connector, expected_args):
url = sa_url.make_url(dsn)
connector, args = self.dialect.create_connect_args(url)
eq_(connector, expected_connector)
eq_(args, expected_args)
def test_create_connect_args(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema", [
'DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'
], {})
def test_create_connect_args_with_driver(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema?driver=FOOBAR",
[
'DRIVER={FOOBAR};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'
], {})
def test_create_connect_args_dsn(self):
self.assert_parsed("exa+pyodbc://scott:tiger@exa_test", [
'DSN=exa_test;EXAHOST=;EXASCHEMA=;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'
], {})
def test_create_connect_args_trusted(self):
self.assert_parsed("exa+pyodbc://192.168.1.2..8:1234/my_schema", [
'DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;Trusted_Connection=Yes;INTTYPESINRESULTSIFPOSSIBLE=y'
], {})
def test_create_connect_args_autotranslate(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema?odbc_autotranslate=Yes",
[
'DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;AutoTranslate=Yes;INTTYPESINRESULTSIFPOSSIBLE=y'
], {})
def test_create_connect_args_with_param(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema?autocommit=true",
[
'DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'
], {'AUTOCOMMIT': True})
def test_create_connect_args_with_param_multiple(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema?autocommit=true&ansi=false&unicode_results=false",
[
'DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y'
], {
'AUTOCOMMIT': True,
'ANSI': False,
'UNICODE_RESULTS': False
})
def test_create_connect_args_with_unknown_params(self):
self.assert_parsed(
"exa+pyodbc://scott:[email protected]:1234/my_schema?clientname=test&querytimeout=10",
[
'DRIVER={EXAODBC};EXAHOST=192.168.1.2..8:1234;EXASCHEMA=my_schema;UID=scott;PWD=tiger;INTTYPESINRESULTSIFPOSSIBLE=y;clientname=test;querytimeout=10'
], {})
def test_is_disconnect(self):
connection = Mock(spec=_ConnectionFairy)
cursor = Mock(spec=pyodbc.Cursor)
errors = [
pyodbc.Error(
'HY000',
'[HY000] [EXASOL][EXASolution driver]Connection lost in socket read attempt. Operation timed out (-1) (SQLExecDirectW)'
),
pyodbc.Error(
'HY000',
'[HY000] [EXASOL][EXASolution driver]Socket closed by peer.'),
]
for error in errors:
status = self.dialect.is_disconnect(error, connection, cursor)
eq_(status, True)
def __init__(self, db_type: DBType):
self.db_type = db_type
# FIXME, inject engine-based components once we support >1 db.
self.connection = ExasolConnection()
self._dialect = EXADialect_pyodbc()
self._layers: List[Layer] = []