diff options
author | Daniele Varrazzo <daniele.varrazzo@gmail.com> | 2017-01-01 08:12:05 +0100 |
---|---|---|
committer | Daniele Varrazzo <daniele.varrazzo@gmail.com> | 2017-01-01 08:12:05 +0100 |
commit | 4a55b8018aab468d80dd926f46ee4c131e64654b (patch) | |
tree | 24de94fbfc3c2a4fa275d9026992f2aefb870a8f /lib/sql.py | |
parent | 41b9bfe4019e4feec7c9847ace42c458c2db320b (diff) | |
download | psycopg2-4a55b8018aab468d80dd926f46ee4c131e64654b.tar.gz |
Adding sql module documentation
Diffstat (limited to 'lib/sql.py')
-rw-r--r-- | lib/sql.py | 171 |
1 files changed, 147 insertions, 24 deletions
@@ -31,8 +31,29 @@ from psycopg2 import extensions as ext class Composable(object): - """Base class for objects that can be used to compose an SQL string.""" + """ + Abstract base class for objects that can be used to compose an SQL string. + + Composables can be passed directly to `~cursor.execute()` and + `~cursor.executemany()`. + + Composables can be joined using the ``+`` operator: the result will be + a `Composed` instance containing the objects joined. The operator ``*`` is + also supported with an integer argument: the result is a `!Composed` + instance containing the left argument repeated as many times as requested. + + .. automethod:: as_string + """ def as_string(self, conn_or_curs): + """ + Return the string value of the object. + + The object is evaluated in the context of the *conn_or_curs* argument. + + The function is automatically invoked by `~cursor.execute()` and + `~cursor.executemany()` if a `!Composable` is passed instead of the + query string. + """ raise NotImplementedError def __add__(self, other): @@ -43,8 +64,26 @@ class Composable(object): else: return NotImplemented + def __mul__(self, n): + return Composed([self] * n) + class Composed(Composable): + """ + A `Composable` object obtained concatenating a sequence of `Composable`. + + The object is usually created using `compose()` and the `Composable` + operators. However it is possible to create a `!Composed` directly + specifying a sequence of `Composable` as arguments. + + Example:: + + >>> sql.Composed([sql.SQL("insert into "), sql.Identifier("table")]) \\ + ... .as_string(conn) + 'insert into "table"' + + .. automethod:: join + """ def __init__(self, seq): self._seq = [] for i in seq: @@ -70,10 +109,20 @@ class Composed(Composable): else: return NotImplemented - def __mul__(self, n): - return Composed(self._seq * n) - def join(self, joiner): + """ + Return a new `!Composed` interposing the *joiner* with the `!Composed` items. + + The *joiner* must be a `SQL` or a string which will be interpreted as + an `SQL`. + + Example:: + + >>> fields = sql.Identifier('foo') + sql.Identifier('bar') # a Composed + >>> fields.join(', ').as_string(conn) + '"foo", "bar"' + + """ if isinstance(joiner, basestring): joiner = SQL(joiner) elif not isinstance(joiner, SQL): @@ -93,10 +142,15 @@ class Composed(Composable): class SQL(Composable): - def __init__(self, wrapped): - if not isinstance(wrapped, basestring): + """ + A `Composable` representing a snippet of SQL string to be included verbatim. + + .. automethod:: join + """ + def __init__(self, string): + if not isinstance(string, basestring): raise TypeError("SQL values must be strings") - self._wrapped = wrapped + self._wrapped = string def __repr__(self): return "sql.SQL(%r)" % (self._wrapped,) @@ -104,10 +158,21 @@ class SQL(Composable): def as_string(self, conn_or_curs): return self._wrapped - def __mul__(self, n): - return Composed([self] * n) - def join(self, seq): + """ + Join a sequence of `Composable` or a `Composed` and return a `!Composed`. + + Use the object value to separate the *seq* elements. + + Example:: + + >>> snip - sql.SQL(', ').join(map(sql.Identifier, ['foo', 'bar', 'baz'])) + >>> snip.as_string(conn) + '"foo", "bar", "baz"' + """ + if isinstance(seq, Composed): + seq = seq._seq + rv = [] it = iter(seq) try: @@ -123,15 +188,21 @@ class SQL(Composable): class Identifier(Composable): - def __init__(self, wrapped): - if not isinstance(wrapped, basestring): + """ + A `Composable` representing an SQL identifer. + + Identifiers usually represent names of database objects, such as tables + or fields. They follow `different rules`__ than SQL string literals for + escaping (e.g. they use double quotes). + + .. __: https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html# \ + SQL-SYNTAX-IDENTIFIERS + """ + def __init__(self, string): + if not isinstance(string, basestring): raise TypeError("SQL identifiers must be strings") - self._wrapped = wrapped - - @property - def wrapped(self): - return self._wrapped + self._wrapped = string def __repr__(self): return "sql.Identifier(%r)" % (self._wrapped,) @@ -141,6 +212,11 @@ class Identifier(Composable): class Literal(Composable): + """ + Represent an SQL value to be included in a query. + + The object follows the normal :ref:`adaptation rules <python-types-adaptation>` + """ def __init__(self, wrapped): self._wrapped = wrapped @@ -166,11 +242,31 @@ class Literal(Composable): return rv - def __mul__(self, n): - return Composed([self] * n) - class Placeholder(Composable): + """A `Composable` representing a placeholder for query parameters. + + If the name is specified, generate a named placeholder (e.g. ``%(name)s``), + otherwise generate a positional placeholder (e.g. ``%s``). + + The object is useful to generate SQL queries with a variable number of + arguments. + + Examples:: + + >>> sql.compose("insert into table (%s) values (%s)", [ + ... sql.SQL(', ').join(map(sql.Identifier, names)), + ... sql.SQL(', ').join(sql.Placeholder() * 3) + ... ]).as_string(conn) + 'insert into table ("foo", "bar", "baz") values (%s, %s, %s)' + + >>> sql.compose("insert into table (%s) values (%s)", [ + ... sql.SQL(', ').join(map(sql.Identifier, names)), + ... sql.SQL(', ').join(map(sql.Placeholder, names)) + ... ]).as_string(conn) + 'insert into table ("foo", "bar", "baz") values (%(foo)s, %(bar)s, %(baz)s)' + """ + def __init__(self, name=None): if isinstance(name, basestring): if ')' in name: @@ -185,9 +281,6 @@ class Placeholder(Composable): return "sql.Placeholder(%r)" % ( self._name if self._name is not None else '',) - def __mul__(self, n): - return Composed([self] * n) - def as_string(self, conn_or_curs): if self._name is not None: return "%%(%s)s" % self._name @@ -204,7 +297,37 @@ re_compose = re.compile(""" """, re.VERBOSE) -def compose(sql, args=()): +def compose(sql, args=None): + """ + Merge an SQL string with some variable parts. + + The *sql* string can contain placeholders such as `%s` or `%(name)s`. + If the string must contain a literal ``%`` symbol use ``%%``. Note that, + unlike `~cursor.execute()`, the replacement ``%%`` |=>| ``%`` is *always* + performed, even if there is no argument. + + .. |=>| unicode:: 0x21D2 .. double right arrow + + *args* must be a sequence or mapping (according to the placeholder style) + of `Composable` instances. + + The value returned is a `Composed` instance obtained replacing the + arguments to the query placeholders. + + Example:: + + >>> query = sql.compose( + ... "select %s from %s", [ + ... sql.SQL(', ').join( + ... [sql.Identifier('foo'), sql.Identifier('bar')]), + ... sql.Identifier('table')]) + >>> query.as_string(conn) + select "foo", "bar" from "table"' + + """ + if args is None: + args = () + phs = list(re_compose.finditer(sql)) # check placeholders consistent |