def test_parse_insert_invalid(self): from google.cloud.spanner_dbapi import exceptions from google.cloud.spanner_dbapi.parse_utils import parse_insert cases = [ ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s), (%s, %s, %s)", [1, 2, 3, 4, 5, 6, 7], "len\\(params\\)=7 MUST be a multiple of len\\(pyformat_args\\)=3", ), ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s), (%s, %s, LOWER(%s))", [1, 2, 3, 4, 5, 6, 7], "Invalid length: VALUES\\(...\\) len: 6 != len\\(params\\): 7", ), ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s), (%s, %s, LOWER(%s)))", [1, 2, 3, 4, 5, 6], "VALUES: expected `,` got \\) in \\)", ), ] for sql, params, wantException in cases: with self.subTest(sql=sql): self.assertRaisesRegex( exceptions.ProgrammingError, wantException, lambda: parse_insert(sql, params), )
def handle_insert(connection, sql, params): parts = parse_insert(sql, params) # The split between the two styles exists because: # in the common case of multiple values being passed # with simple pyformat arguments, # SQL: INSERT INTO T (f1, f2) VALUES (%s, %s, %s) # Params: [(1, 2, 3, 4, 5, 6, 7, 8, 9, 10,)] # we can take advantage of a single RPC with: # transaction.insert(table, columns, values) # instead of invoking: # with transaction: # for sql, params in sql_params_list: # transaction.execute_sql(sql, params, param_types) # which invokes more RPCs and is more costly. if parts.get("homogenous"): # The common case of multiple values being passed in # non-complex pyformat args and need to be uploaded in one RPC. return connection.database.run_in_transaction(_execute_insert_homogenous, parts) else: # All the other cases that are esoteric and need # transaction.execute_sql sql_params_list = parts.get("sql_params_list") return connection.database.run_in_transaction( _execute_insert_heterogenous, sql_params_list )
def test_insert_from_select(self): """Check that INSERT from SELECT clause can be executed with arguments.""" from google.cloud.spanner_dbapi.parse_utils import parse_insert SQL = """ INSERT INTO tab_name (id, data) SELECT tab_name.id + %s AS anon_1, tab_name.data FROM tab_name WHERE tab_name.data IN (%s, %s) """ ARGS = [5, "data2", "data3"] self.assertEqual( parse_insert(SQL, ARGS), {"sql_params_list": [(SQL, ARGS)]}, )
def test_parse_insert(self): from google.cloud.spanner_dbapi.parse_utils import parse_insert from google.cloud.spanner_dbapi.exceptions import ProgrammingError with self.assertRaises(ProgrammingError): parse_insert("bad-sql", None) cases = [ ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)", [1, 2, 3, 4, 5, 6], { "sql_params_list": [ ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)", (1, 2, 3), ), ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)", (4, 5, 6), ), ] }, ), ( "INSERT INTO django_migrations(app, name, applied) VALUES (%s, %s, %s)", [1, 2, 3, 4, 5, 6], { "sql_params_list": [ ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)", (1, 2, 3), ), ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)", (4, 5, 6), ), ] }, ), ( "INSERT INTO sales.addresses (street, city, state, zip_code) " "SELECT street, city, state, zip_code FROM sales.customers" "ORDER BY first_name, last_name", None, { "sql_params_list": [ ( "INSERT INTO sales.addresses (street, city, state, zip_code) " "SELECT street, city, state, zip_code FROM sales.customers" "ORDER BY first_name, last_name", None, ) ] }, ), ( "INSERT INTO ap (n, ct, cn) " "VALUES (%s, %s, %s), (%s, %s, %s), (%s, %s, %s),(%s, %s, %s)", (1, 2, 3, 4, 5, 6, 7, 8, 9), { "sql_params_list": [ ("INSERT INTO ap (n, ct, cn) VALUES (%s, %s, %s)", (1, 2, 3)), ("INSERT INTO ap (n, ct, cn) VALUES (%s, %s, %s)", (4, 5, 6)), ("INSERT INTO ap (n, ct, cn) VALUES (%s, %s, %s)", (7, 8, 9)), ] }, ), ( "INSERT INTO `no` (`yes`) VALUES (%s)", (1, 4, 5), { "sql_params_list": [ ("INSERT INTO `no` (`yes`) VALUES (%s)", (1,)), ("INSERT INTO `no` (`yes`) VALUES (%s)", (4,)), ("INSERT INTO `no` (`yes`) VALUES (%s)", (5,)), ] }, ), ( "INSERT INTO T (f1, f2) VALUES (1, 2)", None, {"sql_params_list": [("INSERT INTO T (f1, f2) VALUES (1, 2)", None)]}, ), ( "INSERT INTO `no` (`yes`, tiff) VALUES (%s, LOWER(%s)), (%s, %s), (%s, %s)", (1, "FOO", 5, 10, 11, 29), { "sql_params_list": [ ( "INSERT INTO `no` (`yes`, tiff) VALUES(%s, LOWER(%s))", (1, "FOO"), ), ("INSERT INTO `no` (`yes`, tiff) VALUES(%s, %s)", (5, 10)), ("INSERT INTO `no` (`yes`, tiff) VALUES(%s, %s)", (11, 29)), ] }, ), ] sql = "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)" with self.assertRaises(ProgrammingError): parse_insert(sql, None) for sql, params, want in cases: with self.subTest(sql=sql): got = parse_insert(sql, params) self.assertEqual(got, want, "Mismatch with parse_insert of `%s`" % sql)
def test_parse_insert(self): cases = [ ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)", [1, 2, 3, 4, 5, 6], { "sql_params_list": [ ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)", (1, 2, 3), ), ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)", (4, 5, 6), ), ] }, ), ( "INSERT INTO django_migrations(app, name, applied) VALUES (%s, %s, %s)", [1, 2, 3, 4, 5, 6], { "sql_params_list": [ ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)", (1, 2, 3), ), ( "INSERT INTO django_migrations (app, name, applied) VALUES (%s, %s, %s)", (4, 5, 6), ), ] }, ), ( "INSERT INTO sales.addresses (street, city, state, zip_code) " "SELECT street, city, state, zip_code FROM sales.customers" "ORDER BY first_name, last_name", None, { "sql_params_list": [( "INSERT INTO sales.addresses (street, city, state, zip_code) " "SELECT street, city, state, zip_code FROM sales.customers" "ORDER BY first_name, last_name", None, )] }, ), ( "INSERT INTO ap (n, ct, cn) " "VALUES (%s, %s, %s), (%s, %s, %s), (%s, %s, %s),(%s, %s, %s)", (1, 2, 3, 4, 5, 6, 7, 8, 9), { "sql_params_list": [ ( "INSERT INTO ap (n, ct, cn) VALUES (%s, %s, %s)", (1, 2, 3), ), ( "INSERT INTO ap (n, ct, cn) VALUES (%s, %s, %s)", (4, 5, 6), ), ( "INSERT INTO ap (n, ct, cn) VALUES (%s, %s, %s)", (7, 8, 9), ), ] }, ), ( "INSERT INTO `no` (`yes`) VALUES (%s)", (1, 4, 5), { "sql_params_list": [ ("INSERT INTO `no` (`yes`) VALUES (%s)", (1, )), ("INSERT INTO `no` (`yes`) VALUES (%s)", (4, )), ("INSERT INTO `no` (`yes`) VALUES (%s)", (5, )), ] }, ), ( "INSERT INTO T (f1, f2) VALUES (1, 2)", None, { "sql_params_list": [("INSERT INTO T (f1, f2) VALUES (1, 2)", None)] }, ), ( "INSERT INTO `no` (`yes`, tiff) VALUES (%s, LOWER(%s)), (%s, %s), (%s, %s)", (1, "FOO", 5, 10, 11, 29), { "sql_params_list": [ ( "INSERT INTO `no` (`yes`, tiff) VALUES(%s, LOWER(%s))", (1, "FOO"), ), ( "INSERT INTO `no` (`yes`, tiff) VALUES(%s, %s)", (5, 10), ), ( "INSERT INTO `no` (`yes`, tiff) VALUES(%s, %s)", (11, 29), ), ] }, ), ] for sql, params, want in cases: with self.subTest(sql=sql): got = parse_insert(sql, params) self.assertEqual(got, want, "Mismatch with parse_insert of `%s`" % sql)