def test_readCSV_returns_a_list_of_lists(self):
with exasol.connect(**self.odbc_kwargs) as ecn:
rows = ecn.readCSV(
'SELECT decimal1 FROM exasol_travis_python.data_exchange_table'
)
self.assertIsInstance(rows, list)
self.assertIsInstance(rows[0], list)
def test_connect_disconnect_with_withstatement(self):
with exasol.connect(**self.odbc_kwargs) as ecn:
crs = ecn.cursor()
crs.execute('select * from dual')
rows = crs.fetchall()
self.assertEqual(1, len(rows))
self.assertIsNone(rows[0][0])
self.assertRaises(pyodbc.ProgrammingError, ecn.close)
def test_raise_runtimeerror_outargs_not_set(self):
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
with self.assertRaises(RuntimeError):
@ecn.createScript(inArgs=[('dummy', INT)])
def foo(dummy):
return
def test_connect_disconnect(self):
ecn = exasol.connect(**self.odbc_kwargs)
crs = ecn.cursor()
crs.execute('select * from dual')
rows = crs.fetchall()
self.assertEqual(1, len(rows))
self.assertIsNone(rows[0][0])
crs.close()
ecn.close()
def test_createScript_defaults_to_pandas(self):
with exasol.connect(**self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(inArgs=[('a', INT)], outArgs=[('a', INT)])
def foo(ctx):
ctx.emit(int(ctx.a))
self.assertIsInstance(foo(3.4, table='dual'), pandas.DataFrame)
def test_createScript_set_default_with_connect(self):
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(inArgs=[('a', INT)], outArgs=[('a', INT)])
def foo(ctx):
ctx.emit(int(ctx.a))
self.assertIsInstance(foo(3.4, table='dual'), list)
def test_writeData_set_default_with_connect(self):
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
c = ecn.cursor()
c.execute('OPEN SCHEMA exasol_travis_python')
c.execute('DROP TABLE IF EXISTS T')
c.execute('CREATE TABLE T (x INT, y INT)')
ecn.writeData([[1, 2], [3, 4]], 'T')
c.execute('DROP TABLE T')
def test_raise_typeerror_outargs(self):
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
with self.assertRaises(TypeError):
@ecn.createScript(inArgs=[('dummy', INT)],
outArgs=[('out', INT)],
outType=RETURNS)
def foo(dummy):
return
def test_no_exception_in_any_thread(self):
try:
_sys_stderr = sys.stderr
sys.stderr = StringIO()
with exasol.connect(clientAddress=(None, 0), **self.odbc_kwargs) as ecn:
pass
finally:
stderr = sys.stderr.getvalue()
sys.stderr = _sys_stderr
self.assertNotIn('Traceback', stderr)
def test_writeData_defaults_to_pandas(self):
with exasol.connect(**self.odbc_kwargs) as ecn:
c = ecn.cursor()
c.execute('OPEN SCHEMA exasol_travis_python')
c.execute('DROP TABLE IF EXISTS T')
c.execute('CREATE TABLE T (x INT, y INT)')
with self.assertRaises(TypeError):
ecn.writeData([[1, 2], [3, 4]], 'T')
c.execute('DROP TABLE exasol_travis_python.t')
def test_raise_typeerror_no_table(self):
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(inArgs=[('dummy', INT)],
outArgs=[('dummy', INT)])
def foo(dummy):
return
with self.assertRaises(TypeError):
foo(42)
def test_readPandas_gets_plausible_data(self):
with exasol.connect(**self.odbc_kwargs) as ecn:
crs = ecn.cursor()
crs.execute(
'SELECT sum(decimal1) FROM exasol_travis_python.data_exchange_table'
)
sum_ = crs.fetchone()[0]
rows = ecn.readPandas(
'SELECT decimal1 FROM exasol_travis_python.data_exchange_table'
)
self.assertAlmostEqual(float(sum_), float(rows.sum()))
def test_createScript_overwrite_default(self):
with exasol.connect(**self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(inArgs=[('a', INT)], outArgs=[('a', INT)])
def foo(ctx):
ctx.emit(int(ctx.a))
self.assertIsInstance(
foo(3.4, table='dual', readCallback=exasol.csvReadCallback),
list)
def test_writeData_overwrite_default(self):
with exasol.connect(**self.odbc_kwargs) as ecn:
c = ecn.cursor()
c.execute('OPEN SCHEMA exasol_travis_python')
c.execute('DROP TABLE IF EXISTS T')
c.execute('CREATE TABLE T (x INT, y INT)')
ecn.writeData([[1, 2], [3, 4]],
'T',
writeCallback=exasol.csvWriteCallback)
c.execute('DROP TABLE T')
def create_script(self, type_):
with exasol.connect(**self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(
name="foobar",
inArgs=[('a', type_)],
outArgs=[('a', INT)],
)
def foo(ctx):
pass
ecn.commit()
def create_script(self, inargs='a', outargs='a'):
with exasol.connect(**self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(
name="foobar",
inArgs=[(x, DOUBLE) for x in inargs],
outArgs=[(x, DOUBLE) for x in outargs],
)
def foo(ctx):
pass
ecn.commit()
def test_createScript_works_set_emits(self):
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(
inArgs=[('a', DOUBLE)],
outArgs=[('a', INT)],
)
def foo(ctx):
ctx.emit(int(ctx.a))
self.assertEqual([['3']], foo(3.4, table='dual'))
def test_python_version(self):
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(inArgs=[('dummy', INT)],
outArgs=[('major', INT), ('minor', INT)])
def python_version(dummy):
dummy.emit(int(sys.version_info[0]), int(sys.version_info[1]))
version = python_version(1, table='dual')
self.assertEqual(expected_version[0], int(version[0][0]))
self.assertEqual(expected_version[1], int(version[0][1]))
def test_raise_exception(self):
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
with self.assertRaises(RuntimeError):
@ecn.createScript(inArgs=[('dummy', INT)],
outArgs=[('major', INT), ('minor', INT)])
def python_version(dummy):
dummy.emit(int(sys.version_info[0]),
int(sys.version_info[1]))
if sys.version_info[0:2] == expected_version:
raise RuntimeError
def test_createScript_schema_default(self):
with exasol.connect(scriptSchema='foo', **self.odbc_kwargs) as ecn:
@ecn.createScript(inArgs=[('a', INT)], outArgs=[('a', INT)])
def bar(ctx):
pass
rows = ecn.cursor().execute(dedent("""\
SELECT *
FROM EXA_ALL_SCRIPTS
WHERE script_name = 'BAR' and
script_schema = 'FOO'
""")).fetchall()
self.assertEqual(1, len(rows))
def test_createScript_works_with_decimal(self):
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(
inArgs=[('a', DOUBLE)],
outArgs=[('a', DECIMAL(10, 4))],
)
def foo(ctx):
import sys
ctx.emit(int(ctx.a))
self.assertEqual([['3']], foo(3.4, table='dual'))
def test_createScript_works_scalar_returns(self):
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(
inType=SCALAR,
inArgs=[('a', DOUBLE)],
outType=RETURNS,
outArgs=INT,
)
def foo(ctx):
return int(ctx.a)
self.assertEqual([['3']], foo(3.4, table='dual'))
def test_readCSV_gets_plausible_data(self):
with exasol.connect(**self.odbc_kwargs) as ecn:
crs = ecn.cursor()
crs.execute(
'SELECT sum(decimal1) FROM exasol_travis_python.data_exchange_table'
)
sum_ = crs.fetchone()[0]
rows = ecn.readCSV(
'SELECT decimal1 FROM exasol_travis_python.data_exchange_table'
)
self.assertEqual(
sum_,
reduce(operator.add,
[Decimal(row[0]) for row in rows if len(row)]))
def test_raises_no_error(self):
_sys_stderr = sys.stderr
try:
sys.stderr = StringIO()
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(inArgs=[('dummy', INT)],
outArgs=[('dummy', INT)])
def foo(dummy):
return
finally:
stderr = sys.stderr.getvalue()
sys.stderr = _sys_stderr
self.assertEqual(0, len(stderr))
def test_created_script_is_persistent(self):
with exasol.connect(**self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(
inArgs=[('a', INT)],
outArgs=[('a', INT)],
)
def foobar(ctx):
ctx.emit(4)
ecn.commit()
with pyodbc.connect(**self.odbc_kwargs) as con:
self.assertEqual(1, len(con.cursor().execute(
"SELECT * FROM EXA_ALL_SCRIPTS WHERE script_name = 'FOOBAR'").fetchall()))
def test_redirect_output_anyport(self):
buffer = StringIO()
with exasol.connect(clientAddress=(None, 0),
outputFile=buffer,
scriptSchema='foo',
useCSV=True,
**self.odbc_kwargs) as ecn:
@ecn.createScript(**self.script_kwargs)
def echo(ctx):
print(ctx.a)
return 'no output'
out = echo("'foobar'", table='dual')
self.assertEqual('no output', out[0][0])
self.assertIn('foobar', buffer.getvalue())
def test_writeCSV_works(self):
with exasol.connect(**self.odbc_kwargs) as ecn:
c = ecn.cursor()
c.execute('OPEN SCHEMA exasol_travis_python')
c.execute('DROP TABLE IF EXISTS T')
c.execute('CREATE TABLE T (x INT, y INT)')
ecn.writeCSV([[1, 2], [3, 4]], 'T')
rows = c.execute('SELECT * FROM exasol_travis_python.t').fetchall()
self.assertEqual(2, len(rows))
row0 = [x for x in rows[0]]
row1 = [x for x in rows[1]]
expected = sorted([row0, row1])
result = sorted([[Decimal(1), Decimal(2)],
[Decimal(3), Decimal(4)]])
c.execute('DROP TABLE exasol_travis_python.t')
self.assertEqual(expected, result)
def test_createScript_local_argorder_args(self):
with exasol.connect(useCSV=True, **self.odbc_kwargs) as ecn:
ecn.execute('OPEN SCHEMA foo')
@ecn.createScript(
inArgs=[
('c', DOUBLE),
('a', DOUBLE),
('d', VARCHAR(20)),
('b', VARCHAR(20)),
],
outType=RETURNS,
outArgs=VARCHAR(40),
)
def foo(ctx):
return 'a=' + str(ctx.a) + '; b=' + str(ctx.b) + '; c=' + str(ctx.c) + '; d=' + str(ctx.d)
result = foo(4.5, 1.3, "'foo'", "'bar'", table='dual')
self.assertEqual('a=1.3; b=bar; c=4.5; d=foo', result[0][0])
def test_writePandas_works(self):
with exasol.connect(**self.odbc_kwargs) as ecn:
c = ecn.cursor()
c.execute('OPEN SCHEMA exasol_travis_python')
c.execute('DROP TABLE IF EXISTS exasol_travis_python.t')
c.execute('CREATE TABLE T (x INT, y VARCHAR(10))')
# numpy arrays are transposed:
data = pandas.DataFrame({1: [1, 2], 2: ["a", "b"]})
ecn.writePandas(data, 'T')
rows = c.execute('SELECT * FROM exasol_travis_python.t').fetchall()
self.assertEqual(2, len(rows))
row0 = [x for x in rows[0]]
row1 = [x for x in rows[1]]
expected = sorted([row0, row1])
result = sorted([[Decimal(1), "a"], [Decimal(2), "b"]])
c.execute('DROP TABLE exasol_travis_python.t')
self.assertEqual(expected, result)
def setUp(self):
self.odbc_kwargs = {
#'DSN':'EXAODBC_TEST',
'Driver': 'EXAODBC',
'EXAHOST': os.environ['ODBC_HOST'],
'EXAUID': os.environ['EXAUSER'],
'EXAPWD': os.environ['EXAPW']
}
if 'ODBC_LOG' in os.environ:
self.odbc_kwargs['LOGFILE'] = os.environ['ODBC_LOG']
with exasol.connect(**self.odbc_kwargs) as ecn:
crs = ecn.cursor()
crs.execute('CREATE SCHEMA IF NOT EXISTS exasol_travis_python')
crs.execute('DROP TABLE IF EXISTS data_exchange_table')
crs.execute('CREATE TABLE data_exchange_table (decimal1 DECIMAL)')
for _ in range(0, 50):
rdm = random.random()
crs.execute(
'INSERT INTO data_exchange_table VALUES {}'.format(rdm))
crs.commit()
