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()