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

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

Small corrections in the docs

  • Property svn:keywords set to Author Date Id Revision
File size: 41.2 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 
388set_namedresult -- set a function that will convert to named tuples
389-------------------------------------------------------------------
390Syntax::
391
392  set_namedresult(func)
393
394Parameters:
395  :func: the function to be used to convert results to named tuples
396
397Description:
398  You can use this if you want to create different kinds of named tuples.
399 
400
401Module constants
402----------------
403Some constants are defined in the module dictionary.
404They are intended to be used as parameters for methods calls.
405You should refer to the libpq description in the PostgreSQL user manual
406for more information about them. These constants are:
407
408:version, __version__: constants that give the current version.
409:INV_READ, INV_WRITE: large objects access modes,
410  used by `(pgobject.)locreate` and `(pglarge.)open`
411:SEEK_SET, SEEK_CUR, SEEK_END: positional flags,
412  used by `(pglarge.)seek`
413
414
415Connection objects: pgobject
416============================
417This object handles a connection to a PostgreSQL database. It embeds and
418hides all the parameters that define this connection, thus just leaving really
419significant parameters in function calls.
420
421.. caution:: Some methods give direct access to the connection socket.
422  *Do not use them unless you really know what you are doing.*
423  If you prefer disabling them,
424  set the -DNO_DIRECT option in the Python setup file.
425
426  **These methods are specified by the tag [DA].**
427
428.. note:: Some other methods give access to large objects
429  (refer to PostgreSQL user manual for more information about these).
430  If you want to forbid access to these from the module,
431  set the -DNO_LARGE option in the Python setup file.
432
433  **These methods are specified by the tag [LO].**
434
435query - executes a SQL command string
436-------------------------------------
437Syntax::
438
439  query(command, [args])
440
441Parameters:
442  :command: SQL command (string)
443  :args: optional positional arguments
444
445Return type:
446  :pgqueryobject, None: result values
447
448Exceptions raised:
449  :TypeError: bad argument type, or too many arguments
450  :TypeError: invalid connection
451  :ValueError: empty SQL query or lost connection
452  :pg.ProgrammingError: error in query
453  :pg.InternalError: error during query processing
454
455Description:
456  This method simply sends a SQL query to the database. If the query is an
457  insert statement that inserted exactly one row into a table that has OIDs, the
458  return value is the OID of the newly inserted row. If the query is an update
459  or delete statement, or an insert statement that did not insert exactly one
460  row in a table with OIDs, then the number of rows affected is returned as a
461  string. If it is a statement that returns rows as a result (usually a select
462  statement, but maybe also an "insert/update ... returning" statement), this
463  method returns a `pgqueryobject` that can be accessed via the `getresult()`,
464  `dictresult()` or `namedresult()` methods or simply printed. Otherwise, it
465  returns `None`.
466
467  The query may optionally contain positional parameters of the form `$1`,
468  `$2`, etc instead of literal data, and the values supplied as a tuple.
469  The values are substituted by the database in such a way that they don't
470  need to be escaped, making this an effective way to pass arbitrary or
471  unknown data without worrying about SQL injection or syntax errors.
472
473  When the database could not process the query, a `pg.ProgrammingError` or
474  a `pg.InternalError` is raised. You can check the "SQLSTATE" code of this
475  error by reading its `sqlstate` attribute.
476
477Example::
478
479  name = raw_input("Name? ")
480  phone = con.query("select phone from employees"
481    " where name=$1", (name, )).getresult()
482
483reset - resets the connection
484-----------------------------
485Syntax::
486
487  reset()
488
489Parameters:
490  None
491
492Return type:
493  None
494
495Exceptions raised:
496  :TypeError: too many (any) arguments
497  :TypeError: invalid connection
498
499Description:
500  This method resets the current database connection.
501
502cancel - abandon processing of current SQL command
503--------------------------------------------------
504Syntax::
505
506  cancel()
507
508Parameters:
509  None
510
511Return type:
512  None
513
514Exceptions raised:
515  :TypeError: too many (any) arguments
516  :TypeError: invalid connection
517
518Description:
519  This method requests that the server abandon processing
520  of the current SQL command.
521
522close - close the database connection
523-------------------------------------
524Syntax::
525
526  close()
527
528Parameters:
529  None
530
531Return type:
532  None
533
534Exceptions raised:
535  :TypeError: too many (any) arguments
536
537Description:
538  This method closes the database connection. The connection will
539  be closed in any case when the connection is deleted but this
540  allows you to explicitly close it. It is mainly here to allow
541  the DB-SIG API wrapper to implement a close function.
542
543fileno - returns the socket used to connect to the database
544-----------------------------------------------------------
545Syntax::
546
547  fileno()
548
549Parameters:
550  None
551
552Exceptions raised:
553  :TypeError: too many (any) arguments
554  :TypeError: invalid connection
555
556Description:
557  This method returns the underlying socket id used to connect
558  to the database. This is useful for use in select calls, etc.
559
560getnotify - gets the last notify from the server
561------------------------------------------------
562Syntax::
563
564  getnotify()
565
566Parameters:
567  None
568
569Return type:
570  :tuple, None: last notify from server
571
572Exceptions raised:
573  :TypeError: too many parameters
574  :TypeError: invalid connection
575
576Description:
577  This methods try to get a notify from the server (from the SQL statement
578  NOTIFY). If the server returns no notify, the methods returns None.
579  Otherwise, it returns a tuple (triplet) `(relname, pid, extra)`, where
580  `relname` is the name of the notify, `pid` is the process id of the
581  connection that triggered the notify, and `extra` is a payload string
582  that has been sent with the notification. Remember to do a listen query
583  first, otherwise getnotify() will always return `None`.
584
585inserttable - insert a list into a table
586----------------------------------------
587Syntax::
588
589  inserttable(table, values)
590
591Parameters:
592  :table: the table name (string)
593  :values: list of rows values (list)
594
595Return type:
596  None
597
598Exceptions raised:
599  :TypeError: invalid connection, bad argument type, or too many arguments
600  :MemoryError: insert buffer could not be allocated
601  :ValueError: unsupported values
602
603Description:
604  This method allow to *quickly* insert large blocks of data in a table:
605  It inserts the whole values list into the given table. Internally, it
606  uses the COPY command of the PostgreSQL database. The list is a list
607  of tuples/lists that define the values for each inserted row. The rows
608  values may contain string, integer, long or double (real) values.
609
610.. caution:: *Be very careful*:
611  This method doesn't typecheck the fields according to the table definition;
612  it just look whether or not it knows how to handle such types.
613
614set_notice_receiver - set a custom notice receiver
615--------------------------------------------------
616Syntax::
617
618  set_notice_receiver(proc)
619
620Parameters:
621  :proc: the custom notice receiver callback function
622
623Return type:
624  None
625
626Exceptions raised:
627  :TypeError: the specified notice receiver is not callable
628
629Description:
630  This method allows setting a custom notice receiver callback function.
631  When a notice or warning message is received from the server,
632  or generated internally by libpq, and the message level is below
633  the one set with `client_min_messages`, the specified notice receiver
634  function will be called. This function must take one parameter,
635  the `pgnotice` object, which provides the following read-only attributes:
636
637    :pgcnx: the connection
638    :message: the full message with a trailing newline
639    :severity: the level of the message, e.g. 'NOTICE' or 'WARNING'
640    :primary: the primary human-readable error message
641    :detail: an optional secondary error message
642    :hint: an optional suggestion what to do about the problem
643
644get_notice_receiver - get the current notice receiver
645-----------------------------------------------------
646Syntax::
647
648  get_notice_receiver()
649
650Parameters:
651  None
652
653Return type:
654  :callable, None: the current notice receiver callable
655
656Exceptions raised:
657  :TypeError: too many (any) arguments
658
659Description:
660  This method gets the custom notice receiver callback function that has
661  been set with `set_notice_receiver()`, or `None` if no custom notice
662  receiver has ever been set on the connection.
663
664putline - writes a line to the server socket [DA]
665-------------------------------------------------
666Syntax::
667
668  putline(line)
669
670Parameters:
671  :line: line to be written (string)
672
673Return type:
674  None
675
676Exceptions raised:
677  :TypeError: invalid connection, bad parameter type, or too many parameters
678
679Description:
680  This method allows to directly write a string to the server socket.
681
682getline - gets a line from server socket [DA]
683---------------------------------------------
684Syntax::
685
686  getline()
687
688Parameters:
689  None
690
691Return type:
692  :string: the line read
693
694Exceptions raised:
695  :TypeError: invalid connection
696  :TypeError: too many parameters
697  :MemoryError: buffer overflow
698
699Description:
700  This method allows to directly read a string from the server socket.
701
702endcopy - synchronizes client and server [DA]
703---------------------------------------------
704Syntax::
705
706  endcopy()
707
708Parameters:
709  None
710
711Return type:
712  None
713
714Exceptions raised:
715  :TypeError: invalid connection
716  :TypeError: too many parameters
717
718Description:
719  The use of direct access methods may desynchonize client and server.
720  This method ensure that client and server will be synchronized.
721
722locreate - create a large object in the database [LO]
723-----------------------------------------------------
724Syntax::
725
726  locreate(mode)
727
728Parameters:
729  :mode: large object create mode
730
731Return type:
732  :pglarge: object handling the PostGreSQL large object
733
734Exceptions raised:
735
736  :TypeError: invalid connection, bad parameter type, or too many parameters
737  :pg.OperationalError: creation error
738
739Description:
740  This method creates a large object in the database. The mode can be defined
741  by OR-ing the constants defined in the pg module (INV_READ, INV_WRITE and
742  INV_ARCHIVE). Please refer to PostgreSQL user manual for a description of
743  the mode values.
744
745getlo - build a large object from given oid [LO]
746------------------------------------------------
747Syntax::
748
749  getlo(oid)
750
751Parameters:
752  :oid: OID of the existing large object (integer)
753
754Return type:
755  :pglarge: object handling the PostGreSQL large object
756
757Exceptions raised:
758  :TypeError:  invalid connection, bad parameter type, or too many parameters
759  :ValueError: bad OID value (0 is invalid_oid)
760
761Description:
762  This method allows to reuse a formerly created large object through the
763  `pglarge` interface, providing the user have its OID.
764
765loimport - import a file to a large object [LO]
766-----------------------------------------------
767Syntax::
768
769  loimport(name)
770
771Parameters:
772  :name: the name of the file to be imported (string)
773
774Return type:
775  :pglarge: object handling the PostGreSQL large object
776
777Exceptions raised:
778  :TypeError: invalid connection, bad argument type, or too many arguments
779  :pg.OperationalError: error during file import
780
781Description:
782  This methods allows to create large objects in a very simple way. You just
783  give the name of a file containing the data to be use.
784
785Object attributes
786-----------------
787Every `pgobject` defines a set of read-only attributes that describe the
788connection and its status. These attributes are:
789
790  :host:             the host name of the server (string)
791  :port:             the port of the server (integer)
792  :db:               the selected database (string)
793  :options:          the connection options (string)
794  :tty:              the connection debug terminal (string)
795  :user:             user name on the database system (string)
796  :protocol_version: the frontend/backend protocol being used (integer)
797  :server_version:   the backend version (integer, e.g. 80305 for 8.3.5)
798  :status:           the status of the connection (integer: 1 - OK, 0 - bad)
799  :error:            the last warning/error message from the server (string)
800
801
802The DB wrapper class
803====================
804The `pgobject` methods are wrapped in the class `DB`.
805The preferred way to use this module is as follows::
806
807  import pg
808
809  db = pg.DB(...) # see below
810
811  for r in db.query( # just for example
812      """SELECT foo,bar
813         FROM foo_bar_table
814         WHERE foo !~ bar"""
815      ).dictresult():
816
817      print '%(foo)s %(bar)s' % r
818
819This class can be subclassed as in this example::
820
821  import pg
822
823  class DB_ride(pg.DB):
824    """This class encapsulates the database functions and the specific
825       methods for the ride database."""
826
827    def __init__(self):
828        """Opens a database connection to the rides database"""
829
830        pg.DB.__init__(self, dbname = 'ride')
831        self.query("""SET DATESTYLE TO 'ISO'""")
832
833    [Add or override methods here]
834
835The following describes the methods and variables of this class.
836
837Initialization
838--------------
839The DB class is initialized with the same arguments as the connect
840function described in section 2. It also initializes a few
841internal variables. The statement `db = DB()` will open the
842local database with the name of the user just like connect() does.
843
844You can also initialize the DB class with an existing `_pg` or `pgdb`
845connection. Pass this connection as a single unnamed parameter, or as a
846single parameter named `db`. This allows you to use all of the methods
847of the DB class with a DB-API 2 compliant connection. Note that the
848`close()` and `reopen()` methods are inoperative in this case.
849
850
851
852pkey - return the primary key of a table
853----------------------------------------
854Syntax::
855
856  pkey(table)
857
858Parameters:
859  :table: name of table
860
861Return type:
862  :string: Name of the field which is the primary key of the table
863
864Description:
865  This method returns the primary key of a table. For composite primary
866  keys, the return value will be a frozenset. Note that this raises an
867  exception if the table does not have a primary key.
868
869get_databases - get list of databases in the system
870---------------------------------------------------
871Syntax::
872
873  get_databases()
874
875Parameters:
876  None
877
878Return type:
879  :list: all databases in the system
880
881Description:
882  Although you can do this with a simple select, it is added here for
883  convenience.
884
885get_relations - get list of relations in connected database
886-----------------------------------------------------------
887Syntax::
888
889  get_relations(kinds)
890
891Parameters:
892  :kinds: a string or sequence of type letters
893
894Description:
895  The type letters are `r` = ordinary table, `i` = index, `S` = sequence,
896  `v` = view, `c` = composite type, `s` = special, `t` = TOAST table.
897  If `kinds` is None or an empty string, all relations are returned (this is
898  also the default). Although you can do this with a simple select, it is
899  added here for convenience.
900
901get_tables - get list of tables in connected database
902-----------------------------------------------------
903Syntax::
904
905  get_tables()
906
907Parameters:
908  None
909
910Returns:
911  :list: all tables in connected database
912
913Description:
914  Although you can do this with a simple select, it is added here for
915  convenience.
916
917get_attnames - get the attribute names of a table
918-------------------------------------------------
919Syntax::
920
921  get_attnames(table)
922
923Parameters:
924  :table: name of table
925
926Returns:
927  :dictionary:  The keys are the attribute names,
928    the values are the type names of the attributes.
929
930Description:
931  Given the name of a table, digs out the set of attribute names.
932
933has_table_privilege - check whether current user has specified table privilege
934------------------------------------------------------------------------------
935Syntax::
936
937    has_table_privilege(table, privilege)
938
939Parameters:
940  :table:     name of table
941  :privilege: privilege to be checked - default is 'select'
942
943Description:
944  Returns True if the current user has the specified privilege for the table.
945
946get - get a row from a database table or view
947---------------------------------------------
948Syntax::
949
950 get(table, arg, [keyname])
951
952Parameters:
953  :table:   name of table or view
954  :arg:     either a dictionary or the value to be looked up
955  :keyname: name of field to use as key (optional)
956
957Return type:
958  :dictionary: The keys are the attribute names,
959    the values are the row values.
960
961Description:
962  This method is the basic mechanism to get a single row. It assumes
963  that the key specifies a unique row. If `keyname` is not specified
964  then the primary key for the table is used. If `arg` is a dictionary
965  then the value for the key is taken from it and it is modified to
966  include the new values, replacing existing values where necessary.
967  For a composite key, `keyname` can also be a sequence of key names.
968  The OID is also put into the dictionary if the table has one, but in
969  order to allow the caller to work with multiple tables, it is munged
970  as `oid(schema.table)`.
971
972insert - insert a row into a database table
973-------------------------------------------
974Syntax::
975
976  insert(table, [d,] [key = val, ...])
977
978Parameters:
979  :table:          name of table
980  :d:              optional dictionary of values
981
982Return type:
983  :dictionary:     The dictionary of values inserted
984
985Description:
986  This method inserts a row into a table.  If the optional dictionary is
987  not supplied then the required values must be included as keyword/value
988  pairs.  If a dictionary is supplied then any keywords provided will be
989  added to or replace the entry in the dictionary.
990
991  The dictionary is then, if possible, reloaded with the values actually
992  inserted in order to pick up values modified by rules, triggers, etc.
993
994  Note: The method currently doesn't support insert into views
995  although PostgreSQL does.
996
997update - update a row in a database table
998-----------------------------------------
999Syntax::
1000
1001  update(table, [d,] [key = val, ...])
1002
1003Parameters:
1004  :table: name of table
1005  :d:     optional dictionary of values
1006
1007Return type:
1008  :dictionary: the new row
1009
1010Description:
1011  Similar to insert but updates an existing row.  The update is based on the
1012  OID value as munged by get or passed as keyword, or on the primary key of
1013  the table.  The dictionary is modified, if possible, to reflect any changes
1014  caused by the update due to triggers, rules, default values, etc.
1015
1016  Like insert, the dictionary is optional and updates will be performed
1017  on the fields in the keywords.  There must be an OID or primary key
1018  either in the dictionary where the OID must be munged, or in the keywords
1019  where it can be simply the string "oid".
1020
1021query - executes a SQL command string
1022-------------------------------------
1023Syntax::
1024
1025  query(command, [arg1, [arg2, ...]])
1026
1027Parameters:
1028  :command: SQL command (string)
1029  :arg*: optional positional arguments
1030
1031Return type:
1032  :pgqueryobject, None: result values
1033
1034Exceptions raised:
1035  :TypeError: bad argument type, or too many arguments
1036  :TypeError: invalid connection
1037  :ValueError: empty SQL query or lost connection
1038  :pg.ProgrammingError: error in query
1039  :pg.InternalError: error during query processing
1040
1041Description:
1042  Similar to the pgobject function with the same name, except that positional
1043  arguments can be passed either as a single list or tuple, or as individual
1044  positional arguments
1045
1046Example::
1047
1048  name = raw_input("Name? ")
1049  phone = raw_input("Phone? "
1050  rows = db.query("update employees set phone=$2"
1051    " where name=$1", (name, phone)).getresult()[0][0]
1052  # or
1053  rows = db.query("update employees set phone=$2"
1054    " where name=$1", name, phone).getresult()[0][0]
1055
1056clear - clears row values in memory
1057-----------------------------------
1058Syntax::
1059
1060 clear(table, [a])
1061
1062Parameters:
1063  :table: name of table
1064  :a:     optional dictionary of values
1065
1066Return type:
1067  :dictionary: an empty row
1068
1069Description:
1070  This method clears all the attributes to values determined by the types.
1071  Numeric types are set to 0, Booleans are set to 'f', dates are set
1072  to 'now()' and everything else is set to the empty string.
1073  If the array argument is present, it is used as the array and any entries
1074  matching attribute names are cleared with everything else left unchanged.
1075
1076  If the dictionary is not supplied a new one is created.
1077
1078delete - delete a row from a database table
1079-------------------------------------------
1080Syntax::
1081
1082  delete(table, [d,] [key = val, ...])
1083
1084Parameters:
1085  :table: name of table
1086  :d:     optional dictionary of values
1087
1088Returns:
1089  None
1090
1091Description:
1092  This method deletes the row from a table.  It deletes based on the OID value
1093  as munged by get or passed as keyword, or on the primary key of the table.
1094  The return value is the number of deleted rows (i.e. 0 if the row did not
1095  exist and 1 if the row was deleted).
1096
1097escape_string - escape a string for use within SQL
1098--------------------------------------------------
1099Syntax::
1100
1101  escape_string(string)
1102
1103Parameters:
1104  :string: the string that is to be escaped
1105
1106Return type:
1107  :str: the escaped string
1108
1109Description:
1110  Similar to the module function with the same name, but the
1111  behavior of this method is adjusted depending on the connection properties
1112  (such as character encoding).
1113
1114escape_bytea - escape binary data for use within SQL as type `bytea`
1115--------------------------------------------------------------------
1116Syntax::
1117
1118  escape_bytea(datastring)
1119
1120Parameters:
1121  :datastring: string containing the binary data that is to be escaped
1122
1123Return type:
1124  :str: the escaped string
1125
1126Description:
1127  Similar to the module function with the same name, but the
1128  behavior of this method is adjusted depending on the connection properties
1129  (in particular, whether standard-conforming strings are enabled).
1130
1131unescape_bytea -- unescape `bytea` data that has been retrieved as text
1132-----------------------------------------------------------------------
1133Syntax::
1134
1135  unescape_bytea(string)
1136
1137Parameters:
1138  :datastring: the `bytea` data string that has been retrieved as text
1139
1140Return type:
1141  :str: string containing the binary data
1142
1143Description:
1144  See the module function with the same name.
1145
1146
1147pgqueryobject methods
1148=====================
1149
1150getresult - get query values as list of tuples
1151-----------------------------------------------
1152Syntax::
1153
1154  getresult()
1155
1156Parameters:
1157  None
1158
1159Return type:
1160  :list: result values as a list of tuples
1161
1162Exceptions raised:
1163  :TypeError: too many (any) parameters
1164  :MemoryError: internal memory error
1165
1166Description:
1167  This method returns the list of the values returned by the query.
1168  More information about this result may be accessed using listfields(),
1169  fieldname() and fieldnum() methods.
1170
1171dictresult - get query values as list of dictionaries
1172-----------------------------------------------------
1173Syntax::
1174
1175  dictresult()
1176
1177Parameters:
1178  None
1179
1180Return type:
1181  :list: result values as a list of dictionaries
1182
1183Exceptions raised:
1184  :TypeError: too many (any) parameters
1185  :MemoryError: internal memory error
1186
1187Description:
1188  This method returns the list of the values returned by the query
1189  with each tuple returned as a dictionary with the field names
1190  used as the dictionary index.
1191
1192namedresult - get query values as list of named tuples
1193------------------------------------------------------
1194Syntax::
1195
1196  namedresult()
1197
1198Parameters:
1199  None
1200
1201Return type:
1202  :list: result values as a list of named tuples
1203
1204Exceptions raised:
1205  :TypeError: too many (any) parameters
1206  :TypeError: named tuples not supported
1207  :MemoryError: internal memory error
1208
1209Description:
1210  This method returns the list of the values returned by the query
1211  with each row returned as a named tuple with proper field names.
1212
1213listfields - lists fields names of previous query result
1214--------------------------------------------------------
1215Syntax::
1216
1217  listfields()
1218
1219Parameters:
1220  None
1221
1222Return type:
1223  :list: field names
1224
1225Exceptions raised:
1226  :TypeError: too many parameters
1227
1228Description:
1229  This method returns the list of names of the fields defined for the
1230  query result. The fields are in the same order as the result values.
1231
1232fieldname, fieldnum - field name/number conversion
1233--------------------------------------------------
1234Syntax::
1235
1236  fieldname(i)
1237
1238Parameters:
1239  :i: field number (integer)
1240
1241Return type:
1242  :string: field name
1243
1244Exceptions raised:
1245  :TypeError: invalid connection, bad parameter type, or too many parameters
1246  :ValueError: invalid field number
1247
1248Description:
1249  This method allows to find a field name from its rank number. It can be
1250  useful for displaying a result. The fields are in the same order as the
1251  result values.
1252
1253Syntax::
1254
1255  fieldnum(name)
1256
1257Parameters:
1258  :name: field name (string)
1259
1260Return type:
1261  :integer: field number
1262
1263Exceptions raised:
1264  :TypeError: invalid connection, bad parameter type, or too many parameters
1265  :ValueError: unknown field name
1266
1267Description:
1268  This method returns a field number from its name. It can be used to
1269  build a function that converts result list strings to their correct
1270  type, using a hardcoded table definition. The number returned is the
1271  field rank in the result values list.
1272
1273ntuples - return number of tuples in query object
1274-------------------------------------------------
1275Syntax::
1276
1277  ntuples()
1278
1279Parameters:
1280  None
1281
1282Return type:
1283  :integer: number of tuples in `pgqueryobject`
1284
1285Exceptions raised:
1286  :TypeError: Too many arguments.
1287
1288Description:
1289  This method returns the number of tuples found in a query.
1290
1291
1292Large objects: pglarge
1293======================
1294This object handles all the request concerning a PostgreSQL large object. It
1295embeds and hides all the "recurrent" variables (object OID and connection),
1296exactly in the same way `pgobjects` do, thus only keeping significant
1297parameters in function calls. It keeps a reference to the `pgobject` used for
1298its creation, sending requests though with its parameters. Any modification but
1299dereferencing the `pgobject` will thus affect the `pglarge` object.
1300Dereferencing the initial `pgobject` is not a problem since Python won't
1301deallocate it before the `pglarge` object dereference it.
1302All functions return a generic error message on call error, whatever the
1303exact error was. The `error` attribute of the object allows to get the exact
1304error message.
1305
1306See also the PostgreSQL programmer's guide for more information about the
1307large object interface.
1308
1309open - opens a large object
1310---------------------------
1311Syntax::
1312
1313  open(mode)
1314
1315Parameters:
1316  :mode: open mode definition (integer)
1317
1318Return type:
1319  None
1320
1321Exceptions raised:
1322  :TypeError: invalid connection, bad parameter type, or too many parameters
1323  :IOError: already opened object, or open error
1324
1325Description:
1326  This method opens a large object for reading/writing, in the same way than
1327  the Unix open() function. The mode value can be obtained by OR-ing the
1328  constants defined in the pgmodule (INV_READ, INV_WRITE).
1329
1330close - closes a large object
1331-----------------------------
1332Syntax::
1333
1334  close()
1335
1336Parameters:
1337  None
1338
1339Return type:
1340  None
1341
1342Exceptions raised:
1343  :TypeError: invalid connection
1344  :TypeError: too many parameters
1345  :IOError: object is not opened, or close error
1346
1347Description:
1348  This method closes a previously opened large object, in the same way than
1349  the Unix close() function.
1350
1351read, write, tell, seek, unlink - file like large object handling
1352-----------------------------------------------------------------
1353Syntax::
1354
1355  read(size)
1356
1357Parameters:
1358  :size: maximal size of the buffer to be read
1359
1360Return type:
1361  :sized string: the read buffer
1362
1363Exceptions raised:
1364  :TypeError: invalid connection, invalid object,
1365    bad parameter type, or too many parameters
1366  :ValueError: if `size` is negative
1367  :IOError: object is not opened, or read error
1368
1369Description:
1370  This function allows to read data from a large object, starting at current
1371  position.
1372
1373Syntax::
1374
1375  write(string)
1376
1377Parameters:
1378  (sized) string - buffer to be written
1379
1380Return type:
1381  None
1382
1383Exceptions raised:
1384  :TypeError: invalid connection, bad parameter type, or too many parameters
1385  :IOError: object is not opened, or write error
1386
1387Description:
1388  This function allows to write data to a large object, starting at current
1389  position.
1390
1391Syntax::
1392
1393  seek(offset, whence)
1394
1395Parameters:
1396  :offset: position offset
1397  :whence: positional parameter
1398
1399Return type:
1400  :integer: new position in object
1401
1402Exceptions raised:
1403  :TypeError: binvalid connection or invalid object,
1404    bad parameter type, or too many parameters
1405  :IOError: object is not opened, or seek error
1406
1407Description:
1408  This method allows to move the position cursor in the large object. The
1409  whence parameter can be obtained by OR-ing the constants defined in the
1410  `pg` module (`SEEK_SET`, `SEEK_CUR`, `SEEK_END`).
1411
1412Syntax::
1413
1414  tell()
1415
1416Parameters:
1417  None
1418
1419Return type:
1420  :integer: current position in large object
1421
1422Exceptions raised:
1423  :TypeError: invalid connection or invalid object
1424  :TypeError: too many parameters
1425  :IOError: object is not opened, or seek error
1426
1427Description:
1428  This method allows to get the current position in the large object.
1429
1430Syntax::
1431
1432  unlink()
1433
1434Parameter:
1435  None
1436
1437Return type:
1438  None
1439
1440Exceptions raised:
1441  :TypeError: invalid connection or invalid object
1442  :TypeError: too many parameters
1443  :IOError: object is not closed, or unlink error
1444
1445Description:
1446  This methods unlinks (deletes) the PostgreSQL large object.
1447
1448size - gives the large object size
1449----------------------------------
1450
1451Syntax::
1452
1453  size()
1454
1455Parameters:
1456  None
1457
1458Return type:
1459  :integer: the large object size
1460
1461Exceptions raised:
1462  :TypeError: invalid connection or invalid object
1463  :TypeError: too many parameters
1464  :IOError: object is not opened, or seek/tell error
1465
1466Description:
1467  This (composite) method allows to get the size of a large object. It was
1468  implemented because this function is very useful for a web interfaced
1469  database. Currently, the large object needs to be opened first.
1470
1471export - saves a large object to a file
1472---------------------------------------
1473Syntax::
1474
1475  export(name)
1476
1477Parameters:
1478  :name: file to be created
1479
1480Return type:
1481  None
1482
1483Exceptions raised:
1484  :TypeError: invalid connection or invalid object,
1485    bad parameter type, or too many parameters
1486  :IOError: object is not closed, or export error
1487
1488Description:
1489  This methods allows to dump the content of a large object in a very simple
1490  way. The exported file is created on the host of the program, not the
1491  server host.
1492
1493Object attributes
1494-----------------
1495`pglarge` objects define a read-only set of attributes that allow to get
1496some information about it. These attributes are:
1497
1498  :oid:   the OID associated with the object
1499  :pgcnx: the `pgobject` associated with the object
1500  :error: the last warning/error message of the connection
1501
1502.. caution:: *Be careful*:
1503  In multithreaded environments, `error` may be modified by another thread
1504  using the same pgobject. Remember these object are shared, not duplicated.
1505  You should provide some locking to be able if you want to check this.
1506  The `oid` attribute is very interesting because it allow you reuse the OID
1507  later, creating the `pglarge` object with a `pgobject` getlo() method call.
Note: See TracBrowser for help on using the repository browser.