def test_to_api_repr_w_record_type(self):
from google.cloud.bigquery._helpers import StructQueryParameter
EXPECTED = {
'parameterType': {
'type': 'ARRAY',
'arrayType': {
'type': 'STRUCT',
'structTypes': [
{'name': 'foo', 'type': {'type': 'STRING'}},
{'name': 'bar', 'type': {'type': 'INT64'}},
],
},
},
'parameterValue': {
'arrayValues': [{
'structValues': {
'foo': {'value': 'Foo'},
'bar': {'value': '123'},
}
}]
},
}
one = _make_subparam('foo', 'STRING', 'Foo')
another = _make_subparam('bar', 'INT64', 123)
struct = StructQueryParameter.positional(one, another)
klass = self._get_target_class()
param = klass.positional(array_type='RECORD', values=[struct])
self.assertEqual(param.to_api_repr(), EXPECTED)
def test_sync_query_w_query_params(self):
from google.cloud.bigquery._helpers import ArrayQueryParameter
from google.cloud.bigquery._helpers import ScalarQueryParameter
from google.cloud.bigquery._helpers import StructQueryParameter
question = 'What is the answer to life, the universe, and everything?'
question_param = ScalarQueryParameter(
name='question', type_='STRING', value=question)
answer = 42
answer_param = ScalarQueryParameter(
name='answer', type_='INT64', value=answer)
pi = 3.1415926
pi_param = ScalarQueryParameter(
name='pi', type_='FLOAT64', value=pi)
truthy = True
truthy_param = ScalarQueryParameter(
name='truthy', type_='BOOL', value=truthy)
beef = b'DEADBEEF'
beef_param = ScalarQueryParameter(
name='beef', type_='BYTES', value=beef)
naive = datetime.datetime(2016, 12, 5, 12, 41, 9)
naive_param = ScalarQueryParameter(
name='naive', type_='DATETIME', value=naive)
naive_date_param = ScalarQueryParameter(
name='naive_date', type_='DATE', value=naive.date())
naive_time_param = ScalarQueryParameter(
name='naive_time', type_='TIME', value=naive.time())
zoned = naive.replace(tzinfo=UTC)
zoned_param = ScalarQueryParameter(
name='zoned', type_='TIMESTAMP', value=zoned)
array_param = ArrayQueryParameter(
name='array_param', array_type='INT64', values=[1, 2])
struct_param = StructQueryParameter(
'hitchhiker', question_param, answer_param)
phred_name = 'Phred Phlyntstone'
phred_name_param = ScalarQueryParameter(
name='name', type_='STRING', value=phred_name)
phred_age = 32
phred_age_param = ScalarQueryParameter(
name='age', type_='INT64', value=phred_age)
phred_param = StructQueryParameter(
None, phred_name_param, phred_age_param)
bharney_name = 'Bharney Rhubbyl'
bharney_name_param = ScalarQueryParameter(
name='name', type_='STRING', value=bharney_name)
bharney_age = 31
bharney_age_param = ScalarQueryParameter(
name='age', type_='INT64', value=bharney_age)
bharney_param = StructQueryParameter(
None, bharney_name_param, bharney_age_param)
characters_param = ArrayQueryParameter(
name=None, array_type='RECORD',
values=[phred_param, bharney_param])
hero_param = StructQueryParameter(
'hero', phred_name_param, phred_age_param)
sidekick_param = StructQueryParameter(
'sidekick', bharney_name_param, bharney_age_param)
roles_param = StructQueryParameter(
'roles', hero_param, sidekick_param)
friends_param = ArrayQueryParameter(
name='friends', array_type='STRING',
values=[phred_name, bharney_name])
with_friends_param = StructQueryParameter(None, friends_param)
EXAMPLES = [
{
'sql': 'SELECT @question',
'expected': question,
'query_parameters': [question_param],
},
{
'sql': 'SELECT @answer',
'expected': answer,
'query_parameters': [answer_param],
},
{
'sql': 'SELECT @pi',
'expected': pi,
'query_parameters': [pi_param],
},
{
'sql': 'SELECT @truthy',
'expected': truthy,
'query_parameters': [truthy_param],
},
{
'sql': 'SELECT @beef',
'expected': beef,
'query_parameters': [beef_param],
},
{
'sql': 'SELECT @naive',
'expected': naive,
'query_parameters': [naive_param],
},
{
'sql': 'SELECT @naive_date',
'expected': naive.date(),
'query_parameters': [naive_date_param],
},
{
'sql': 'SELECT @naive_time',
'expected': naive.time(),
'query_parameters': [naive_time_param],
},
{
'sql': 'SELECT @zoned',
'expected': zoned,
'query_parameters': [zoned_param],
},
{
'sql': 'SELECT @array_param',
'expected': [1, 2],
'query_parameters': [array_param],
},
{
'sql': 'SELECT (@hitchhiker.question, @hitchhiker.answer)',
'expected': ({'_field_1': question, '_field_2': answer}),
'query_parameters': [struct_param],
},
{
'sql': 'SELECT ?',
'expected': [
{'name': phred_name, 'age': phred_age},
{'name': bharney_name, 'age': bharney_age},
],
'query_parameters': [characters_param],
},
{
'sql': 'SELECT @roles',
'expected': {
'hero': {'name': phred_name, 'age': phred_age},
'sidekick': {'name': bharney_name, 'age': bharney_age},
},
'query_parameters': [roles_param],
},
{
'sql': 'SELECT ?',
'expected': {
'friends': [phred_name, bharney_name],
},
'query_parameters': [with_friends_param],
},
]
for example in EXAMPLES:
query = Config.CLIENT.run_sync_query(
example['sql'],
query_parameters=example['query_parameters'])
query.use_legacy_sql = False
query.run()
self.assertEqual(len(query.rows), 1)
self.assertEqual(len(query.rows[0]), 1)
self.assertEqual(query.rows[0][0], example['expected'])
def test_sync_query_w_standard_sql_types(self):
import datetime
from google.cloud._helpers import UTC
from google.cloud.bigquery._helpers import ArrayQueryParameter
from google.cloud.bigquery._helpers import ScalarQueryParameter
from google.cloud.bigquery._helpers import StructQueryParameter
naive = datetime.datetime(2016, 12, 5, 12, 41, 9)
stamp = '%s %s' % (naive.date().isoformat(), naive.time().isoformat())
zoned = naive.replace(tzinfo=UTC)
zoned_param = ScalarQueryParameter(
name='zoned', type_='TIMESTAMP', value=zoned)
question = 'What is the answer to life, the universe, and everything?'
question_param = ScalarQueryParameter(
name='question', type_='STRING', value=question)
answer = 42
answer_param = ScalarQueryParameter(
name='answer', type_='INT64', value=answer)
array_param = ArrayQueryParameter(
name='array_param', array_type='INT64', values=[1, 2])
struct_param = StructQueryParameter(
'hitchhiker', question_param, answer_param)
EXAMPLES = [
{
'sql': 'SELECT 1',
'expected': 1,
},
{
'sql': 'SELECT 1.3',
'expected': 1.3,
},
{
'sql': 'SELECT TRUE',
'expected': True,
},
{
'sql': 'SELECT "ABC"',
'expected': 'ABC',
},
{
'sql': 'SELECT CAST("foo" AS BYTES)',
'expected': b'foo',
},
{
'sql': 'SELECT TIMESTAMP "%s"' % (stamp,),
'expected': zoned,
},
{
'sql': 'SELECT DATETIME(TIMESTAMP "%s")' % (stamp,),
'expected': naive,
},
{
'sql': 'SELECT DATE(TIMESTAMP "%s")' % (stamp,),
'expected': naive.date(),
},
{
'sql': 'SELECT TIME(TIMESTAMP "%s")' % (stamp,),
'expected': naive.time(),
},
{
'sql': 'SELECT (1, 2)',
'expected': {'_field_1': 1, '_field_2': 2},
},
{
'sql': 'SELECT ((1, 2), (3, 4), 5)',
'expected': {
'_field_1': {'_field_1': 1, '_field_2': 2},
'_field_2': {'_field_1': 3, '_field_2': 4},
'_field_3': 5,
},
},
{
'sql': 'SELECT [1, 2, 3]',
'expected': [1, 2, 3],
},
{
'sql': 'SELECT ([1, 2], 3, [4, 5])',
'expected':
{'_field_1': [1, 2], '_field_2': 3, '_field_3': [4, 5]},
},
{
'sql': 'SELECT [(1, 2, 3), (4, 5, 6)]',
'expected': [
{'_field_1': 1, '_field_2': 2, '_field_3': 3},
{'_field_1': 4, '_field_2': 5, '_field_3': 6},
],
},
{
'sql': 'SELECT [([1, 2, 3], 4), ([5, 6], 7)]',
'expected': [
{u'_field_1': [1, 2, 3], u'_field_2': 4},
{u'_field_1': [5, 6], u'_field_2': 7},
],
},
{
'sql': 'SELECT ARRAY(SELECT STRUCT([1, 2]))',
'expected': [{u'_field_1': [1, 2]}],
},
{
'sql': 'SELECT @zoned',
'expected': zoned,
'query_parameters': [zoned_param],
},
{
'sql': 'SELECT @array_param',
'expected': [1, 2],
'query_parameters': [array_param],
},
{
'sql': 'SELECT (@hitchhiker.question, @hitchhiker.answer)',
'expected': ({'_field_1': question, '_field_2': answer}),
'query_parameters': [struct_param],
},
]
for example in EXAMPLES:
query = Config.CLIENT.run_sync_query(
example['sql'],
query_parameters=example.get('query_parameters', ()))
query.use_legacy_sql = False
query.run()
self.assertEqual(len(query.rows), 1)
self.assertEqual(len(query.rows[0]), 1)
self.assertEqual(query.rows[0][0], example['expected'])