Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/functions.py: 72%

1# sql/functions.py 

2# Copyright (C) 2005-2022 the SQLAlchemy authors and contributors 

3# <see AUTHORS file> 

4# 

5# This module is part of SQLAlchemy and is released under 

6# the MIT License: https://www.opensource.org/licenses/mit-license.php 

7 

8"""SQL function API, factories, and built-in functions. 

9 

10""" 

11from . import annotation 

12from . import coercions 

13from . import operators 

14from . import roles 

15from . import schema 

16from . import sqltypes 

17from . import util as sqlutil 

18from .base import _entity_namespace 

19from .base import ColumnCollection 

20from .base import Executable 

21from .base import Generative 

22from .base import HasMemoized 

23from .elements import _type_from_args 

24from .elements import BinaryExpression 

25from .elements import BindParameter 

26from .elements import Cast 

27from .elements import ClauseList 

28from .elements import ColumnElement 

29from .elements import Extract 

30from .elements import FunctionFilter 

31from .elements import Grouping 

32from .elements import literal_column 

33from .elements import NamedColumn 

34from .elements import Over 

35from .elements import WithinGroup 

36from .selectable import FromClause 

37from .selectable import Select 

38from .selectable import TableValuedAlias 

39from .visitors import InternalTraversal 

40from .visitors import TraversibleType 

41from .. import util 

42 

43 

44_registry = util.defaultdict(dict) 

45 

46 

47def register_function(identifier, fn, package="_default"): 

48 """Associate a callable with a particular func. name. 

49 

50 This is normally called by _GenericMeta, but is also 

51 available by itself so that a non-Function construct 

52 can be associated with the :data:`.func` accessor (i.e. 

53 CAST, EXTRACT). 

54 

55 """ 

56 reg = _registry[package] 

57 

58 identifier = util.text_type(identifier).lower() 

59 

60 # Check if a function with the same identifier is registered. 

61 if identifier in reg: 

62 util.warn( 

63 "The GenericFunction '{}' is already registered and " 

64 "is going to be overridden.".format(identifier) 

65 ) 

66 reg[identifier] = fn 

67 

68 

69class FunctionElement(Executable, ColumnElement, FromClause, Generative): 

70 """Base for SQL function-oriented constructs. 

71 

72 .. seealso:: 

73 

74 :ref:`tutorial_functions` - in the :ref:`unified_tutorial` 

75 

76 :class:`.Function` - named SQL function. 

77 

78 :data:`.func` - namespace which produces registered or ad-hoc 

79 :class:`.Function` instances. 

80 

81 :class:`.GenericFunction` - allows creation of registered function 

82 types. 

83 

84 """ 

85 

86 _traverse_internals = [ 

87 ("clause_expr", InternalTraversal.dp_clauseelement), 

88 ("_with_ordinality", InternalTraversal.dp_boolean), 

89 ("_table_value_type", InternalTraversal.dp_has_cache_key), 

90 ] 

91 

92 packagenames = () 

93 

94 _has_args = False 

95 _with_ordinality = False 

96 _table_value_type = None 

97 

98 def __init__(self, *clauses, **kwargs): 

99 r"""Construct a :class:`.FunctionElement`. 

100 

101 :param \*clauses: list of column expressions that form the arguments 

102 of the SQL function call. 

103 

104 :param \**kwargs: additional kwargs are typically consumed by 

105 subclasses. 

106 

107 .. seealso:: 

108 

109 :data:`.func` 

110 

111 :class:`.Function` 

112 

113 """ 

114 args = [ 

115 coercions.expect( 

116 roles.ExpressionElementRole, 

117 c, 

118 name=getattr(self, "name", None), 

119 apply_propagate_attrs=self, 

120 ) 

121 for c in clauses 

122 ] 

123 self._has_args = self._has_args or bool(args) 

124 self.clause_expr = ClauseList( 

125 operator=operators.comma_op, group_contents=True, *args 

126 ).self_group() 

127 

128 _non_anon_label = None 

129 

130 @property 

131 def _proxy_key(self): 

132 return super(FunctionElement, self)._proxy_key or getattr( 

133 self, "name", None 

134 ) 

135 

136 def _execute_on_connection( 

137 self, connection, multiparams, params, execution_options 

138 ): 

139 return connection._execute_function( 

140 self, multiparams, params, execution_options 

141 ) 

142 

143 def scalar_table_valued(self, name, type_=None): 

