def test_select_single_row_bool_result(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'SELECT TRUE') as query: cursor.execute(query) row = cursor.fetchone() assert row == [True] with query_fixture(cursor, configuration, 'SELECT FALSE') as query: cursor.execute(query) row = cursor.fetchone() assert row == [False]
def test_arrow_timelike_column_with_null(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, "INSERT TIMESTAMP") as table_name: cursor.execute(f"INSERT INTO {table_name} VALUES (?)", [None]) cursor.execute(f"SELECT a FROM {table_name}") result = cursor.fetchallarrow() assert result.column(0).to_pylist() == [None]
def test_arrow_string_column(dsn, configuration, strings_as_dictionary): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, "INSERT UNICODE") as table_name: cursor.execute(f"INSERT INTO {table_name} VALUES (?)", ["unicode \u2665"]) cursor.execute(f"SELECT a FROM {table_name}") result = cursor.fetchallarrow(strings_as_dictionary=strings_as_dictionary) assert result.column(0).to_pylist() == ["unicode \u2665"]
def test_arrow_reference_count(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, "INSERT INTEGER") as table_name: cursor.execute(f"SELECT a FROM {table_name}") result = cursor.fetchallarrow() gc.collect() assert sys.getrefcount(result) == 2
def test_passing_empty_list_of_columns_is_ok(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: cursor.executemanycolumns("INSERT INTO {} VALUES (42)".format(table_name), []) results = cursor.execute("SELECT A FROM {} ORDER BY A".format(table_name)).fetchall() assert results == [[42]]
def test_fetchmany_with_default_arraysize(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'SELECT MULTIPLE INTEGERS') as query: cursor.execute(query) rows = cursor.fetchmany() assert len(rows) == 1 assert rows[0] == [42]
def test_column_with_multiple_dimensions_raises(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: two_dimensional = array([[1, 2, 3], [4, 5, 6]], dtype='int64') columns = [two_dimensional] with pytest.raises(turbodbc.InterfaceError): cursor.executemanycolumns("INSERT INTO {} VALUES (?)".format(table_name), columns)
def test_columns_of_unequal_sizes_raise(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: columns = [MaskedArray([1, 2, 3], mask=False, dtype='int64'), MaskedArray([1, 2], mask=False, dtype='int64')] with pytest.raises(turbodbc.InterfaceError): cursor.executemanycolumns("INSERT INTO {} VALUES (?)".format(table_name), columns)
def test_rowcount_works_without_parameter_sets(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: cursor.execute( "INSERT INTO {} VALUES (42), (17)".format(table_name)) assert cursor.rowcount == 2
def test_description(dsn, configuration): capabilities = configuration['capabilities'] with open_cursor(configuration) as cursor: assert None == cursor.description def fix_case(string): if capabilities['reports_column_names_as_upper_case']: return string.upper() else: return string with query_fixture(cursor, configuration, 'DESCRIPTION') as table_name: cursor.execute("SELECT * FROM {}".format(table_name)) nullness_for_null_column = not capabilities[ 'indicates_null_columns'] expected = [(fix_case('as_int'), turbodbc.NUMBER, None, None, None, None, True), (fix_case('as_double'), turbodbc.NUMBER, None, None, None, None, True), (fix_case('as_varchar'), turbodbc.STRING, None, None, None, None, True), (fix_case('as_date'), turbodbc.DATETIME, None, None, None, None, True), (fix_case('as_timestamp'), turbodbc.DATETIME, None, None, None, None, True), (fix_case('as_int_not_null'), turbodbc.NUMBER, None, None, None, None, nullness_for_null_column)] assert expected == cursor.description
def test_arrow_string_column(dsn, configuration, strings_as_dictionary): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT UNICODE') as table_name: cursor.execute('INSERT INTO {} VALUES (?)'.format(table_name), [u'unicode \u2665']) cursor.execute('SELECT a FROM {}'.format(table_name)) result = cursor.fetchallarrow(strings_as_dictionary=strings_as_dictionary) assert result.column(0).to_pylist() == [u'unicode \u2665']
def test_arrow_reference_count(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: cursor.execute("SELECT a FROM {}".format(table_name)) result = cursor.fetchallarrow() gc.collect() assert sys.getrefcount(result) == 2
def test_numpy_string_column_with_null(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT STRING') as table_name: cursor.execute('INSERT INTO {} VALUES (?)'.format(table_name), [None]) cursor.execute('SELECT a FROM {}'.format(table_name)) results = cursor.fetchallnumpy() expected = MaskedArray([None], mask=[0], dtype=numpy.object_) assert_equal(results[_fix_case(configuration, 'a')], expected)
def test_number_of_columns_does_not_match_parameter_count(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, "INSERT INTEGER") as table_name: columns = [array([42], dtype="int64"), array([17], dtype="int64")] with pytest.raises(turbodbc.InterfaceError): cursor.executemanycolumns( f"INSERT INTO {table_name} VALUES (?)", columns)
def test_column_with_incompatible_dtype_raises(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, "INSERT INTEGER") as table_name: columns = [MaskedArray([1, 2, 3], mask=False, dtype="int16")] with pytest.raises(turbodbc.InterfaceError): cursor.executemanycolumns( f"INSERT INTO {table_name} VALUES (?)", columns)
def test_numpy_double_column(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'SELECT DOUBLE') as query: cursor.execute(query) results = cursor.fetchallnumpy() expected = MaskedArray([3.14], mask=[0]) assert results[_fix_case(configuration, 'a')].dtype == numpy.float64 assert_equal(results[_fix_case(configuration, 'a')], expected)
def test_numpy_empty_column(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: cursor.execute("SELECT a FROM {}".format(table_name)) results = cursor.fetchallnumpy() assert isinstance(results, OrderedDict) assert len(results) == 1 # ncols assert isinstance(results[_fix_case(configuration, 'a')], MaskedArray)
def test_column_of_unsupported_type_raises(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, "INSERT INTEGER") as table_name: columns = ["this is not a NumPy MaskedArray"] with pytest.raises(turbodbc.InterfaceError): cursor.executemanycolumns( f"INSERT INTO {table_name} VALUES (?)", columns)
def test_arrow_two_columns(dsn, configuration): with open_cursor(configuration, rows_to_buffer=1) as cursor: with query_fixture(cursor, configuration, 'INSERT TWO INTEGER COLUMNS') as table_name: cursor.executemany("INSERT INTO {} VALUES (?, ?)".format(table_name), [[1, 42], [2, 41]]) cursor.execute("SELECT a, b FROM {} ORDER BY a".format(table_name)) result = list(cursor.fetcharrowbatches()) assert len(result) == 2
def test_insert_no_parameter_list(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: cursor.executemany("INSERT INTO {} VALUES (?)".format(table_name)) assert 0 == cursor.rowcount cursor.execute("SELECT a FROM {}".format(table_name)) inserted = [list(row) for row in cursor.fetchall()] assert 0 == len(inserted)
def test_insert_empty_parameter_list(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, "INSERT INTEGER") as table_name: cursor.executemany(f"INSERT INTO {table_name} VALUES (?)", []) assert 0 == cursor.rowcount cursor.execute(f"SELECT a FROM {table_name}") inserted = [list(row) for row in cursor.fetchall()] assert [] == inserted
def test_arrow_string_column_with_null(dsn, configuration, strings_as_dictionary): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT STRING') as table_name: cursor.execute('INSERT INTO {} VALUES (?)'.format(table_name), [None]) cursor.execute('SELECT a FROM {}'.format(table_name)) result = cursor.fetchallarrow(strings_as_dictionary=strings_as_dictionary) result.column(0).null_count == 1 result.column(0).to_pylist() == [None]
def test_insert_duplicate_uniquecol_raises(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT DUPLICATE UNIQUECOL') as table_name: with pytest.raises(DatabaseError) as ex: cursor.execute("INSERT INTO {table_name} VALUES (1)".format(table_name=table_name)) # some databases (e.g. exasol) report failure not in the execute statement above, but only # when closing the odbc handle, i.e. at cursor.close: cursor.close()
def test_arrow_string_column_with_null(dsn, configuration, strings_as_dictionary): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, "INSERT STRING") as table_name: cursor.execute(f"INSERT INTO {table_name} VALUES (?)", [None]) cursor.execute(f"SELECT a FROM {table_name}") result = cursor.fetchallarrow(strings_as_dictionary=strings_as_dictionary) result.column(0).null_count == 1 result.column(0).to_pylist() == [None]
def test_rowcount_is_reset_after_executemany_raises(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: cursor.execute("INSERT INTO {} VALUES (?)".format(table_name), [42]) assert cursor.rowcount == 1 with pytest.raises(Error): cursor.executemany("this is not even a valid SQL statement") assert cursor.rowcount == -1
def test_passing_empty_column_is_ok(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: columns = [array([], dtype='int64')] cursor.executemanycolumns("INSERT INTO {} VALUES (?)".format(table_name), columns) results = cursor.execute("SELECT A FROM {} ORDER BY A".format(table_name)).fetchall() assert results == []
def test_rowcount_is_reset_after_execute_raises(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: cursor.execute("INSERT INTO {} VALUES (?)".format(table_name), [42]) assert cursor.rowcount == 1 with pytest.raises(Error): cursor.execute("this is not even a valid SQL statement") assert cursor.rowcount == -1
def test_arrow_empty_column(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: cursor.execute("SELECT a FROM {}".format(table_name)) result = cursor.fetchallarrow() assert isinstance(result, pa.Table) assert result.num_columns == 1 assert result.num_rows == 0
def test_arrow_empty_column(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, "INSERT INTEGER") as table_name: cursor.execute(f"SELECT a FROM {table_name}") result = cursor.fetchallarrow() assert isinstance(result, pa.Table) assert result.num_columns == 1 assert result.num_rows == 0
def _test_insert_one(configuration, fixture_name, data): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, fixture_name) as table_name: cursor.execute("INSERT INTO {} VALUES (?)".format(table_name), data) assert 1 == cursor.rowcount cursor.execute("SELECT a FROM {}".format(table_name)) inserted = cursor.fetchall() assert [list(data)] == inserted
def test_numpy_unicode_column_with_truncation(dsn, configuration): with open_cursor(configuration, rows_to_buffer=1, varchar_max_character_limit=9, limit_varchar_results_to_max=True) as cursor: with query_fixture(cursor, configuration, 'INSERT UNICODE MAX') as table_name: cursor.execute('INSERT INTO {} VALUES (?)'.format(table_name), [u'truncated string']) cursor.execute('SELECT a FROM {}'.format(table_name)) results = cursor.fetchallnumpy() expected = MaskedArray([u'truncated'], mask=[0], dtype=numpy.object_) assert results[_fix_case(configuration, 'a')].dtype == numpy.object_ assert_equal(results[_fix_case(configuration, 'a')], expected)
def test_arrow_date_column(dsn, configuration): date = datetime.date(2015, 12, 31) with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, "INSERT DATE") as table_name: cursor.execute(f"INSERT INTO {table_name} VALUES (?)", [date]) cursor.execute(f"SELECT a FROM {table_name}") result = cursor.fetchallarrow() result.column(0).to_pylist() == [datetime.date(2015, 12, 31)]
def test_arrow_date_column(dsn, configuration): date = datetime.date(2015, 12, 31) with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT DATE') as table_name: cursor.execute('INSERT INTO {} VALUES (?)'.format(table_name), [date]) cursor.execute('SELECT a FROM {}'.format(table_name)) result = cursor.fetchallarrow() result.column(0).to_pylist() == [datetime.date(2015, 12, 31)]
def test_insert_string_column_with_truncation(dsn, configuration): with open_cursor(configuration, varchar_max_character_limit=9, limit_varchar_results_to_max=True) as cursor: with query_fixture(cursor, configuration, 'INSERT LONG STRING') as table_name: cursor.execute("INSERT INTO {} VALUES (?)".format(table_name), ['Truncated strings suck']) cursor.execute("SELECT a FROM {}".format(table_name)) assert cursor.fetchall() == [['Truncated']]
def test_arrow_binary_column_larger_than_batch_size(dsn, configuration): with open_cursor(configuration, rows_to_buffer=2) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: cursor.executemany("INSERT INTO {} VALUES (?)".format(table_name), [[1], [2], [3], [4], [5]]) cursor.execute("SELECT a FROM {} ORDER BY a".format(table_name)) result = cursor.fetchallarrow() assert isinstance(result, pa.Table) assert result.column(0).to_pylist() == [1, 2, 3, 4, 5]
def test_fetchall(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'SELECT MULTIPLE INTEGERS') as query: cursor.execute(query) rows = cursor.fetchall() assert len(rows) == 3 assert rows[0] == [42] assert rows[1] == [43] assert rows[2] == [44]
def test_fetchmany_with_bad_arraysize_parameter_raises(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'SELECT MULTIPLE INTEGERS') as query: cursor.execute(query) with pytest.raises(turbodbc.InterfaceError): cursor.fetchmany(-1) with pytest.raises(turbodbc.InterfaceError): cursor.fetchmany(0)
def test_numpy_unicode_column(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT UNICODE') as table_name: cursor.execute('INSERT INTO {} VALUES (?)'.format(table_name), [u'unicode \u2665']) cursor.execute('SELECT a FROM {}'.format(table_name)) results = cursor.fetchallnumpy() expected = MaskedArray([u'unicode \u2665'], mask=[0], dtype=numpy.object_) assert results[_fix_case(configuration, 'a')].dtype == numpy.object_ assert_equal(results[_fix_case(configuration, 'a')], expected)
def _test_insert_many(configuration, fixture_name, data): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, fixture_name) as table_name: cursor.executemany("INSERT INTO {} VALUES (?)".format(table_name), data) assert len(data) == cursor.rowcount cursor.execute("SELECT a FROM {} ORDER BY a".format(table_name)) inserted = cursor.fetchall() data = [list(row) for row in data] assert data == inserted
def test_numpy_binary_column_with_null(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT TWO INTEGER COLUMNS') as table_name: cursor.executemany("INSERT INTO {} VALUES (?, ?)".format(table_name), [[42, 1], [None, 2]]) # second column to enforce ordering cursor.execute("SELECT a FROM {} ORDER BY b".format(table_name)) results = cursor.fetchallnumpy() expected = MaskedArray([42, 0], mask=[0, 1]) assert_equal(results[_fix_case(configuration, 'a')], expected)
def test_numpy_binary_column_larger_than_batch_size(dsn, configuration): with open_cursor(configuration, rows_to_buffer=2) as cursor: with query_fixture(cursor, configuration, 'INSERT INTEGER') as table_name: cursor.executemany("INSERT INTO {} VALUES (?)".format(table_name), [[1], [2], [3], [4], [5]]) cursor.execute("SELECT a FROM {} ORDER BY a".format(table_name)) results = cursor.fetchallnumpy() expected = MaskedArray([1, 2, 3, 4, 5], mask=False) assert_equal(results[_fix_case(configuration, 'a')], expected)
def test_arrow_timelike_column_with_null(dsn, configuration): fill_value = 0; with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT TIMESTAMP') as table_name: cursor.execute('INSERT INTO {} VALUES (?)'.format(table_name), [None]) cursor.execute('SELECT a FROM {}'.format(table_name)) result = cursor.fetchallarrow() assert result.column(0).to_pylist() == [None]
def test_numpy_two_columns(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT TWO INTEGER COLUMNS') as table_name: cursor.executemany("INSERT INTO {} VALUES (?, ?)".format(table_name), [[1, 42], [2, 41]]) cursor.execute("SELECT a, b FROM {} ORDER BY a".format(table_name)) results = cursor.fetchallnumpy() assert_equal(results[_fix_case(configuration, 'a')], MaskedArray([1, 2], mask=False)) assert_equal(results[_fix_case(configuration, 'b')], MaskedArray([42, 41], mask=False))
def test_numpy_boolean_column(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT INDEXED BOOL') as table_name: cursor.executemany('INSERT INTO {} VALUES (?, ?)'.format(table_name), [[True, 1], [False, 2], [True, 3]]) cursor.execute('SELECT a FROM {} ORDER BY b'.format(table_name)) results = cursor.fetchallnumpy() expected = MaskedArray([True, False, True], mask=[0]) assert results[_fix_case(configuration, 'a')].dtype == numpy.bool_ assert_equal(results[_fix_case(configuration, 'a')], expected)
def test_arrow_timestamp_column(dsn, configuration): supported_digits = configuration['capabilities']['fractional_second_digits'] fractional = generate_microseconds_with_precision(supported_digits) timestamp = datetime.datetime(2015, 12, 31, 1, 2, 3, fractional) with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT TIMESTAMP') as table_name: cursor.execute('INSERT INTO {} VALUES (?)'.format(table_name), [timestamp]) cursor.execute('SELECT a FROM {}'.format(table_name)) result = cursor.fetchallarrow() assert result.column(0).to_pylist() == [timestamp]
def test_arrow_two_columns(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'INSERT TWO INTEGER COLUMNS') as table_name: cursor.executemany("INSERT INTO {} VALUES (?, ?)".format(table_name), [[1, 42], [2, 41]]) cursor.execute("SELECT a, b FROM {} ORDER BY a".format(table_name)) result = cursor.fetchallarrow() assert result.to_pydict() == OrderedDict([ (_fix_case(configuration, 'a'), [1, 2]), (_fix_case(configuration, 'b'), [42, 41])] )
def test_arrow_double_column(dsn, configuration): with open_cursor(configuration) as cursor: with query_fixture(cursor, configuration, 'SELECT DOUBLE') as query: cursor.execute(query) result = cursor.fetchallarrow() assert isinstance(result, pa.Table) assert result.num_columns == 1 assert result.num_rows == 1 assert result.column(0).name == _fix_case(configuration, "a") assert str(result.column(0).type) == "double" assert result.column(0).to_pylist() == [3.14]