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

Last change on this file since 781 was 781, checked in by cito, 4 years ago

Add full support for PostgreSQL array types

At the core of this patch is a fast parser for the peculiar syntax of
literal array expressions in PostgreSQL that was added to the C module.
This is not trivial, because PostgreSQL arrays can be multidimensional
and the syntax is different from Python and SQL expressions.

The Python pg and pgdb modules make use of this parser so that they can
return database columns containing PostgreSQL arrays to Python as lists.
Also added quoting methods that allow passing PostgreSQL arrays as lists
to insert()/update() and execute/executemany(). These methods are simpler
and were implemented in Python but needed support from the regex module.

The patch also adds makes getresult() in pg automatically return bytea
values in unescaped form as bytes strings. Before, it was necessary to
call unescape_bytea manually. The pgdb module did this already.

The patch includes some more refactorings and simplifications regarding
the quoting and casting in pg and pgdb.

Some references to antique PostgreSQL types that are not used any more
in the supported PostgreSQL versions have been removed.

Also added documentation and tests for the new features.

File size: 29.4 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)
88
89    Get the list of relations in connected database
90
91    :param str kinds: a string or sequence of type letters
92    :returns: all relations of the given kinds in the database
93    :rtype: list
94
95The type letters are ``r`` = ordinary table, ``i`` = index, ``S`` = sequence,
96``v`` = view, ``c`` = composite type, ``s`` = special, ``t`` = TOAST table.
97If `kinds` is None or an empty string, all relations are returned (this is
98also the default). Although you can do this with a simple select, it is
99added here for convenience.
100
101get_tables -- get list of tables in connected database
102------------------------------------------------------
103
104.. method:: DB.get_tables()
105
106    Get the list of tables in connected database
107
108    :returns: all tables in connected database
109    :rtype: list
110
111This is a shortcut for ``get_relations('r')`` that has been added for
112convenience.
113
114get_attnames -- get the attribute names of a table
115--------------------------------------------------
116
117.. method:: DB.get_attnames(table)
118
119    Get the attribute names of a table
120
121    :param str table: name of table
122    :returns: an ordered dictionary mapping attribute names to type names
123
124Given the name of a table, digs out the set of attribute names.
125
126Returns a read-only dictionary of attribute names (the names are the keys,
127the values are the names of the attributes' types) with the column names
128in the proper order if you iterate over it.
129
130By default, only a limited number of simple types will be returned.
131You can get the regular types after enabling this by calling the
132:meth:`DB.use_regtypes` method.
133
134has_table_privilege -- check table privilege
135--------------------------------------------
136
137.. method:: DB.has_table_privilege(table, privilege)
138
139    Check whether current user has specified table privilege
140
141    :param str table: the name of the table
142    :param str privilege: privilege to be checked -- default is 'select'
143    :returns: whether current user has specified table privilege
144    :rtype: bool
145
146Returns True if the current user has the specified privilege for the table.
147
148.. versionadded:: 4.0
149
150get/set_parameter -- get or set  run-time parameters
151----------------------------------------------------
152
153.. method:: DB.get_parameter(parameter)
154
155    Get the value of run-time parameters
156
157    :param parameter: the run-time parameter(s) to get
158    :type param: str, tuple, list or dict
159    :returns: the current value(s) of the run-time parameter(s)
160    :rtype: str, list or dict
161    :raises TypeError: Invalid parameter type(s)
162    :raises pg.ProgrammingError: Invalid parameter name(s)
163
164If the parameter is a string, the return value will also be a string
165that is the current setting of the run-time parameter with that name.
166
167You can get several parameters at once by passing a list, set or dict.
168When passing a list of parameter names, the return value will be a
169corresponding list of parameter settings.  When passing a set of
170parameter names, a new dict will be returned, mapping these parameter
171names to their settings.  Finally, if you pass a dict as parameter,
172its values will be set to the current parameter settings corresponding
173to its keys.
174
175By passing the special name `'all'` as the parameter, you can get a dict
176of all existing configuration parameters.
177
178.. versionadded:: 4.2
179
180.. method:: DB.set_parameter(parameter, [value], [local])
181
182    Set the value of run-time parameters
183
184    :param parameter: the run-time parameter(s) to set
185    :type param: string, tuple, list or dict
186    :param value: the value to set
187    :type param: str or None
188    :raises TypeError: Invalid parameter type(s)
189    :raises ValueError: Invalid value argument(s)
190    :raises pg.ProgrammingError: Invalid parameter name(s) or values
191
192If the parameter and the value are strings, the run-time parameter
193will be set to that value.  If no value or *None* is passed as a value,
194then the run-time parameter will be restored to its default value.
195
196You can set several parameters at once by passing a list of parameter
197names, together with a single value that all parameters should be
198set to or with a corresponding list of values.  You can also pass
199the parameters as a set if you only provide a single value.
200Finally, you can pass a dict with parameter names as keys.  In this
201case, you should not pass a value, since the values for the parameters
202will be taken from the dict.
203
204By passing the special name `'all'` as the parameter, you can reset
205all existing settable run-time parameters to their default values.
206
207If you set *local* to `True`, then the command takes effect for only the
208current transaction.  After :meth:`DB.commit` or :meth:`DB.rollback`,
209the session-level setting takes effect again.  Setting *local* to `True`
210will appear to have no effect if it is executed outside a transaction,
211since the transaction will end immediately.
212
213.. versionadded:: 4.2
214
215begin/commit/rollback/savepoint/release -- transaction handling
216---------------------------------------------------------------
217
218.. method:: DB.begin([mode])
219
220    Begin a transaction
221
222    :param str mode: an optional transaction mode such as 'READ ONLY'
223
224    This initiates a transaction block, that is, all following queries
225    will be executed in a single transaction until :meth:`DB.commit`
226    or :meth:`DB.rollback` is called.
227
228.. versionadded:: 4.1
229
230.. method:: DB.start()
231
232    This is the same as the :meth:`DB.begin` method.
233
234.. method:: DB.commit()
235
236    Commit a transaction
237
238    This commits the current transaction. All changes made by the
239    transaction become visible to others and are guaranteed to be
240    durable if a crash occurs.
241
242.. method:: DB.end()
243
244    This is the same as the :meth:`DB.commit` method.
245
246.. versionadded:: 4.1
247
248.. method:: DB.rollback([name])
249
250    Roll back a transaction
251
252    :param str name: optionally, roll back to the specified savepoint
253
254    This rolls back the current transaction and causes all the updates
255    made by the transaction to be discarded.
256
257.. method:: DB.abort()
258
259    This is the same as the :meth:`DB.rollback` method.
260
261.. versionadded:: 4.2
262
263.. method:: DB.savepoint(name)
264
265    Define a new savepoint
266
267    :param str name: the name to give to the new savepoint
268
269    This establishes a new savepoint within the current transaction.
270
271.. versionadded:: 4.1
272
273.. method:: DB.release(name)
274
275    Destroy a savepoint
276
277    :param str name: the name of the savepoint to destroy
278
279    This destroys a savepoint previously defined in the current transaction.
280
281.. versionadded:: 4.1
282
283get -- get a row from a database table or view
284----------------------------------------------
285
286.. method:: DB.get(table, row, [keyname])
287
288    Get a row from a database table or view
289
290    :param str table: name of table or view
291    :param row: either a dictionary or the value to be looked up
292    :param str keyname: name of field to use as key (optional)
293    :returns: A dictionary - the keys are the attribute names,
294      the values are the row values.
295    :raises pg.ProgrammingError: table has no primary key or missing privilege
296    :raises KeyError: missing key value for the row
297
298This method is the basic mechanism to get a single row.  It assumes
299that the *keyname* specifies a unique row.  It must be the name of a
300single column or a tuple of column names.  If *keyname* is not specified,
301then the primary key for the table is used.
302
303If *row* is a dictionary, then the value for the key is taken from it.
304Otherwise, the row must be a single value or a tuple of values
305corresponding to the passed *keyname* or primary key.  The fetched row
306from the table will be returned as a new dictionary or used to replace
307the existing values when row was passed as aa dictionary.
308
309The OID is also put into the dictionary if the table has one, but
310in order to allow the caller to work with multiple tables, it is
311munged as ``oid(table)`` using the actual name of the table.
312
313Note that since PyGreSQL 5.0 this will return the value of an array
314type column as a Python list.
315
316insert -- insert a row into a database table
317--------------------------------------------
318
319.. method:: DB.insert(table, [row], [col=val, ...])
320
321    Insert a row into a database table
322
323    :param str table: name of table
324    :param dict row: optional dictionary of values
325    :param col: optional keyword arguments for updating the dictionary
326    :returns: the inserted values in the database
327    :rtype: dict
328    :raises pg.ProgrammingError: missing privilege or conflict
329
330This method inserts a row into a table.  If the optional dictionary is
331not supplied then the required values must be included as keyword/value
332pairs.  If a dictionary is supplied then any keywords provided will be
333added to or replace the entry in the dictionary.
334
335The dictionary is then reloaded with the values actually inserted in order
336to pick up values modified by rules, triggers, etc.
337
338Note that since PyGreSQL 5.0 it is possible to insert a value for an
339array type column by passing it as Python list.
340
341update -- update a row in a database table
342------------------------------------------
343
344.. method:: DB.update(table, [row], [col=val, ...])
345
346    Update a row in a database table
347
348    :param str table: name of table
349    :param dict row: optional dictionary of values
350    :param col: optional keyword arguments for updating the dictionary
351    :returns: the new row in the database
352    :rtype: dict
353    :raises pg.ProgrammingError: table has no primary key or missing privilege
354    :raises KeyError: missing key value for the row
355
356Similar to insert but updates an existing row.  The update is based on
357the primary key of the table or the OID value as munged by :meth:`DB.get`
358or passed as keyword.
359
360The dictionary is then modified to reflect any changes caused by the
361update due to triggers, rules, default values, etc.
362
363Like insert, the dictionary is optional and updates will be performed
364on the fields in the keywords.  There must be an OID or primary key
365either in the dictionary where the OID must be munged, or in the keywords
366where it can be simply the string ``'oid'``.
367
368upsert -- insert a row with conflict resolution
369-----------------------------------------------
370
371.. method:: DB.upsert(table, [row], [col=val, ...])
372
373    Insert a row into a database table with conflict resolution
374
375    :param str table: name of table
376    :param dict row: optional dictionary of values
377    :param col: optional keyword arguments for specifying the update
378    :returns: the new row in the database
379    :rtype: dict
380    :raises pg.ProgrammingError: table has no primary key or missing privilege
381
382This method inserts a row into a table, but instead of raising a
383ProgrammingError exception in case a row with the same primary key already
384exists, an update will be executed instead.  This will be performed as a
385single atomic operation on the database, so race conditions can be avoided.
386
387Like the insert method, the first parameter is the name of the table and the
388second parameter can be used to pass the values to be inserted as a dictionary.
389
390Unlike the insert und update statement, keyword parameters are not used to
391modify the dictionary, but to specify which columns shall be updated in case
392of a conflict, and in which way:
393
394A value of `False` or `None` means the column shall not be updated,
395a value of `True` means the column shall be updated with the value that
396has been proposed for insertion, i.e. has been passed as value in the
397dictionary.  Columns that are not specified by keywords but appear as keys
398in the dictionary are also updated like in the case keywords had been passed
399with the value `True`.
400
401So if in the case of a conflict you want to update every column that has been
402passed in the dictionary `d` , you would call ``upsert(table, d)``.  If you
403don't want to do anything in case of a conflict, i.e. leave the existing row
404as it is, call ``upsert(table, d, **dict.fromkeys(d))``.
405
406If you need more fine-grained control of what gets updated, you can also pass
407strings in the keyword parameters.  These strings will be used as SQL
408expressions for the update columns.  In these expressions you can refer
409to the value that already exists in the table by writing the table prefix
410``included.`` before the column name, and you can refer to the value that
411has been proposed for insertion by writing ``excluded.`` as table prefix.
412
413The dictionary is modified in any case to reflect the values in the database
414after the operation has completed.
415
416.. note::
417
418    The method uses the PostgreSQL "upsert" feature which is only available
419    since PostgreSQL 9.5. With older PostgreSQL versions, you will get a
420    ProgrammingError if you use this method.
421
422.. versionadded:: 5.0
423
424query -- execute a SQL command string
425-------------------------------------
426
427.. method:: DB.query(command, [arg1, [arg2, ...]])
428
429    Execute a SQL command string
430
431    :param str command: SQL command
432    :param arg*: optional positional arguments
433    :returns: result values
434    :rtype: :class:`Query`, None
435    :raises TypeError: bad argument type, or too many arguments
436    :raises TypeError: invalid connection
437    :raises ValueError: empty SQL query or lost connection
438    :raises pg.ProgrammingError: error in query
439    :raises pg.InternalError: error during query processing
440
441Similar to the :class:`Connection` function with the same name, except that
442positional arguments can be passed either as a single list or tuple, or as
443individual positional arguments.
444
445Example::
446
447    name = input("Name? ")
448    phone = input("Phone? ")
449    rows = db.query("update employees set phone=$2 where name=$1",
450        (name, phone)).getresult()[0][0]
451    # or
452    rows = db.query("update employees set phone=$2 where name=$1",
453         name, phone).getresult()[0][0]
454
455clear -- clear row values in memory
456-----------------------------------
457
458.. method:: DB.clear(table, [row])
459
460    Clear row values in memory
461
462    :param str table: name of table
463    :param dict row: optional dictionary of values
464    :returns: an empty row
465    :rtype: dict
466
467This method clears all the attributes to values determined by the types.
468Numeric types are set to 0, Booleans are set to ``'f'``, and everything
469else is set to the empty string.  If the row argument is present, it is
470used as the row dictionary and any entries matching attribute names are
471cleared with everything else left unchanged.
472
473If the dictionary is not supplied a new one is created.
474
475delete -- delete a row from a database table
476--------------------------------------------
477
478.. method:: DB.delete(table, [row], [col=val, ...])
479
480    Delete a row from a database table
481
482    :param str table: name of table
483    :param dict d: optional dictionary of values
484    :param col: optional keyword arguments for updating the dictionary
485    :rtype: None
486    :raises pg.ProgrammingError: table has no primary key,
487        row is still referenced or missing privilege
488    :raises KeyError: missing key value for the row
489
490This method deletes the row from a table.  It deletes based on the
491primary key of the table or the OID value as munged by :meth:`DB.get`
492or passed as keyword.
493
494The return value is the number of deleted rows (i.e. 0 if the row did not
495exist and 1 if the row was deleted).
496
497Note that if the row cannot be deleted because e.g. it is still referenced
498by another table, this method will raise a ProgrammingError.
499
500truncate -- quickly empty database tables
501-----------------------------------------
502
503.. method:: DB.truncate(table, [restart], [cascade], [only])
504
505    Empty a table or set of tables
506
507    :param table: the name of the table(s)
508    :type table: str, list or set
509    :param bool restart: whether table sequences should be restarted
510    :param bool cascade: whether referenced tables should also be truncated
511    :param only: whether only parent tables should be truncated
512    :type only: bool or list
513
514This method quickly removes all rows from the given table or set
515of tables.  It has the same effect as an unqualified DELETE on each
516table, but since it does not actually scan the tables it is faster.
517Furthermore, it reclaims disk space immediately, rather than requiring
518a subsequent VACUUM operation. This is most useful on large tables.
519
520If *restart* is set to `True`, sequences owned by columns of the truncated
521table(s) are automatically restarted.  If *cascade* is set to `True`, it
522also truncates all tables that have foreign-key references to any of
523the named tables.  If the parameter *only* is not set to `True`, all the
524descendant tables (if any) will also be truncated. Optionally, a ``*``
525can be specified after the table name to explicitly indicate that
526descendant tables are included.  If the parameter *table* is a list,
527the parameter *only* can also be a list of corresponding boolean values.
528
529.. versionadded:: 4.2
530
531get_as_list/dict -- read a table as a list or dictionary
532--------------------------------------------------------
533
534.. method:: DB.get_as_list(table, [what], [where], [order], [limit], [offset], [scalar])
535
536    Get a table as a list
537
538    :param str table: the name of the table (the FROM clause)
539    :param what: column(s) to be returned (the SELECT clause)
540    :type what: str, list, tuple or None
541    :param where: conditions(s) to be fulfilled (the WHERE clause)
542    :type where: str, list, tuple or None
543    :param order: column(s) to sort by (the ORDER BY clause)
544    :type order: str, list, tuple, False or None
545    :param int limit: maximum number of rows returned (the LIMIT clause)
546    :param int offset: number of rows to be skipped (the OFFSET clause)
547    :param bool scalar: whether only the first column shall be returned
548    :returns: the content of the table as a list
549    :rtype: list
550    :raises TypeError: the table name has not been specified
551
552This gets a convenient representation of the table as a list of named tuples
553in Python.  You only need to pass the name of the table (or any other SQL
554expression returning rows).  Note that by default this will return the full
555content of the table which can be huge and overflow your memory.  However, you
556can control the amount of data returned using the other optional parameters.
557
558The parameter *what* can restrict the query to only return a subset of the
559table columns.  The parameter *where* can restrict the query to only return a
560subset of the table rows.  The specified SQL expressions all need to be
561fulfilled for a row to get into the result.  The parameter *order* specifies
562the ordering of the rows.  If no ordering is specified, the result will be
563ordered by the primary key(s) or all columns if no primary key exists.
564You can set *order* to *False* if you don't care about the ordering.
565The parameters *limit* and *offset* specify the maximum number of rows
566returned and a number of rows skipped over.
567
568If you set the *scalar* option to *True*, then instead of the named tuples
569you will get the first items of these tuples.  This is useful if the result
570has only one column anyway.
571
572.. method:: DB.get_as_dict(table, [keyname], [what], [where], [order], [limit], [offset], [scalar])
573
574    Get a table as a dictionary
575
576    :param str table: the name of the table (the FROM clause)
577    :param keyname: column(s) to be used as key(s) of the dictionary
578    :type keyname: str, list, tuple or None
579    :param what: column(s) to be returned (the SELECT clause)
580    :type what: str, list, tuple or None
581    :param where: conditions(s) to be fulfilled (the WHERE clause)
582    :type where: str, list, tuple or None
583    :param order: column(s) to sort by (the ORDER BY clause)
584    :type order: str, list, tuple, False or None
585    :param int limit: maximum number of rows returned (the LIMIT clause)
586    :param int offset: number of rows to be skipped (the OFFSET clause)
587    :param bool scalar: whether only the first column shall be returned
588    :returns: the content of the table as a list
589    :rtype: dict or OrderedDict
590    :raises TypeError: the table name has not been specified
591    :raises KeyError: keyname(s) are invalid or not part of the result
592    :raises pg.ProgrammingError: no keyname(s) and table has no primary key
593
594This method is similar to :meth:`DB.get_as_list`, but returns the table as
595a Python dict instead of a Python list, which can be even more convenient.
596The primary key column(s) of the table will be used as the keys of the
597dictionary, while the other column(s) will be the corresponding values.
598The keys will be named tuples if the table has a composite primary key.
599The rows will be also named tuples unless the *scalar* option has been set
600to *True*.  With the optional parameter *keyname* you can specify a different
601set of columns to be used as the keys of the dictionary.
602
603If the Python version supports it, the dictionary will be an *OrderedDict*
604using the order specified with the *order* parameter or the key column(s)
605if not specified.  You can set *order* to *False* if you don't care about the
606ordering.  In this case the returned dictionary will be an ordinary one.
607
608escape_literal/identifier/string/bytea -- escape for SQL
609--------------------------------------------------------
610
611The following methods escape text or binary strings so that they can be
612inserted directly into an SQL command.  Except for :meth:`DB.escape_byte`,
613you don't need to call these methods for the strings passed as parameters
614to :meth:`DB.query`.  You also don't need to call any of these methods
615when storing data using :meth:`DB.insert` and similar.
616
617.. method:: DB.escape_literal(string)
618
619    Escape a string for use within SQL as a literal constant
620
621    :param str string: the string that is to be escaped
622    :returns: the escaped string
623    :rtype: str
624
625This method escapes a string for use within an SQL command. This is useful
626when inserting data values as literal constants in SQL commands. Certain
627characters (such as quotes and backslashes) must be escaped to prevent them
628from being interpreted specially by the SQL parser.
629
630.. versionadded:: 4.1
631
632.. method:: DB.escape_identifier(string)
633
634    Escape a string for use within SQL as an identifier
635
636    :param str string: the string that is to be escaped
637    :returns: the escaped string
638    :rtype: str
639
640This method escapes a string for use as an SQL identifier, such as a table,
641column, or function name. This is useful when a user-supplied identifier
642might contain special characters that would otherwise not be interpreted
643as part of the identifier by the SQL parser, or when the identifier might
644contain upper case characters whose case should be preserved.
645
646.. versionadded:: 4.1
647
648.. method:: DB.escape_string(string)
649
650    Escape a string for use within SQL
651
652    :param str string: the string that is to be escaped
653    :returns: the escaped string
654    :rtype: str
655
656Similar to the module function :func:`pg.escape_string` with the same name,
657but the behavior of this method is adjusted depending on the connection
658properties (such as character encoding).
659
660.. method:: DB.escape_bytea(datastring)
661
662    Escape binary data for use within SQL as type ``bytea``
663
664    :param str datastring: string containing the binary data that is to be escaped
665    :returns: the escaped string
666    :rtype: str
667
668Similar to the module function :func:`pg.escape_bytea` with the same name,
669but the behavior of this method is adjusted depending on the connection
670properties (in particular, whether standard-conforming strings are enabled).
671
672unescape_bytea -- unescape data retrieved from the database
673-----------------------------------------------------------
674
675.. method:: DB.unescape_bytea(string)
676
677    Unescape ``bytea`` data that has been retrieved as text
678
679    :param datastring: the ``bytea`` data string that has been retrieved as text
680    :returns: byte string containing the binary data
681    :rtype: bytes
682
683Converts an escaped string representation of binary data stored as ``bytea``
684into the raw byte string representing the binary data  -- this is the reverse
685of :meth:`DB.escape_bytea`.  Since the :class:`Query` results will already
686return unescaped byte strings, you normally don't have to use this method.
687
688encode/decode_json -- encode and decode JSON data
689-------------------------------------------------
690
691The following methods can be used to encode end decode data in
692`JSON <http://www.json.org/>`_ format.
693
694.. method:: DB.encode_json(obj)
695
696    Encode a Python object for use within SQL as type ``json`` or ``jsonb``
697
698    :param obj: Python object that shall be encoded to JSON format
699    :type obj: dict, list or None
700    :returns: string representation of the Python object in JSON format
701    :rtype: str
702
703This method serializes a Python object into a JSON formatted string that can
704be used within SQL.  You don't need to use this method on the data stored
705with :meth:`DB.insert` and similar, only if you store the data directly as
706part of an SQL command or parameter with :meth:`DB.query`.  This is the same
707as the :func:`json.dumps` function from the standard library.
708
709.. versionadded:: 5.0
710
711.. method:: DB.decode_json(string)
712
713    Decode ``json`` or ``jsonb`` data that has been retrieved as text
714
715    :param string: JSON formatted string shall be decoded into a Python object
716    :type string: str
717    :returns: Python object representing the JSON formatted string
718    :rtype: dict, list or None
719
720This method deserializes a JSON formatted string retrieved as text from the
721database to a Python object.  You normally don't need to use this method as
722JSON data is automatically decoded by PyGreSQL.  If you don't want the data
723to be decoded, then you can cast ``json`` or ``jsonb`` columns to ``text``
724in PostgreSQL or you can set the decoding function to *None* or a different
725function using :func:`pg.set_jsondecode`.  By default this is the same as
726the :func:`json.dumps` function from the standard library.
727
728.. versionadded:: 5.0
729
730use_regtypes -- determine use of regular type names
731---------------------------------------------------
732
733.. method:: DB.use_regtypes([regtypes])
734
735    Determine whether regular type names shall be used
736
737    :param bool regtypes: if passed, set whether regular type names shall be used
738    :returns: whether regular type names are used
739
740The :meth:`DB.get_attnames` method can return either simplified "classic"
741type names (the default) or more specific "regular" type names. Which kind
742of type names is used can be changed by calling :meth:`DB.get_regtypes`.
743If you pass a boolean, it sets whether regular type names shall be used.
744The method can also be used to check through its return value whether
745currently regular type names are used.
746
747.. versionadded:: 4.1
748
749notification_handler -- create a notification handler
750-----------------------------------------------------
751
752.. class:: DB.notification_handler(event, callback, [arg_dict], [timeout], [stop_event])
753
754    Create a notification handler instance
755
756    :param str event: the name of an event to listen for
757    :param callback: a callback function
758    :param dict arg_dict: an optional dictionary for passing arguments
759    :param timeout: the time-out when waiting for notifications
760    :type timeout: int, float or None
761    :param str stop_event: an optional different name to be used as stop event
762
763This method creates a :class:`pg.NotificationHandler` object using the
764:class:`DB` connection as explained under :doc:`notification`.
765
766.. versionadded:: 4.1.1
Note: See TracBrowser for help on using the repository browser.