source: trunk/docs/pg.txt @ 425

Last change on this file since 425 was 423, checked in by D'Arcy J.M. Cain, 8 years ago

In early November, 2011 the system housing PyGreSQL was hacked and wiped.
We lost the current repository. I restored an old backup copy of the
repository. This commit brings us back to the present.

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