144 """Return a column expression that's against this 

145 :class:`_functions.FunctionElement` as a scalar 

146 table-valued expression. 

147 

148 The returned expression is similar to that returned by a single column 

149 accessed off of a :meth:`_functions.FunctionElement.table_valued` 

150 construct, except no FROM clause is generated; the function is rendered 

151 in the similar way as a scalar subquery. 

152 

153 E.g.:: 

154 

155 >>> from sqlalchemy import func, select 

156 >>> fn = func.jsonb_each("{'k', 'v'}").scalar_table_valued("key") 

157 >>> print(select(fn)) 

158 SELECT (jsonb_each(:jsonb_each_1)).key 

159 

160 .. versionadded:: 1.4.0b2 

161 

162 .. seealso:: 

163 

164 :meth:`_functions.FunctionElement.table_valued` 

165 

166 :meth:`_functions.FunctionElement.alias` 

167 

168 :meth:`_functions.FunctionElement.column_valued` 

169 

170 """ # noqa: E501 

171 

172 return ScalarFunctionColumn(self, name, type_) 

173 

174 def table_valued(self, *expr, **kw): 

175 r"""Return a :class:`_sql.TableValuedAlias` representation of this 

176 :class:`_functions.FunctionElement` with table-valued expressions added. 

177 

178 e.g.:: 

179 

180 >>> fn = ( 

181 ... func.generate_series(1, 5). 

182 ... table_valued("value", "start", "stop", "step") 

183 ... ) 

184 

185 >>> print(select(fn)) 

186 SELECT anon_1.value, anon_1.start, anon_1.stop, anon_1.step 

187 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1 

188 

189 >>> print(select(fn.c.value, fn.c.stop).where(fn.c.value > 2)) 

190 SELECT anon_1.value, anon_1.stop 

191 FROM generate_series(:generate_series_1, :generate_series_2) AS anon_1 

192 WHERE anon_1.value > :value_1 

193 

194 A WITH ORDINALITY expression may be generated by passing the keyword 

195 argument "with_ordinality":: 

196 

197 >>> fn = func.generate_series(4, 1, -1).table_valued("gen", with_ordinality="ordinality") 

198 >>> print(select(fn)) 

199 SELECT anon_1.gen, anon_1.ordinality 

200 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) WITH ORDINALITY AS anon_1 

201 

202 :param \*expr: A series of string column names that will be added to the 

203 ``.c`` collection of the resulting :class:`_sql.TableValuedAlias` 

204 construct as columns. :func:`_sql.column` objects with or without 

205 datatypes may also be used. 

206 

207 :param name: optional name to assign to the alias name that's generated. 

208 If omitted, a unique anonymizing name is used. 

209 

210 :param with_ordinality: string name that when present results in the 

211 ``WITH ORDINALITY`` clause being added to the alias, and the given 

212 string name will be added as a column to the .c collection 

213 of the resulting :class:`_sql.TableValuedAlias`. 

214 

215 :param joins_implicitly: when True, the table valued function may be 

216 used in the FROM clause without any explicit JOIN to other tables 

217 in the SQL query, and no "cartesian product" warning will be generated. 

218 May be useful for SQL functions such as ``func.json_each()``. 

219 

220 .. versionadded:: 1.4.33 

221 

222 .. versionadded:: 1.4.0b2 

223 

224 

225 .. seealso:: 

226 

227 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial` 

228 

229 :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation 

230 

231 :meth:`_functions.FunctionElement.scalar_table_valued` - variant of 

232 :meth:`_functions.FunctionElement.table_valued` which delivers the 

233 complete table valued expression as a scalar column expression 

234 

235 :meth:`_functions.FunctionElement.column_valued` 

236 

237 :meth:`_sql.TableValuedAlias.render_derived` - renders the alias 

238 using a derived column clause, e.g. ``AS name(col1, col2, ...)`` 

239 

240 """ # noqa: 501 

241 

242 new_func = self._generate() 

243 

244 with_ordinality = kw.pop("with_ordinality", None) 

245 joins_implicitly = kw.pop("joins_implicitly", None) 

246 name = kw.pop("name", None) 

247 

248 if with_ordinality: 

249 expr += (with_ordinality,) 

250 new_func._with_ordinality = True 

251 

252 new_func.type = new_func._table_value_type = sqltypes.TableValueType( 

253 *expr 

254 ) 

255 

256 return new_func.alias(name=name, joins_implicitly=joins_implicitly) 

257 

258 def column_valued(self, name=None): 

259 """Return this :class:`_functions.FunctionElement` as a column expression that 

260 selects from itself as a FROM clause. 

261 

262 E.g.:: 

263 

264 >>> from sqlalchemy import select, func 

265 >>> gs = func.generate_series(1, 5, -1).column_valued() 

266 >>> print(select(gs)) 

267 SELECT anon_1 

268 FROM generate_series(:generate_series_1, :generate_series_2, :generate_series_3) AS anon_1 

269 

270 This is shorthand for:: 

271 

272 gs = func.generate_series(1, 5, -1).alias().column 

273 

274 

275 .. seealso:: 

276 

277 :ref:`tutorial_functions_column_valued` - in the :ref:`unified_tutorial` 

278 

279 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation 

280 

281 :meth:`_functions.FunctionElement.table_valued` 

282 

283 """ # noqa: 501 

