def test_peekIterator_nonlist_rows_unconverted(self):
from google.cloud.spanner_dbapi.utils import PeekIterator
pi = PeekIterator(["a", "b", "c", "d", "e"])
got = list(pi)
want = ["a", "b", "c", "d", "e"]
self.assertEqual(got, want, "Values should be returned unchanged")
def _handle_DQL(self, sql, params):
with self.connection.database.snapshot() as snapshot:
# Reference
# https://googleapis.dev/python/spanner/latest/session-api.html#google.cloud.spanner_v1.session.Session.execute_sql
sql, params = parse_utils.sql_pyformat_args_to_spanner(sql, params)
res = snapshot.execute_sql(
sql, params=params, param_types=get_param_types(params)
)
if type(res) == int:
self._row_count = res
self._itr = None
else:
# Immediately using:
# iter(response)
# here, because this Spanner API doesn't provide
# easy mechanisms to detect when only a single item
# is returned or many, yet mixing results that
# are for .fetchone() with those that would result in
# many items returns a RuntimeError if .fetchone() is
# invoked and vice versa.
self._result_set = res
# Read the first element so that the StreamedResultSet can
# return the metadata after a DQL statement. See issue #155.
while True:
try:
self._itr = PeekIterator(self._result_set)
break
except Aborted:
self.connection.retry_transaction()
# Unfortunately, Spanner doesn't seem to send back
# information about the number of rows available.
self._row_count = _UNSET_COUNT
def _handle_DQL_with_snapshot(self, snapshot, sql, params):
self._result_set = snapshot.execute_sql(sql, params, get_param_types(params))
# Read the first element so that the StreamedResultSet can
# return the metadata after a DQL statement.
self._itr = PeekIterator(self._result_set)
# Unfortunately, Spanner doesn't seem to send back
# information about the number of rows available.
self._row_count = _UNSET_COUNT
def test_peekIterator_list_rows_converted_to_tuples(self):
# Cloud Spanner returns results in lists e.g. [result].
# PeekIterator is used by BaseCursor in its fetch* methods.
# This test ensures that anything passed into PeekIterator
# will be returned as a tuple.
pit = PeekIterator([["a"], ["b"], ["c"], ["d"], ["e"]])
got = list(pit)
want = [("a",), ("b",), ("c",), ("d",), ("e",)]
self.assertEqual(
got, want, "Rows of type list must be returned as tuples"
)
seventeen = PeekIterator([[17]])
self.assertEqual(list(seventeen), [(17,)])
pit = PeekIterator([["%", "%d"]])
self.assertEqual(next(pit), ("%", "%d"))
pit = PeekIterator([("Clark", "Kent")])
self.assertEqual(next(pit), ("Clark", "Kent"))
def execute(self, sql, args=None):
"""Prepares and executes a Spanner database operation.
:type sql: str
:param sql: A SQL query statement.
:type args: list
:param args: Additional parameters to supplement the SQL query.
"""
if not self.connection:
raise ProgrammingError("Cursor is not connected to the database")
self._raise_if_closed()
self._result_set = None
# Classify whether this is a read-only SQL statement.
try:
classification = parse_utils.classify_stmt(sql)
if classification == parse_utils.STMT_DDL:
self.connection._ddl_statements.append(sql)
return
# For every other operation, we've got to ensure that
# any prior DDL statements were run.
# self._run_prior_DDL_statements()
self.connection.run_prior_DDL_statements()
if not self.connection.autocommit:
transaction = self.connection.transaction_checkout()
sql, params = parse_utils.sql_pyformat_args_to_spanner(
sql, args)
self._result_set = transaction.execute_sql(
sql, params, param_types=get_param_types(params))
self._itr = PeekIterator(self._result_set)
return
if classification == parse_utils.STMT_NON_UPDATING:
self._handle_DQL(sql, args or None)
elif classification == parse_utils.STMT_INSERT:
_helpers.handle_insert(self.connection, sql, args or None)
else:
self.connection.database.run_in_transaction(
self._do_execute_update, sql, args or None)
except (AlreadyExists, FailedPrecondition) as e:
raise IntegrityError(e.details if hasattr(e, "details") else e)
except InvalidArgument as e:
raise ProgrammingError(e.details if hasattr(e, "details") else e)
except InternalServerError as e:
raise OperationalError(e.details if hasattr(e, "details") else e)
def test_PeekIterator(self):
cases = [
("list", [1, 2, 3, 4, 6, 7], [1, 2, 3, 4, 6, 7]),
("iter_from_list", iter([1, 2, 3, 4, 6, 7]), [1, 2, 3, 4, 6, 7]),
("tuple", ("a", 12, 0xFF), ["a", 12, 0xFF]),
("iter_from_tuple", iter(("a", 12, 0xFF)), ["a", 12, 0xFF]),
("no_args", (), []),
]
for name, data_in, expected in cases:
with self.subTest(name=name):
pitr = PeekIterator(data_in)
actual = list(pitr)
self.assertEqual(actual, expected)
def execute(self, sql, args=None):
"""Prepares and executes a Spanner database operation.
:type sql: str
:param sql: A SQL query statement.
:type args: list
:param args: Additional parameters to supplement the SQL query.
"""
if not self.connection:
raise ProgrammingError("Cursor is not connected to the database")
self._raise_if_closed()
self._result_set = None
# Classify whether this is a read-only SQL statement.
try:
classification = parse_utils.classify_stmt(sql)
if classification == parse_utils.STMT_DDL:
ddl_statements = []
for ddl in sqlparse.split(sql):
if ddl:
if ddl[-1] == ";":
ddl = ddl[:-1]
if parse_utils.classify_stmt(ddl) != parse_utils.STMT_DDL:
raise ValueError("Only DDL statements may be batched.")
ddl_statements.append(ddl)
# Only queue DDL statements if they are all correctly classified.
self.connection._ddl_statements.extend(ddl_statements)
if self.connection.autocommit:
self.connection.run_prior_DDL_statements()
return
# For every other operation, we've got to ensure that
# any prior DDL statements were run.
# self._run_prior_DDL_statements()
self.connection.run_prior_DDL_statements()
if not self.connection.autocommit:
if classification == parse_utils.STMT_UPDATING:
sql = parse_utils.ensure_where_clause(sql)
if classification != parse_utils.STMT_INSERT:
sql, args = sql_pyformat_args_to_spanner(sql, args or None)
statement = Statement(
sql,
args,
get_param_types(args or None)
if classification != parse_utils.STMT_INSERT
else {},
ResultsChecksum(),
classification == parse_utils.STMT_INSERT,
)
(self._result_set, self._checksum,) = self.connection.run_statement(
statement
)
while True:
try:
self._itr = PeekIterator(self._result_set)
break
except Aborted:
self.connection.retry_transaction()
return
if classification == parse_utils.STMT_NON_UPDATING:
self._handle_DQL(sql, args or None)
elif classification == parse_utils.STMT_INSERT:
_helpers.handle_insert(self.connection, sql, args or None)
else:
self.connection.database.run_in_transaction(
self._do_execute_update, sql, args or None
)
except (AlreadyExists, FailedPrecondition) as e:
raise IntegrityError(e.details if hasattr(e, "details") else e)
except InvalidArgument as e:
raise ProgrammingError(e.details if hasattr(e, "details") else e)
except InternalServerError as e:
raise OperationalError(e.details if hasattr(e, "details") else e)
def execute(self, sql, args=None):
"""Prepares and executes a Spanner database operation.
:type sql: str
:param sql: A SQL query statement.
:type args: list
:param args: Additional parameters to supplement the SQL query.
"""
self._result_set = None
try:
if self.connection.read_only:
self._handle_DQL(sql, args or None)
return
class_ = parse_utils.classify_stmt(sql)
if class_ == parse_utils.STMT_DDL:
self._batch_DDLs(sql)
if self.connection.autocommit:
self.connection.run_prior_DDL_statements()
return
# For every other operation, we've got to ensure that
# any prior DDL statements were run.
# self._run_prior_DDL_statements()
self.connection.run_prior_DDL_statements()
if class_ == parse_utils.STMT_UPDATING:
sql = parse_utils.ensure_where_clause(sql)
if class_ != parse_utils.STMT_INSERT:
sql, args = sql_pyformat_args_to_spanner(sql, args or None)
if not self.connection.autocommit:
statement = Statement(
sql,
args,
get_param_types(args or None)
if class_ != parse_utils.STMT_INSERT
else {},
ResultsChecksum(),
class_ == parse_utils.STMT_INSERT,
)
(
self._result_set,
self._checksum,
) = self.connection.run_statement(statement)
while True:
try:
self._itr = PeekIterator(self._result_set)
break
except Aborted:
self.connection.retry_transaction()
return
if class_ == parse_utils.STMT_NON_UPDATING:
self._handle_DQL(sql, args or None)
elif class_ == parse_utils.STMT_INSERT:
_helpers.handle_insert(self.connection, sql, args or None)
else:
self.connection.database.run_in_transaction(
self._do_execute_update, sql, args or None
)
except (AlreadyExists, FailedPrecondition, OutOfRange) as e:
raise IntegrityError(getattr(e, "details", e))
except InvalidArgument as e:
raise ProgrammingError(getattr(e, "details", e))
except InternalServerError as e:
raise OperationalError(getattr(e, "details", e))
def test_peekIterator_nonlist_rows_unconverted(self):
pi = PeekIterator(["a", "b", "c", "d", "e"])
got = list(pi)
want = ["a", "b", "c", "d", "e"]
self.assertEqual(got, want, "Values should be returned unchanged")