Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/psycopg2/sql.py: 57%
155 statements
« prev ^ index » next coverage.py v7.3.1, created at 2023-09-25 06:18 +0000
« prev ^ index » next coverage.py v7.3.1, created at 2023-09-25 06:18 +0000
1"""SQL composition utility module
2"""
4# psycopg/sql.py - SQL composition utility module
5#
6# Copyright (C) 2016-2019 Daniele Varrazzo <daniele.varrazzo@gmail.com>
7# Copyright (C) 2020-2021 The Psycopg Team
8#
9# psycopg2 is free software: you can redistribute it and/or modify it
10# under the terms of the GNU Lesser General Public License as published
11# by the Free Software Foundation, either version 3 of the License, or
12# (at your option) any later version.
13#
14# In addition, as a special exception, the copyright holders give
15# permission to link this program with the OpenSSL library (or with
16# modified versions of OpenSSL that use the same license as OpenSSL),
17# and distribute linked combinations including the two.
18#
19# You must obey the GNU Lesser General Public License in all respects for
20# all of the code used other than OpenSSL.
21#
22# psycopg2 is distributed in the hope that it will be useful, but WITHOUT
23# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
24# FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public
25# License for more details.
27import string
29from psycopg2 import extensions as ext
32_formatter = string.Formatter()
35class Composable:
36 """
37 Abstract base class for objects that can be used to compose an SQL string.
39 `!Composable` objects can be passed directly to `~cursor.execute()`,
40 `~cursor.executemany()`, `~cursor.copy_expert()` in place of the query
41 string.
43 `!Composable` objects can be joined using the ``+`` operator: the result
44 will be a `Composed` instance containing the objects joined. The operator
45 ``*`` is also supported with an integer argument: the result is a
46 `!Composed` instance containing the left argument repeated as many times as
47 requested.
48 """
49 def __init__(self, wrapped):
50 self._wrapped = wrapped
52 def __repr__(self):
53 return f"{self.__class__.__name__}({self._wrapped!r})"
55 def as_string(self, context):
56 """
57 Return the string value of the object.
59 :param context: the context to evaluate the string into.
60 :type context: `connection` or `cursor`
62 The method is automatically invoked by `~cursor.execute()`,
63 `~cursor.executemany()`, `~cursor.copy_expert()` if a `!Composable` is
64 passed instead of the query string.
65 """
66 raise NotImplementedError
68 def __add__(self, other):
69 if isinstance(other, Composed):
70 return Composed([self]) + other
71 if isinstance(other, Composable):
72 return Composed([self]) + Composed([other])
73 else:
74 return NotImplemented
76 def __mul__(self, n):
77 return Composed([self] * n)
79 def __eq__(self, other):
80 return type(self) is type(other) and self._wrapped == other._wrapped
82 def __ne__(self, other):
83 return not self.__eq__(other)
86class Composed(Composable):
87 """
88 A `Composable` object made of a sequence of `!Composable`.
90 The object is usually created using `!Composable` operators and methods.
91 However it is possible to create a `!Composed` directly specifying a
92 sequence of `!Composable` as arguments.
94 Example::
96 >>> comp = sql.Composed(
97 ... [sql.SQL("insert into "), sql.Identifier("table")])
98 >>> print(comp.as_string(conn))
99 insert into "table"
101 `!Composed` objects are iterable (so they can be used in `SQL.join` for
102 instance).
103 """
104 def __init__(self, seq):
105 wrapped = []
106 for i in seq:
107 if not isinstance(i, Composable):
108 raise TypeError(
109 f"Composed elements must be Composable, got {i!r} instead")
110 wrapped.append(i)
112 super().__init__(wrapped)
114 @property
115 def seq(self):
116 """The list of the content of the `!Composed`."""
117 return list(self._wrapped)
119 def as_string(self, context):
120 rv = []
121 for i in self._wrapped:
122 rv.append(i.as_string(context))
123 return ''.join(rv)
125 def __iter__(self):
126 return iter(self._wrapped)
128 def __add__(self, other):
129 if isinstance(other, Composed):
130 return Composed(self._wrapped + other._wrapped)
131 if isinstance(other, Composable):
132 return Composed(self._wrapped + [other])
133 else:
134 return NotImplemented
136 def join(self, joiner):
137 """
138 Return a new `!Composed` interposing the *joiner* with the `!Composed` items.
140 The *joiner* must be a `SQL` or a string which will be interpreted as
141 an `SQL`.
143 Example::
145 >>> fields = sql.Identifier('foo') + sql.Identifier('bar') # a Composed
146 >>> print(fields.join(', ').as_string(conn))
147 "foo", "bar"
149 """
150 if isinstance(joiner, str):
151 joiner = SQL(joiner)
152 elif not isinstance(joiner, SQL):
153 raise TypeError(
154 "Composed.join() argument must be a string or an SQL")
156 return joiner.join(self)
159class SQL(Composable):
160 """
161 A `Composable` representing a snippet of SQL statement.
163 `!SQL` exposes `join()` and `format()` methods useful to create a template
164 where to merge variable parts of a query (for instance field or table
165 names).
167 The *string* doesn't undergo any form of escaping, so it is not suitable to
168 represent variable identifiers or values: you should only use it to pass
169 constant strings representing templates or snippets of SQL statements; use
170 other objects such as `Identifier` or `Literal` to represent variable
171 parts.
173 Example::
175 >>> query = sql.SQL("select {0} from {1}").format(
176 ... sql.SQL(', ').join([sql.Identifier('foo'), sql.Identifier('bar')]),
177 ... sql.Identifier('table'))
178 >>> print(query.as_string(conn))
179 select "foo", "bar" from "table"
180 """
181 def __init__(self, string):
182 if not isinstance(string, str):
183 raise TypeError("SQL values must be strings")
184 super().__init__(string)
186 @property
187 def string(self):
188 """The string wrapped by the `!SQL` object."""
189 return self._wrapped
191 def as_string(self, context):
192 return self._wrapped
194 def format(self, *args, **kwargs):
195 """
196 Merge `Composable` objects into a template.
198 :param `Composable` args: parameters to replace to numbered
199 (``{0}``, ``{1}``) or auto-numbered (``{}``) placeholders
200 :param `Composable` kwargs: parameters to replace to named (``{name}``)
201 placeholders
202 :return: the union of the `!SQL` string with placeholders replaced
203 :rtype: `Composed`
205 The method is similar to the Python `str.format()` method: the string
206 template supports auto-numbered (``{}``), numbered (``{0}``,
207 ``{1}``...), and named placeholders (``{name}``), with positional
208 arguments replacing the numbered placeholders and keywords replacing
209 the named ones. However placeholder modifiers (``{0!r}``, ``{0:<10}``)
210 are not supported. Only `!Composable` objects can be passed to the
211 template.
213 Example::
215 >>> print(sql.SQL("select * from {} where {} = %s")
216 ... .format(sql.Identifier('people'), sql.Identifier('id'))
217 ... .as_string(conn))
218 select * from "people" where "id" = %s
220 >>> print(sql.SQL("select * from {tbl} where {pkey} = %s")
221 ... .format(tbl=sql.Identifier('people'), pkey=sql.Identifier('id'))
222 ... .as_string(conn))
223 select * from "people" where "id" = %s
225 """
226 rv = []
227 autonum = 0
228 for pre, name, spec, conv in _formatter.parse(self._wrapped):
229 if spec:
230 raise ValueError("no format specification supported by SQL")
231 if conv:
232 raise ValueError("no format conversion supported by SQL")
233 if pre:
234 rv.append(SQL(pre))
236 if name is None:
237 continue
239 if name.isdigit():
240 if autonum:
241 raise ValueError(
242 "cannot switch from automatic field numbering to manual")
243 rv.append(args[int(name)])
244 autonum = None
246 elif not name:
247 if autonum is None:
248 raise ValueError(
249 "cannot switch from manual field numbering to automatic")
250 rv.append(args[autonum])
251 autonum += 1
253 else:
254 rv.append(kwargs[name])
256 return Composed(rv)
258 def join(self, seq):
259 """
260 Join a sequence of `Composable`.
262 :param seq: the elements to join.
263 :type seq: iterable of `!Composable`
265 Use the `!SQL` object's *string* to separate the elements in *seq*.
266 Note that `Composed` objects are iterable too, so they can be used as
267 argument for this method.
269 Example::
271 >>> snip = sql.SQL(', ').join(
272 ... sql.Identifier(n) for n in ['foo', 'bar', 'baz'])
273 >>> print(snip.as_string(conn))
274 "foo", "bar", "baz"
275 """
276 rv = []
277 it = iter(seq)
278 try:
279 rv.append(next(it))
280 except StopIteration:
281 pass
282 else:
283 for i in it:
284 rv.append(self)
285 rv.append(i)
287 return Composed(rv)
290class Identifier(Composable):
291 """
292 A `Composable` representing an SQL identifier or a dot-separated sequence.
294 Identifiers usually represent names of database objects, such as tables or
295 fields. PostgreSQL identifiers follow `different rules`__ than SQL string
296 literals for escaping (e.g. they use double quotes instead of single).
298 .. __: https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html# \
299 SQL-SYNTAX-IDENTIFIERS
301 Example::
303 >>> t1 = sql.Identifier("foo")
304 >>> t2 = sql.Identifier("ba'r")
305 >>> t3 = sql.Identifier('ba"z')
306 >>> print(sql.SQL(', ').join([t1, t2, t3]).as_string(conn))
307 "foo", "ba'r", "ba""z"
309 Multiple strings can be passed to the object to represent a qualified name,
310 i.e. a dot-separated sequence of identifiers.
312 Example::
314 >>> query = sql.SQL("select {} from {}").format(
315 ... sql.Identifier("table", "field"),
316 ... sql.Identifier("schema", "table"))
317 >>> print(query.as_string(conn))
318 select "table"."field" from "schema"."table"
320 """
321 def __init__(self, *strings):
322 if not strings:
323 raise TypeError("Identifier cannot be empty")
325 for s in strings:
326 if not isinstance(s, str):
327 raise TypeError("SQL identifier parts must be strings")
329 super().__init__(strings)
331 @property
332 def strings(self):
333 """A tuple with the strings wrapped by the `Identifier`."""
334 return self._wrapped
336 @property
337 def string(self):
338 """The string wrapped by the `Identifier`.
339 """
340 if len(self._wrapped) == 1:
341 return self._wrapped[0]
342 else:
343 raise AttributeError(
344 "the Identifier wraps more than one than one string")
346 def __repr__(self):
347 return f"{self.__class__.__name__}({', '.join(map(repr, self._wrapped))})"
349 def as_string(self, context):
350 return '.'.join(ext.quote_ident(s, context) for s in self._wrapped)
353class Literal(Composable):
354 """
355 A `Composable` representing an SQL value to include in a query.
357 Usually you will want to include placeholders in the query and pass values
358 as `~cursor.execute()` arguments. If however you really really need to
359 include a literal value in the query you can use this object.
361 The string returned by `!as_string()` follows the normal :ref:`adaptation
362 rules <python-types-adaptation>` for Python objects.
364 Example::
366 >>> s1 = sql.Literal("foo")
367 >>> s2 = sql.Literal("ba'r")
368 >>> s3 = sql.Literal(42)
369 >>> print(sql.SQL(', ').join([s1, s2, s3]).as_string(conn))
370 'foo', 'ba''r', 42
372 """
373 @property
374 def wrapped(self):
375 """The object wrapped by the `!Literal`."""
376 return self._wrapped
378 def as_string(self, context):
379 # is it a connection or cursor?
380 if isinstance(context, ext.connection):
381 conn = context
382 elif isinstance(context, ext.cursor):
383 conn = context.connection
384 else:
385 raise TypeError("context must be a connection or a cursor")
387 a = ext.adapt(self._wrapped)
388 if hasattr(a, 'prepare'):
389 a.prepare(conn)
391 rv = a.getquoted()
392 if isinstance(rv, bytes):
393 rv = rv.decode(ext.encodings[conn.encoding])
395 return rv
398class Placeholder(Composable):
399 """A `Composable` representing a placeholder for query parameters.
401 If the name is specified, generate a named placeholder (e.g. ``%(name)s``),
402 otherwise generate a positional placeholder (e.g. ``%s``).
404 The object is useful to generate SQL queries with a variable number of
405 arguments.
407 Examples::
409 >>> names = ['foo', 'bar', 'baz']
411 >>> q1 = sql.SQL("insert into table ({}) values ({})").format(
412 ... sql.SQL(', ').join(map(sql.Identifier, names)),
413 ... sql.SQL(', ').join(sql.Placeholder() * len(names)))
414 >>> print(q1.as_string(conn))
415 insert into table ("foo", "bar", "baz") values (%s, %s, %s)
417 >>> q2 = sql.SQL("insert into table ({}) values ({})").format(
418 ... sql.SQL(', ').join(map(sql.Identifier, names)),
419 ... sql.SQL(', ').join(map(sql.Placeholder, names)))
420 >>> print(q2.as_string(conn))
421 insert into table ("foo", "bar", "baz") values (%(foo)s, %(bar)s, %(baz)s)
423 """
425 def __init__(self, name=None):
426 if isinstance(name, str):
427 if ')' in name:
428 raise ValueError(f"invalid name: {name!r}")
430 elif name is not None:
431 raise TypeError(f"expected string or None as name, got {name!r}")
433 super().__init__(name)
435 @property
436 def name(self):
437 """The name of the `!Placeholder`."""
438 return self._wrapped
440 def __repr__(self):
441 if self._wrapped is None:
442 return f"{self.__class__.__name__}()"
443 else:
444 return f"{self.__class__.__name__}({self._wrapped!r})"
446 def as_string(self, context):
447 if self._wrapped is not None:
448 return f"%({self._wrapped})s"
449 else:
450 return "%s"
453# Literals
454NULL = SQL("NULL")
455DEFAULT = SQL("DEFAULT")