284 

285 return self.alias(name=name).column 

286 

287 @property 

288 def columns(self): 

289 r"""The set of columns exported by this :class:`.FunctionElement`. 

290 

291 This is a placeholder collection that allows the function to be 

292 placed in the FROM clause of a statement:: 

293 

294 >>> from sqlalchemy import column, select, func 

295 >>> stmt = select(column('x'), column('y')).select_from(func.myfunction()) 

296 >>> print(stmt) 

297 SELECT x, y FROM myfunction() 

298 

299 The above form is a legacy feature that is now superseded by the 

300 fully capable :meth:`_functions.FunctionElement.table_valued` 

301 method; see that method for details. 

302 

303 .. seealso:: 

304 

305 :meth:`_functions.FunctionElement.table_valued` - generates table-valued 

306 SQL function expressions. 

307 

308 """ # noqa: E501 

309 

310 return ColumnCollection( 

311 columns=[(col.key, col) for col in self._all_selected_columns] 

312 ) 

313 

314 @property 

315 def _all_selected_columns(self): 

316 if self.type._is_table_value: 

317 cols = self.type._elements 

318 else: 

319 cols = [self.label(None)] 

320 

321 return cols 

322 

323 @property 

324 def exported_columns(self): 

325 return self.columns 

326 

327 @HasMemoized.memoized_attribute 

328 def clauses(self): 

329 """Return the underlying :class:`.ClauseList` which contains 

330 the arguments for this :class:`.FunctionElement`. 

331 

332 """ 

333 return self.clause_expr.element 

334 

335 def over(self, partition_by=None, order_by=None, rows=None, range_=None): 

336 """Produce an OVER clause against this function. 

337 

338 Used against aggregate or so-called "window" functions, 

339 for database backends that support window functions. 

340 

341 The expression:: 

342 

343 func.row_number().over(order_by='x') 

344 

345 is shorthand for:: 

346 

347 from sqlalchemy import over 

348 over(func.row_number(), order_by='x') 

349 

350 See :func:`_expression.over` for a full description. 

351 

352 .. seealso:: 

353 

354 :func:`_expression.over` 

355 

356 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial` 

357 

358 """ 

359 return Over( 

360 self, 

361 partition_by=partition_by, 

362 order_by=order_by, 

363 rows=rows, 

364 range_=range_, 

365 ) 

366 

367 def within_group(self, *order_by): 

368 """Produce a WITHIN GROUP (ORDER BY expr) clause against this function. 

369 

370 Used against so-called "ordered set aggregate" and "hypothetical 

371 set aggregate" functions, including :class:`.percentile_cont`, 

372 :class:`.rank`, :class:`.dense_rank`, etc. 

373 

374 See :func:`_expression.within_group` for a full description. 

375 

376 .. versionadded:: 1.1 

377 

378 

379 .. seealso:: 

380 

381 :ref:`tutorial_functions_within_group` - 

382 in the :ref:`unified_tutorial` 

383 

384 

385 """ 

386 return WithinGroup(self, *order_by) 

387 

388 def filter(self, *criterion): 

389 """Produce a FILTER clause against this function. 

390 

391 Used against aggregate and window functions, 

392 for database backends that support the "FILTER" clause. 

393 

394 The expression:: 

395 

396 func.count(1).filter(True) 

397 

398 is shorthand for:: 

399 

400 from sqlalchemy import funcfilter 

401 funcfilter(func.count(1), True) 

402 

403 .. versionadded:: 1.0.0 

404 

405 .. seealso:: 

406 

407 :ref:`tutorial_functions_within_group` - 

408 in the :ref:`unified_tutorial` 

409 

410 :class:`.FunctionFilter` 

411 

412 :func:`.funcfilter` 

413 

414 

415 """ 

416 if not criterion: 

417 return self 

418 return FunctionFilter(self, *criterion) 

419 

420 def as_comparison(self, left_index, right_index): 

