source: trunk/docs/classic.txt @ 638

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

Update the documentation

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