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)