421 """Interpret this expression as a boolean comparison between two 

422 values. 

423 

424 This method is used for an ORM use case described at 

425 :ref:`relationship_custom_operator_sql_function`. 

426 

427 A hypothetical SQL function "is_equal()" which compares to values 

428 for equality would be written in the Core expression language as:: 

429 

430 expr = func.is_equal("a", "b") 

431 

432 If "is_equal()" above is comparing "a" and "b" for equality, the 

433 :meth:`.FunctionElement.as_comparison` method would be invoked as:: 

434 

435 expr = func.is_equal("a", "b").as_comparison(1, 2) 

436 

437 Where above, the integer value "1" refers to the first argument of the 

438 "is_equal()" function and the integer value "2" refers to the second. 

439 

440 This would create a :class:`.BinaryExpression` that is equivalent to:: 

441 

442 BinaryExpression("a", "b", operator=op.eq) 

443 

444 However, at the SQL level it would still render as 

445 "is_equal('a', 'b')". 

446 

447 The ORM, when it loads a related object or collection, needs to be able 

448 to manipulate the "left" and "right" sides of the ON clause of a JOIN 

449 expression. The purpose of this method is to provide a SQL function 

450 construct that can also supply this information to the ORM, when used 

451 with the :paramref:`_orm.relationship.primaryjoin` parameter. The 

452 return value is a containment object called :class:`.FunctionAsBinary`. 

453 

454 An ORM example is as follows:: 

455 

456 class Venue(Base): 

457 __tablename__ = 'venue' 

458 id = Column(Integer, primary_key=True) 

459 name = Column(String) 

460 

461 descendants = relationship( 

462 "Venue", 

463 primaryjoin=func.instr( 

464 remote(foreign(name)), name + "/" 

465 ).as_comparison(1, 2) == 1, 

466 viewonly=True, 

467 order_by=name 

468 ) 

469 

470 Above, the "Venue" class can load descendant "Venue" objects by 

471 determining if the name of the parent Venue is contained within the 

472 start of the hypothetical descendant value's name, e.g. "parent1" would 

473 match up to "parent1/child1", but not to "parent2/child1". 

474 

475 Possible use cases include the "materialized path" example given above, 

476 as well as making use of special SQL functions such as geometric 

477 functions to create join conditions. 

478 

479 :param left_index: the integer 1-based index of the function argument 

480 that serves as the "left" side of the expression. 

481 :param right_index: the integer 1-based index of the function argument 

482 that serves as the "right" side of the expression. 

483 

484 .. versionadded:: 1.3 

485 

486 .. seealso:: 

487 

488 :ref:`relationship_custom_operator_sql_function` - 

489 example use within the ORM 

490 

491 """ 

492 return FunctionAsBinary(self, left_index, right_index) 

493 

494 @property 

495 def _from_objects(self): 

496 return self.clauses._from_objects 

497 

498 def within_group_type(self, within_group): 

499 """For types that define their return type as based on the criteria 

500 within a WITHIN GROUP (ORDER BY) expression, called by the 

501 :class:`.WithinGroup` construct. 

502 

503 Returns None by default, in which case the function's normal ``.type`` 

504 is used. 

505 

506 """ 

507 

508 return None 

509 

510 def alias(self, name=None, joins_implicitly=False): 

511 r"""Produce a :class:`_expression.Alias` construct against this 

512 :class:`.FunctionElement`. 

513 

514 .. tip:: 

515 

516 The :meth:`_functions.FunctionElement.alias` method is part of the 

517 mechanism by which "table valued" SQL functions are created. 

518 However, most use cases are covered by higher level methods on 

519 :class:`_functions.FunctionElement` including 

520 :meth:`_functions.FunctionElement.table_valued`, and 

521 :meth:`_functions.FunctionElement.column_valued`. 

522 

523 This construct wraps the function in a named alias which 

524 is suitable for the FROM clause, in the style accepted for example 

525 by PostgreSQL. A column expression is also provided using the 

526 special ``.column`` attribute, which may 

527 be used to refer to the output of the function as a scalar value 

528 in the columns or where clause, for a backend such as PostgreSQL. 

529 

530 For a full table-valued expression, use the 

531 :meth:`_functions.FunctionElement.table_valued` method first to 

532 establish named columns. 

533 

534 e.g.:: 

535 

536 >>> from sqlalchemy import func, select, column 

537 >>> data_view = func.unnest([1, 2, 3]).alias("data_view") 

538 >>> print(select(data_view.column)) 

539 SELECT data_view 

540 FROM unnest(:unnest_1) AS data_view 

541 

542 The :meth:`_functions.FunctionElement.column_valued` method provides 

543 a shortcut for the above pattern:: 

544 

545 >>> data_view = func.unnest([1, 2, 3]).column_valued("data_view") 

546 >>> print(select(data_view)) 

547 SELECT data_view 

548 FROM unnest(:unnest_1) AS data_view 

549 

550 .. versionadded:: 1.4.0b2 Added the ``.column`` accessor 

551 

552 :param name: alias name, will be rendered as ``AS <name>`` in the 

553 FROM clause 

554 

555 :param joins_implicitly: when True, the table valued function may be 

556 used in the FROM clause without any explicit JOIN to other tables 

557 in the SQL query, and no "cartesian product" warning will be 

558 generated. May be useful for SQL functions such as 

559 ``func.json_each()``. 

560 

561 .. versionadded:: 1.4.33 

562 

563 .. seealso:: 

564 

565 :ref:`tutorial_functions_table_valued` - 

566 in the :ref:`unified_tutorial` 

567 

568 :meth:`_functions.FunctionElement.table_valued` 

569 

570 :meth:`_functions.FunctionElement.scalar_table_valued` 

571 

572 :meth:`_functions.FunctionElement.column_valued` 

573 

574 

575 """ 

