Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8bcf397

Browse files
Jim Fultontswast
Jim Fulton
andauthored
feat: Extended DB API parameter syntax to optionally provide parameter types (#626)
* Added explicit type documentation.* Extended query-parameter system for specifying parameter types.assed.* Serialize non-floats (e.g. Decimals) using in FLOAT64 parameters.Co-authored-by: Tim Swast <swast@google.com>* De-reference aliases in SqlParameterScalarTypes when checking typesCo-authored-by: Tim Swast <swast@google.com>
1 parentc085186 commit8bcf397

File tree

8 files changed

+396
-60
lines changed

8 files changed

+396
-60
lines changed

‎docs/dbapi.rst‎

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4,3 +4,40 @@ DB-API Reference
44
..automodule::google.cloud.bigquery.dbapi
55
:members:
66
:show-inheritance:
7+
8+
9+
DB-API Query-Parameter Syntax
10+
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
11+
12+
The BigQuery DB-API uses the `qmark` `parameter style
13+
<https://www.python.org/dev/peps/pep-0249/#paramstyle>`_ for
14+
unnamed/positional parameters and the `pyformat` parameter style for
15+
named parameters.
16+
17+
An example of a query using unnamed parameters::
18+
19+
insert into people (name, income) values (?, ?)
20+
21+
and using named parameters::
22+
23+
insert into people (name, income) values (%(name)s, %(income)s)
24+
25+
Providing explicit type information
26+
-----------------------------------
27+
28+
BigQuery requires type information for parameters. The The BigQuery
29+
DB-API can usually determine parameter types for parameters based on
30+
provided values. Sometimes, however, types can't be determined (for
31+
example when `None` is passed) or are determined incorrectly (for
32+
example when passing a floating-point value to a numeric column).
33+
34+
The BigQuery DB-API provides an extended parameter syntax. For named
35+
parameters, a BigQuery type is provided after the name separated by a
36+
colon, as in::
37+
38+
insert into people (name, income) values (%(name:string)s, %(income:numeric)s)
39+
40+
For unnamed parameters, use the named syntax with a type, but now
41+
name, as in::
42+
43+
insert into people (name, income) values (%(:string)s, %(:numeric)s)

‎google/cloud/bigquery/_helpers.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -275,7 +275,7 @@ def _int_to_json(value):
275275

276276
def_float_to_json(value):
277277
"""Coerce 'value' to an JSON-compatible representation."""
278-
returnvalue
278+
returnvalueifvalueisNoneelsefloat(value)
279279

280280

281281
def_decimal_to_json(value):

‎google/cloud/bigquery/dbapi/_helpers.py‎

Lines changed: 73 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -20,15 +20,36 @@
2020
importnumbers
2121

2222
fromgoogle.cloudimportbigquery
23-
fromgoogle.cloud.bigqueryimporttable
23+
fromgoogle.cloud.bigqueryimporttable,enums
2424
fromgoogle.cloud.bigquery.dbapiimportexceptions
2525

2626

2727
_NUMERIC_SERVER_MIN=decimal.Decimal("-9.9999999999999999999999999999999999999E+28")
2828
_NUMERIC_SERVER_MAX=decimal.Decimal("9.9999999999999999999999999999999999999E+28")
2929

3030

