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

Last change on this file since 958 was 958, checked in by cito, 10 months ago

Add important note regarding unnamed prepared statements

File size: 37.1 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 regular types after enabling this 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
186like it is the case for :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. All changes made by the
249    transaction become visible to others and are guaranteed to be
250    durable if a crash occurs.
251
252.. method:: DB.end()
253
254    This is the same as the :meth:`DB.commit` method.
255
256.. versionadded:: 4.1
257
258.. method:: DB.rollback([name])
259
260    Roll back a transaction
261
262    :param str name: optionally, roll back to the specified savepoint
263
264    This rolls back the current transaction and causes all the updates
265    made by the transaction to be discarded.
266
267.. method:: DB.abort()
268
269    This is the same as the :meth:`DB.rollback` method.
270
271.. versionadded:: 4.2
272
273.. method:: DB.savepoint(name)
274
275    Define a new savepoint
276
277    :param str name: the name to give to the new savepoint
278
279    This establishes a new savepoint within the current transaction.
280
281.. versionadded:: 4.1
282
283.. method:: DB.release(name)
284
285    Destroy a savepoint
286
287    :param str name: the name of the savepoint to destroy
288
289    This destroys a savepoint previously defined in the current transaction.
290
291.. versionadded:: 4.1
292
293get -- get a row from a database table or view
294----------------------------------------------
295
296.. method:: DB.get(table, row, [keyname])
297
298    Get a row from a database table or view
299
300    :param str table: name of table or view
301    :param row: either a dictionary or the value to be looked up
302    :param str keyname: name of field to use as key (optional)
303    :returns: A dictionary - the keys are the attribute names,
304      the values are the row values.
305    :raises pg.ProgrammingError: table has no primary key or missing privilege
306    :raises KeyError: missing key value for the row
307
308This method is the basic mechanism to get a single row.  It assumes
309that the *keyname* specifies a unique row.  It must be the name of a
310single column or a tuple of column names.  If *keyname* is not specified,
311then the primary key for the table is used.
312
313If *row* is a dictionary, then the value for the key is taken from it.
314Otherwise, the row must be a single value or a tuple of values
315corresponding to the passed *keyname* or primary key.  The fetched row
316from the table will be returned as a new dictionary or used to replace
317the existing values when row was passed as aa dictionary.
318
319The OID is also put into the dictionary if the table has one, but
320in order to allow the caller to work with multiple tables, it is
321munged as ``oid(table)`` using the actual name of the table.
322
323Note that since PyGreSQL 5.0 this will return the value of an array
324type column as a Python list by default.
325
326insert -- insert a row into a database table
327--------------------------------------------
328
329.. method:: DB.insert(table, [row], [col=val, ...])
330
331    Insert a row into a database table
332
333    :param str table: name of table
334    :param dict row: optional dictionary of values
335    :param col: optional keyword arguments for updating the dictionary
336    :returns: the inserted values in the database
337    :rtype: dict
338    :raises pg.ProgrammingError: missing privilege or conflict
339
340This method inserts a row into a table.  If the optional dictionary is
341not supplied then the required values must be included as keyword/value
342pairs.  If a dictionary is supplied then any keywords provided will be
343added to or replace the entry in the dictionary.
344
345The dictionary is then reloaded with the values actually inserted in order
346to pick up values modified by rules, triggers, etc.
347
348Note that since PyGreSQL 5.0 it is possible to insert a value for an
349array type column by passing it as Python list.
350
351update -- update a row in a database table
352------------------------------------------
353
354.. method:: DB.update(table, [row], [col=val, ...])
355
356    Update a row in a database table
357
358    :param str table: name of table
359    :param dict row: optional dictionary of values
360    :param col: optional keyword arguments for updating the dictionary
361    :returns: the new row in the database
362    :rtype: dict
363    :raises pg.ProgrammingError: table has no primary key or missing privilege
364    :raises KeyError: missing key value for the row
365
366Similar to insert, but updates an existing row.  The update is based on
367the primary key of the table or the OID value as munged by :meth:`DB.get`
368or passed as keyword.  The OID will take precedence if provided, so that it
369is possible to update the primary key itself.
370
371The dictionary is then modified to reflect any changes caused by the
372update due to triggers, rules, default values, etc.
373
374Like insert, the dictionary is optional and updates will be performed
375on the fields in the keywords.  There must be an OID or primary key
376either in the dictionary where the OID must be munged, or in the keywords
377where it can be simply the string ``'oid'``.
378
379upsert -- insert a row with conflict resolution
380-----------------------------------------------
381
382.. method:: DB.upsert(table, [row], [col=val, ...])
383
384    Insert a row into a database table with conflict resolution
385
386    :param str table: name of table
387    :param dict row: optional dictionary of values
388    :param col: optional keyword arguments for specifying the update
389    :returns: the new row in the database
390    :rtype: dict
391    :raises pg.ProgrammingError: table has no primary key or missing privilege
392
393This method inserts a row into a table, but instead of raising a
394ProgrammingError exception in case a row with the same primary key already
395exists, an update will be executed instead.  This will be performed as a
396single atomic operation on the database, so race conditions can be avoided.
397
398Like the insert method, the first parameter is the name of the table and the
399second parameter can be used to pass the values to be inserted as a dictionary.
400
401Unlike the insert und update statement, keyword parameters are not used to
402modify the dictionary, but to specify which columns shall be updated in case
403of a conflict, and in which way:
404
405A value of `False` or `None` means the column shall not be updated,
406a value of `True` means the column shall be updated with the value that
407has been proposed for insertion, i.e. has been passed as value in the
408dictionary.  Columns that are not specified by keywords but appear as keys
409in the dictionary are also updated like in the case keywords had been passed
410with the value `True`.
411
412So if in the case of a conflict you want to update every column that has been
413passed in the dictionary `d` , you would call ``upsert(table, d)``.  If you
414don't want to do anything in case of a conflict, i.e. leave the existing row
415as it is, call ``upsert(table, d, **dict.fromkeys(d))``.
416
417If you need more fine-grained control of what gets updated, you can also pass
418strings in the keyword parameters.  These strings will be used as SQL
419expressions for the update columns.  In these expressions you can refer
420to the value that already exists in the table by writing the table prefix
421``included.`` before the column name, and you can refer to the value that
422has been proposed for insertion by writing ``excluded.`` as table prefix.
423
424The dictionary is modified in any case to reflect the values in the database
425after the operation has completed.
426
427.. note::
428
429    The method uses the PostgreSQL "upsert" feature which is only available
430    since PostgreSQL 9.5. With older PostgreSQL versions, you will get a
431    ProgrammingError if you use this method.
432
433.. versionadded:: 5.0
434
435query -- execute a SQL command string
436-------------------------------------
437
438.. method:: DB.query(command, [arg1, [arg2, ...]])
439
440    Execute a SQL command string
441
442    :param str command: SQL command
443    :param arg*: optional positional arguments
444    :returns: result values
445    :rtype: :class:`Query`, None
446    :raises TypeError: bad argument type, or too many arguments
447    :raises TypeError: invalid connection
448    :raises ValueError: empty SQL query or lost connection
449    :raises pg.ProgrammingError: error in query
450    :raises pg.InternalError: error during query processing
451
452Similar to the :class:`Connection` function with the same name, except that
453positional arguments can be passed either as a single list or tuple, or as
454individual positional arguments.  These arguments will then be used as
455parameter values of parameterized queries.
456
457Example::
458
459    name = input("Name? ")
460    phone = input("Phone? ")
461    rows = db.query("update employees set phone=$2 where name=$1",
462        name, phone).getresult()[0][0]
463    # or
464    rows = db.query("update employees set phone=$2 where name=$1",
465        (name, phone)).getresult()[0][0]
466
467query_formatted -- execute a formatted SQL command string
468---------------------------------------------------------
469
470.. method:: DB.query_formatted(command, [parameters], [types], [inline])
471
472    Execute a formatted SQL command string
473
474    :param str command: SQL command
475    :param parameters: the values of the parameters for the SQL command
476    :type parameters: tuple, list or dict
477    :param types: optionally, the types of the parameters
478    :type types: tuple, list or dict
479    :param bool inline: whether the parameters should be passed in the SQL
480    :rtype: :class:`Query`, None
481    :raises TypeError: bad argument type, or too many arguments
482    :raises TypeError: invalid connection
483    :raises ValueError: empty SQL query or lost connection
484    :raises pg.ProgrammingError: error in query
485    :raises pg.InternalError: error during query processing
486
487Similar to :meth:`DB.query`, but using Python format placeholders of the form
488``%s`` or ``%(names)s`` instead of PostgreSQL placeholders of the form ``$1``.
489The parameters must be passed as a tuple, list or dict.  You can also pass a
490corresponding tuple, list or dict of database types in order to format the
491parameters properly in case there is ambiguity.
492
493If you set *inline* to True, the parameters will be sent to the database
494embedded in the SQL command, otherwise they will be sent separately.
495
496Example::
497
498    name = input("Name? ")
499    phone = input("Phone? ")
500    rows = db.query_formatted(
501        "update employees set phone=%s where name=%s",
502        (phone, name)).getresult()[0][0]
503    # or
504    rows = db.query_formatted(
505        "update employees set phone=%(phone)s where name=%(name)s",
506        dict(name=name, phone=phone)).getresult()[0][0]
507
508query_prepared -- execute a prepared statement
509----------------------------------------------
510
511.. method:: DB.query_prepared([arg1, [arg2, ...]], [name=...])
512
513    Execute a prepared statement
514
515    :param str name: name of the prepared statement
516    :param arg*: optional positional arguments
517    :returns: result values
518    :rtype: :class:`Query`, None
519    :raises TypeError: bad argument type, or too many arguments
520    :raises TypeError: invalid connection
521    :raises ValueError: empty SQL query or lost connection
522    :raises pg.ProgrammingError: error in query
523    :raises pg.InternalError: error during query processing
524    :raises pg.OperationalError: prepared statement does not exist
525
526This methods works like the :meth:`DB.query` method, except that instead of
527passing the SQL command, you pass the name of a prepared statement via the
528keyword-only argument *name*.  If you don't pass a name, the unnamed
529statement will be executed, if you created one before.
530
531You must have created the corresponding named or unnamed statement with
532the :meth:`DB.prepare` method before, otherwise an :exc:`pg.OperationalError`
533will be raised.
534
535.. versionadded:: 5.1
536
537prepare -- create a prepared statement
538--------------------------------------
539
540.. method:: DB.prepare(command, [name])
541
542    Create a prepared statement
543
544    :param str command: SQL command
545    :param str name: name of the prepared statement
546    :rtype: None
547    :raises TypeError: bad argument types, or wrong number of arguments
548    :raises TypeError: invalid connection
549    :raises pg.ProgrammingError: error in query or duplicate query
550
551This method creates a prepared statement for the given command with the
552given name for later execution with the :meth:`DB.query_prepared` method.
553The name can be empty or left out to create an unnamed statement, in which
554case any pre-existing unnamed statement is automatically replaced;
555otherwise a :exc:`pg.ProgrammingError` is raised if the statement name is
556already defined in the current database session.
557
558The SQL command may optionally contain positional parameters of the form
559``$1``, ``$2``, etc instead of literal data.  The corresponding values
560must then later be passed to the :meth:`Connection.query_prepared` method
561as positional arguments.
562
563Example::
564
565    db.prepare("update employees set phone=$2 where ein=$1",
566        name='update employees')
567    while True:
568        ein = input("Employee ID? ")
569        if not ein:
570            break
571        phone = input("Phone? ")
572        rows = db.query_prepared(ein, phone,
573            name='update employees).getresult()[0][0]
574
575.. note::
576
577    The DB wrapper sometimes issues parameterized queries behind the scenes
578    (for instance to find unknown database types) which could replace the
579    unnamed statement. So we advice to always name prepared statements.
580
581.. versionadded:: 5.1
582
583describe_prepared -- describe a prepared statement
584--------------------------------------------------
585
586.. method:: DB.describe_prepared([name])
587
588    Describe a prepared statement
589
590    :param str name: name of the prepared statement
591    :rtype: :class:`Query`
592    :raises TypeError: bad argument type, or too many arguments
593    :raises TypeError: invalid connection
594    :raises pg.OperationalError: prepared statement does not exist
595
596This method returns a :class:`Query` object describing the prepared
597statement with the given name.  You can also pass an empty name in order
598to describe the unnamed statement.  Information on the fields of the
599corresponding query can be obtained through the :meth:`Query.listfields`,
600:meth:`Query.fieldname` and :meth:`Query.fieldnum` methods.
601
602.. versionadded:: 5.1
603
604delete_prepared -- delete a prepared statement
605----------------------------------------------
606
607.. method:: DB.delete_prepared([name])
608
609    Delete a prepared statement
610
611    :param str name: name of the prepared statement
612    :rtype: None
613    :raises TypeError: bad argument type, or too many arguments
614    :raises TypeError: invalid connection
615    :raises pg.OperationalError: prepared statement does not exist
616
617This method deallocates a previously prepared SQL statement with the given
618name, or deallocates all prepared statements if you do not specify a name.
619Note that prepared statements are also deallocated automatically when the
620current session ends.
621
622.. versionadded:: 5.1
623
624clear -- clear row values in memory
625-----------------------------------
626
627.. method:: DB.clear(table, [row])
628
629    Clear row values in memory
630
631    :param str table: name of table
632    :param dict row: optional dictionary of values
633    :returns: an empty row
634    :rtype: dict
635
636This method clears all the attributes to values determined by the types.
637Numeric types are set to 0, Booleans are set to *False*, and everything
638else is set to the empty string.  If the row argument is present, it is
639used as the row dictionary and any entries matching attribute names are
640cleared with everything else left unchanged.
641
642If the dictionary is not supplied a new one is created.
643
644delete -- delete a row from a database table
645--------------------------------------------
646
647.. method:: DB.delete(table, [row], [col=val, ...])
648
649    Delete a row from a database table
650
651    :param str table: name of table
652    :param dict d: optional dictionary of values
653    :param col: optional keyword arguments for updating the dictionary
654    :rtype: None
655    :raises pg.ProgrammingError: table has no primary key,
656        row is still referenced or missing privilege
657    :raises KeyError: missing key value for the row
658
659This method deletes the row from a table.  It deletes based on the
660primary key of the table or the OID value as munged by :meth:`DB.get`
661or passed as keyword.  The OID will take precedence if provided.
662
663The return value is the number of deleted rows (i.e. 0 if the row did not
664exist and 1 if the row was deleted).
665
666Note that if the row cannot be deleted because e.g. it is still referenced
667by another table, this method will raise a ProgrammingError.
668
669truncate -- quickly empty database tables
670-----------------------------------------
671
672.. method:: DB.truncate(table, [restart], [cascade], [only])
673
674    Empty a table or set of tables
675
676    :param table: the name of the table(s)
677    :type table: str, list or set
678    :param bool restart: whether table sequences should be restarted
679    :param bool cascade: whether referenced tables should also be truncated
680    :param only: whether only parent tables should be truncated
681    :type only: bool or list
682
683This method quickly removes all rows from the given table or set
684of tables.  It has the same effect as an unqualified DELETE on each
685table, but since it does not actually scan the tables it is faster.
686Furthermore, it reclaims disk space immediately, rather than requiring
687a subsequent VACUUM operation. This is most useful on large tables.
688
689If *restart* is set to `True`, sequences owned by columns of the truncated
690table(s) are automatically restarted.  If *cascade* is set to `True`, it
691also truncates all tables that have foreign-key references to any of
692the named tables.  If the parameter *only* is not set to `True`, all the
693descendant tables (if any) will also be truncated. Optionally, a ``*``
694can be specified after the table name to explicitly indicate that
695descendant tables are included.  If the parameter *table* is a list,
696the parameter *only* can also be a list of corresponding boolean values.
697
698.. versionadded:: 4.2
699
700get_as_list/dict -- read a table as a list or dictionary
701--------------------------------------------------------
702
703.. method:: DB.get_as_list(table, [what], [where], [order], [limit], [offset], [scalar])
704
705    Get a table as a list
706
707    :param str table: the name of the table (the FROM clause)
708    :param what: column(s) to be returned (the SELECT clause)
709    :type what: str, list, tuple or None
710    :param where: conditions(s) to be fulfilled (the WHERE clause)
711    :type where: str, list, tuple or None
712    :param order: column(s) to sort by (the ORDER BY clause)
713    :type order: str, list, tuple, False or None
714    :param int limit: maximum number of rows returned (the LIMIT clause)
715    :param int offset: number of rows to be skipped (the OFFSET clause)
716    :param bool scalar: whether only the first column shall be returned
717    :returns: the content of the table as a list
718    :rtype: list
719    :raises TypeError: the table name has not been specified
720
721This gets a convenient representation of the table as a list of named tuples
722in Python.  You only need to pass the name of the table (or any other SQL
723expression returning rows).  Note that by default this will return the full
724content of the table which can be huge and overflow your memory.  However, you
725can control the amount of data returned using the other optional parameters.
726
727The parameter *what* can restrict the query to only return a subset of the
728table columns.  The parameter *where* can restrict the query to only return a
729subset of the table rows.  The specified SQL expressions all need to be
730fulfilled for a row to get into the result.  The parameter *order* specifies
731the ordering of the rows.  If no ordering is specified, the result will be
732ordered by the primary key(s) or all columns if no primary key exists.
733You can set *order* to *False* if you don't care about the ordering.
734The parameters *limit* and *offset* specify the maximum number of rows
735returned and a number of rows skipped over.
736
737If you set the *scalar* option to *True*, then instead of the named tuples
738you will get the first items of these tuples.  This is useful if the result
739has only one column anyway.
740
741.. method:: DB.get_as_dict(table, [keyname], [what], [where], [order], [limit], [offset], [scalar])
742
743    Get a table as a dictionary
744
745    :param str table: the name of the table (the FROM clause)
746    :param keyname: column(s) to be used as key(s) of the dictionary
747    :type keyname: str, list, tuple or None
748    :param what: column(s) to be returned (the SELECT clause)
749    :type what: str, list, tuple or None
750    :param where: conditions(s) to be fulfilled (the WHERE clause)
751    :type where: str, list, tuple or None
752    :param order: column(s) to sort by (the ORDER BY clause)
753    :type order: str, list, tuple, False or None
754    :param int limit: maximum number of rows returned (the LIMIT clause)
755    :param int offset: number of rows to be skipped (the OFFSET clause)
756    :param bool scalar: whether only the first column shall be returned
757    :returns: the content of the table as a list
758    :rtype: dict or OrderedDict
759    :raises TypeError: the table name has not been specified
760    :raises KeyError: keyname(s) are invalid or not part of the result
761    :raises pg.ProgrammingError: no keyname(s) and table has no primary key
762
763This method is similar to :meth:`DB.get_as_list`, but returns the table as
764a Python dict instead of a Python list, which can be even more convenient.
765The primary key column(s) of the table will be used as the keys of the
766dictionary, while the other column(s) will be the corresponding values.
767The keys will be named tuples if the table has a composite primary key.
768The rows will be also named tuples unless the *scalar* option has been set
769to *True*.  With the optional parameter *keyname* you can specify a different
770set of columns to be used as the keys of the dictionary.
771
772If the Python version supports it, the dictionary will be an *OrderedDict*
773using the order specified with the *order* parameter or the key column(s)
774if not specified.  You can set *order* to *False* if you don't care about the
775ordering.  In this case the returned dictionary will be an ordinary one.
776
777escape_literal/identifier/string/bytea -- escape for SQL
778--------------------------------------------------------
779
780The following methods escape text or binary strings so that they can be
781inserted directly into an SQL command.  Except for :meth:`DB.escape_byte`,
782you don't need to call these methods for the strings passed as parameters
783to :meth:`DB.query`.  You also don't need to call any of these methods
784when storing data using :meth:`DB.insert` and similar.
785
786.. method:: DB.escape_literal(string)
787
788    Escape a string for use within SQL as a literal constant
789
790    :param str string: the string that is to be escaped
791    :returns: the escaped string
792    :rtype: str
793
794This method escapes a string for use within an SQL command. This is useful
795when inserting data values as literal constants in SQL commands. Certain
796characters (such as quotes and backslashes) must be escaped to prevent them
797from being interpreted specially by the SQL parser.
798
799.. versionadded:: 4.1
800
801.. method:: DB.escape_identifier(string)
802
803    Escape a string for use within SQL as an identifier
804
805    :param str string: the string that is to be escaped
806    :returns: the escaped string
807    :rtype: str
808
809This method escapes a string for use as an SQL identifier, such as a table,
810column, or function name. This is useful when a user-supplied identifier
811might contain special characters that would otherwise not be interpreted
812as part of the identifier by the SQL parser, or when the identifier might
813contain upper case characters whose case should be preserved.
814
815.. versionadded:: 4.1
816
817.. method:: DB.escape_string(string)
818
819    Escape a string for use within SQL
820
821    :param str string: the string that is to be escaped
822    :returns: the escaped string
823    :rtype: str
824
825Similar to the module function :func:`pg.escape_string` with the same name,
826but the behavior of this method is adjusted depending on the connection
827properties (such as character encoding).
828
829.. method:: DB.escape_bytea(datastring)
830
831    Escape binary data for use within SQL as type ``bytea``
832
833    :param str datastring: string containing the binary data that is to be escaped
834    :returns: the escaped string
835    :rtype: str
836
837Similar to the module function :func:`pg.escape_bytea` with the same name,
838but the behavior of this method is adjusted depending on the connection
839properties (in particular, whether standard-conforming strings are enabled).
840
841unescape_bytea -- unescape data retrieved from the database
842-----------------------------------------------------------
843
844.. method:: DB.unescape_bytea(string)
845
846    Unescape ``bytea`` data that has been retrieved as text
847
848    :param datastring: the ``bytea`` data string that has been retrieved as text
849    :returns: byte string containing the binary data
850    :rtype: bytes
851
852Converts an escaped string representation of binary data stored as ``bytea``
853into the raw byte string representing the binary data  -- this is the reverse
854of :meth:`DB.escape_bytea`.  Since the :class:`Query` results will already
855return unescaped byte strings, you normally don't have to use this method.
856
857encode/decode_json -- encode and decode JSON data
858-------------------------------------------------
859
860The following methods can be used to encode end decode data in
861`JSON <http://www.json.org/>`_ format.
862
863.. method:: DB.encode_json(obj)
864
865    Encode a Python object for use within SQL as type ``json`` or ``jsonb``
866
867    :param obj: Python object that shall be encoded to JSON format
868    :type obj: dict, list or None
869    :returns: string representation of the Python object in JSON format
870    :rtype: str
871
872This method serializes a Python object into a JSON formatted string that can
873be used within SQL.  You don't need to use this method on the data stored
874with :meth:`DB.insert` and similar, only if you store the data directly as
875part of an SQL command or parameter with :meth:`DB.query`.  This is the same
876as the :func:`json.dumps` function from the standard library.
877
878.. versionadded:: 5.0
879
880.. method:: DB.decode_json(string)
881
882    Decode ``json`` or ``jsonb`` data that has been retrieved as text
883
884    :param string: JSON formatted string shall be decoded into a Python object
885    :type string: str
886    :returns: Python object representing the JSON formatted string
887    :rtype: dict, list or None
888
889This method deserializes a JSON formatted string retrieved as text from the
890database to a Python object.  You normally don't need to use this method as
891JSON data is automatically decoded by PyGreSQL.  If you don't want the data
892to be decoded, then you can cast ``json`` or ``jsonb`` columns to ``text``
893in PostgreSQL or you can set the decoding function to *None* or a different
894function using :func:`pg.set_jsondecode`.  By default this is the same as
895the :func:`json.dumps` function from the standard library.
896
897.. versionadded:: 5.0
898
899use_regtypes -- determine use of regular type names
900---------------------------------------------------
901
902.. method:: DB.use_regtypes([regtypes])
903
904    Determine whether regular type names shall be used
905
906    :param bool regtypes: if passed, set whether regular type names shall be used
907    :returns: whether regular type names are used
908
909The :meth:`DB.get_attnames` method can return either simplified "classic"
910type names (the default) or more specific "regular" type names. Which kind
911of type names is used can be changed by calling :meth:`DB.get_regtypes`.
912If you pass a boolean, it sets whether regular type names shall be used.
913The method can also be used to check through its return value whether
914currently regular type names are used.
915
916.. versionadded:: 4.1
917
918notification_handler -- create a notification handler
919-----------------------------------------------------
920
921.. class:: DB.notification_handler(event, callback, [arg_dict], [timeout], [stop_event])
922
923    Create a notification handler instance
924
925    :param str event: the name of an event to listen for
926    :param callback: a callback function
927    :param dict arg_dict: an optional dictionary for passing arguments
928    :param timeout: the time-out when waiting for notifications
929    :type timeout: int, float or None
930    :param str stop_event: an optional different name to be used as stop event
931
932This method creates a :class:`pg.NotificationHandler` object using the
933:class:`DB` connection as explained under :doc:`notification`.
934
935.. versionadded:: 4.1.1
936
937Attributes of the DB wrapper class
938----------------------------------
939
940.. attribute:: DB.db
941
942    The wrapped :class:`Connection` object
943
944You normally don't need this, since all of the members can be accessed
945from the :class:`DB` wrapper class as well.
946
947.. attribute:: DB.dbname
948
949    The name of the database that the connection is using
950
951.. attribute:: DB.dbtypes
952
953    A dictionary with the various type names for the PostgreSQL types
954
955This can be used for getting more information on the PostgreSQL database
956types or changing the typecast functions used for the connection.  See the
957description of the :class:`DbTypes` class for details.
958
959.. versionadded:: 5.0
960
961.. attribute:: DB.adapter
962
963    A class with some helper functions for adapting parameters
964
965This can be used for building queries with parameters.  You normally will
966not need this, as you can use the :class:`DB.query_formatted` method.
967
968.. versionadded:: 5.0
Note: See TracBrowser for help on using the repository browser.