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

Commit38b3ef9

Browse files
author
Jim Fulton
authored
feat: Support passing struct data to the DB API (#718)
1 parente99abbb commit38b3ef9

File tree

8 files changed

+597
-51
lines changed

8 files changed

+597
-51
lines changed

‎docs/dbapi.rst‎

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,7 +25,7 @@ and using named parameters::
2525
Providing explicit type information
2626
-----------------------------------
2727

28-
BigQuery requires type information for parameters. TheTheBigQuery
28+
BigQuery requires type information for parameters. The BigQuery
2929
DB-API can usually determine parameter types for parameters based on
3030
provided values. Sometimes, however, types can't be determined (for
3131
example when `None` is passed) or are determined incorrectly (for
@@ -37,7 +37,14 @@ colon, as in::
3737

3838
insert into people (name, income) values (%(name:string)s, %(income:numeric)s)
3939

40-
For unnamed parameters, use the named syntax with a type, butnow
40+
For unnamed parameters, use the named syntax with a type, butno
4141
name, as in::
4242

4343
insert into people (name, income) values (%(:string)s, %(:numeric)s)
44+
45+
Providing type information is the *only* way to pass `struct` data::
46+
47+
cursor.execute(
48+
"insert into points (point) values (%(:struct<x float64, y float64>)s)",
49+
[{"x": 10, "y": 20}],
50+
)

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

Lines changed: 216 additions & 36 deletions
Original file line numberDiff line numberDiff line change
@@ -18,18 +18,34 @@
1818
importdecimal
1919
importfunctools
2020
importnumbers
21+
importre
22+
importtyping
2123

2224
fromgoogle.cloudimportbigquery
23-
fromgoogle.cloud.bigqueryimporttable,enums
25+
fromgoogle.cloud.bigqueryimporttable,enums,query
2426
fromgoogle.cloud.bigquery.dbapiimportexceptions
2527

2628

2729
_NUMERIC_SERVER_MIN=decimal.Decimal("-9.9999999999999999999999999999999999999E+28")
2830
_NUMERIC_SERVER_MAX=decimal.Decimal("9.9999999999999999999999999999999999999E+28")
2931

32+
type_parameters_re=re.compile(
33+
r"""
34+
\(
35+
\s*[0-9]+\s*
36+
(,
37+
\s*[0-9]+\s*
38+
)*
39+
\)
40+
""",
41+
re.VERBOSE,
42+
)
43+
3044

3145
def_parameter_type(name,value,query_parameter_type=None,value_doc=""):
3246
ifquery_parameter_type:
47+
# Strip type parameters
48+
query_parameter_type=type_parameters_re.sub("",query_parameter_type)
3349
try:
3450
parameter_type=getattr(
3551
enums.SqlParameterScalarTypes,query_parameter_type.upper()
@@ -113,6 +129,197 @@ def array_to_query_parameter(value, name=None, query_parameter_type=None):
113129
returnbigquery.ArrayQueryParameter(name,array_type,value)
114130

115131

132+
def_parse_struct_fields(
133+
fields,
134+
base,
135+
parse_struct_field=re.compile(
136+
r"""
137+
(?:(\w+)\s+) # field name
138+
([A-Z0-9<> ,()]+) # Field type
139+
$""",
140+
re.VERBOSE|re.IGNORECASE,
141+
).match,
142+
):
143+
# Split a string of struct fields. They're defined by commas, but
144+
# we have to avoid splitting on commas internal to fields. For
145+
# example:
146+
# name string, children array<struct<name string, bdate date>>
147+
#
148+
# only has 2 top-level fields.
149+
fields=fields.split(",")
150+
fields=list(reversed(fields))# in the off chance that there are very many
151+
whilefields:
152+
field=fields.pop()
153+
whilefieldsandfield.count("<")!=field.count(">"):
154+
field+=","+fields.pop()
155+
156+
m=parse_struct_field(field.strip())
157+
ifnotm:
158+
raiseexceptions.ProgrammingError(
159+
f"Invalid struct field,{field}, in{base}"
160+
)
161+
yieldm.group(1,2)
162+
163+
164+
SCALAR,ARRAY,STRUCT="sar"
165+
166+
167+
def_parse_type(
168+
type_,
169+
name,
170+
base,
171+
complex_query_parameter_parse=re.compile(
172+
r"""
173+
\s*
174+
(ARRAY|STRUCT|RECORD) # Type
175+
\s*
176+
<([A-Z0-9<> ,()]+)> # Subtype(s)
177+
\s*$
178+
""",
179+
re.IGNORECASE|re.VERBOSE,
180+
).match,
181+
):
182+
if"<"notintype_:
183+
# Scalar
184+
185+
# Strip type parameters
186+
type_=type_parameters_re.sub("",type_).strip()
187+
try:
188+
type_=getattr(enums.SqlParameterScalarTypes,type_.upper())
189+
exceptAttributeError:
190+
raiseexceptions.ProgrammingError(
191+
f"The given parameter type,{type_},"
192+
f"{' for '+nameifnameelse''}"
193+
f" is not a valid BigQuery scalar type, in{base}."
194+
)
195+
ifname:
196+
type_=type_.with_name(name)
197+
returnSCALAR,type_
198+
199+
m=complex_query_parameter_parse(type_)
200+
ifnotm:
201+
raiseexceptions.ProgrammingError(f"Invalid parameter type,{type_}")
202+
tname,sub=m.group(1,2)
203+
iftname.upper()=="ARRAY":
204+
sub_type=complex_query_parameter_type(None,sub,base)
205+
ifisinstance(sub_type,query.ArrayQueryParameterType):
206+
raiseexceptions.ProgrammingError(f"Array can't contain an array in{base}")
207+
sub_type._complex__src=sub
208+
returnARRAY,sub_type
209+
else:
210+
returnSTRUCT,_parse_struct_fields(sub,base)
211+
212+
213+
defcomplex_query_parameter_type(name:typing.Optional[str],type_:str,base:str):
214+
"""Construct a parameter type (`StructQueryParameterType`) for a complex type
215+
216+
or a non-complex type that's part of a complex type.
217+
218+
Examples:
219+
220+
array<struct<x float64, y float64>>
221+
222+
struct<name string, children array<struct<name string, bdate date>>>
223+
224+
This is used for computing array types.
225+
"""
226+
227+
type_type,sub_type=_parse_type(type_,name,base)
228+
iftype_type==SCALAR:
229+
type_=sub_type
230+
eliftype_type==ARRAY:
231+
type_=query.ArrayQueryParameterType(sub_type,name=name)
232+
eliftype_type==STRUCT:
233+
fields= [
234+
complex_query_parameter_type(field_name,field_type,base)
235+
forfield_name,field_typeinsub_type
236+
]
237+
type_=query.StructQueryParameterType(*fields,name=name)
238+
else:# pragma: NO COVER
239+
raiseAssertionError("Bad type_type",type_type)# Can't happen :)
240+
241+
returntype_
242+
243+
244+
defcomplex_query_parameter(
245+
name:typing.Optional[str],value,type_:str,base:typing.Optional[str]=None
246+
):
247+
"""
248+
Construct a query parameter for a complex type (array or struct record)
249+
250+
or for a subtype, which may not be complex
251+
252+
Examples:
253+
254+
array<struct<x float64, y float64>>
255+
256+
struct<name string, children array<struct<name string, bdate date>>>
257+
258+
"""
259+
base=baseortype_
260+
261+
type_type,sub_type=_parse_type(type_,name,base)
262+
263+
iftype_type==SCALAR:
264+
param=query.ScalarQueryParameter(name,sub_type._type,value)
265+
eliftype_type==ARRAY:
266+
ifnotarray_like(value):
267+
raiseexceptions.ProgrammingError(
268+
f"Array type with non-array-like value"
269+
f" with type{type(value).__name__}"
270+
)
271+
param=query.ArrayQueryParameter(
272+
name,
273+
sub_type,
274+
value
275+
ifisinstance(sub_type,query.ScalarQueryParameterType)
276+
else [
277+
complex_query_parameter(None,v,sub_type._complex__src,base)
278+
forvinvalue
279+
],
280+
)
281+
eliftype_type==STRUCT:
282+
ifnotisinstance(value,collections_abc.Mapping):
283+
raiseexceptions.ProgrammingError(f"Non-mapping value for type{type_}")
284+
value_keys=set(value)
285+
fields= []
286+
forfield_name,field_typeinsub_type:
287+
iffield_namenotinvalue:
288+
raiseexceptions.ProgrammingError(
289+
f"No field value for{field_name} in{type_}"
290+
)
291+
value_keys.remove(field_name)
292+
fields.append(
293+
complex_query_parameter(field_name,value[field_name],field_type,base)
294+
)
295+
ifvalue_keys:
296+
raiseexceptions.ProgrammingError(f"Extra data keys for{type_}")
297+
298+
param=query.StructQueryParameter(name,*fields)
299+
else:# pragma: NO COVER
300+
raiseAssertionError("Bad type_type",type_type)# Can't happen :)
301+
302+
returnparam
303+
304+
305+
def_dispatch_parameter(type_,value,name=None):
306+
iftype_isnotNoneand"<"intype_:
307+
param=complex_query_parameter(name,value,type_)
308+
elifisinstance(value,collections_abc.Mapping):
309+
raiseNotImplementedError(
310+
f"STRUCT-like parameter values are not supported"
311+
f"{' (parameter '+name+')'ifnameelse''},"
312+
f" unless an explicit type is give in the parameter placeholder"
313+
f" (e.g. '%({nameifnameelse''}:struct<...>)s')."
314+
)
315+
elifarray_like(value):
316+
param=array_to_query_parameter(value,name,type_)
317+
else:
318+
param=scalar_to_query_parameter(value,name,type_)
319+
320+
returnparam
321+
322+
116323
defto_query_parameters_list(parameters,parameter_types):
117324
"""Converts a sequence of parameter values into query parameters.
118325
@@ -126,19 +333,10 @@ def to_query_parameters_list(parameters, parameter_types):
126333
List[google.cloud.bigquery.query._AbstractQueryParameter]:
127334
A list of query parameters.
128335
"""
129-
result= []
130-
131-
forvalue,type_inzip(parameters,parameter_types):
132-
ifisinstance(value,collections_abc.Mapping):
133-
raiseNotImplementedError("STRUCT-like parameter values are not supported.")
134-
elifarray_like(value):
135-
param=array_to_query_parameter(value,None,type_)
136-
else:
137-
param=scalar_to_query_parameter(value,None,type_)
138-
139-
result.append(param)
140-
141-
returnresult
336+
return [
337+
_dispatch_parameter(type_,value)
338+
forvalue,type_inzip(parameters,parameter_types)
339+
]
142340

143341

144342
defto_query_parameters_dict(parameters,query_parameter_types):
@@ -154,28 +352,10 @@ def to_query_parameters_dict(parameters, query_parameter_types):
154352
List[google.cloud.bigquery.query._AbstractQueryParameter]:
155353
A list of named query parameters.
156354
"""
157-
result= []
158-
159-
forname,valueinparameters.items():
160-
ifisinstance(value,collections_abc.Mapping):
161-
raiseNotImplementedError(
162-
"STRUCT-like parameter values are not supported "
163-
"(parameter {}).".format(name)
164-
)
165-
else:
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-
176-
result.append(param)
177-
178-
returnresult
355+
return [
356+
_dispatch_parameter(query_parameter_types.get(name),value,name)
357+
forname,valueinparameters.items()
358+
]
179359

180360

181361
defto_query_parameters(parameters,parameter_types):

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

Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -483,7 +483,33 @@ def _format_operation(operation, parameters):
483483

484484

485485
def_extract_types(
486-
operation,extra_type_sub=re.compile(r"(%*)%(?:\(([^:)]*)(?::(\w+))?\))?s").sub
486+
operation,
487+
extra_type_sub=re.compile(
488+
r"""
489+
(%*) # Extra %s. We'll deal with these in the replacement code
490+
491+
% # Beginning of replacement, %s, %(...)s
492+
493+
(?:\( # Begin of optional name and/or type
494+
([^:)]*) # name
495+
(?:: # ':' introduces type
496+
( # start of type group
497+
[a-zA-Z0-9<>, ]+ # First part, no parens
498+
499+
(?: # start sets of parens + non-paren text
500+
\([0-9 ,]+\) # comma-separated groups of digits in parens
501+
# (e.g. string(10))
502+
(?=[, >)]) # Must be followed by ,>) or space
503+
[a-zA-Z0-9<>, ]* # Optional non-paren chars
504+
)* # Can be zero or more of parens and following text
505+
) # end of type group
506+
)? # close type clause ":type"
507+
\))? # End of optional name and/or type
508+
509+
s # End of replacement
510+
""",
511+
re.VERBOSE,
512+
).sub,
487513
):
488514
"""Remove type information from parameter placeholders.
489515

‎tests/system/conftest.py‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -31,9 +31,14 @@ def bqstorage_client(bigquery_client):
3131
returnbigquery_storage.BigQueryReadClient(credentials=bigquery_client._credentials)
3232

3333

34-
@pytest.fixture
34+
@pytest.fixture(scope="session")
3535
defdataset_id(bigquery_client):
3636
dataset_id=f"bqsystem_{helpers.temp_suffix()}"
3737
bigquery_client.create_dataset(dataset_id)
3838
yielddataset_id
3939
bigquery_client.delete_dataset(dataset_id,delete_contents=True)
40+
41+
42+
@pytest.fixture
43+
deftable_id(dataset_id):
44+
returnf"{dataset_id}.table_{helpers.temp_suffix()}"

0 commit comments

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