source: trunk/docs/contents/pg/db_wrapper.rst @ 1015

Last change on this file since 1015 was 1015, checked in by cito, 2 months ago

Fix several typos in the documentation.

As suggested by Justin Pryzby on the mailing list.

File size: 37.8 KB
Line 
1The DB wrapper class
2====================
3
4.. py:currentmodule:: pg
5
6.. class:: DB
7
8The :class:`Connection` methods are wrapped in the class :class:`DB`
9which also adds convenient higher level methods for working with the
10database.  It also serves as a context manager for the connection.
11The preferred way to use this module is as follows::
12
13    import pg
14
15    with pg.DB(...) as db:  # for parameters, see below
16        for r in db.query(  # just for example
17                "SELECT foo, bar FROM foo_bar_table WHERE foo !~ bar"
18                ).dictresult():
19            print('%(foo)s %(bar)s' % r)
20
21This class can be subclassed as in this example::
22
23    import pg
24
25    class DB_ride(pg.DB):
26        """Ride database wrapper
27
28        This class encapsulates the database functions and the specific
29        methods for the ride database."""
30
31    def __init__(self):
32        """Open a database connection to the rides database"""
33        pg.DB.__init__(self, dbname='ride')
34        self.query("SET DATESTYLE TO 'ISO'")
35
36    [Add or override methods here]
37
38The following describes the methods and variables of this class.
39
40Initialization
41--------------
42The :class:`DB` class is initialized with the same arguments as the
43:func:`connect` function described above. It also initializes a few
44internal variables. The statement ``db = DB()`` will open the local
45database with the name of the user just like ``connect()`` does.
46
47You can also initialize the DB class with an existing :mod:`pg` or :mod:`pgdb`
48connection. Pass this connection as a single unnamed parameter, or as a
49single parameter named ``db``. This allows you to use all of the methods
50of the DB class with a DB-API 2 compliant connection. Note that the
51:meth:`Connection.close` and :meth:`Connection.reopen` methods are inoperative
52in this case.
53
54pkey -- return the primary key of a table
55-----------------------------------------
56
57.. method:: DB.pkey(table)
58
59    Return the primary key of a table
60
61    :param str table: name of table
62    :returns: Name of the field which is the primary key of the table
63    :rtype: str
64    :raises KeyError: the table does not have a primary key
65
66This method returns the primary key of a table.  Single primary keys are
67returned as strings unless you set the composite flag.  Composite primary
68keys are always represented as tuples.  Note that this raises a KeyError
69if the table does not have a primary key.
70
71get_databases -- get list of databases in the system
72----------------------------------------------------
73
74.. method:: DB.get_databases()
75
76    Get the list of databases in the system
77
78    :returns: all databases in the system
79    :rtype: list
80
81Although you can do this with a simple select, it is added here for
82convenience.
83
84get_relations -- get list of relations in connected database
85------------------------------------------------------------
86
87.. method:: DB.get_relations([kinds], [system])
88
89    Get the list of relations in connected database
90
91    :param str kinds: a string or sequence of type letters
92    :param bool system: whether system relations should be returned
93    :returns: all relations of the given kinds in the database
94    :rtype: list
95
96This method returns the list of relations in the connected database.  Although
97you can do this with a simple select, it is added here for convenience.  You
98can select which kinds of relations you are interested in by passing type
99letters in the `kinds` parameter.  The type letters are ``r`` = ordinary table,
100``i`` = index, ``S`` = sequence, ``v`` = view, ``c`` = composite type,
101``s`` = special, ``t`` = TOAST table.  If `kinds` is None or an empty string,
102all relations are returned (this is also the default).  If `system` is set to
103`True`, then system tables and views (temporary tables, toast tables, catalog
104vies and tables) will be returned as well, otherwise they will be ignored.
105
106get_tables -- get list of tables in connected database
107------------------------------------------------------
108
109.. method:: DB.get_tables([system])
110
111    Get the list of tables in connected database
112
113    :param bool system: whether system tables should be returned
114    :returns: all tables in connected database
115    :rtype: list
116
117This is a shortcut for ``get_relations('r', system)`` that has been added for
118convenience.
119
120get_attnames -- get the attribute names of a table
121--------------------------------------------------
122
123.. method:: DB.get_attnames(table)
124
125    Get the attribute names of a table
126
127    :param str table: name of table
128    :returns: an ordered dictionary mapping attribute names to type names
129
130Given the name of a table, digs out the set of attribute names.
131
132Returns a read-only dictionary of attribute names (the names are the keys,
133the values are the names of the attributes' types) with the column names
134in the proper order if you iterate over it.
135
136By default, only a limited number of simple types will be returned.
137You can get the registered types instead, if enabled by calling the
138:meth:`DB.use_regtypes` method.
139
140has_table_privilege -- check table privilege
141--------------------------------------------
142
143.. method:: DB.has_table_privilege(table, privilege)
144
145    Check whether current user has specified table privilege
146
147    :param str table: the name of the table
148    :param str privilege: privilege to be checked -- default is 'select'
149    :returns: whether current user has specified table privilege
150    :rtype: bool
151
152Returns True if the current user has the specified privilege for the table.
153
154.. versionadded:: 4.0
155
156get/set_parameter -- get or set  run-time parameters
157----------------------------------------------------
158
159.. method:: DB.get_parameter(parameter)
160
161    Get the value of run-time parameters
162
163    :param parameter: the run-time parameter(s) to get
164    :type param: str, tuple, list or dict
165    :returns: the current value(s) of the run-time parameter(s)
166    :rtype: str, list or dict
167    :raises TypeError: Invalid parameter type(s)
168    :raises pg.ProgrammingError: Invalid parameter name(s)
169
170If the parameter is a string, the return value will also be a string
171that is the current setting of the run-time parameter with that name.
172
173You can get several parameters at once by passing a list, set or dict.
174When passing a list of parameter names, the return value will be a
175corresponding list of parameter settings.  When passing a set of
176parameter names, a new dict will be returned, mapping these parameter
177names to their settings.  Finally, if you pass a dict as parameter,
178its values will be set to the current parameter settings corresponding
179to its keys.
180
181By passing the special name ``'all'`` as the parameter, you can get a dict
182of all existing configuration parameters.
183
184Note that you can request most of the important parameters also using
185:meth:`Connection.parameter()` which does not involve a database query,
186unlike :meth:`DB.get_parameter` and :meth:`DB.set_parameter`.
187
188.. versionadded:: 4.2
189
190.. method:: DB.set_parameter(parameter, [value], [local])
191
192    Set the value of run-time parameters
193
194    :param parameter: the run-time parameter(s) to set
195    :type param: string, tuple, list or dict
196    :param value: the value to set
197    :type param: str or None
198    :raises TypeError: Invalid parameter type(s)
199    :raises ValueError: Invalid value argument(s)
200    :raises pg.ProgrammingError: Invalid parameter name(s) or values
201
202If the parameter and the value are strings, the run-time parameter
203will be set to that value.  If no value or *None* is passed as a value,
204then the run-time parameter will be restored to its default value.
205
206You can set several parameters at once by passing a list of parameter
207names, together with a single value that all parameters should be
208set to or with a corresponding list of values.  You can also pass
209the parameters as a set if you only provide a single value.
210Finally, you can pass a dict with parameter names as keys.  In this
211case, you should not pass a value, since the values for the parameters
212will be taken from the dict.
213
214By passing the special name ``'all'`` as the parameter, you can reset
215all existing settable run-time parameters to their default values.
216
217If you set *local* to `True`, then the command takes effect for only the
218current transaction.  After :meth:`DB.commit` or :meth:`DB.rollback`,
219the session-level setting takes effect again.  Setting *local* to `True`
220will appear to have no effect if it is executed outside a transaction,
221since the transaction will end immediately.
222
223.. versionadded:: 4.2
224
225begin/commit/rollback/savepoint/release -- transaction handling
226---------------------------------------------------------------
227
228.. method:: DB.begin([mode])
229
230    Begin a transaction
231
232    :param str mode: an optional transaction mode such as 'READ ONLY'
233
234    This initiates a transaction block, that is, all following queries
235    will be executed in a single transaction until :meth:`DB.commit`
236    or :meth:`DB.rollback` is called.
237
238.. versionadded:: 4.1
239
240.. method:: DB.start()
241
242    This is the same as the :meth:`DB.begin` method.
243
244.. method:: DB.commit()
245
246    Commit a transaction
247
248    This commits the current transaction.
249
250.. method:: DB.end()
251
252    This is the same as the :meth:`DB.commit` method.
253
254.. versionadded:: 4.1
255
256.. method:: DB.rollback([name])
257
258    Roll back a transaction
259
260    :param str name: optionally, roll back to the specified savepoint
261
262    This rolls back the current transaction, discarding all its changes.
263
264.. method:: DB.abort()
265
266    This is the same as the :meth:`DB.rollback` method.
267
268.. versionadded:: 4.2
269
270.. method:: DB.savepoint(name)
271
272    Define a new savepoint
273
274    :param str name: the name to give to the new savepoint
275
276    This establishes a new savepoint within the current transaction.
277
278.. versionadded:: 4.1
279
280.. method:: DB.release(name)
281
282    Destroy a savepoint
283
284    :param str name: the name of the savepoint to destroy
285
286    This destroys a savepoint previously defined in the current transaction.
287
288.. versionadded:: 4.1
289
290get -- get a row from a database table or view
291----------------------------------------------
292
293.. method:: DB.get(table, row, [keyname])
294
295    Get a row from a database table or view
296
297    :param str table: name of table or view
298    :param row: either a dictionary or the value to be looked up
299    :param str keyname: name of field to use as key (optional)
300    :returns: A dictionary - the keys are the attribute names,
301      the values are the row values.
302    :raises pg.ProgrammingError: table has no primary key or missing privilege
303    :raises KeyError: missing key value for the row
304
305This method is the basic mechanism to get a single row.  It assumes
306that the *keyname* specifies a unique row.  It must be the name of a
307single column or a tuple of column names.  If *keyname* is not specified,
308then the primary key for the table is used.
309
310If *row* is a dictionary, then the value for the key is taken from it.
311Otherwise, the row must be a single value or a tuple of values
312corresponding to the passed *keyname* or primary key.  The fetched row
313from the table will be returned as a new dictionary or used to replace
314the existing values if the row was passed as a dictionary.
315
316The OID is also put into the dictionary if the table has one, but
317in order to allow the caller to work with multiple tables, it is
318munged as ``oid(table)`` using the actual name of the table.
319
320Note that since PyGreSQL 5.0 this will return the value of an array
321type column as a Python list by default.
322
323insert -- insert a row into a database table
324--------------------------------------------
325
326.. method:: DB.insert(table, [row], [col=val, ...])
327
328    Insert a row into a database table
329
330    :param str table: name of table
331    :param dict row: optional dictionary of values
332    :param col: optional keyword arguments for updating the dictionary
333    :returns: the inserted values in the database
334    :rtype: dict
335    :raises pg.ProgrammingError: missing privilege or conflict
336
337This method inserts a row into a table.  If the optional dictionary is
338not supplied then the required values must be included as keyword/value
339pairs.  If a dictionary is supplied then any keywords provided will be
340added to or replace the entry in the dictionary.
341
342The dictionary is then reloaded with the values actually inserted in order
343to pick up values modified by rules, triggers, etc.
344
345Note that since PyGreSQL 5.0 it is possible to insert a value for an
346array type column by passing it as a Python list.
347
348update -- update a row in a database table
349------------------------------------------
350
351.. method:: DB.update(table, [row], [col=val, ...])
352
353    Update a row in a database table
354
355    :param str table: name of table
356    :param dict row: optional dictionary of values
357    :param col: optional keyword arguments for updating the dictionary
358    :returns: the new row in the database
359    :rtype: dict
360    :raises pg.ProgrammingError: table has no primary key or missing privilege
361    :raises KeyError: missing key value for the row
362
363Similar to insert, but updates an existing row.  The update is based on
364the primary key of the table or the OID value as munged by :meth:`DB.get`
365or passed as keyword.  The OID will take precedence if provided, so that it
366is possible to update the primary key itself.
367
368The dictionary is then modified to reflect any changes caused by the
369update due to triggers, rules, default values, etc.
370
371Like insert, the dictionary is optional and updates will be performed
372on the fields in the keywords.  There must be an OID or primary key either
373specified using the ``'oid'`` keyword or in the dictionary, in which case the
374OID must be munged.
375
376
377upsert -- insert a row with conflict resolution
378-----------------------------------------------
379
380.. method:: DB.upsert(table, [row], [col=val, ...])
381
382    Insert a row into a database table with conflict resolution
383
384    :param str table: name of table
385    :param dict row: optional dictionary of values
386    :param col: optional keyword arguments for specifying the update
387    :returns: the new row in the database
388    :rtype: dict
389    :raises pg.ProgrammingError: table has no primary key or missing privilege
390
391This method inserts a row into a table, but instead of raising a
392ProgrammingError exception in case of violating a constraint or unique index,
393an update will be executed instead.  This will be performed as a
394single atomic operation on the database, so race conditions can be avoided.
395
396Like the insert method, the first parameter is the name of the table and the
397second parameter can be used to pass the values to be inserted as a dictionary.
398
399Unlike the insert und update statement, keyword parameters are not used to
400modify the dictionary, but to specify which columns shall be updated in case
401of a conflict, and in which way:
402
403A value of `False` or `None` means the column shall not be updated,
404a value of `True` means the column shall be updated with the value that
405has been proposed for insertion, i.e. has been passed as value in the
406dictionary.  Columns that are not specified by keywords but appear as keys
407in the dictionary are also updated like in the case keywords had been passed
408with the value `True`.
409
410So if in the case of a conflict you want to update every column that has been
411passed in the dictionary `d` , you would call ``upsert(table, d)``.  If you
412don't want to do anything in case of a conflict, i.e. leave the existing row
413as it is, call ``upsert(table, d, **dict.fromkeys(d))``.
414
415If you need more fine-grained control of what gets updated, you can also pass
416strings in the keyword parameters.  These strings will be used as SQL
417expressions for the update columns.  In these expressions you can refer
418to the value that already exists in the table by writing the table prefix
419``included.`` before the column name, and you can refer to the value that
420has been proposed for insertion by writing ``excluded.`` as table prefix.
421
422The dictionary is modified in any case to reflect the values in the database
423after the operation has completed.
424
425.. note::
426
427    The method uses the PostgreSQL "upsert" feature which is only available
428    since PostgreSQL 9.5. With older PostgreSQL versions, you will get a
429    ProgrammingError if you use this method.
430
431.. versionadded:: 5.0
432
433query -- execute a SQL command string
434-------------------------------------
435
436.. method:: DB.query(command, [arg1, [arg2, ...]])
437
438    Execute a SQL command string
439
440    :param str command: SQL command
441    :param arg*: optional positional arguments
442    :returns: result values
443    :rtype: :class:`Query`, None
444    :raises TypeError: bad argument type, or too many arguments
445    :raises TypeError: invalid connection
446    :raises ValueError: empty SQL query or lost connection
447    :raises pg.ProgrammingError: error in query
448    :raises pg.InternalError: error during query processing
449
450Similar to the :class:`Connection` function with the same name, except that
451positional arguments can be passed either as a single list or tuple, or as
452individual positional arguments.  These arguments will then be used as
453parameter values of parameterized queries.
454
455Example::
456
457    name = input("Name? ")
458    phone = input("Phone? ")
459    rows = db.query("update employees set phone=$2 where name=$1",
460        name, phone).getresult()[0][0]
461    # or
462    rows = db.query("update employees set phone=$2 where name=$1",
463        (name, phone)).getresult()[0][0]
464
465query_formatted -- execute a formatted SQL command string
466---------------------------------------------------------
467
468.. method:: DB.query_formatted(command, [parameters], [types], [inline])
469
470    Execute a formatted SQL command string
471
472    :param str command: SQL command
473    :param parameters: the values of the parameters for the SQL command
474    :type parameters: tuple, list or dict
475    :param types: optionally, the types of the parameters
476    :type types: tuple, list or dict
477    :param bool inline: whether the parameters should be passed in the SQL
478    :rtype: :class:`Query`, None
479    :raises TypeError: bad argument type, or too many arguments
480    :raises TypeError: invalid connection
481    :raises ValueError: empty SQL query or lost connection
482    :raises pg.ProgrammingError: error in query
483    :raises pg.InternalError: error during query processing
484
485Similar to :meth:`DB.query`, but using Python format placeholders of the form
486``%s`` or ``%(names)s`` instead of PostgreSQL placeholders of the form ``$1``.
487The parameters must be passed as a tuple, list or dict.  You can also pass a
488corresponding tuple, list or dict of database types in order to format the
489parameters properly in case there is ambiguity.
490
491If you set *inline* to True, the parameters will be sent to the database
492embedded in the SQL command, otherwise they will be sent separately.
493
494If you set *inline* to True or don't pass any parameters, the command string
495can also include multiple SQL commands (separated by semicolons). You will
496only get the result for the last command in this case.
497
498Note that the adaptation and conversion of the parameters causes a certain
499performance overhead. Depending on the type of values, the overhead can be
500smaller for *inline* queries or if you pass the types of the parameters,
501so that they don't need to be guessed from the values. For best performance,
502we recommend using a raw :meth:`DB.query` or :meth:`DB.query_prepared` if you
503are executing many of the same operations with different parameters.
504
505
506Example::
507
508    name = input("Name? ")
509    phone = input("Phone? ")
510    rows = db.query_formatted(
511        "update employees set phone=%s where name=%s",
512        (phone, name)).getresult()[0][0]
513    # or
514    rows = db.query_formatted(
515        "update employees set phone=%(phone)s where name=%(name)s",
516        dict(name=name, phone=phone)).getresult()[0][0]
517
518query_prepared -- execute a prepared statement
519----------------------------------------------
520
521.. method:: DB.query_prepared(name, [arg1, [arg2, ...]])
522
523    Execute a prepared statement
524
525    :param str name: name of the prepared statement
526    :param arg*: optional positional arguments
527    :returns: result values
528    :rtype: :class:`Query`, None
529    :raises TypeError: bad argument type, or too many arguments
530    :raises TypeError: invalid connection
531    :raises ValueError: empty SQL query or lost connection
532    :raises pg.ProgrammingError: error in query
533    :raises pg.InternalError: error during query processing
534    :raises pg.OperationalError: prepared statement does not exist
535
536This methods works like the :meth:`DB.query` method, except that instead of
537passing the SQL command, you pass the name of a prepared statement
538created previously using the :meth:`DB.prepare` method.
539
540Passing an empty string or *None* as the name will execute the unnamed
541statement (see warning about the limited lifetime of the unnamed statement
542in :meth:`DB.prepare`).
543
544The functionality of this method is equivalent to that of the SQL ``EXECUTE``
545command.  Note that calling EXECUTE would require parameters to be sent
546inline, and be properly sanitized (escaped, quoted).
547
548.. versionadded:: 5.1
549
550prepare -- create a prepared statement
551--------------------------------------
552
553.. method:: DB.prepare(name, command)
554
555    Create a prepared statement
556
557    :param str command: SQL command
558    :param str name: name of the prepared statement
559    :rtype: None
560    :raises TypeError: bad argument types, or wrong number of arguments
561    :raises TypeError: invalid connection
562    :raises pg.ProgrammingError: error in query or duplicate query
563
564This method creates a prepared statement with the specified name for later
565execution of the given command with the :meth:`DB.query_prepared` method.
566
567If the name is empty or *None*, the unnamed prepared statement is used,
568in which case any pre-existing unnamed statement is replaced.
569
570Otherwise, if a prepared statement with the specified name is already defined
571in the current database session, a :exc:`pg.ProgrammingError` is raised.
572
573The SQL command may optionally contain positional parameters of the form
574``$1``, ``$2``, etc instead of literal data.  The corresponding values
575must then be passed to the :meth:`Connection.query_prepared` method
576as positional arguments.
577
578The functionality of this method is equivalent to that of the SQL ``PREPARE``
579command.
580
581Example::
582
583    db.prepare('change phone',
584        "update employees set phone=$2 where ein=$1")
585    while True:
586        ein = input("Employee ID? ")
587        if not ein:
588            break
589        phone = input("Phone? ")
590        db.query_prepared('change phone', ein, phone)
591
592.. note::
593
594     We recommend always using named queries, since unnamed queries have a
595     limited lifetime and can be automatically replaced or destroyed by
596     various operations on the database.
597
598.. versionadded:: 5.1
599
600describe_prepared -- describe a prepared statement
601--------------------------------------------------
602
603.. method:: DB.describe_prepared([name])
604
605    Describe a prepared statement
606
607    :param str name: name of the prepared statement
608    :rtype: :class:`Query`
609    :raises TypeError: bad argument type, or too many arguments
610    :raises TypeError: invalid connection
611    :raises pg.OperationalError: prepared statement does not exist
612
613This method returns a :class:`Query` object describing the prepared
614statement with the given name.  You can also pass an empty name in order
615to describe the unnamed statement.  Information on the fields of the
616corresponding query can be obtained through the :meth:`Query.listfields`,
617:meth:`Query.fieldname` and :meth:`Query.fieldnum` methods.
618
619.. versionadded:: 5.1
620
621delete_prepared -- delete a prepared statement
622----------------------------------------------
623
624.. method:: DB.delete_prepared([name])
625
626    Delete a prepared statement
627
628    :param str name: name of the prepared statement
629    :rtype: None
630    :raises TypeError: bad argument type, or too many arguments
631    :raises TypeError: invalid connection
632    :raises pg.OperationalError: prepared statement does not exist
633
634This method deallocates a previously prepared SQL statement with the given
635name, or deallocates all prepared statements if you do not specify a name.
636Note that prepared statements are always deallocated automatically when the
637current session ends.
638
639.. versionadded:: 5.1
640
641clear -- clear row values in memory
642-----------------------------------
643
644.. method:: DB.clear(table, [row])
645
646    Clear row values in memory
647
648    :param str table: name of table
649    :param dict row: optional dictionary of values
650    :returns: an empty row
651    :rtype: dict
652
653This method clears all the attributes to values determined by the types.
654Numeric types are set to 0, Booleans are set to *False*, and everything
655else is set to the empty string.  If the row argument is present, it is
656used as the row dictionary and any entries matching attribute names are
657cleared with everything else left unchanged.
658
659If the dictionary is not supplied a new one is created.
660
661delete -- delete a row from a database table
662--------------------------------------------
663
664.. method:: DB.delete(table, [row], [col=val, ...])
665
666    Delete a row from a database table
667
668    :param str table: name of table
669    :param dict d: optional dictionary of values
670    :param col: optional keyword arguments for updating the dictionary
671    :rtype: None
672    :raises pg.ProgrammingError: table has no primary key,
673        row is still referenced or missing privilege
674    :raises KeyError: missing key value for the row
675
676This method deletes the row from a table.  It deletes based on the
677primary key of the table or the OID value as munged by :meth:`DB.get`
678or passed as keyword.  The OID will take precedence if provided.
679
680The return value is the number of deleted rows (i.e. 0 if the row did not
681exist and 1 if the row was deleted).
682
683Note that if the row cannot be deleted because e.g. it is still referenced
684by another table, this method will raise a ProgrammingError.
685
686truncate -- quickly empty database tables
687-----------------------------------------
688
689.. method:: DB.truncate(table, [restart], [cascade], [only])
690
691    Empty a table or set of tables
692
693    :param table: the name of the table(s)
694    :type table: str, list or set
695    :param bool restart: whether table sequences should be restarted
696    :param bool cascade: whether referenced tables should also be truncated
697    :param only: whether only parent tables should be truncated
698    :type only: bool or list
699
700This method quickly removes all rows from the given table or set
701of tables.  It has the same effect as an unqualified DELETE on each
702table, but since it does not actually scan the tables it is faster.
703Furthermore, it reclaims disk space immediately, rather than requiring
704a subsequent VACUUM operation. This is most useful on large tables.
705
706If *restart* is set to `True`, sequences owned by columns of the truncated
707table(s) are automatically restarted.  If *cascade* is set to `True`, it
708also truncates all tables that have foreign-key references to any of
709the named tables.  If the parameter *only* is not set to `True`, all the
710descendant tables (if any) will also be truncated. Optionally, a ``*``
711can be specified after the table name to explicitly indicate that
712descendant tables are included.  If the parameter *table* is a list,
713the parameter *only* can also be a list of corresponding boolean values.
714
715.. versionadded:: 4.2
716
717get_as_list/dict -- read a table as a list or dictionary
718--------------------------------------------------------
719
720.. method:: DB.get_as_list(table, [what], [where], [order], [limit], [offset], [scalar])
721
722    Get a table as a list
723
724    :param str table: the name of the table (the FROM clause)
725    :param what: column(s) to be returned (the SELECT clause)
726    :type what: str, list, tuple or None
727    :param where: conditions(s) to be fulfilled (the WHERE clause)
728    :type where: str, list, tuple or None
729    :param order: column(s) to sort by (the ORDER BY clause)
730    :type order: str, list, tuple, False or None
731    :param int limit: maximum number of rows returned (the LIMIT clause)
732    :param int offset: number of rows to be skipped (the OFFSET clause)
733    :param bool scalar: whether only the first column shall be returned
734    :returns: the content of the table as a list
735    :rtype: list
736    :raises TypeError: the table name has not been specified
737
738This gets a convenient representation of the table as a list of named tuples
739in Python.  You only need to pass the name of the table (or any other SQL
740expression returning rows).  Note that by default this will return the full
741content of the table which can be huge and overflow your memory.  However, you
742can control the amount of data returned using the other optional parameters.
743
744The parameter *what* can restrict the query to only return a subset of the
745table columns.  The parameter *where* can restrict the query to only return a
746subset of the table rows.  The specified SQL expressions all need to be
747fulfilled for a row to get into the result.  The parameter *order* specifies
748the ordering of the rows.  If no ordering is specified, the result will be
749ordered by the primary key(s) or all columns if no primary key exists.
750You can set *order* to *False* if you don't care about the ordering.
751The parameters *limit* and *offset* specify the maximum number of rows
752returned and a number of rows skipped over.
753
754If you set the *scalar* option to *True*, then instead of the named tuples
755you will get the first items of these tuples.  This is useful if the result
756has only one column anyway.
757
758.. versionadded:: 5.0
759
760.. method:: DB.get_as_dict(table, [keyname], [what], [where], [order], [limit], [offset], [scalar])
761
762    Get a table as a dictionary
763
764    :param str table: the name of the table (the FROM clause)
765    :param keyname: column(s) to be used as key(s) of the dictionary
766    :type keyname: str, list, tuple or None
767    :param what: column(s) to be returned (the SELECT clause)
768    :type what: str, list, tuple or None
769    :param where: conditions(s) to be fulfilled (the WHERE clause)
770    :type where: str, list, tuple or None
771    :param order: column(s) to sort by (the ORDER BY clause)
772    :type order: str, list, tuple, False or None
773    :param int limit: maximum number of rows returned (the LIMIT clause)
774    :param int offset: number of rows to be skipped (the OFFSET clause)
775    :param bool scalar: whether only the first column shall be returned
776    :returns: the content of the table as a list
777    :rtype: dict or OrderedDict
778    :raises TypeError: the table name has not been specified
779    :raises KeyError: keyname(s) are invalid or not part of the result
780    :raises pg.ProgrammingError: no keyname(s) and table has no primary key
781
782This method is similar to :meth:`DB.get_as_list`, but returns the table as
783a Python dict instead of a Python list, which can be even more convenient.
784The primary key column(s) of the table will be used as the keys of the
785dictionary, while the other column(s) will be the corresponding values.
786The keys will be named tuples if the table has a composite primary key.
787The rows will be also named tuples unless the *scalar* option has been set
788to *True*.  With the optional parameter *keyname* you can specify a different
789set of columns to be used as the keys of the dictionary.
790
791If the Python version supports it, the dictionary will be an *OrderedDict*
792using the order specified with the *order* parameter or the key column(s)
793if not specified.  You can set *order* to *False* if you don't care about the
794ordering.  In this case the returned dictionary will be an ordinary one.
795
796.. versionadded:: 5.0
797
798escape_literal/identifier/string/bytea -- escape for SQL
799--------------------------------------------------------
800
801The following methods escape text or binary strings so that they can be
802inserted directly into an SQL command.  Except for :meth:`DB.escape_byte`,
803you don't need to call these methods for the strings passed as parameters
804to :meth:`DB.query`.  You also don't need to call any of these methods
805when storing data using :meth:`DB.insert` and similar.
806
807.. method:: DB.escape_literal(string)
808
809    Escape a string for use within SQL as a literal constant
810
811    :param str string: the string that is to be escaped
812    :returns: the escaped string
813    :rtype: str
814
815This method escapes a string for use within an SQL command. This is useful
816when inserting data values as literal constants in SQL commands. Certain
817characters (such as quotes and backslashes) must be escaped to prevent them
818from being interpreted specially by the SQL parser.
819
820.. versionadded:: 4.1
821
822.. method:: DB.escape_identifier(string)
823
824    Escape a string for use within SQL as an identifier
825
826    :param str string: the string that is to be escaped
827    :returns: the escaped string
828    :rtype: str
829
830This method escapes a string for use as an SQL identifier, such as a table,
831column, or function name. This is useful when a user-supplied identifier
832might contain special characters that would otherwise be misinterpreted
833by the SQL parser, or when the identifier might contain upper case characters
834whose case should be preserved.
835
836.. versionadded:: 4.1
837
838.. method:: DB.escape_string(string)
839
840    Escape a string for use within SQL
841
842    :param str string: the string that is to be escaped
843    :returns: the escaped string
844    :rtype: str
845
846Similar to the module function :func:`pg.escape_string` with the same name,
847but the behavior of this method is adjusted depending on the connection
848properties (such as character encoding).
849
850.. method:: DB.escape_bytea(datastring)
851
852    Escape binary data for use within SQL as type ``bytea``
853
854    :param str datastring: string containing the binary data that is to be escaped
855    :returns: the escaped string
856    :rtype: str
857
858Similar to the module function :func:`pg.escape_bytea` with the same name,
859but the behavior of this method is adjusted depending on the connection
860properties (in particular, whether standard-conforming strings are enabled).
861
862unescape_bytea -- unescape data retrieved from the database
863-----------------------------------------------------------
864
865.. method:: DB.unescape_bytea(string)
866
867    Unescape ``bytea`` data that has been retrieved as text
868
869    :param datastring: the ``bytea`` data string that has been retrieved as text
870    :returns: byte string containing the binary data
871    :rtype: bytes
872
873Converts an escaped string representation of binary data stored as ``bytea``
874into the raw byte string representing the binary data  -- this is the reverse
875of :meth:`DB.escape_bytea`.  Since the :class:`Query` results will already
876return unescaped byte strings, you normally don't have to use this method.
877
878encode/decode_json -- encode and decode JSON data
879-------------------------------------------------
880
881The following methods can be used to encode end decode data in
882`JSON <http://www.json.org/>`_ format.
883
884.. method:: DB.encode_json(obj)
885
886    Encode a Python object for use within SQL as type ``json`` or ``jsonb``
887
888    :param obj: Python object that shall be encoded to JSON format
889    :type obj: dict, list or None
890    :returns: string representation of the Python object in JSON format
891    :rtype: str
892
893This method serializes a Python object into a JSON formatted string that can
894be used within SQL.  You don't need to use this method on the data stored
895with :meth:`DB.insert` and similar, only if you store the data directly as
896part of an SQL command or parameter with :meth:`DB.query`.  This is the same
897as the :func:`json.dumps` function from the standard library.
898
899.. versionadded:: 5.0
900
901.. method:: DB.decode_json(string)
902
903    Decode ``json`` or ``jsonb`` data that has been retrieved as text
904
905    :param string: JSON formatted string shall be decoded into a Python object
906    :type string: str
907    :returns: Python object representing the JSON formatted string
908    :rtype: dict, list or None
909
910This method deserializes a JSON formatted string retrieved as text from the
911database to a Python object.  You normally don't need to use this method as
912JSON data is automatically decoded by PyGreSQL.  If you don't want the data
913to be decoded, then you can cast ``json`` or ``jsonb`` columns to ``text``
914in PostgreSQL or you can set the decoding function to *None* or a different
915function using :func:`pg.set_jsondecode`.  By default this is the same as
916the :func:`json.loads` function from the standard library.
917
918.. versionadded:: 5.0
919
920use_regtypes -- choose usage of registered type names
921-----------------------------------------------------
922
923.. method:: DB.use_regtypes([regtypes])
924
925    Determine whether registered type names shall be used
926
927    :param bool regtypes: if passed, set whether registered type names shall be used
928    :returns: whether registered type names are used
929
930The :meth:`DB.get_attnames` method can return either simplified "classic"
931type names (the default) or more fine-grained "registered" type names.
932Which kind of type names is used can be changed by calling
933:meth:`DB.get_regtypes`. If you pass a boolean, it sets whether registered
934type names shall be used. The method can also be used to check through its
935return value whether registered type names are currently used.
936
937.. versionadded:: 4.1
938
939notification_handler -- create a notification handler
940-----------------------------------------------------
941
942.. class:: DB.notification_handler(event, callback, [arg_dict], [timeout], [stop_event])
943
944    Create a notification handler instance
945
946    :param str event: the name of an event to listen for
947    :param callback: a callback function
948    :param dict arg_dict: an optional dictionary for passing arguments
949    :param timeout: the time-out when waiting for notifications
950    :type timeout: int, float or None
951    :param str stop_event: an optional different name to be used as stop event
952
953This method creates a :class:`pg.NotificationHandler` object using the
954:class:`DB` connection as explained under :doc:`notification`.
955
956.. versionadded:: 4.1.1
957
958Attributes of the DB wrapper class
959----------------------------------
960
961.. attribute:: DB.db
962
963    The wrapped :class:`Connection` object
964
965You normally don't need this, since all of the members can be accessed
966from the :class:`DB` wrapper class as well.
967
968.. attribute:: DB.dbname
969
970    The name of the database that the connection is using
971
972.. attribute:: DB.dbtypes
973
974    A dictionary with the various type names for the PostgreSQL types
975
976This can be used for getting more information on the PostgreSQL database
977types or changing the typecast functions used for the connection.  See the
978description of the :class:`DbTypes` class for details.
979
980.. versionadded:: 5.0
981
982.. attribute:: DB.adapter
983
984    A class with some helper functions for adapting parameters
985
986This can be used for building queries with parameters.  You normally will
987not need this, as you can use the :class:`DB.query_formatted` method.
988
989.. versionadded:: 5.0
Note: See TracBrowser for help on using the repository browser.