576 

577 return TableValuedAlias._construct( 

578 self, 

579 name, 

580 table_value_type=self.type, 

581 joins_implicitly=joins_implicitly, 

582 ) 

583 

584 def select(self): 

585 """Produce a :func:`_expression.select` construct 

586 against this :class:`.FunctionElement`. 

587 

588 This is shorthand for:: 

589 

590 s = select(function_element) 

591 

592 """ 

593 s = Select._create_select(self) 

594 if self._execution_options: 

595 s = s.execution_options(**self._execution_options) 

596 return s 

597 

598 @util.deprecated_20( 

599 ":meth:`.FunctionElement.scalar`", 

600 alternative="Scalar execution in SQLAlchemy 2.0 is performed " 

601 "by the :meth:`_engine.Connection.scalar` method of " 

602 ":class:`_engine.Connection`, " 

603 "or in the ORM by the :meth:`.Session.scalar` method of " 

604 ":class:`.Session`.", 

605 ) 

606 def scalar(self): 

607 """Execute this :class:`.FunctionElement` against an embedded 

608 'bind' and return a scalar value. 

609 

610 This first calls :meth:`~.FunctionElement.select` to 

611 produce a SELECT construct. 

612 

613 Note that :class:`.FunctionElement` can be passed to 

614 the :meth:`.Connectable.scalar` method of :class:`_engine.Connection` 

615 or :class:`_engine.Engine`. 

616 

617 """ 

618 return self.select().execute().scalar() 

619 

620 @util.deprecated_20( 

621 ":meth:`.FunctionElement.execute`", 

622 alternative="All statement execution in SQLAlchemy 2.0 is performed " 

623 "by the :meth:`_engine.Connection.execute` method of " 

624 ":class:`_engine.Connection`, " 

625 "or in the ORM by the :meth:`.Session.execute` method of " 

626 ":class:`.Session`.", 

627 ) 

628 def execute(self): 

629 """Execute this :class:`.FunctionElement` against an embedded 

630 'bind'. 

631 

632 This first calls :meth:`~.FunctionElement.select` to 

633 produce a SELECT construct. 

634 

635 Note that :class:`.FunctionElement` can be passed to 

636 the :meth:`.Connectable.execute` method of :class:`_engine.Connection` 

637 or :class:`_engine.Engine`. 

638 

639 """ 

640 return self.select().execute() 

641 

642 def _bind_param(self, operator, obj, type_=None, **kw): 

643 return BindParameter( 

644 None, 

645 obj, 

646 _compared_to_operator=operator, 

647 _compared_to_type=self.type, 

648 unique=True, 

649 type_=type_, 

650 **kw 

651 ) 

652 

653 def self_group(self, against=None): 

654 # for the moment, we are parenthesizing all array-returning 

655 # expressions against getitem. This may need to be made 

656 # more portable if in the future we support other DBs 

657 # besides postgresql. 

658 if against is operators.getitem and isinstance( 

659 self.type, sqltypes.ARRAY 

660 ): 

661 return Grouping(self) 

662 else: 

663 return super(FunctionElement, self).self_group(against=against) 

664 

665 @property 

666 def entity_namespace(self): 

667 """overrides FromClause.entity_namespace as functions are generally 

668 column expressions and not FromClauses. 

669 

670 """ 

671 # ideally functions would not be fromclauses but we failed to make 

672 # this adjustment in 1.4 

673 return _entity_namespace(self.clause_expr) 

674 

675 

676class FunctionAsBinary(BinaryExpression): 

677 _traverse_internals = [ 

678 ("sql_function", InternalTraversal.dp_clauseelement), 

679 ("left_index", InternalTraversal.dp_plain_obj), 

680 ("right_index", InternalTraversal.dp_plain_obj), 

681 ("modifiers", InternalTraversal.dp_plain_dict), 

682 ] 

683 

684 def _gen_cache_key(self, anon_map, bindparams): 

685 return ColumnElement._gen_cache_key(self, anon_map, bindparams) 

686 

687 def __init__(self, fn, left_index, right_index): 

688 self.sql_function = fn 

689 self.left_index = left_index 

690 self.right_index = right_index 

691 

692 self.operator = operators.function_as_comparison_op 

693 self.type = sqltypes.BOOLEANTYPE 

