def test_run_w_positional_query_paramter(self):
from google.cloud.bigquery._helpers import ScalarQueryParameter
PATH = 'projects/%s/queries' % self.PROJECT
RESOURCE = self._makeResource(complete=False)
RESOURCE['parameterMode'] = 'POSITIONAL'
RESOURCE['queryParameters'] = [
{
'parameterType': {
'type': 'INT64',
},
'parameterValue': {
'value': 123,
},
},
]
query_parameters = [ScalarQueryParameter.positional('INT64', 123)]
conn = _Connection(RESOURCE)
client = _Client(project=self.PROJECT, connection=conn)
query = self._make_one(self.QUERY,
client,
query_parameters=query_parameters)
query.run()
self.assertEqual(len(conn._requested), 1)
req = conn._requested[0]
self.assertEqual(req['method'], 'POST')
self.assertEqual(req['path'], '/%s' % PATH)
SENT = {
'query': self.QUERY,
'parameterMode': 'POSITIONAL',
'queryParameters': RESOURCE['queryParameters'],
}
self.assertEqual(req['data'], SENT)
self._verifyResourceProperties(query, RESOURCE)
def test_run_w_positional_query_paramter(self):
from google.cloud.bigquery._helpers import ScalarQueryParameter
PATH = 'projects/%s/queries' % self.PROJECT
RESOURCE = self._makeResource(complete=False)
RESOURCE['parameterMode'] = 'POSITIONAL'
RESOURCE['queryParameters'] = [
{
'parameterType': {
'type': 'INT64',
},
'parameterValue': {
'value': '123',
},
},
]
query_parameters = [ScalarQueryParameter.positional('INT64', 123)]
conn = _Connection(RESOURCE)
client = _Client(project=self.PROJECT, connection=conn)
query = self._make_one(self.QUERY, client,
query_parameters=query_parameters)
query.run()
self.assertEqual(len(conn._requested), 1)
req = conn._requested[0]
self.assertEqual(req['method'], 'POST')
self.assertEqual(req['path'], '/%s' % PATH)
SENT = {
'query': self.QUERY,
'parameterMode': 'POSITIONAL',
'queryParameters': RESOURCE['queryParameters'],
}
self.assertEqual(req['data'], SENT)
self._verifyResourceProperties(query, RESOURCE)
def test_ctor_w_query_parameters(self):
from google.cloud.bigquery._helpers import ScalarQueryParameter
query_parameters = [ScalarQueryParameter("foo", 'INT64', 123)]
client = _Client(self.PROJECT)
query = self._make_one(self.QUERY, client,
query_parameters=query_parameters)
self.assertEqual(query.query_parameters, query_parameters)
def test_instance_getter_w_non_empty_list(self):
from google.cloud.bigquery._helpers import ScalarQueryParameter
query_parameters = [ScalarQueryParameter("foo", 'INT64', 123)]
_, klass = self._descriptor_and_klass()
instance = klass()
instance._query_parameters = tuple(query_parameters)
self.assertEqual(instance.query_parameters, query_parameters)
def test_instance_setter_w_valid_udf(self):
from google.cloud.bigquery._helpers import ScalarQueryParameter
query_parameters = [ScalarQueryParameter("foo", 'INT64', 123)]
_, klass = self._descriptor_and_klass()
instance = klass()
instance.query_parameters = query_parameters
self.assertEqual(instance.query_parameters, query_parameters)
def test_run_sync_query_w_query_parameters(self):
from google.cloud.bigquery._helpers import ScalarQueryParameter
from google.cloud.bigquery.query import QueryResults
PROJECT = 'PROJECT'
QUERY = 'select count(*) from persons'
creds = _make_credentials()
http = object()
client = self._make_one(project=PROJECT, credentials=creds, http=http)
query_parameters = [ScalarQueryParameter('foo', 'INT64', 123)]
query = client.run_sync_query(QUERY, query_parameters=query_parameters)
self.assertIsInstance(query, QueryResults)
self.assertIs(query._client, client)
self.assertIsNone(query.name)
self.assertEqual(query.query, QUERY)
self.assertEqual(query.udf_resources, [])
self.assertEqual(query.query_parameters, query_parameters)
def test_run_async_w_query_parameters(self):
from google.cloud.bigquery._helpers import ScalarQueryParameter
from google.cloud.bigquery.job import QueryJob
PROJECT = 'PROJECT'
JOB = 'job_name'
QUERY = 'select count(*) from persons'
creds = _make_credentials()
http = object()
client = self._make_one(project=PROJECT, credentials=creds, http=http)
query_parameters = [ScalarQueryParameter('foo', 'INT64', 123)]
job = client.run_async_query(JOB, QUERY,
query_parameters=query_parameters)
self.assertIsInstance(job, QueryJob)
self.assertIs(job._client, client)
self.assertEqual(job.name, JOB)
self.assertEqual(job.query, QUERY)
self.assertEqual(job.udf_resources, [])
self.assertEqual(job.query_parameters, query_parameters)
def _make_subparam(name, type_, value):
from google.cloud.bigquery._helpers import ScalarQueryParameter
return ScalarQueryParameter(name, type_, value)
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'])
