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

Last change on this file since 781 was 781, checked in by cito, 3 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: 16.5 KB
Line 
1Module functions and constants
2==============================
3
4.. py:currentmodule:: pg
5
6The :mod:`pg` module defines a few functions that allow to connect
7to a database and to define "default variables" that override
8the environment variables used by PostgreSQL.
9
10These "default variables" were designed to allow you to handle general
11connection parameters without heavy code in your programs. You can prompt the
12user for a value, put it in the default variable, and forget it, without
13having to modify your environment. The support for default variables can be
14disabled by setting the ``-DNO_DEF_VAR`` option in the Python setup file.
15Methods relative to this are specified by the tag [DV].
16
17All variables are set to ``None`` at module initialization, specifying that
18standard environment variables should be used.
19
20connect -- Open a PostgreSQL connection
21---------------------------------------
22
23.. function:: connect([dbname], [host], [port], [opt], [user], [passwd])
24
25    Open a :mod:`pg` connection
26
27    :param dbname: name of connected database (*None* = :data:`defbase`)
28    :type str: str or None
29    :param host: name of the server host (*None* = :data:`defhost`)
30    :type host:  str or None
31    :param port: port used by the database server (-1 = :data:`defport`)
32    :type port: int
33    :param opt: connection options (*None* = :data:`defopt`)
34    :type opt: str or None
35    :param user: PostgreSQL user (*None* = :data:`defuser`)
36    :type user: str or None
37    :param passwd: password for user (*None* = :data:`defpasswd`)
38    :type passwd: str or None
39    :returns: If successful, the :class:`Connection` handling the connection
40    :rtype: :class:`Connection`
41    :raises TypeError: bad argument type, or too many arguments
42    :raises SyntaxError: duplicate argument definition
43    :raises pg.InternalError: some error occurred during pg connection definition
44    :raises Exception: (all exceptions relative to object allocation)
45
46This function opens a connection to a specified database on a given
47PostgreSQL server. You can use keywords here, as described in the
48Python tutorial. The names of the keywords are the name of the
49parameters given in the syntax line. For a precise description
50of the parameters, please refer to the PostgreSQL user manual.
51
52Example::
53
54    import pg
55
56    con1 = pg.connect('testdb', 'myhost', 5432, None, None, 'bob', None)
57    con2 = pg.connect(dbname='testdb', host='localhost', user='bob')
58
59get/set_defhost -- default server host [DV]
60-------------------------------------------
61
62.. function:: get_defhost(host)
63
64    Get the default host
65
66    :returns: the current default host specification
67    :rtype: str or None
68    :raises TypeError: too many arguments
69
70This method returns the current default host specification,
71or ``None`` if the environment variables should be used.
72Environment variables won't be looked up.
73
74.. function:: set_defhost(host)
75
76    Set the default host
77
78    :param host: the new default host specification
79    :type host: str or None
80    :returns: the previous default host specification
81    :rtype: str or None
82    :raises TypeError: bad argument type, or too many arguments
83
84This methods sets the default host value for new connections.
85If ``None`` is supplied as parameter, environment variables will
86be used in future connections. It returns the previous setting
87for default host.
88
89get/set_defport -- default server port [DV]
90-------------------------------------------
91
92.. function:: get_defport()
93
94    Get the default port
95
96    :returns: the current default port specification
97    :rtype: int
98    :raises TypeError: too many arguments
99
100This method returns the current default port specification,
101or ``None`` if the environment variables should be used.
102Environment variables won't be looked up.
103
104.. function::  set_defport(port)
105
106    Set the default port
107
108    :param port: the new default port
109    :type port: int
110    :returns: previous default port specification
111    :rtype: int or None
112
113This methods sets the default port value for new connections. If -1 is
114supplied as parameter, environment variables will be used in future
115connections. It returns the previous setting for default port.
116
117get/set_defopt --  default connection options [DV]
118--------------------------------------------------
119
120.. function:: get_defopt()
121
122    Get the default connection options
123
124    :returns: the current default options specification
125    :rtype: str or None
126    :raises TypeError: too many arguments
127
128This method returns the current default connection options specification,
129or ``None`` if the environment variables should be used. Environment variables
130won't be looked up.
131
132.. function:: set_defopt(options)
133
134    Set the default connection options
135
136    :param options: the new default connection options
137    :type options: str or None
138    :returns: previous default options specification
139    :rtype: str or None
140    :raises TypeError: bad argument type, or too many arguments
141
142This methods sets the default connection options value for new connections.
143If ``None`` is supplied as parameter, environment variables will be used in
144future connections. It returns the previous setting for default options.
145
146get/set_defbase -- default database name [DV]
147---------------------------------------------
148
149.. function:: get_defbase()
150
151    Get the default database name
152
153    :returns: the current default database name specification
154    :rtype: str or None
155    :raises TypeError: too many arguments
156
157This method returns the current default database name specification, or
158``None`` if the environment variables should be used. Environment variables
159won't be looked up.
160
161.. function:: set_defbase(base)
162
163    Set the default database name
164
165    :param base: the new default base name
166    :type base: str or None
167    :returns: the previous default database name specification
168    :rtype: str or None
169    :raises TypeError: bad argument type, or too many arguments
170
171This method sets the default database name value for new connections. If
172``None`` is supplied as parameter, environment variables will be used in
173future connections. It returns the previous setting for default host.
174
175get/set_defuser -- default database user [DV]
176---------------------------------------------
177
178.. function:: get_defuser()
179
180    Get the default database user
181
182    :returns: the current default database user specification
183    :rtype: str or None
184    :raises TypeError: too many arguments
185
186This method returns the current default database user specification, or
187``None`` if the environment variables should be used. Environment variables
188won't be looked up.
189
190.. function:: set_defuser(user)
191
192    Set the default database user
193
194    :param user: the new default database user
195    :type base: str or None
196    :returns: the previous default database user specification
197    :rtype: str or None
198    :raises TypeError: bad argument type, or too many arguments
199
200This method sets the default database user name for new connections. If
201``None`` is supplied as parameter, environment variables will be used in
202future connections. It returns the previous setting for default host.
203
204get/set_defpasswd -- default database password [DV]
205---------------------------------------------------
206
207.. function:: get_defpasswd()
208
209    Get the default database password
210
211    :returns: the current default database password specification
212    :rtype: str or None
213    :raises TypeError: too many arguments
214
215This method returns the current default database password specification, or
216``None`` if the environment variables should be used. Environment variables
217won't be looked up.
218
219.. function:: set_defpasswd(passwd)
220
221    Set the default database password
222
223    :param passwd: the new default database password
224    :type base: str or None
225    :returns: the previous default database password specification
226    :rtype: str or None
227    :raises TypeError: bad argument type, or too many arguments
228
229This method sets the default database password for new connections. If
230``None`` is supplied as parameter, environment variables will be used in
231future connections. It returns the previous setting for default host.
232
233escape_string -- escape a string for use within SQL
234---------------------------------------------------
235
236.. function:: escape_string(string)
237
238    Escape a string for use within SQL
239
240    :param str string: the string that is to be escaped
241    :returns: the escaped string
242    :rtype: str
243    :raises TypeError: bad argument type, or too many arguments
244
245This function escapes a string for use within an SQL command.
246This is useful when inserting data values as literal constants
247in SQL commands. Certain characters (such as quotes and backslashes)
248must be escaped to prevent them from being interpreted specially
249by the SQL parser. :func:`escape_string` performs this operation.
250Note that there is also a :class:`Connection` method with the same name
251which takes connection properties into account.
252
253.. note::
254
255   It is especially important to do proper escaping when
256   handling strings that were received from an untrustworthy source.
257   Otherwise there is a security risk: you are vulnerable to "SQL injection"
258   attacks wherein unwanted SQL commands are fed to your database.
259
260Example::
261
262    name = input("Name? ")
263    phone = con.query("select phone from employees where name='%s'"
264        % escape_string(name)).getresult()
265
266escape_bytea -- escape binary data for use within SQL
267-----------------------------------------------------
268
269.. function:: escape_bytea(datastring)
270
271    escape binary data for use within SQL as type ``bytea``
272
273    :param str datastring: string containing the binary data that is to be escaped
274    :returns: the escaped string
275    :rtype: str
276    :raises TypeError: bad argument type, or too many arguments
277
278Escapes binary data for use within an SQL command with the type ``bytea``.
279As with :func:`escape_string`, this is only used when inserting data directly
280into an SQL command string.
281
282Note that there is also a :class:`Connection` method with the same name
283which takes connection properties into account.
284
285Example::
286
287    picture = open('garfield.gif', 'rb').read()
288    con.query("update pictures set img='%s' where name='Garfield'"
289        % escape_bytea(picture))
290
291unescape_bytea -- unescape data that has been retrieved as text
292---------------------------------------------------------------
293
294.. function:: unescape_bytea(string)
295
296    Unescape ``bytea`` data that has been retrieved as text
297
298    :param str datastring: the ``bytea`` data string that has been retrieved as text
299    :returns: byte string containing the binary data
300    :rtype: bytes
301    :raises TypeError: bad argument type, or too many arguments
302
303Converts an escaped string representation of binary data stored as ``bytea``
304into the raw byte string representing the binary data  -- this is the reverse
305of :func:`escape_bytea`.  Since the :class:`Query` results will already
306return unescaped byte strings, you normally don't have to use this method.
307
308Note that there is also a :class:`DB` method with the same name
309which does exactly the same.
310
311get/set_decimal -- decimal type to be used for numeric values
312-------------------------------------------------------------
313
314.. function:: get_decimal()
315
316    Get the decimal type to be used for numeric values
317
318    :returns: the Python class used for PostgreSQL numeric values
319    :rtype: class
320
321This function returns the Python class that is used by PyGreSQL to hold
322PostgreSQL numeric values. The default class is :class:`decimal.Decimal`
323if available, otherwise the :class:`float` type is used.
324
325.. function:: set_decimal(cls)
326
327    Set a decimal type to be used for numeric values
328
329    :param class cls: the Python class to be used for PostgreSQL numeric values
330
331This function can be used to specify the Python class that shall
332be used by PyGreSQL to hold PostgreSQL numeric values.
333The default class is :class:`decimal.Decimal` if available,
334otherwise the :class:`float` type is used.
335
336get/set_decimal_point -- decimal mark used for monetary values
337--------------------------------------------------------------
338
339.. function:: get_decimal_point()
340
341    Get the decimal mark used for monetary values
342
343    :returns: string with one character representing the decimal mark
344    :rtype: str
345
346This function returns the decimal mark used by PyGreSQL to interpret
347PostgreSQL monetary values when converting them to decimal numbers.
348The default setting is ``'.'`` as a decimal point. This setting is not
349adapted automatically to the locale used by PostGreSQL, but you can
350use ``set_decimal()`` to set a different decimal mark manually. A return
351value of ``None`` means monetary values are not interpreted as decimal
352numbers, but returned as strings including the formatting and currency.
353
354.. versionadded:: 4.1.1
355
356.. function:: set_decimal_point(string)
357
358    Specify which decimal mark is used for interpreting monetary values
359
360    :param str string: string with one character representing the decimal mark
361
362This function can be used to specify the decimal mark used by PyGreSQL
363to interpret PostgreSQL monetary values. The default value is '.' as
364a decimal point. This value is not adapted automatically to the locale
365used by PostGreSQL, so if you are dealing with a database set to a
366locale that uses a ``','`` instead of ``'.'`` as the decimal point,
367then you need to call ``set_decimal(',')`` to have PyGreSQL interpret
368monetary values correctly. If you don't want money values to be converted
369to decimal numbers, then you can call ``set_decimal(None)``, which will
370cause PyGreSQL to return monetary values as strings including their
371formatting and currency.
372
373.. versionadded:: 4.1.1
374
375get/set_bool -- whether boolean values are returned as bool objects
376-------------------------------------------------------------------
377
378.. function:: get_bool()
379
380    Check whether boolean values are returned as bool objects
381
382    :returns: whether or not bool objects will be returned
383    :rtype: bool
384
385This function checks whether PyGreSQL returns PostgreSQL boolean
386values converted to Python bool objects, or as ``'f'`` and ``'t'``
387strings which are the values used internally by PostgreSQL. By default,
388conversion to bool objects is not activated, but you can enable
389this with the ``set_bool()`` method.
390
391.. versionadded:: 4.2
392
393.. function:: set_bool(on)
394
395    Set whether boolean values are returned as bool objects
396
397    :param on: whether or not bool objects shall be returned
398
399This function can be used to specify whether PyGreSQL shall return
400PostgreSQL boolean values converted to Python bool objects, or as
401``'f'`` and ``'t'`` strings which are the values used internally by PostgreSQL.
402By default, conversion to bool objects is not activated, but you can
403enable this by calling ``set_bool(True)``.
404
405.. versionadded:: 4.2
406
407get/set_namedresult -- conversion to named tuples
408-------------------------------------------------
409
410.. function:: get_namedresult()
411
412    Get the function that converts to named tuples
413
414This returns the function used by PyGreSQL to construct the result of the
415:meth:`Query.namedresult` method.
416
417.. versionadded:: 4.1
418
419.. function:: set_namedresult(func)
420
421    Set a function that will convert to named tuples
422
423    :param func: the function to be used to convert results to named tuples
424
425You can use this if you want to create different kinds of named tuples
426returned by the :meth:`Query.namedresult` method.  If you set this function
427to *None*, then it will become equal to :meth:`Query.getresult`.
428
429.. versionadded:: 4.1
430
431get/set_jsondecode -- decoding JSON format
432------------------------------------------
433
434.. function:: get_jsondecode()
435
436    Get the function that deserializes JSON formatted strings
437
438This returns the function used by PyGreSQL to construct Python objects
439from JSON formatted strings.
440
441.. function:: set_jsondecode(func)
442
443    Set a function that will deserialize JSON formatted strings
444
445    :param func: the function to be used for deserializing JSON strings
446
447You can use this if you do not want to deserialize JSON strings coming
448in from the database, or if want to use a different function than the
449standard function :meth:`json.loads` or if you want to use it with parameters
450different from the default ones.  If you set this function to *None*, then
451the automatic deserialization of JSON strings will be deactivated.
452
453.. versionadded:: 5.0
454
455
456Module constants
457----------------
458Some constants are defined in the module dictionary.
459They are intended to be used as parameters for methods calls.
460You should refer to the libpq description in the PostgreSQL user manual
461for more information about them. These constants are:
462
463.. data:: version, __version__
464
465    constants that give the current version
466
467.. data:: INV_READ, INV_WRITE
468
469    large objects access modes,
470    used by :meth:`Connection.locreate` and :meth:`LargeObject.open`
471
472.. data:: SEEK_SET, SEEK_CUR, SEEK_END:
473
474    positional flags, used by :meth:`LargeObject.seek`
Note: See TracBrowser for help on using the repository browser.