694 self.negate = None 

695 self._is_implicitly_boolean = True 

696 self.modifiers = {} 

697 

698 @property 

699 def left(self): 

700 return self.sql_function.clauses.clauses[self.left_index - 1] 

701 

702 @left.setter 

703 def left(self, value): 

704 self.sql_function.clauses.clauses[self.left_index - 1] = value 

705 

706 @property 

707 def right(self): 

708 return self.sql_function.clauses.clauses[self.right_index - 1] 

709 

710 @right.setter 

711 def right(self, value): 

712 self.sql_function.clauses.clauses[self.right_index - 1] = value 

713 

714 

715class ScalarFunctionColumn(NamedColumn): 

716 __visit_name__ = "scalar_function_column" 

717 

718 _traverse_internals = [ 

719 ("name", InternalTraversal.dp_anon_name), 

720 ("type", InternalTraversal.dp_type), 

721 ("fn", InternalTraversal.dp_clauseelement), 

722 ] 

723 

724 is_literal = False 

725 table = None 

726 

727 def __init__(self, fn, name, type_=None): 

728 self.fn = fn 

729 self.name = name 

730 self.type = sqltypes.to_instance(type_) 

731 

732 

733class _FunctionGenerator(object): 

734 """Generate SQL function expressions. 

735 

736 :data:`.func` is a special object instance which generates SQL 

737 functions based on name-based attributes, e.g.:: 

738 

739 >>> print(func.count(1)) 

740 count(:param_1) 

741 

742 The returned object is an instance of :class:`.Function`, and is a 

743 column-oriented SQL element like any other, and is used in that way:: 

744 

745 >>> print(select(func.count(table.c.id))) 

746 SELECT count(sometable.id) FROM sometable 

747 

748 Any name can be given to :data:`.func`. If the function name is unknown to 

749 SQLAlchemy, it will be rendered exactly as is. For common SQL functions 

750 which SQLAlchemy is aware of, the name may be interpreted as a *generic 

751 function* which will be compiled appropriately to the target database:: 

752 

753 >>> print(func.current_timestamp()) 

754 CURRENT_TIMESTAMP 

755 

756 To call functions which are present in dot-separated packages, 

757 specify them in the same manner:: 

758 

759 >>> print(func.stats.yield_curve(5, 10)) 

760 stats.yield_curve(:yield_curve_1, :yield_curve_2) 

761 

762 SQLAlchemy can be made aware of the return type of functions to enable 

763 type-specific lexical and result-based behavior. For example, to ensure 

764 that a string-based function returns a Unicode value and is similarly 

765 treated as a string in expressions, specify 

766 :class:`~sqlalchemy.types.Unicode` as the type: 

767 

768 >>> print(func.my_string(u'hi', type_=Unicode) + ' ' + 

769 ... func.my_string(u'there', type_=Unicode)) 

770 my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3) 

771 

772 The object returned by a :data:`.func` call is usually an instance of 

773 :class:`.Function`. 

774 This object meets the "column" interface, including comparison and labeling 

775 functions. The object can also be passed the :meth:`~.Connectable.execute` 

776 method of a :class:`_engine.Connection` or :class:`_engine.Engine`, 

777 where it will be 

778 wrapped inside of a SELECT statement first:: 

779 

780 print(connection.execute(func.current_timestamp()).scalar()) 

781 

782 In a few exception cases, the :data:`.func` accessor 

783 will redirect a name to a built-in expression such as :func:`.cast` 

784 or :func:`.extract`, as these names have well-known meaning 

785 but are not exactly the same as "functions" from a SQLAlchemy 

786 perspective. 

787 

788 Functions which are interpreted as "generic" functions know how to 

789 calculate their return type automatically. For a listing of known generic 

790 functions, see :ref:`generic_functions`. 

791 

792 .. note:: 

793 

794 The :data:`.func` construct has only limited support for calling 

795 standalone "stored procedures", especially those with special 

796 parameterization concerns. 

797 

798 See the section :ref:`stored_procedures` for details on how to use 

799 the DBAPI-level ``callproc()`` method for fully traditional stored 

800 procedures. 

801 

802 .. seealso:: 

803 

804 :ref:`tutorial_functions` - in the :ref:`unified_tutorial` 

805 

806 :class:`.Function` 

807 

808 """ 

809 

810 def __init__(self, **opts): 

811 self.__names = [] 

812 self.opts = opts 

813 

814 def __getattr__(self, name): 

815 # passthru __ attributes; fixes pydoc 

816 if name.startswith("__"): 

817 try: 

818 return self.__dict__[name] 

819 except KeyError: 

820 raise AttributeError(name) 

821 

822 elif name.endswith("_"): 

823 name = name[0:-1] 

824 f = _FunctionGenerator(**self.opts) 