31-
defscalar_to_query_parameter(value,name=None):
31+
def_parameter_type(name,value,query_parameter_type=None,value_doc=""):
32+
ifquery_parameter_type:
33+
try:
34+
parameter_type=getattr(
35+
enums.SqlParameterScalarTypes,query_parameter_type.upper()
36+
)._type
37+
exceptAttributeError:
38+
raiseexceptions.ProgrammingError(
39+
f"The given parameter type,{query_parameter_type},"
40+
f" for{name} is not a valid BigQuery scalar type."
41+
)
42+
else:
43+
parameter_type=bigquery_scalar_type(value)
44+
ifparameter_typeisNone:
45+
raiseexceptions.ProgrammingError(
46+
f"Encountered parameter{name} with "
47+
f"{value_doc} value{value} of unexpected type."
48+
)
49+
returnparameter_type
50+
51+
52+
defscalar_to_query_parameter(value,name=None,query_parameter_type=None):
3253
"""Convert a scalar value into a query parameter.
3354
3455
Args:
@@ -37,6 +58,7 @@ def scalar_to_query_parameter(value, name=None):
3758
3859
name (str):
3960
(Optional) Name of the query parameter.
61+
query_parameter_type (Optional[str]): Given type for the parameter.
4062
4163
Returns:
4264
google.cloud.bigquery.ScalarQueryParameter:
@@ -47,24 +69,19 @@ def scalar_to_query_parameter(value, name=None):
4769
google.cloud.bigquery.dbapi.exceptions.ProgrammingError:
4870
if the type cannot be determined.
4971
"""
50-
parameter_type=bigquery_scalar_type(value)
51-
52-
ifparameter_typeisNone:
53-
raiseexceptions.ProgrammingError(
54-
"encountered parameter {} with value {} of unexpected type".format(
55-
name,value
56-
)
57-
)
58-
returnbigquery.ScalarQueryParameter(name,parameter_type,value)
72+
returnbigquery.ScalarQueryParameter(
73+
name,_parameter_type(name,value,query_parameter_type),value
74+
)
5975

6076

61-
defarray_to_query_parameter(value,name=None):
77+
defarray_to_query_parameter(value,name=None,query_parameter_type=None):
6278
"""Convert an array-like value into a query parameter.
6379
6480
Args:
6581
value (Sequence[Any]): The elements of the array (should not be a
6682
string-like Sequence).
6783
name (Optional[str]): Name of the query parameter.
84+
query_parameter_type (Optional[str]): Given type for the parameter.
6885
6986
Returns:
7087
A query parameter corresponding with the type and value of the plain
@@ -80,53 +97,58 @@ def array_to_query_parameter(value, name=None):
8097
"not string-like.".format(name)
8198
)
8299

83-
ifnotvalue:
100+
ifquery_parameter_typeorvalue:
101+
array_type=_parameter_type(
102+
name,
103+
value[0]ifvalueelseNone,
104+
query_parameter_type,
105+
value_doc="array element ",
106+
)
107+
else:
84108
raiseexceptions.ProgrammingError(
85109
"Encountered an empty array-like value of parameter {}, cannot "
86110
"determine array elements type.".format(name)
87111
)
88112

89-
# Assume that all elements are of the same type, and let the backend handle
90-
# any type incompatibilities among the array elements
91-
array_type=bigquery_scalar_type(value[0])
92-
ifarray_typeisNone:
93-
raiseexceptions.ProgrammingError(
94-
"Encountered unexpected first array element of parameter {}, "
95-
"cannot determine array elements type.".format(name)
96-
)
97-
98113
returnbigquery.ArrayQueryParameter(name,array_type,value)
99114

100115

101-
defto_query_parameters_list(parameters):
116+
defto_query_parameters_list(parameters,parameter_types):
102117
"""Converts a sequence of parameter values into query parameters.
103118
104119
Args:
105120
parameters (Sequence[Any]): Sequence of query parameter values.
121+
parameter_types:
122+
A list of parameter types, one for each parameter.
123+
Unknown types are provided as None.
106124
107125
Returns:
108126
List[google.cloud.bigquery.query._AbstractQueryParameter]:
109127
A list of query parameters.
110128
"""
111129
result= []
112130

113-
forvalueinparameters:
131+
forvalue,type_inzip(parameters,parameter_types):
114132
ifisinstance(value,collections_abc.Mapping):
115133
raiseNotImplementedError("STRUCT-like parameter values are not supported.")
116134
elifarray_like(value):
117-
param=array_to_query_parameter(value)
135+
param=array_to_query_parameter(value,None,type_)
118136
else:
119-
param=scalar_to_query_parameter(value)
137+
param=scalar_to_query_parameter(value,None,type_)
138+
120139
result.append(param)
121140

122141
returnresult
123142

124143

125-
defto_query_parameters_dict(parameters):
144+
defto_query_parameters_dict(parameters,query_parameter_types):
126145
"""Converts a dictionary of parameter values into query parameters.
127146
128147
Args:
129148
parameters (Mapping[str, Any]): Dictionary of query parameter values.
149+
parameter_types:
150+
A dictionary of parameter types. It needn't have a key for each
151+
parameter.
130152
131153
Returns:
132154
List[google.cloud.bigquery.query._AbstractQueryParameter]:
@@ -140,21 +162,38 @@ def to_query_parameters_dict(parameters):
140162
"STRUCT-like parameter values are not supported "
141163
"(parameter {}).".format(name)
142164
)
143-
elifarray_like(value):
144-
param=array_to_query_parameter(value,name=name)
145165
else:
146-
param=scalar_to_query_parameter(value,name=name)
166+
query_parameter_type=query_parameter_types.get(name)
167+
ifarray_like(value):
168+
param=array_to_query_parameter(
169+
value,name=name,query_parameter_type=query_parameter_type
170+
)
171+
else:
172+
param=scalar_to_query_parameter(
173+
value,name=name,query_parameter_type=query_parameter_type,
174+
)
175+
147176
result.append(param)
148177

