source: branches/4.x/docs/pg.rst @ 680

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

Remove the deprecated tty parameter and attribute

This parameter has been ignored by PostgreSQL since version 7.4.

  • Property svn:keywords set to Author Date Id Revision
File size: 49.6 KB
Line 
1--------------------------------------------
2:mod:`pg` --- The Classic PyGreSQL Interface
3--------------------------------------------
4
5.. module:: pg
6
7.. contents:: Contents
8
9
10Introduction
11============
12
13You may either choose to use the "classic" PyGreSQL interface
14provided by the :mod:`pg` module or else the
15DB-API 2.0 compliant interface provided by the :mod:`pgdb` module.
16
17The following part of the documentation covers only the older :mod:`pg` API.
18
19The :mod:`pg` module handles three types of objects,
20
21- the :class:`pgobject`, which handles the connection
22  and all the requests to the database,
23- the :class:`pglarge` object, which handles
24  all the accesses to PostgreSQL large objects,
25- the :class:`pgqueryobject` that handles query results
26
27and it provides a convenient wrapper class :class:`DB`
28for the :class:`pgobject`.
29
30If you want to see a simple example of the use of some of these functions,
31see the :doc:`examples` page.
32
33
34Module functions and constants
35==============================
36
37The :mod:`pg` module defines a few functions that allow to connect
38to a database and to define "default variables" that override
39the environment variables used by PostgreSQL.
40
41These "default variables" were designed to allow you to handle general
42connection parameters without heavy code in your programs. You can prompt the
43user for a value, put it in the default variable, and forget it, without
44having to modify your environment. The support for default variables can be
45disabled by setting the ``-DNO_DEF_VAR`` option in the Python setup file.
46Methods relative to this are specified by the tag [DV].
47
48All variables are set to ``None`` at module initialization, specifying that
49standard environment variables should be used.
50
51connect -- Open a PostgreSQL connection
52---------------------------------------
53
54.. function:: connect([dbname], [host], [port], [opt], [tty], [user], [passwd])
55
56    Open a :mod:`pg` connection
57
58    :param dbname: name of connected database (*None* = :data:`defbase`)
59    :type str: str or None
60    :param host: name of the server host (*None* = :data:`defhost`)
61    :type host:  str or None
62    :param port: port used by the database server (-1 = :data:`defport`)
63    :type port: int
64    :param opt: connection options (*None* = :data:`defopt`)
65    :type opt: str or None
66    :param tty: debug terminal (*None* = :data:`deftty`)
67    :type tty: str or None
68    :param user: PostgreSQL user (*None* = :data:`defuser`)
69    :type user: str or None
70    :param passwd: password for user (*None* = :data:`defpasswd`)
71    :type passwd: str or None
72    :returns: If successful, the :class:`pgobject` handling the connection
73    :rtype: :class:`pgobject`
74    :raises TypeError: bad argument type, or too many arguments
75    :raises SyntaxError: duplicate argument definition
76    :raises pg.InternalError: some error occurred during pg connection definition
77    :raises Exception: (all exceptions relative to object allocation)
78
79This function opens a connection to a specified database on a given
80PostgreSQL server. You can use keywords here, as described in the
81Python tutorial. The names of the keywords are the name of the
82parameters given in the syntax line. For a precise description
83of the parameters, please refer to the PostgreSQL user manual.
84
85Example::
86
87    import pg
88
89    con1 = pg.connect('testdb', 'myhost', 5432, None, None, 'bob', None)
90    con2 = pg.connect(dbname='testdb', host='localhost', user='bob')
91
92get/set_defhost -- default server host [DV]
93-------------------------------------------
94
95.. function:: get_defhost(host)
96
97    Get the default host
98
99    :returns: the current default host specification
100    :rtype: str or None
101    :raises TypeError: too many arguments
102
103This method returns the current default host specification,
104or ``None`` if the environment variables should be used.
105Environment variables won't be looked up.
106
107.. function:: set_defhost(host)
108
109    Set the default host
110
111    :param host: the new default host specification
112    :type host: str or None
113    :returns: the previous default host specification
114    :rtype: str or None
115    :raises TypeError: bad argument type, or too many arguments
116
117This methods sets the default host value for new connections.
118If ``None`` is supplied as parameter, environment variables will
119be used in future connections. It returns the previous setting
120for default host.
121
122get/set_defport -- default server port [DV]
123-------------------------------------------
124
125.. function:: get_defport()
126
127    Get the default port
128
129    :returns: the current default port specification
130    :rtype: int
131    :raises TypeError: too many arguments
132
133This method returns the current default port specification,
134or ``None`` if the environment variables should be used.
135Environment variables won't be looked up.
136
137.. function::  set_defport(port)
138
139    Set the default port
140
141    :param port: the new default port
142    :type port: int
143    :returns: previous default port specification
144    :rtype: int or None
145
146This methods sets the default port value for new connections. If -1 is
147supplied as parameter, environment variables will be used in future
148connections. It returns the previous setting for default port.
149
150get/set_defopt --  default connection options [DV]
151--------------------------------------------------
152
153.. function:: get_defopt()
154
155    Get the default connection options
156
157    :returns: the current default options specification
158    :rtype: str or None
159    :raises TypeError: too many arguments
160
161This method returns the current default connection options specification,
162or ``None`` if the environment variables should be used. Environment variables
163won't be looked up.
164
165.. function:: set_defopt(options)
166
167    Set the default connection options
168
169    :param options: the new default connection options
170    :type options: str or None
171    :returns: previous default options specification
172    :rtype: str or None
173    :raises TypeError: bad argument type, or too many arguments
174
175This methods sets the default connection options value for new connections.
176If ``None`` is supplied as parameter, environment variables will be used in
177future connections. It returns the previous setting for default options.
178
179get/set_deftty -- default debug tty [DV]
180----------------------------------------
181
182.. function:: get_deftty()
183
184    Get the default debug terminal
185
186    :returns: the current default debug terminal specification
187    :rtype: str or None
188    :raises TypeError: too many arguments
189
190This method returns the current default debug terminal specification, or
191``None`` if the environment variables should be used. Environment variables
192won't be looked up. Note that this is ignored in newer PostgreSQL versions.
193
194.. function:: set_deftty(terminal)
195
196    Set the default debug terminal
197
198    :param terminal: the new default debug terminal
199    :type terminal: str or None
200    :returns: the previous default debug terminal specification
201    :rtype: str or None
202    :raises TypeError: bad argument type, or too many arguments
203
204This methods sets the default debug terminal value for new connections.
205If ``None`` is supplied as parameter, environment variables will be used
206in future connections. It returns the previous setting for default terminal.
207Note that this is ignored in newer PostgreSQL versions.
208
209get/set_defbase -- default database name [DV]
210---------------------------------------------
211
212.. function:: get_defbase()
213
214    Get the default database name
215
216    :returns: the current default database name specification
217    :rtype: str or None
218    :raises TypeError: too many arguments
219
220This method returns the current default database name specification, or
221``None`` if the environment variables should be used. Environment variables
222won't be looked up.
223
224.. function:: set_defbase(base)
225
226    Set the default database name
227
228    :param base: the new default base name
229    :type base: str or None
230    :returns: the previous default database name specification
231    :rtype: str or None
232    :raises TypeError: bad argument type, or too many arguments
233
234This method sets the default database name value for new connections. If
235``None`` is supplied as parameter, environment variables will be used in
236future connections. It returns the previous setting for default host.
237
238get/set_defuser -- default database user [DV]
239---------------------------------------------
240
241.. function:: get_defuser()
242
243    Get the default database user
244
245    :returns: the current default database user specification
246    :rtype: str or None
247    :raises TypeError: too many arguments
248
249This method returns the current default database user specification, or
250``None`` if the environment variables should be used. Environment variables
251won't be looked up.
252
253.. function:: set_defuser(user)
254
255    Set the default database user
256
257    :param user: the new default database user
258    :type base: str or None
259    :returns: the previous default database user specification
260    :rtype: str or None
261    :raises TypeError: bad argument type, or too many arguments
262
263This method sets the default database user name for new connections. If
264``None`` is supplied as parameter, environment variables will be used in
265future connections. It returns the previous setting for default host.
266
267get/set_defpasswd -- default database password [DV]
268---------------------------------------------------
269
270.. function:: get_defpasswd()
271
272    Get the default database password
273
274    :returns: the current default database password specification
275    :rtype: str or None
276    :raises TypeError: too many arguments
277
278This method returns the current default database password specification, or
279``None`` if the environment variables should be used. Environment variables
280won't be looked up.
281
282.. function:: set_defpasswd(passwd)
283
284    Set the default database password
285
286    :param passwd: the new default database password
287    :type base: str or None
288    :returns: the previous default database password specification
289    :rtype: str or None
290    :raises TypeError: bad argument type, or too many arguments
291
292This method sets the default database password for new connections. If
293``None`` is supplied as parameter, environment variables will be used in
294future connections. It returns the previous setting for default host.
295
296escape_string -- escape a string for use within SQL
297---------------------------------------------------
298
299.. function:: escape_string(string)
300
301    Escape a string for use within SQL
302
303    :param str string: the string that is to be escaped
304    :returns: the escaped string
305    :rtype: str
306    :raises TypeError: bad argument type, or too many arguments
307
308This function escapes a string for use within an SQL command.
309This is useful when inserting data values as literal constants
310in SQL commands. Certain characters (such as quotes and backslashes)
311must be escaped to prevent them from being interpreted specially
312by the SQL parser. :func:`escape_string` performs this operation.
313Note that there is also a :class:`pgobject` method with the same name
314which takes connection properties into account.
315
316.. note::
317
318   It is especially important to do proper escaping when
319   handling strings that were received from an untrustworthy source.
320   Otherwise there is a security risk: you are vulnerable to "SQL injection"
321   attacks wherein unwanted SQL commands are fed to your database.
322
323Example::
324
325    name = raw_input("Name? ")
326    phone = con.query("select phone from employees where name='%s'"
327        % escape_string(name)).getresult()
328
329escape_bytea -- escape binary data for use within SQL
330-----------------------------------------------------
331
332.. function:: escape_bytea(datastring)
333
334    escape binary data for use within SQL as type ``bytea``
335
336    :param str datastring: string containing the binary data that is to be escaped
337    :returns: the escaped string
338    :rtype: str
339    :raises TypeError: bad argument type, or too many arguments
340
341Escapes binary data for use within an SQL command with the type ``bytea``.
342As with :func:`escape_string`, this is only used when inserting data directly
343into an SQL command string.
344Note that there is also a :class:`pgobject` method with the same name
345which takes connection properties into account.
346
347Example::
348
349    picture = open('garfield.gif', 'rb').read()
350    con.query("update pictures set img='%s' where name='Garfield'"
351        % escape_bytea(picture))
352
353unescape_bytea -- unescape data that has been retrieved as text
354---------------------------------------------------------------
355
356.. function:: unescape_bytea(string)
357
358    Unescape ``bytea`` data that has been retrieved as text
359
360    :param str datastring: the ``bytea`` data string that has been retrieved as text
361    :returns: byte string containing the binary data
362    :rtype: str
363    :raises TypeError: bad argument type, or too many arguments
364
365Converts an escaped string representation of binary data into binary
366data -- the reverse of :func:`escape_bytea`. This is needed when retrieving
367``bytea`` data with one of the :meth:`pgqueryobject.getresult`,
368:meth:`pgqueryobject.dictresult` or :meth:`pgqueryobject.namedresult` methods.
369
370Example::
371
372    picture = unescape_bytea(con.query(
373          "select img from pictures where name='Garfield'").getresult[0][0])
374    open('garfield.gif', 'wb').write(picture)
375
376get/set_decimal -- decimal type to be used for numeric values
377-------------------------------------------------------------
378
379.. function:: get_decimal()
380
381    Get the decimal type to be used for numeric values
382
383    :returns: the Python class used for PostgreSQL numeric values
384    :rtype: class
385
386This function returns the Python class that is used by PyGreSQL to hold
387PostgreSQL numeric values. The default class is :class:`decimal.Decimal`
388if available, otherwise the :class:`float` type is used.
389
390.. function:: set_decimal(cls)
391
392    Set a decimal type to be used for numeric values
393
394    :param class cls: the Python class to be used for PostgreSQL numeric values
395
396This function can be used to specify the Python class that shall
397be used by PyGreSQL to hold PostgreSQL numeric values.
398The default class is :class:`decimal.Decimal` if available,
399otherwise the :class:`float` type is used.
400
401get/set_decimal_point -- decimal mark used for monetary values
402--------------------------------------------------------------
403
404.. function:: get_decimal_point()
405
406    Get the decimal mark used for monetary values
407
408    :returns: string with one character representing the decimal mark
409    :rtype: str
410
411This function returns the decimal mark used by PyGreSQL to interpret
412PostgreSQL monetary values when converting them to decimal numbers.
413The default setting is ``'.'`` as a decimal point. This setting is not
414adapted automatically to the locale used by PostGreSQL, but you can
415use ``set_decimal()`` to set a different decimal mark manually. A return
416value of ``None`` means monetary values are not interpreted as decimal
417numbers, but returned as strings including the formatting and currency.
418
419.. function:: set_decimal_point(string)
420
421    Specify which decimal mark is used for interpreting monetary values
422
423    :param str string: string with one character representing the decimal mark
424
425This function can be used to specify the decimal mark used by PyGreSQL
426to interpret PostgreSQL monetary values. The default value is '.' as
427a decimal point. This value is not adapted automatically to the locale
428used by PostGreSQL, so if you are dealing with a database set to a
429locale that uses a ``','`` instead of ``'.'`` as the decimal point,
430then you need to call ``set_decimal(',')`` to have PyGreSQL interpret
431monetary values correctly. If you don't want money values to be converted
432to decimal numbers, then you can call ``set_decimal(None)``, which will
433cause PyGreSQL to return monetary values as strings including their
434formatting and currency.
435
436get/set_bool -- whether boolean values are returned as bool objects
437-------------------------------------------------------------------
438
439.. function:: get_bool()
440
441    Check whether boolean values are returned as bool objects
442
443    :returns: whether or not bool objects will be returned
444    :rtype: bool
445
446This function checks whether PyGreSQL returns PostgreSQL boolean
447values converted to Python bool objects, or as ``'f'`` and ``'t'``
448strings which are the values used internally by PostgreSQL. By default,
449conversion to bool objects is not activated, but you can enable
450this with the ``set_bool()`` method.
451
452.. function:: set_bool(on)
453
454    Set whether boolean values are returned as bool objects
455
456    :param on: whether or not bool objects shall be returned
457
458This function can be used to specify whether PyGreSQL shall return
459PostgreSQL boolean values converted to Python bool objects, or as
460``'f'`` and ``'t'`` strings which are the values used internally by PostgreSQL.
461By default, conversion to bool objects is not activated, but you can
462enable this by calling ``set_bool(True)``.
463
464get/set_namedresult -- conversion to named tuples
465-------------------------------------------------
466
467.. function:: get_namedresult()
468
469    Get the function that converts to named tuples
470
471This function returns the function used by PyGreSQL to construct the
472result of the :meth:`pgqueryobject.namedresult` method.
473
474.. function:: set_namedresult(func)
475
476    Set a function that will convert to named tuples
477
478    :param func: the function to be used to convert results to named tuples
479
480You can use this if you want to create different kinds of named tuples
481returned by the :meth:`pgqueryobject.namedresult` method.
482
483
484Module constants
485----------------
486Some constants are defined in the module dictionary.
487They are intended to be used as parameters for methods calls.
488You should refer to the libpq description in the PostgreSQL user manual
489for more information about them. These constants are:
490
491.. data:: version, __version__
492
493    constants that give the current version
494
495.. data:: INV_READ, INV_WRITE
496
497    large objects access modes,
498    used by :meth:`pgobject.locreate` and :meth:`pglarge.open`
499
500.. data:: SEEK_SET, SEEK_CUR, SEEK_END:
501
502    positional flags, used by :meth:`pglarge.seek`
503
504
505pgobject -- The connection object
506=================================
507
508.. class:: pgobject
509
510This object handles a connection to a PostgreSQL database. It embeds and
511hides all the parameters that define this connection, thus just leaving really
512significant parameters in function calls.
513
514.. note::
515
516    Some methods give direct access to the connection socket.
517    *Do not use them unless you really know what you are doing.*
518    If you prefer disabling them,
519    set the ``-DNO_DIRECT`` option in the Python setup file.
520    These methods are specified by the tag [DA].
521
522.. note::
523
524    Some other methods give access to large objects
525    (refer to PostgreSQL user manual for more information about these).
526    If you want to forbid access to these from the module,
527    set the ``-DNO_LARGE`` option in the Python setup file.
528    These methods are specified by the tag [LO].
529
530query -- execute a SQL command string
531-------------------------------------
532
533.. method:: pgobject.query(command, [args])
534
535    Execute a SQL command string
536
537    :param str command: SQL command
538    :param args: optional positional arguments
539    :returns: result values
540    :rtype: :class:`pgqueryobject`, None
541    :raises TypeError: bad argument type, or too many arguments
542    :raises TypeError: invalid connection
543    :raises ValueError: empty SQL query or lost connection
544    :raises pg.ProgrammingError: error in query
545    :raises pg.InternalError: error during query processing
546
547This method simply sends a SQL query to the database. If the query is an
548insert statement that inserted exactly one row into a table that has OIDs, the
549return value is the OID of the newly inserted row. If the query is an update
550or delete statement, or an insert statement that did not insert exactly one
551row in a table with OIDs, then the number of rows affected is returned as a
552string. If it is a statement that returns rows as a result (usually a select
553statement, but maybe also an ``"insert/update ... returning"`` statement),
554this method returns a :class:`pgqueryobject` that can be accessed via the
555:meth:`pgqueryobject.getresult`, :meth:`pgqueryobject.dictresult` or
556:meth:`pgqueryobject.namedresult` methods or simply printed.
557Otherwise, it returns ``None``.
558
559The query may optionally contain positional parameters of the form ``$1``,
560``$2``, etc instead of literal data, and the values supplied as a tuple.
561The values are substituted by the database in such a way that they don't
562need to be escaped, making this an effective way to pass arbitrary or
563unknown data without worrying about SQL injection or syntax errors.
564
565When the database could not process the query, a :exc:`pg.ProgrammingError` or
566a :exc:`pg.InternalError` is raised. You can check the ``SQLSTATE`` code of
567this error by reading its :attr:`sqlstate` attribute.
568
569Example::
570
571    name = raw_input("Name? ")
572    phone = con.query("select phone from employees where name=$1",
573        (name,)).getresult()
574
575reset -- reset the connection
576-----------------------------
577
578.. method:: pgobject.reset()
579
580    Reset the :mod:`pg` connection
581
582    :rtype: None
583    :raises TypeError: too many (any) arguments
584    :raises TypeError: invalid connection
585
586This method resets the current database connection.
587
588cancel -- abandon processing of current SQL command
589---------------------------------------------------
590
591.. method:: pgobject.cancel()
592
593    :rtype: None
594    :raises TypeError: too many (any) arguments
595    :raises TypeError: invalid connection
596
597This method requests that the server abandon processing
598of the current SQL command.
599
600close -- close the database connection
601--------------------------------------
602
603.. method:: pgobject.close()
604
605    Close the :mod:`pg` connection
606
607    :rtype: None
608    :raises TypeError: too many (any) arguments
609
610This method closes the database connection. The connection will
611be closed in any case when the connection is deleted but this
612allows you to explicitly close it. It is mainly here to allow
613the DB-SIG API wrapper to implement a close function.
614
615fileno -- returns the socket used to connect to the database
616------------------------------------------------------------
617
618.. method:: pgobject.fileno()
619
620    Return the socket used to connect to the database
621
622    :returns: the socket id of the database connection
623    :rtype: int
624    :raises TypeError: too many (any) arguments
625    :raises TypeError: invalid connection
626
627This method returns the underlying socket id used to connect
628to the database. This is useful for use in select calls, etc.
629
630getnotify -- get the last notify from the server
631------------------------------------------------
632
633.. method:: pgobject.getnotify()
634
635    Get the last notify from the server
636
637    :returns: last notify from server
638    :rtype: tuple, None
639    :raises TypeError: too many parameters
640    :raises TypeError: invalid connection
641
642This method tries to get a notify from the server (from the SQL statement
643NOTIFY). If the server returns no notify, the methods returns None.
644Otherwise, it returns a tuple (triplet) *(relname, pid, extra)*, where
645*relname* is the name of the notify, *pid* is the process id of the
646connection that triggered the notify, and *extra* is a payload string
647that has been sent with the notification. Remember to do a listen query
648first, otherwise :meth:`pgobject.getnotify` will always return ``None``.
649
650inserttable -- insert a list into a table
651-----------------------------------------
652
653.. method:: pgobject.inserttable(table, values)
654
655    Insert a Python list into a database table
656
657    :param str table: the table name
658    :param list values: list of rows values
659    :rtype: None
660    :raises TypeError: invalid connection, bad argument type, or too many arguments
661    :raises MemoryError: insert buffer could not be allocated
662    :raises ValueError: unsupported values
663
664This method allows to *quickly* insert large blocks of data in a table:
665It inserts the whole values list into the given table. Internally, it
666uses the COPY command of the PostgreSQL database. The list is a list
667of tuples/lists that define the values for each inserted row. The rows
668values may contain string, integer, long or double (real) values.
669
670.. note::
671
672   **Be very careful**:
673   This method doesn't type check the fields according to the table definition;
674   it just look whether or not it knows how to handle such types.
675
676get/set_notice_receiver -- custom notice receiver
677-------------------------------------------------
678
679.. method:: pgobject.get_notice_receiver()
680
681    Get the current notice receiver
682
683    :returns: the current notice receiver callable
684    :rtype: callable, None
685    :raises TypeError: too many (any) arguments
686
687This method gets the custom notice receiver callback function that has
688been set with :meth:`pgobject.set_notice_receiver`, or ``None`` if no
689custom notice receiver has ever been set on the connection.
690
691.. method:: pgobject.set_notice_receiver(proc)
692
693    Set a custom notice receiver
694
695    :param proc: the custom notice receiver callback function
696    :rtype: None
697    :raises TypeError: the specified notice receiver is not callable
698
699This method allows setting a custom notice receiver callback function.
700When a notice or warning message is received from the server,
701or generated internally by libpq, and the message level is below
702the one set with ``client_min_messages``, the specified notice receiver
703function will be called. This function must take one parameter,
704the :class:`pgnotice` object, which provides the following read-only
705attributes:
706
707    .. attribute:: pgnotice.pgcnx
708
709        the connection
710
711    .. attribute:: pgnotice.message
712
713        the full message with a trailing newline
714
715    .. attribute:: pgnotice.severity
716
717        the level of the message, e.g. 'NOTICE' or 'WARNING'
718
719    .. attribute:: pgnotice.primary
720
721        the primary human-readable error message
722
723    .. attribute:: pgnotice.detail
724
725        an optional secondary error message
726
727    .. attribute:: pgnotice.hint
728
729        an optional suggestion what to do about the problem
730
731putline -- write a line to the server socket [DA]
732-------------------------------------------------
733
734.. method:: pgobject.putline(line)
735
736    Write a line to the server socket
737
738    :param str line: line to be written
739    :rtype: None
740    :raises TypeError: invalid connection, bad parameter type, or too many parameters
741
742This method allows to directly write a string to the server socket.
743
744getline -- get a line from server socket [DA]
745---------------------------------------------
746
747.. method:: pgobject.getline()
748
749    Get a line from server socket
750
751    :returns:  the line read
752    :rtype: str
753    :raises TypeError: invalid connection
754    :raises TypeError: too many parameters
755    :raises MemoryError: buffer overflow
756
757This method allows to directly read a string from the server socket.
758
759endcopy -- synchronize client and server [DA]
760---------------------------------------------
761
762.. method:: pgobject.endcopy()
763
764    Synchronize client and server
765
766    :rtype: None
767    :raises TypeError: invalid connection
768    :raises TypeError: too many parameters
769
770The use of direct access methods may desynchronize client and server.
771This method ensure that client and server will be synchronized.
772
773locreate -- create a large object in the database [LO]
774------------------------------------------------------
775
776.. method:: pgobject.locreate(mode)
777
778    Create a large object in the database
779
780    :param int mode: large object create mode
781    :returns: object handling the PostGreSQL large object
782    :rtype: :class:`pglarge`
783    :raises TypeError: invalid connection, bad parameter type, or too many parameters
784    :raises pg.OperationalError: creation error
785
786This method creates a large object in the database. The mode can be defined
787by OR-ing the constants defined in the :mod:`pg` module (:const:`INV_READ`,
788:const:`INV_WRITE` and :const:`INV_ARCHIVE`). Please refer to PostgreSQL
789user manual for a description of the mode values.
790
791getlo -- build a large object from given oid [LO]
792-------------------------------------------------
793
794.. method:: pgobject.getlo(oid)
795
796    Create a large object in the database
797
798    :param int oid: OID of the existing large object
799    :returns: object handling the PostGreSQL large object
800    :rtype: :class:`pglarge`
801    :raises TypeError:  invalid connection, bad parameter type, or too many parameters
802    :raises ValueError: bad OID value (0 is invalid_oid)
803
804This method allows to reuse a formerly created large object through the
805:class:`pglarge` interface, providing the user have its OID.
806
807loimport -- import a file to a large object [LO]
808------------------------------------------------
809
810.. method:: pgobject.loimport(name)
811
812    Import a file to a large object
813
814    :param str name: the name of the file to be imported
815    :returns: object handling the PostGreSQL large object
816    :rtype: :class:`pglarge`
817    :raises TypeError: invalid connection, bad argument type, or too many arguments
818    :raises pg.OperationalError: error during file import
819
820This methods allows to create large objects in a very simple way. You just
821give the name of a file containing the data to be used.
822
823Object attributes
824-----------------
825Every :class:`pgobject` defines a set of read-only attributes that describe
826the connection and its status. These attributes are:
827
828.. attribute:: pgobject.host
829
830   the host name of the server (str)
831
832.. attribute:: pgobject.port
833
834   the port of the server (int)
835
836.. attribute:: pgobject.db
837
838   the selected database (str)
839
840.. attribute:: pgobject.options
841
842   the connection options (str)
843
844.. attribute:: pgobject.tty
845
846   the connection debug terminal (str)
847
848.. attribute:: pgobject.user
849
850    user name on the database system (str)
851
852.. attribute:: pgobject.protocol_version
853
854   the frontend/backend protocol being used (int)
855
856.. attribute:: pgobject.server_version
857
858   the backend version (int, e.g. 80305 for 8.3.5)
859
860.. attribute:: pgobject.status
861
862   the status of the connection (int: 1 = OK, 0 = bad)
863
864.. attribute:: pgobject.error
865
866   the last warning/error message from the server (str)
867
868
869The DB wrapper class
870====================
871
872.. class:: DB
873
874The :class:`pgobject` methods are wrapped in the class :class:`DB`.
875The preferred way to use this module is as follows::
876
877    import pg
878
879    db = pg.DB(...)  # see below
880
881    for r in db.query(  # just for example
882        """SELECT foo,bar
883         FROM foo_bar_table
884         WHERE foo !~ bar"""
885        ).dictresult():
886
887        print '%(foo)s %(bar)s' % r
888
889This class can be subclassed as in this example::
890
891    import pg
892
893    class DB_ride(pg.DB):
894        """Ride database wrapper
895
896        This class encapsulates the database functions and the specific
897        methods for the ride database."""
898
899    def __init__(self):
900        """Open a database connection to the rides database"""
901        pg.DB.__init__(self, dbname='ride')
902        self.query("SET DATESTYLE TO 'ISO'")
903
904    [Add or override methods here]
905
906The following describes the methods and variables of this class.
907
908Initialization
909--------------
910The :class:`DB` class is initialized with the same arguments as the
911:func:`connect` function described above. It also initializes a few
912internal variables. The statement ``db = DB()`` will open the local
913database with the name of the user just like ``connect()`` does.
914
915You can also initialize the DB class with an existing :mod:`pg` or :mod:`pgdb`
916connection. Pass this connection as a single unnamed parameter, or as a
917single parameter named ``db``. This allows you to use all of the methods
918of the DB class with a DB-API 2 compliant connection. Note that the
919:meth:`pgobject.close` and :meth:`pgobject.reopen` methods are inoperative
920in this case.
921
922pkey -- return the primary key of a table
923-----------------------------------------
924
925.. method:: DB.pkey(table)
926
927    Return the primary key of a table
928
929    :param str table: name of table
930    :returns: Name of the field which is the primary key of the table
931    :rtype: str
932
933This method returns the primary key of a table. For composite primary
934keys, the return value will be a frozenset. Note that this raises an
935exception if the table does not have a primary key.
936
937get_databases -- get list of databases in the system
938----------------------------------------------------
939
940.. method:: DB.get_databases()
941
942    Get the list of databases in the system
943
944    :returns: all databases in the system
945    :rtype: list
946
947Although you can do this with a simple select, it is added here for
948convenience.
949
950get_relations -- get list of relations in connected database
951------------------------------------------------------------
952
953.. method:: DB.get_relations(kinds)
954
955    Get the list of relations in connected database
956
957    :param str kinds: a string or sequence of type letters
958    :returns: all relations of the given kinds in the database
959    :rtype: list
960
961The type letters are ``r`` = ordinary table, ``i`` = index, ``S`` = sequence,
962``v`` = view, ``c`` = composite type, ``s`` = special, ``t`` = TOAST table.
963If `kinds` is None or an empty string, all relations are returned (this is
964also the default). Although you can do this with a simple select, it is
965added here for convenience.
966
967get_tables -- get list of tables in connected database
968------------------------------------------------------
969
970.. method:: DB.get_tables()
971
972    Get the list of tables in connected database
973
974    :returns: all tables in connected database
975    :rtype: list
976
977This is a shortcut for ``get_relations('r')`` that has been added for
978convenience.
979
980get_attnames -- get the attribute names of a table
981--------------------------------------------------
982
983.. method:: DB.get_attnames(table)
984
985    Get the attribute names of a table
986
987    :param str table: name of table
988    :returns: A dictionary -- the keys are the attribute names,
989     the values are the type names of the attributes.
990
991Given the name of a table, digs out the set of attribute names.
992
993has_table_privilege -- check whether current user has specified table privilege
994-------------------------------------------------------------------------------
995
996.. method:: DB.has_table_privilege(table, privilege)
997
998    Check whether current user has specified table privilege
999
1000    :param str table: the name of the table
1001    :param str privilege: privilege to be checked -- default is 'select'
1002    :returns: whether current user has specified table privilege
1003    :rtype: bool
1004
1005Returns True if the current user has the specified privilege for the table.
1006
1007get -- get a row from a database table or view
1008----------------------------------------------
1009
1010.. method:: DB.get(table, arg, [keyname])
1011
1012    Get a row from a database table or view
1013
1014    :param str table:  name of table or view
1015    :param arg:  either a dictionary or the value to be looked up
1016    :param str keyname: name of field to use as key (optional)
1017    :returns: A dictionary - the keys are the attribute names,
1018      the values are the row values.
1019
1020This method is the basic mechanism to get a single row. It assumes
1021that the key specifies a unique row. If *keyname* is not specified,
1022then the primary key for the table is used. If *arg* is a dictionary
1023then the value for the key is taken from it and it is modified to
1024include the new values, replacing existing values where necessary.
1025For a composite key, *keyname* can also be a sequence of key names.
1026The OID is also put into the dictionary if the table has one, but in
1027order to allow the caller to work with multiple tables, it is munged
1028as ``oid(schema.table)``.
1029
1030insert -- insert a row into a database table
1031--------------------------------------------
1032
1033.. method:: DB.insert(table, [d,] [key = val, ...])
1034
1035    Insert a row into a database table
1036
1037    :param str table: name of table
1038    :param dict d: optional dictionary of values
1039    :returns: the inserted values
1040    :rtype: dict
1041
1042This method inserts a row into a table.  If the optional dictionary is
1043not supplied then the required values must be included as keyword/value
1044pairs.  If a dictionary is supplied then any keywords provided will be
1045added to or replace the entry in the dictionary.
1046
1047The dictionary is then, if possible, reloaded with the values actually
1048inserted in order to pick up values modified by rules, triggers, etc.
1049
1050Note: The method currently doesn't support insert into views
1051although PostgreSQL does.
1052
1053update -- update a row in a database table
1054------------------------------------------
1055
1056.. method:: DB.update(table, [d,] [key = val, ...])
1057
1058    Update a row in a database table
1059
1060    :param str table: name of table
1061    :param dict d: optional dictionary of values
1062    :returns: the new row
1063    :rtype: dict
1064
1065Similar to insert but updates an existing row.  The update is based on the
1066OID value as munged by get or passed as keyword, or on the primary key of
1067the table.  The dictionary is modified, if possible, to reflect any changes
1068caused by the update due to triggers, rules, default values, etc.
1069
1070Like insert, the dictionary is optional and updates will be performed
1071on the fields in the keywords.  There must be an OID or primary key
1072either in the dictionary where the OID must be munged, or in the keywords
1073where it can be simply the string 'oid'.
1074
1075query -- execute a SQL command string
1076-------------------------------------
1077
1078.. method:: DB.query(command, [arg1, [arg2, ...]])
1079
1080    Execute a SQL command string
1081
1082    :param str command: SQL command
1083    :param arg*: optional positional arguments
1084    :returns: result values
1085    :rtype: :class:`pgqueryobject`, None
1086    :raises TypeError: bad argument type, or too many arguments
1087    :raises TypeError: invalid connection
1088    :raises ValueError: empty SQL query or lost connection
1089    :raises pg.ProgrammingError: error in query
1090    :raises pg.InternalError: error during query processing
1091
1092Similar to the :class:`pgobject` function with the same name, except that
1093positional arguments can be passed either as a single list or tuple, or as
1094individual positional arguments.
1095
1096Example::
1097
1098    name = raw_input("Name? ")
1099    phone = raw_input("Phone? ")
1100    rows = db.query("update employees set phone=$2 where name=$1",
1101        (name, phone)).getresult()[0][0]
1102    # or
1103    rows = db.query("update employees set phone=$2 where name=$1",
1104         name, phone).getresult()[0][0]
1105
1106clear -- clear row values in memory
1107-----------------------------------
1108
1109.. method:: DB.clear(table, [a])
1110
1111    Clear row values in memory
1112
1113    :param str table: name of table
1114    :param dict a: optional dictionary of values
1115    :returns: an empty row
1116    :rtype: dict
1117
1118This method clears all the attributes to values determined by the types.
1119Numeric types are set to 0, Booleans are set to ``'f'``, dates are set
1120to ``'now()'`` and everything else is set to the empty string.
1121If the array argument is present, it is used as the array and any entries
1122matching attribute names are cleared with everything else left unchanged.
1123
1124If the dictionary is not supplied a new one is created.
1125
1126delete -- delete a row from a database table
1127--------------------------------------------
1128
1129.. method:: DB.delete(table, [d,] [key = val, ...])
1130
1131    Delete a row from a database table
1132
1133    :param str table: name of table
1134    :param dict d: optional dictionary of values
1135    :rtype: None
1136
1137This method deletes the row from a table.  It deletes based on the OID value
1138as munged by get or passed as keyword, or on the primary key of the table.
1139The return value is the number of deleted rows (i.e. 0 if the row did not
1140exist and 1 if the row was deleted).
1141
1142escape_string -- escape a string for use within SQL
1143---------------------------------------------------
1144
1145.. method:: DB.escape_string(string)
1146
1147    Escape a string for use within SQL
1148
1149    :param str string: the string that is to be escaped
1150    :returns: the escaped string
1151    :rtype: str
1152
1153Similar to the module function with the same name, but the
1154behavior of this method is adjusted depending on the connection properties
1155(such as character encoding).
1156
1157escape_bytea -- escape binary data for use within SQL
1158-----------------------------------------------------
1159
1160.. method:: DB.escape_bytea(datastring)
1161
1162    Escape binary data for use within SQL as type ``bytea``
1163
1164    :param str datastring: string containing the binary data that is to be escaped
1165    :returns: the escaped string
1166    :rtype: str
1167
1168Similar to the module function with the same name, but the
1169behavior of this method is adjusted depending on the connection properties
1170(in particular, whether standard-conforming strings are enabled).
1171
1172unescape_bytea -- unescape data that has been retrieved as text
1173---------------------------------------------------------------
1174
1175.. method:: DB.unescape_bytea(string)
1176
1177    Unescape ``bytea`` data that has been retrieved as text
1178
1179    :param datastring: the ``bytea`` data string that has been retrieved as text
1180    :returns: byte string containing the binary data
1181    :rtype: str
1182
1183See the module function with the same name.
1184
1185
1186pgqueryobject methods
1187=====================
1188
1189.. class:: pgqueryobject
1190
1191The :class:`pgqueryobject` returned by :meth:`pgobject.query` and
1192:meth:`DB.query` provides the following methods for accessing
1193the results of the query:
1194
1195getresult -- get query values as list of tuples
1196-----------------------------------------------
1197
1198.. method:: pgqueryobject.getresult()
1199
1200    Get query values as list of tuples
1201
1202    :returns: result values as a list of tuples
1203    :rtype: list
1204    :raises TypeError: too many (any) parameters
1205    :raises MemoryError: internal memory error
1206
1207This method returns the list of the values returned by the query.
1208More information about this result may be accessed using
1209:meth:`pgqueryobject.listfields`, :meth:`pgqueryobject.fieldname`
1210and :meth:`pgqueryobject.fieldnum` methods.
1211
1212dictresult -- get query values as list of dictionaries
1213------------------------------------------------------
1214
1215.. method:: pgqueryobject.dictresult()
1216
1217    Get query values as list of dictionaries
1218
1219    :returns: result values as a list of dictionaries
1220    :rtype: list
1221    :raises TypeError: too many (any) parameters
1222    :raises MemoryError: internal memory error
1223
1224This method returns the list of the values returned by the query
1225with each tuple returned as a dictionary with the field names
1226used as the dictionary index.
1227
1228namedresult -- get query values as list of named tuples
1229-------------------------------------------------------
1230
1231.. method:: pgqueryobject.namedresult()
1232
1233    Get query values as list of named tuples
1234
1235    :returns: result values as a list of named tuples
1236    :rtype: list
1237    :raises TypeError: too many (any) parameters
1238    :raises TypeError: named tuples not supported
1239    :raises MemoryError: internal memory error
1240
1241This method returns the list of the values returned by the query
1242with each row returned as a named tuple with proper field names.
1243
1244listfields -- list fields names of previous query result
1245--------------------------------------------------------
1246
1247.. method:: pgqueryobject.listfields()
1248
1249    List fields names of previous query result
1250
1251    :returns: field names
1252    :rtype: list
1253    :raises TypeError: too many parameters
1254
1255This method returns the list of names of the fields defined for the
1256query result. The fields are in the same order as the result values.
1257
1258fieldname, fieldnum -- field name/number conversion
1259---------------------------------------------------
1260
1261.. method:: pgqueryobject.fieldname(num)
1262
1263    Get field name from its number
1264
1265    :param int num: field number
1266    :returns: field name
1267    :rtype: str
1268    :raises TypeError: invalid connection, bad parameter type, or too many parameters
1269    :raises ValueError: invalid field number
1270
1271This method allows to find a field name from its rank number. It can be
1272useful for displaying a result. The fields are in the same order as the
1273result values.
1274
1275.. method:: pgqueryobject.fieldnum(name)
1276
1277    Get field number from its name
1278
1279    :param str name: field name
1280    :returns: field number
1281    :rtype: int
1282    :raises TypeError: invalid connection, bad parameter type, or too many parameters
1283    :raises ValueError: unknown field name
1284
1285This method returns a field number from its name. It can be used to
1286build a function that converts result list strings to their correct
1287type, using a hardcoded table definition. The number returned is the
1288field rank in the result values list.
1289
1290ntuples -- return number of tuples in query object
1291--------------------------------------------------
1292
1293.. method:: pgqueryobject.ntuples()
1294
1295    Return number of tuples in query object
1296
1297    :returns: number of tuples in :class:`pgqueryobject`
1298    :rtype: int
1299    :raises TypeError: Too many arguments.
1300
1301This method returns the number of tuples found in a query.
1302
1303
1304pglarge -- Large Objects
1305========================
1306
1307.. class:: pglarge
1308
1309Objects that are instances of the class :class:`pglarge` are used to handle
1310all the requests concerning a PostgreSQL large object. These objects embed
1311and hide all the "recurrent" variables (object OID and connection), exactly
1312in the same way :class:`pgobject` instances do, thus only keeping significant
1313parameters in function calls. The class:`pglarge` object keeps a reference
1314to the :class:`pgobject` used for its creation, sending requests though with
1315its parameters. Any modification but dereferencing the :class:`pgobject`
1316will thus affect the :class:`pglarge` object. Dereferencing the initial
1317:class:`pgobject` is not a problem since Python won't deallocate it before
1318the :class:`pglarge` object dereferences it. All functions return a generic
1319error message on call error, whatever the exact error was. The :attr:`error`
1320attribute of the object allows to get the exact error message.
1321
1322See also the PostgreSQL programmer's guide for more information about the
1323large object interface.
1324
1325open -- open a large object
1326---------------------------
1327
1328.. method:: pglarge.open(mode)
1329
1330    Open a large object
1331
1332    :param int mode: open mode definition
1333    :rtype: None
1334    :raises TypeError: invalid connection, bad parameter type, or too many parameters
1335    :raises IOError: already opened object, or open error
1336
1337This method opens a large object for reading/writing, in the same way than the
1338Unix open() function. The mode value can be obtained by OR-ing the constants
1339defined in the :mod:`pg` module (:const:`INV_READ`, :const:`INV_WRITE`).
1340
1341close -- close a large object
1342-----------------------------
1343
1344.. method:: pglarge.close()
1345
1346    Close a large object
1347
1348    :rtype: None
1349    :raises TypeError: invalid connection
1350    :raises TypeError: too many parameters
1351    :raises IOError: object is not opened, or close error
1352
1353This method closes a previously opened large object, in the same way than
1354the Unix close() function.
1355
1356read, write, tell, seek, unlink -- file-like large object handling
1357------------------------------------------------------------------
1358
1359.. method:: pglarge.read(size)
1360
1361    Read data from large object
1362
1363    :param int size: maximal size of the buffer to be read
1364    :returns: the read buffer
1365    :rtype: str
1366    :raises TypeError: invalid connection, invalid object,
1367     bad parameter type, or too many parameters
1368    :raises ValueError: if `size` is negative
1369    :raises IOError: object is not opened, or read error
1370
1371This function allows to read data from a large object, starting at current
1372position.
1373
1374.. method:: pglarge.write(string)
1375
1376    Read data to large object
1377
1378    :param str string: string buffer to be written
1379    :rtype: None
1380    :raises TypeError: invalid connection, bad parameter type, or too many parameters
1381    :raises IOError: object is not opened, or write error
1382
1383This function allows to write data to a large object, starting at current
1384position.
1385
1386.. method:: pglarge.seek(offset, whence)
1387
1388    Change current position in large object
1389
1390    :param int offset: position offset
1391    :param int whence: positional parameter
1392    :returns: new position in object
1393    :rtype: int
1394    :raises TypeError: invalid connection or invalid object,
1395     bad parameter type, or too many parameters
1396    :raises IOError: object is not opened, or seek error
1397
1398This method allows to move the position cursor in the large object.
1399The valid values for the whence parameter are defined as constants in the
1400:mod:`pg` module (:const:`SEEK_SET`, :const:`SEEK_CUR`, :const:`SEEK_END`).
1401
1402.. method:: pglarge.tell()
1403
1404    Return current position in large object
1405
1406    :returns: current position in large object
1407    :rtype: int
1408    :raises TypeError: invalid connection or invalid object
1409    :raises TypeError: too many parameters
1410    :raises IOError: object is not opened, or seek error
1411
1412This method allows to get the current position in the large object.
1413
1414.. method:: pglarge.unlink()
1415
1416    Delete large object
1417
1418    :rtype: None
1419    :raises TypeError: invalid connection or invalid object
1420    :raises TypeError: too many parameters
1421    :raises IOError: object is not closed, or unlink error
1422
1423This methods unlinks (deletes) the PostgreSQL large object.
1424
1425size -- get the large object size
1426---------------------------------
1427
1428.. method:: pglarge.size()
1429
1430    Return the large object size
1431
1432    :returns: the large object size
1433    :rtype: int
1434    :raises TypeError: invalid connection or invalid object
1435    :raises TypeError: too many parameters
1436    :raises IOError: object is not opened, or seek/tell error
1437
1438This (composite) method allows to get the size of a large object. It was
1439implemented because this function is very useful for a web interfaced
1440database. Currently, the large object needs to be opened first.
1441
1442export -- save a large object to a file
1443---------------------------------------
1444
1445.. method:: pglarge.export(name)
1446
1447    Export a large object to a file
1448
1449    :param str name: file to be created
1450    :rtype: None
1451    :raises TypeError: invalid connection or invalid object,
1452     bad parameter type, or too many parameters
1453    :raises IOError: object is not closed, or export error
1454
1455This methods allows to dump the content of a large object in a very simple
1456way. The exported file is created on the host of the program, not the
1457server host.
1458
1459Object attributes
1460-----------------
1461:class:`pglarge` objects define a read-only set of attributes that allow
1462to get some information about it. These attributes are:
1463
1464.. attribute:: pglarge.oid
1465
1466   the OID associated with the object (int)
1467
1468.. attribute:: pglarge.pgcnx
1469
1470   the :class:`pgobject` associated with the object
1471
1472.. attribute:: pglarge.error
1473
1474   the last warning/error message of the connection
1475
1476.. note::
1477
1478    **Be careful**:
1479    In multithreaded environments, :attr:`pglarge.error` may be modified by
1480    another thread using the same :class:`pgobject`. Remember these object
1481    are shared, not duplicated. You should provide some locking to be able
1482    if you want to check this. The :attr:`pglarge.oid` attribute is very
1483    interesting, because it allows you to reuse the OID later, creating the
1484    :class:`pglarge` object with a :meth:`pgobject.getlo` method call.
Note: See TracBrowser for help on using the repository browser.