def open(self): if not self.dbdir: url = ffi.NULL else: url = str(self.dbdir).encode() p_connection = ffi.new("monetdbe_database *") p_options = ffi.new("monetdbe_options *") p_options.memorylimit = self.memorylimit p_options.querytimeout = self.querytimeout p_options.sessiontimeout = self.sessiontimeout p_options.nr_threads = self.nr_threads result_code = lib.monetdbe_open(p_connection, url, p_options) connection = p_connection[0] errors = { 0: "OK", -1: "Allocation failed", -2: "Error in DB", } if result_code: if result_code == -2: error = ffi.string(lib.monetdbe_error(connection)).decode() lib.monetdbe_close(connection) else: error = errors.get(result_code, "unknown error") raise exceptions.OperationalError( f"Failed to open database: {error} (code {result_code})") return connection
def query(self, query: str, make_result: bool = False) -> Tuple[Optional[Any], int]: """ Execute a query. Args: query: the query make_results: Create and return a result object. If enabled, you need to call cleanup_result on the result afterwards returns: result, affected_rows """ if make_result: p_result = ffi.new("monetdbe_result **") else: p_result = ffi.NULL affected_rows = ffi.new("monetdbe_cnt *") check_error( lib.monetdbe_query(self._connection, query.encode(), p_result, affected_rows)) if make_result: result = p_result[0] else: result = None return result, affected_rows[0]
def append(self, table: str, data: Mapping[str, np.ndarray], schema: str = 'sys') -> None: """ Directly append an array structure """ n_columns = len(data) existing_columns = list(self.get_columns(schema=schema, table=table)) existing_names, existing_types = zip(*existing_columns) if not set(existing_names) == set(data.keys()): error = f"Appended column names ({', '.join(str(i) for i in data.keys())}) " \ f"don't match existing column names ({', '.join(existing_names)})" raise exceptions.ProgrammingError(error) work_columns = ffi.new(f'monetdbe_column * [{n_columns}]') work_objs = [] for column_num, (column_name, existing_type) in enumerate(existing_columns): column_values = data[column_name] work_column = ffi.new('monetdbe_column *') work_type_string, work_type = numpy_monetdb_map(column_values.dtype) if not work_type == existing_type: existing_type_string = monet_numpy_map[existing_type][0] error = f"Type '{work_type_string}' for appended column '{column_name}' " \ f"does not match table type '{existing_type_string}'" raise exceptions.ProgrammingError(error) work_column.type = work_type work_column.count = column_values.shape[0] work_column.name = ffi.new('char[]', column_name.encode()) work_column.data = ffi.cast(f"{work_type_string} *", ffi.from_buffer(column_values)) work_columns[column_num] = work_column work_objs.append(work_column) check_error(lib.monetdbe_append(self._connection, schema.encode(), table.encode(), work_columns, n_columns))
def get_columns(self, table: str, schema: str = 'sys') -> Iterator[Tuple[str, int]]: count_p = ffi.new('size_t *') names_p = ffi.new('char ***') types_p = ffi.new('int **') lib.monetdbe_get_columns(self._connection, schema.encode(), table.encode(), count_p, names_p, types_p) for i in range(count_p[0]): name = ffi.string(names_p[0][i]).decode() type_ = types_p[0][i] yield name, type_
def get_columns(self, table: str, schema: str = 'sys') -> Iterator[Tuple[str, int]]: self._switch() count_p = ffi.new('size_t*') columns_p = ffi.new('monetdbe_column**') lib.monetdbe_get_columns(self._monetdbe_database, schema.encode(), table.encode(), count_p, columns_p) for i in range(count_p[0]): name = ffi.string(columns_p[0][i].name).decode() type_ = columns_p[0][i].type yield name, type_
def bind_time(data: datetime.time) -> ffi.CData: struct = ffi.new("monetdbe_data_time *") struct.ms = int(data.microsecond / 1000) struct.seconds = data.second struct.minutes = data.minute struct.hours = data.hour return struct
def bind_timedelta(data: datetime.timedelta) -> ffi.CData: struct = ffi.new("monetdbe_data_time *") struct.ms = int(data.microseconds / 1000) struct.seconds = data.seconds struct.minutes = 0 struct.hours = 0 return struct
def prepare(self, query: str) -> monetdbe_statement: self._switch() stmt = ffi.new("monetdbe_statement **") check_error( lib.monetdbe_prepare(self._monetdbe_database, str(query).encode(), stmt)) return stmt[0]
def execute(statement: monetdbe_statement, make_result: bool = False) -> Tuple[monetdbe_result, int]: if make_result: p_result = ffi.new("monetdbe_result **") else: p_result = ffi.NULL affected_rows = ffi.new("monetdbe_cnt *") check_error(lib.monetdbe_execute(statement, p_result, affected_rows)) if make_result: result = p_result[0] else: result = None return result, affected_rows[0]
def bind_datetime(data: datetime.datetime) -> ffi.CData: struct = ffi.new("monetdbe_data_timestamp *") struct.date.day = data.day struct.date.month = data.month struct.date.year = data.year struct.time.ms = int(data.microsecond / 1000) struct.time.seconds = data.second struct.time.minutes = data.minute struct.time.hours = data.hour return struct
def prepare(self, query: str) -> monetdbe_statement: self._switch() stmt = ffi.new("monetdbe_statement **") p_result = ffi.new("monetdbe_result **") check_error( lib.monetdbe_prepare(self._monetdbe_database, str(query).encode(), stmt, p_result)) input_parameter_info = list() for r in range(p_result[0].nrows): if (extract(result_fetch(p_result[0], 3), r)) is None: row = TypeInfo(impl_type=extract(result_fetch(p_result[0], 6), r), sql_type=extract(result_fetch(p_result[0], 0), r), scale=extract(result_fetch(p_result[0], 2), r)) input_parameter_info.append(row) return stmt[0], input_parameter_info
def prepare(self, query): # todo (gijs): use :) stmt = ffi.new("monetdbe_statement **") lib.monetdbe_prepare(self._connection, query.encode(), stmt) return stmt[0]
def get_autocommit(): value = ffi.new("int *") check_error(lib.monetdbe_get_autocommit(value)) return value[0]
def result_fetch(result: ffi.CData, column: int): p_rcol = ffi.new("monetdbe_column **") check_error(lib.monetdbe_result_fetch(result, p_rcol, column)) return p_rcol[0]
def monetdbe_decimal_to_sht(data: int) -> ffi.CData: return ffi.new("int16_t *", data)
def monetdbe_decimal_to_lng(data: int) -> ffi.CData: return ffi.new("int64_t *", data)
def bind_str(data: str) -> ffi.CData: return ffi.new("char[]", str(data).encode())
def monetdbe_decimal_to_bte(data: int) -> ffi.CData: return ffi.new("int8_t *", data)
def append(self, table: str, data: Mapping[str, np.ndarray], schema: str = 'sys') -> None: """ Directly append an array structure """ self._switch() n_columns = len(data) existing_columns = list(self.get_columns(schema=schema, table=table)) existing_names, existing_types = zip(*existing_columns) if not set(existing_names) == set(data.keys()): error = f"Appended column names ({', '.join(str(i) for i in data.keys())}) " \ f"don't match existing column names ({', '.join(existing_names)})" raise exceptions.ProgrammingError(error) work_columns = ffi.new(f'monetdbe_column * [{n_columns}]') work_objs = [] # cffi_objects assists to keep all in-memory native data structure alive during the execution of this call cffi_objects = list() for column_num, (column_name, existing_type) in enumerate(existing_columns): column_values = data[column_name] work_column = ffi.new('monetdbe_column *') type_info = numpy_monetdb_map(column_values.dtype) # try to convert the values if types don't match if type_info.c_type != existing_type: if type_info.c_type == lib.monetdbe_timestamp and existing_type == lib.monetdbe_date and np.issubdtype( column_values.dtype, np.datetime64): """ We are going to cast to a monetdbe_date and consider monetdbe_timestamp as a 'base type' to signal this. """ type_info = timestamp_to_date() else: precision_warning(type_info.c_type, existing_type) to_numpy_type = monet_c_type_map[existing_type].numpy_type try: column_values = column_values.astype(to_numpy_type) type_info = numpy_monetdb_map(column_values.dtype) except Exception as e: existing_type_string = monet_c_type_map[ existing_type].c_string_type error = f"Can't convert '{type_info.c_string_type}' " \ f"to type '{existing_type_string}' for column '{column_name}': {e} " raise ValueError(error) work_column.type = type_info.c_type work_column.count = column_values.shape[0] work_column.name = ffi.new('char[]', column_name.encode()) if type_info.numpy_type.kind == 'M': t = ffi.new('monetdbe_data_timestamp[]', work_column.count) cffi_objects.append(t) unit = np.datetime_data(column_values.dtype)[0].encode() p = ffi.from_buffer("int64_t*", column_values) lib.initialize_timestamp_array_from_numpy( self._monetdbe_database, t, work_column.count, p, unit, existing_type) work_column.data = t elif type_info.numpy_type.kind == 'U': # first massage the numpy array of unicode into a matrix of null terminated rows of bytes. m = ffi.from_buffer( "bool*", column_values.mask) if np.ma.isMaskedArray( column_values) else 0 # type: ignore[attr-defined] cffi_objects.append(m) v = np.char.encode(column_values).view('b').reshape( (work_column.count, -1)) v = np.c_[v, np.zeros(work_column.count, dtype=np.int8)] stride_length = v.shape[1] cffi_objects.append(v) t = ffi.new('char*[]', work_column.count) cffi_objects.append(t) p = ffi.from_buffer("char*", v) cffi_objects.append(p) lib.initialize_string_array_from_numpy(t, work_column.count, p, stride_length, ffi.cast("bool*", m)) work_column.data = t else: p = ffi.from_buffer(f"{type_info.c_string_type}*", column_values) cffi_objects.append(p) work_column.data = p work_columns[column_num] = work_column work_objs.append(work_column) check_error( lib.monetdbe_append(self._monetdbe_database, schema.encode(), table.encode(), work_columns, n_columns))
def bind_date(data: datetime.date) -> ffi.CData: struct = ffi.new("monetdbe_data_date *") struct.day = data.day struct.month = data.month struct.year = data.year return struct
def monetdbe_int(data: int) -> ffi.CData: if data > 2**32: return ffi.new("int64_t *", data) else: return ffi.new("int *", data)
def bind_float(data: float) -> ffi.CData: return ffi.new("double *", data)
def bind_memoryview(data: memoryview) -> ffi.CData: struct = ffi.new("monetdbe_data_blob *") bytes_ = data.tobytes() struct.size = len(bytes_) struct.data = ffi.new("char []", bytes_) return struct