source: branches/4.x/docs/pg.txt @ 672

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

Use consistent indentation in docs

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