def test_classify_stmt(self): from google.cloud.spanner_dbapi.parse_utils import STMT_DDL from google.cloud.spanner_dbapi.parse_utils import STMT_INSERT from google.cloud.spanner_dbapi.parse_utils import STMT_NON_UPDATING from google.cloud.spanner_dbapi.parse_utils import STMT_UPDATING from google.cloud.spanner_dbapi.parse_utils import classify_stmt cases = ( ("SELECT 1", STMT_NON_UPDATING), ("SELECT s.SongName FROM Songs AS s", STMT_NON_UPDATING), ( "WITH sq AS (SELECT SchoolID FROM Roster) SELECT * from sq", STMT_NON_UPDATING, ), ( "CREATE TABLE django_content_type (id STRING(64) NOT NULL, name STRING(100) " "NOT NULL, app_label STRING(100) NOT NULL, model STRING(100) NOT NULL) PRIMARY KEY(id)", STMT_DDL, ), ( "CREATE INDEX SongsBySingerAlbumSongNameDesc ON " "Songs(SingerId, AlbumId, SongName DESC), INTERLEAVE IN Albums", STMT_DDL, ), ("CREATE INDEX SongsBySongName ON Songs(SongName)", STMT_DDL), ( "CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)", STMT_DDL, ), ("INSERT INTO table (col1) VALUES (1)", STMT_INSERT), ("UPDATE table SET col1 = 1 WHERE col1 = NULL", STMT_UPDATING), ) for query, want_class in cases: self.assertEqual(classify_stmt(query), want_class)
def executemany(self, operation, seq_of_params): """Execute the given SQL with every parameters set from the given sequence of parameters. :type operation: str :param operation: SQL code to execute. :type seq_of_params: list :param seq_of_params: Sequence of additional parameters to run the query with. """ self._raise_if_closed() classification = parse_utils.classify_stmt(operation) if classification == parse_utils.STMT_DDL: raise ProgrammingError( "Executing DDL statements with executemany() method is not allowed." ) many_result_set = StreamedManyResultSets() for params in seq_of_params: self.execute(operation, params) many_result_set.add_iter(self._itr) self._result_set = many_result_set self._itr = many_result_set
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 _batch_DDLs(self, sql): """ Check that the given operation contains only DDL statements and batch them into an internal list. :type sql: str :param sql: A SQL query statement. :raises: :class:`ValueError` in case not a DDL statement present in the operation. """ statements = [] for ddl in sqlparse.split(sql): if ddl: ddl = ddl.rstrip(";") if parse_utils.classify_stmt(ddl) != parse_utils.STMT_DDL: raise ValueError("Only DDL statements may be batched.") statements.append(ddl) # Only queue DDL statements if they are all correctly classified. self.connection._ddl_statements.extend(statements)
def executemany(self, operation, seq_of_params): """Execute the given SQL with every parameters set from the given sequence of parameters. :type operation: str :param operation: SQL code to execute. :type seq_of_params: list :param seq_of_params: Sequence of additional parameters to run the query with. """ self._raise_if_closed() classification = parse_utils.classify_stmt(operation) if classification == parse_utils.STMT_DDL: raise ProgrammingError( "Executing DDL statements with executemany() method is not allowed." ) many_result_set = StreamedManyResultSets() if classification in (parse_utils.STMT_INSERT, parse_utils.STMT_UPDATING): statements = [] for params in seq_of_params: sql, params = parse_utils.sql_pyformat_args_to_spanner( operation, params ) statements.append((sql, params, get_param_types(params))) if self.connection.autocommit: self.connection.database.run_in_transaction( self._do_batch_update, statements, many_result_set ) else: retried = False while True: try: transaction = self.connection.transaction_checkout() res_checksum = ResultsChecksum() if not retried: self.connection._statements.append( (statements, res_checksum) ) status, res = transaction.batch_update(statements) many_result_set.add_iter(res) res_checksum.consume_result(res) res_checksum.consume_result(status.code) if status.code == ABORTED: self.connection._transaction = None raise Aborted(status.details) elif status.code != OK: raise OperationalError(status.details) break except Aborted: self.connection.retry_transaction() retried = True else: for params in seq_of_params: self.execute(operation, params) many_result_set.add_iter(self._itr) self._result_set = many_result_set self._itr = many_result_set
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))