149178
returnresult
150179

151180

152-
defto_query_parameters(parameters):
181+
defto_query_parameters(parameters,parameter_types):
153182
"""Converts DB-API parameter values into query parameters.
154183
155184
Args:
156185
parameters (Union[Mapping[str, Any], Sequence[Any]]):
157186
A dictionary or sequence of query parameter values.
187+
parameter_types (Union[Mapping[str, str], Sequence[str]]):
188+
A dictionary or list of parameter types.
189+
190+
If parameters is a mapping, then this must be a dictionary
191+
of parameter types. It needn't have a key for each
192+
parameter.
193+
194+
If parameters is a sequence, then this must be a list of
195+
parameter types, one for each paramater. Unknown types
196+
are provided as None.
158197
159198
Returns:
160199
List[google.cloud.bigquery.query._AbstractQueryParameter]:
@@ -164,9 +203,9 @@ def to_query_parameters(parameters):
164203
return []
165204

166205
ifisinstance(parameters,collections_abc.Mapping):
167-
returnto_query_parameters_dict(parameters)
168-
169-
returnto_query_parameters_list(parameters)
206+
returnto_query_parameters_dict(parameters,parameter_types)
207+
else:
208+
returnto_query_parameters_list(parameters,parameter_types)
170209

171210

172211
defbigquery_scalar_type(value):

‎google/cloud/bigquery/dbapi/cursor.py‎

Lines changed: 85 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -18,6 +18,7 @@
1818
fromcollectionsimportabcascollections_abc
1919
importcopy
2020
importlogging
21+
importre
2122

2223
try:
2324
fromgoogle.cloud.bigquery_storageimportArrowSerializationOptions
@@ -161,6 +162,14 @@ def execute(self, operation, parameters=None, job_id=None, job_config=None):
161162
job_config (google.cloud.bigquery.job.QueryJobConfig):
162163
(Optional) Extra configuration options for the query job.
163164
"""
165+
formatted_operation,parameter_types=_format_operation(operation,parameters)
166+
self._execute(
167+
formatted_operation,parameters,job_id,job_config,parameter_types
168+
)
169+
170+
def_execute(
171+
self,formatted_operation,parameters,job_id,job_config,parameter_types
172+
):
164173
self._query_data=None
165174
self._query_job=None
166175
client=self.connection._client
@@ -169,8 +178,7 @@ def execute(self, operation, parameters=None, job_id=None, job_config=None):
169178
# query parameters was not one of the standard options. Convert both
170179
# the query and the parameters to the format expected by the client
171180
# libraries.
172-
formatted_operation=_format_operation(operation,parameters=parameters)
173-
query_parameters=_helpers.to_query_parameters(parameters)
181+
query_parameters=_helpers.to_query_parameters(parameters,parameter_types)
174182

