source: trunk/docs/classic.txt @ 637

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

More doc cleanup in the trunk

The last commit was not complete

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