825 f.__names = list(self.__names) + [name] 

826 return f 

827 

828 def __call__(self, *c, **kwargs): 

829 o = self.opts.copy() 

830 o.update(kwargs) 

831 

832 tokens = len(self.__names) 

833 

834 if tokens == 2: 

835 package, fname = self.__names 

836 elif tokens == 1: 

837 package, fname = "_default", self.__names[0] 

838 else: 

839 package = None 

840 

841 if package is not None: 

842 func = _registry[package].get(fname.lower()) 

843 if func is not None: 

844 return func(*c, **o) 

845 

846 return Function( 

847 self.__names[-1], packagenames=tuple(self.__names[0:-1]), *c, **o 

848 ) 

849 

850 

851func = _FunctionGenerator() 

852func.__doc__ = _FunctionGenerator.__doc__ 

853 

854modifier = _FunctionGenerator(group=False) 

855 

856 

857class Function(FunctionElement): 

858 r"""Describe a named SQL function. 

859 

860 The :class:`.Function` object is typically generated from the 

861 :data:`.func` generation object. 

862 

863 

864 :param \*clauses: list of column expressions that form the arguments 

865 of the SQL function call. 

866 

867 :param type\_: optional :class:`.TypeEngine` datatype object that will be 

868 used as the return value of the column expression generated by this 

869 function call. 

870 

871 :param packagenames: a string which indicates package prefix names 

872 to be prepended to the function name when the SQL is generated. 

873 The :data:`.func` generator creates these when it is called using 

874 dotted format, e.g.:: 

875 

876 func.mypackage.some_function(col1, col2) 

877 

878 .. seealso:: 

879 

880 :ref:`tutorial_functions` - in the :ref:`unified_tutorial` 

881 

882 :data:`.func` - namespace which produces registered or ad-hoc 

883 :class:`.Function` instances. 

884 

885 :class:`.GenericFunction` - allows creation of registered function 

886 types. 

887 

888 """ 

889 

890 __visit_name__ = "function" 

891 

892 _traverse_internals = FunctionElement._traverse_internals + [ 

893 ("packagenames", InternalTraversal.dp_plain_obj), 

894 ("name", InternalTraversal.dp_string), 

895 ("type", InternalTraversal.dp_type), 

896 ] 

897 

898 type = sqltypes.NULLTYPE 

899 """A :class:`_types.TypeEngine` object which refers to the SQL return 

900 type represented by this SQL function. 

901 

902 This datatype may be configured when generating a 

903 :class:`_functions.Function` object by passing the 

904 :paramref:`_functions.Function.type_` parameter, e.g.:: 

905 

906 >>> select(func.lower("some VALUE", type_=String)) 

907 

908 The small number of built-in classes of :class:`_functions.Function` come 

909 with a built-in datatype that's appropriate to the class of function and 

910 its arguments. For functions that aren't known, the type defaults to the 

911 "null type". 

912 

913 """ 

914 

915 @util.deprecated_params( 

916 bind=( 

917 "2.0", 

918 "The :paramref:`_sql.text.bind` argument is deprecated and " 

919 "will be removed in SQLAlchemy 2.0.", 

920 ), 

921 ) 

922 def __init__(self, name, *clauses, **kw): 

923 """Construct a :class:`.Function`. 

924 

925 The :data:`.func` construct is normally used to construct 

926 new :class:`.Function` instances. 

927 

928 """ 

929 self.packagenames = kw.pop("packagenames", None) or () 

930 self.name = name 

931 

932 self._bind = self._get_bind(kw) 

933 self.type = sqltypes.to_instance(kw.get("type_", None)) 

934 

935 FunctionElement.__init__(self, *clauses, **kw) 

936 

937 def _get_bind(self, kw): 

938 if "bind" in kw: 

939 util.warn_deprecated_20( 

940 "The Function.bind argument is deprecated and " 

941 "will be removed in SQLAlchemy 2.0.", 

942 ) 

943 return kw["bind"] 

944 

945 def _bind_param(self, operator, obj, type_=None, **kw): 

946 return BindParameter( 

947 self.name, 

948 obj, 

949 _compared_to_operator=operator, 

950 _compared_to_type=self.type, 

951 type_=type_, 

952 unique=True, 

953 **kw 

954 ) 

955 

956 

957class _GenericMeta(TraversibleType): 

958 def __init__(cls, clsname, bases, clsdict): 

959 if annotation.Annotated not in cls.__mro__: 

960 cls.name = name = clsdict.get("name", clsname) 

961 cls.identifier = identifier = clsdict.get("identifier", name) 

962 package = clsdict.pop("package", "_default") 

963 # legacy 

964 if "__return_type__" in clsdict: 

965 cls.type = clsdict["__return_type__"] 

966 

967 # Check _register attribute status 

968 cls._register = getattr(cls, "_register", True) 

969 

970 # Register the function if required 

971 if cls._register: 

972 register_function(identifier, cls, package) 

973 else: 

974 # Set _register to True to register child classes by default 

975 cls._register = True 

976 

977 super(_GenericMeta, cls).__init__(clsname, bases, clsdict) 

978 

979 

980class GenericFunction(util.with_metaclass(_GenericMeta, Function)): 

981 """Define a 'generic' function. 

982 

983 A generic function is a pre-established :class:`.Function` 

984 class that is instantiated automatically when called 

985 by name from the :data:`.func` attribute. Note that 

986 calling any name from :data:`.func` has the effect that 

987 a new :class:`.Function` instance is created automatically, 

988 given that name. The primary use case for defining 

989 a :class:`.GenericFunction` class is so that a function 

990 of a particular name may be given a fixed return type. 

991 It can also include custom argument parsing schemes as well 

992 as additional methods. 

993 

994 Subclasses of :class:`.GenericFunction` are automatically 

995 registered under the name of the class. For 

996 example, a user-defined function ``as_utc()`` would 

997 be available immediately:: 

998 

999 from sqlalchemy.sql.functions import GenericFunction 

1000 from sqlalchemy.types import DateTime 

1001 

1002 class as_utc(GenericFunction): 

1003 type = DateTime 

1004 inherit_cache = True 

1005 

1006 print(select(func.as_utc())) 

1007 

1008 User-defined generic functions can be organized into 

1009 packages by specifying the "package" attribute when defining 

1010 :class:`.GenericFunction`. Third party libraries 

1011 containing many functions may want to use this in order 

1012 to avoid name conflicts with other systems. For example, 

1013 if our ``as_utc()`` function were part of a package 

1014 "time":: 

1015 

1016 class as_utc(GenericFunction): 

1017 type = DateTime 

1018 package = "time" 

1019 inherit_cache = True 

1020 

1021 The above function would be available from :data:`.func` 

1022 using the package name ``time``:: 

1023 

1024 print(select(func.time.as_utc())) 

1025 

1026 A final option is to allow the function to be accessed 

1027 from one name in :data:`.func` but to render as a different name. 

1028 The ``identifier`` attribute will override the name used to 

1029 access the function as loaded from :data:`.func`, but will retain 

1030 the usage of ``name`` as the rendered name:: 

1031 

1032 class GeoBuffer(GenericFunction): 

1033 type = Geometry 

1034 package = "geo" 

1035 name = "ST_Buffer" 

1036 identifier = "buffer" 

1037 inherit_cache = True 

1038 

1039 The above function will render as follows:: 

1040 

1041 >>> print(func.geo.buffer()) 

1042 ST_Buffer() 

1043 

1044 The name will be rendered as is, however without quoting unless the name 

1045 contains special characters that require quoting. To force quoting 

1046 on or off for the name, use the :class:`.sqlalchemy.sql.quoted_name` 

1047 construct:: 

1048 

1049 from sqlalchemy.sql import quoted_name 

1050 

1051 class GeoBuffer(GenericFunction): 

1052 type = Geometry 

1053 package = "geo" 

1054 name = quoted_name("ST_Buffer", True) 

1055 identifier = "buffer" 

1056 inherit_cache = True 

1057 

1058 The above function will render as:: 

1059 

1060 >>> print(func.geo.buffer()) 

1061 "ST_Buffer"() 

1062 

1063 .. versionadded:: 1.3.13 The :class:`.quoted_name` construct is now 

1064 recognized for quoting when used with the "name" attribute of the 

1065 object, so that quoting can be forced on or off for the function 

1066 name. 

1067 

1068 

1069 """ 

1070 

1071 coerce_arguments = True 

1072 _register = False 

1073 inherit_cache = True 

1074 

1075 def __init__(self, *args, **kwargs): 

1076 parsed_args = kwargs.pop("_parsed_args", None) 

1077 if parsed_args is None: 

1078 parsed_args = [ 

1079 coercions.expect( 

1080 roles.ExpressionElementRole, 

1081 c, 

1082 name=self.name, 

1083 apply_propagate_attrs=self, 

1084 ) 

1085 for c in args 

1086 ] 

1087 self._has_args = self._has_args or bool(parsed_args) 

1088 self.packagenames = () 

1089 self._bind = self._get_bind(kwargs) 

1090 self.clause_expr = ClauseList( 

1091 operator=operators.comma_op, group_contents=True, *parsed_args 

1092 ).self_group() 

1093 self.type = sqltypes.to_instance( 

1094 kwargs.pop("type_", None) or getattr(self, "type", None) 

1095 ) 

1096 

1097 

1098register_function("cast", Cast)