175183
ifclient._default_query_job_config:
176184
ifjob_config:
@@ -209,8 +217,19 @@ def executemany(self, operation, seq_of_parameters):
209217
seq_of_parameters (Union[Sequence[Mapping[str, Any], Sequence[Any]]]):
210218
Sequence of many sets of parameter values.
211219
"""
212-
forparametersinseq_of_parameters:
213-
self.execute(operation,parameters)
220+
ifseq_of_parameters:
221+
# There's no reason to format the line more than once, as
222+
# the operation only barely depends on the parameters. So
223+
# we just use the first set of parameters. If there are
224+
# different numbers or types of parameters, we'll error
225+
# anyway.
226+
formatted_operation,parameter_types=_format_operation(
227+
operation,seq_of_parameters[0]
228+
)
229+
forparametersinseq_of_parameters:
230+
self._execute(
231+
formatted_operation,parameters,None,None,parameter_types
232+
)
214233

215234
def_try_fetch(self,size=None):
216235
"""Try to start fetching data, if not yet started.
@@ -427,7 +446,7 @@ def _format_operation_dict(operation, parameters):
427446
raiseexceptions.ProgrammingError(exc)
428447

429448

430-
def_format_operation(operation,parameters=None):
449+
def_format_operation(operation,parameters):
431450
"""Formats parameters in operation in way BigQuery expects.
432451
433452
Args:
@@ -445,9 +464,67 @@ def _format_operation(operation, parameters=None):
445464
``parameters`` argument.
446465
"""
447466
ifparametersisNoneorlen(parameters)==0:
448-
returnoperation.replace("%%","%")# Still do percent de-escaping.
467+
returnoperation.replace("%%","%"),None# Still do percent de-escaping.
468+
469+
operation,parameter_types=_extract_types(operation)
470+
ifparameter_typesisNone:
471+
raiseexceptions.ProgrammingError(
472+
f"Parameters were provided, but{repr(operation)} has no placeholders."
473+
)
449474

450475
ifisinstance(parameters,collections_abc.Mapping):
451-
return_format_operation_dict(operation,parameters)
476+
return_format_operation_dict(operation,parameters),parameter_types
477+
478+
return_format_operation_list(operation,parameters),parameter_types
479+
480+
481+
def_extract_types(
482+
operation,extra_type_sub=re.compile(r"(%*)%(?:\(([^:)]*)(?::(\w+))?\))?s").sub
483+
):
484+
"""Remove type information from parameter placeholders.
485+
486+
For every parameter of the form %(name:type)s, replace with %(name)s and add the
487+
item name->type to dict that's returned.
488+
489+
Returns operation without type information and a dictionary of names and types.
490+
"""
491+
parameter_types=None
492+
493+
defrepl(m):
494+
nonlocalparameter_types
495+
prefix,name,type_=m.groups()
496+
iflen(prefix)%2:
497+
# The prefix has an odd number of %s, the last of which
498+
# escapes the % we're looking for, so we don't want to
499+
# change anything.
500+
returnm.group(0)
501+
502+
try:
503+
ifname:
504+
ifnotparameter_types:
505+
parameter_types= {}
506+
iftype_:
507+
ifnameinparameter_types:
508+
iftype_!=parameter_types[name]:
509+
raiseexceptions.ProgrammingError(
510+
f"Conflicting types for{name}: "
511+
f"{parameter_types[name]} and{type_}."
512+
)
513+
else:
514+
parameter_types[name]=type_
515+
else:
516+
ifnotisinstance(parameter_types,dict):
517+
raiseTypeError()
518+
519+
returnf"{prefix}%({name})s"
520+
else:
521+
ifparameter_typesisNone:
522+
parameter_types= []
523+
parameter_types.append(type_)
524+
returnf"{prefix}%s"
525+
except (AttributeError,TypeError):
526+
raiseexceptions.ProgrammingError(
527+
f"{repr(operation)} mixes named and unamed parameters."
528+
)
452529

453-
return_format_operation_list(operation,parameters)
530+
returnextra_type_sub(repl,operation),parameter_types

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp