def _execute_python(self,
operation: str,
parameters: parameters_type = None) -> 'Cursor':
"""
Execute operation with Python based statement preparation.
Args:
operation: the query you want to execute
parameters: an optional iterable containing arguments for the operation.
Returns:
the cursor object itself
Raises:
OperationalError: if the execution failed
"""
self._check_connection()
self.description = None # which will be set later in fetchall
self.connection.cleanup_result()
splitted = strip_split_and_clean(operation)
if len(splitted) == 0:
raise ProgrammingError("Empty query")
if len(splitted) > 1:
raise ProgrammingError("Multiple queries in one execute() call")
formatted = format_query(operation, parameters)
self.connection.result, self.rowcount = self.connection.query(
formatted, make_result=True)
self.connection.total_changes += self.rowcount
self._set_description()
return self
def _format_iterable(cleaned_query: str, parameters: Sequence[Any],
query: str):
# we do this a bit strange to make sure the test_ExecuteParamSequence sqlite test passes
escaped_list: List[str] = [
convert(parameters[i]) for i in range(len(parameters))
]
if ':' in cleaned_query:
x = sub(r':(\w+)', r'{\1}', query)
# The numbering used starts at 1, while python starts 0, so we insert a bogus prefix
prefixed = [''] + escaped_list
return x.format(*prefixed)
if '?' in cleaned_query: # qmark style
if cleaned_query.count('?') != len(escaped_list):
raise ProgrammingError(
f"Number of arguments ({len(escaped_list)}) doesn't "
f"match number of '?' ({cleaned_query.count('?')})")
return query.replace('?', '{}').format(*escaped_list)
elif '%s' in cleaned_query: # pyformat style
return query % tuple(escaped_list)
else:
return cleaned_query
def format_query(query: str, parameters: parameters_type = None) -> str:
if type(query) != str:
raise TypeError
cleaned_query = remove_quoted_substrings(query)
if parameters is None:
for symbol in ':?':
if symbol in cleaned_query:
raise ProgrammingError(
f"unexpected symbol '{symbol}' in operation")
return query
# named, numeric or format style
if isinstance(parameters, Dict):
return _format_mapping(cleaned_query, parameters, query)
# qmark or pyformat style
elif isinstance(parameters,
Sequence) or (isinstance(parameters, Sized)
and hasattr(parameters, '__getitem__')):
return _format_iterable(cleaned_query, parameters, query)
else:
raise ValueError(
f"parameters '{parameters}' type '{type(parameters)}' not supported"
)
def _check_result(self) -> None:
"""
Check if an operation has been executed and a result is available.
Raises:
ProgrammingError: if no result is available.
"""
if not self.result:
raise ProgrammingError("fetching data but no query executed")
def _check_connection(self):
"""
Check if we are attached to the lower level interface
Raises:
ProgrammingError: if no lower level interface is attached
"""
if not hasattr(self, 'connection') or not self.connection:
raise ProgrammingError(
"no connection to lower level database available")
def commit(self) -> 'Cursor':
"""
Commit the current pending transaction.
Returns:
the current cursor
"""
if not hasattr(self, 'connection') or not self.connection:
raise ProgrammingError(
"no connection to lower level database available")
self.connection.commit()
return self
def _format_mapping(cleaned_query: str, parameters: Dict[str, Any],
query: str):
if '?' in cleaned_query:
raise ProgrammingError("'?' in formatting with mapping as parameters")
escaped: Dict[str, str] = {k: convert(v) for k, v in parameters.items()}
if ':' in cleaned_query: # qmark
x = sub(r':(\w+)', r'{\1}', query)
elif '%' in cleaned_query:
return query % escaped
if hasattr(type(parameters), '__missing__'):
# this is something like a dict with a default value
try:
# mypy doesn't understand that this is a dict-like with a default __missing__ value
return DefaultFormatter(parameters).format(x, **escaped)
except KeyError as e:
raise ProgrammingError(e)
try:
return x.format(**escaped)
except KeyError as e:
raise ProgrammingError(e)
def execute(self,
operation: str,
parameters: Optional[Iterable] = None) -> 'Cursor':
"""
Execute operation
Args:
operation: the query you want to execute
parameters: an optional iterable containing arguments for the operation.
Returns:
the cursor object itself
Raises:
OperationalError: if the execution failed
"""
self._check_connection()
self.description = None # which will be set later in fetchall
self._fetch_generator = None
if self.result:
self.connection.lowlevel.cleanup_result(
self.result) # type: ignore
self.result = None
splitted = strip_split_and_clean(operation)
if len(splitted) == 0:
raise ProgrammingError("Empty query")
if len(splitted) > 1:
raise ProgrammingError("Multiple queries in one execute() call")
formatted = format_query(operation, parameters)
self.result, self.rowcount = self.connection.lowlevel.query(
formatted, make_result=True) # type: ignore
self.connection.total_changes += self.rowcount
self._set_description()
return self
def numpy_monetdb_map(numpy_type: np.dtype):
if numpy_type.kind == 'U':
# this is an odd one, the numpy type string includes the width. Also, we don't format
# monetdb string columns as fixed width numpy columns yet, so technically this type is
# non-reversable for now.
return MonetdbTypeInfo(lib.monetdbe_str, "string", numpy_type,
"char *", None)
if numpy_type.kind == 'M':
# TODO: another odd one
return MonetdbTypeInfo(lib.monetdbe_timestamp, "timestamp",
np.dtype(np.datetime64), "int64_t", None)
if numpy_type.kind in supported_numpy_types: # type: ignore
return numpy_type_map[numpy_type]
raise ProgrammingError(
f"append() called with unsupported type {numpy_type}")
def executemany(
self, operation: str,
seq_of_parameters: Union[Iterator,
Iterable[Iterable]]) -> 'Cursor':
"""
Prepare a database operation (query or command) and then execute it against all parameter sequences or
mappings found in the sequence seq_of_parameters.
Args:
operation: the SQL query to execute
seq_of_parameters: An optional iterator or iterable containing an iterable of arguments
"""
self._check_connection()
self.description = None # which will be set later in fetchall
if self.result:
self.connection.lowlevel.cleanup_result(
self.result) # type: ignore
self.result = None
total_affected_rows = 0
if operation[:6].lower().strip() == 'select':
raise ProgrammingError(
"Don't use a SELECT statement with executemany()")
if hasattr(seq_of_parameters, '__iter__'):
iterator = iter(seq_of_parameters)
else:
iterator = seq_of_parameters # type: ignore # mypy gets confused here
while True:
try:
parameters = next(iterator)
except StopIteration:
break
formatted = format_query(operation, parameters)
self.result, affected_rows = self.connection.lowlevel.query(
formatted, make_result=True) # type: ignore
total_affected_rows += affected_rows
self.rowcount = total_affected_rows
self.connection.total_changes += total_affected_rows
self._set_description()
return self
def format_query(
query: str, # type: ignore
parameters: Optional[Union[Iterable[str], Dict[str, Any], Sized]] = None
) -> str: # type: ignore
if type(query) != str:
raise TypeError
cleaned_query = remove_quoted_substrings(query)
if parameters is not None:
# named, numeric or format style
if hasattr(type(parameters), '__getitem__') and hasattr(type(parameters), 'keys') \
and hasattr(type(parameters), 'items'):
if '?' in cleaned_query:
raise ProgrammingError(
"'?' in formatting with mapping as parameters")
escaped: Dict[str, str] = {
k: convert(v)
for k, v in parameters.items()
} # type: ignore
if ':' in cleaned_query: # qmark
x = sub(r':(\w+)', r'{\1}', query)
elif '%' in cleaned_query: # pyformat
return query % escaped
if hasattr(type(parameters), '__missing__'):
# this is something like a dict with a default value
try:
# mypy doesn't understand that this is a dict-like with a default __missing__ value
return DefaultFormatter(parameters).format(
x, **escaped) # type: ignore
except KeyError as e:
raise ProgrammingError(e)
try:
return x.format(**escaped)
except KeyError as e:
raise ProgrammingError(e)
# qmark or pyformat style
elif hasattr(type(parameters), '__iter__') \
or (hasattr(type(parameters), '__len__') and hasattr(type(parameters), '__getitem__')):
# todo (gijs): check typing
# we do this a bit strange to make sure the test_ExecuteParamSequence sqlite test passes
escaped_list: List[Optional[str]] = [
convert(parameters[i]) for i in range(len(parameters))
] # type: ignore
if ':' in cleaned_query:
# raise ProgrammingError("':' in formatting with named style parameters")
x = sub(r':(\w+)', r'{\1}', query)
# off by one error
# todo (gijs): check typing
prefixed = [None] + escaped_list # type: ignore
return x.format(*prefixed)
if '?' in cleaned_query: # named style
if cleaned_query.count('?') != len(escaped_list):
raise ProgrammingError(
f"Number of arguments ({len(escaped_list)}) doesn't "
f"match number of '?' ({cleaned_query.count('?')})")
return query.replace('?', '{}').format(*escaped_list)
elif '%s' in cleaned_query: # pyformat style
return query % tuple(escaped_list)
else:
return cleaned_query
else:
raise ValueError(
f"parameters '{parameters}' type '{type(parameters)}' not supported"
)
else:
for symbol in ':?':
if symbol in cleaned_query:
raise ProgrammingError(
f"unexpected symbol '{symbol}' in operation")
return query
def numpy_monetdb_map(numpy_type: np.dtype):
if numpy_type.kind in ('i', 'f'): # type: ignore
return numpy_type_map[numpy_type]
raise ProgrammingError("append() only support int and float family types")