source: trunk/docs/contents/pg/connection.rst @ 963

Last change on this file since 963 was 963, checked in by cito, 9 months ago

Fix doc error: get/set_cast_hook are connection methods

File size: 18.8 KB
Line 
1Connection -- The connection object
2===================================
3
4.. py:currentmodule:: pg
5
6.. class:: Connection
7
8This object handles a connection to a PostgreSQL database. It embeds and
9hides all the parameters that define this connection, thus just leaving really
10significant parameters in function calls.
11
12.. note::
13
14    Some methods give direct access to the connection socket.
15    *Do not use them unless you really know what you are doing.*
16    If you prefer disabling them,
17    set the ``-DNO_DIRECT`` option in the Python setup file.
18    These methods are specified by the tag [DA].
19
20.. note::
21
22    Some other methods give access to large objects
23    (refer to PostgreSQL user manual for more information about these).
24    If you want to forbid access to these from the module,
25    set the ``-DNO_LARGE`` option in the Python setup file.
26    These methods are specified by the tag [LO].
27
28query -- execute a SQL command string
29-------------------------------------
30
31.. method:: Connection.query(command, [args])
32
33    Execute a SQL command string
34
35    :param str command: SQL command
36    :param args: optional parameter values
37    :returns: result values
38    :rtype: :class:`Query`, None
39    :raises TypeError: bad argument type, or too many arguments
40    :raises TypeError: invalid connection
41    :raises ValueError: empty SQL query or lost connection
42    :raises pg.ProgrammingError: error in query
43    :raises pg.InternalError: error during query processing
44
45This method simply sends a SQL query to the database. If the query is an
46insert statement that inserted exactly one row into a table that has OIDs,
47the return value is the OID of the newly inserted row as an integer.
48If the query is an update or delete statement, or an insert statement that
49did not insert exactly one row, or on a table without OIDs, then the number
50of rows affected is returned as a string. If it is a statement that returns
51rows as a result (usually a select statement, but maybe also an
52``"insert/update ... returning"`` statement), this method returns
53a :class:`Query` that can be accessed via the
54:meth:`Query.getresult`, :meth:`Query.dictresult` or
55:meth:`Query.namedresult` methods or simply printed.
56Otherwise, it returns ``None``.
57
58The SQL command may optionally contain positional parameters of the form
59``$1``, ``$2``, etc instead of literal data, in which case the values
60must be supplied separately as a tuple.  The values are substituted by
61the database in such a way that they don't need to be escaped, making this
62an effective way to pass arbitrary or unknown data without worrying about
63SQL injection or syntax errors.
64
65When the database could not process the query, a :exc:`pg.ProgrammingError` or
66a :exc:`pg.InternalError` is raised. You can check the ``SQLSTATE`` error code
67of this error by reading its :attr:`sqlstate` attribute.
68
69Example::
70
71    name = input("Name? ")
72    phone = con.query("select phone from employees where name=$1",
73        (name,)).getresult()
74
75query_prepared -- execute a prepared statement
76----------------------------------------------
77
78.. method:: Connection.query_prepared(name, [args])
79
80    Execute a prepared statement
81
82    :param str name: name of the prepared statement
83    :param args: optional parameter values
84    :returns: result values
85    :rtype: :class:`Query`, None
86    :raises TypeError: bad argument type, or too many arguments
87    :raises TypeError: invalid connection
88    :raises ValueError: empty SQL query or lost connection
89    :raises pg.ProgrammingError: error in query
90    :raises pg.InternalError: error during query processing
91    :raises pg.OperationalError: prepared statement does not exist
92
93This method works exactly like :meth:`Connection.query` except that instead
94of passing the command itself, you pass the name of a prepared statement.
95An empty name corresponds to the unnamed statement.  You must have previously
96created the corresponding named or unnamed statement with
97:meth:`Connection.prepare`, or an :exc:`pg.OperationalError` will be raised.
98
99.. versionadded:: 5.1
100
101prepare -- create a prepared statement
102--------------------------------------
103
104.. method:: Connection.prepare(name, command)
105
106    Create a prepared statement
107
108    :param str name: name of the prepared statement
109    :param str command: SQL command
110    :rtype: None
111    :raises TypeError: bad argument types, or wrong number of arguments
112    :raises TypeError: invalid connection
113    :raises pg.ProgrammingError: error in query or duplicate query
114
115This method creates a prepared statement with the specified name for the
116given command for later execution with the :meth:`Connection.query_prepared`
117method. The name can be empty to create an unnamed statement, in which case
118any pre-existing unnamed statement is automatically replaced; otherwise a
119:exc:`pg.ProgrammingError` is raised if the statement name is already defined
120in the current database session.
121
122The SQL command may optionally contain positional parameters of the form
123``$1``, ``$2``, etc instead of literal data.  The corresponding values
124must then later be passed to the :meth:`Connection.query_prepared` method
125separately as a tuple.
126
127.. versionadded:: 5.1
128
129describe_prepared -- describe a prepared statement
130--------------------------------------------------
131
132.. method:: Connection.describe_prepared(name)
133
134    Describe a prepared statement
135
136    :param str name: name of the prepared statement
137    :rtype: :class:`Query`
138    :raises TypeError: bad argument type, or too many arguments
139    :raises TypeError: invalid connection
140    :raises pg.OperationalError: prepared statement does not exist
141
142This method returns a :class:`Query` object describing the prepared
143statement with the given name.  You can also pass an empty name in order
144to describe the unnamed statement.  Information on the fields of the
145corresponding query can be obtained through the :meth:`Query.listfields`,
146:meth:`Query.fieldname` and :meth:`Query.fieldnum` methods.
147
148.. versionadded:: 5.1
149
150reset -- reset the connection
151-----------------------------
152
153.. method:: Connection.reset()
154
155    Reset the :mod:`pg` connection
156
157    :rtype: None
158    :raises TypeError: too many (any) arguments
159    :raises TypeError: invalid connection
160
161This method resets the current database connection.
162
163cancel -- abandon processing of current SQL command
164---------------------------------------------------
165
166.. method:: Connection.cancel()
167
168    :rtype: None
169    :raises TypeError: too many (any) arguments
170    :raises TypeError: invalid connection
171
172This method requests that the server abandon processing
173of the current SQL command.
174
175close -- close the database connection
176--------------------------------------
177
178.. method:: Connection.close()
179
180    Close the :mod:`pg` connection
181
182    :rtype: None
183    :raises TypeError: too many (any) arguments
184
185This method closes the database connection. The connection will
186be closed in any case when the connection is deleted but this
187allows you to explicitly close it. It is mainly here to allow
188the DB-SIG API wrapper to implement a close function.
189
190transaction -- get the current transaction state
191------------------------------------------------
192
193.. method:: Connection.transaction()
194
195    Get the current in-transaction status of the server
196
197    :returns: the current in-transaction status
198    :rtype: int
199    :raises TypeError: too many (any) arguments
200    :raises TypeError: invalid connection
201
202The status returned by this method can be :const:`TRANS_IDLE` (currently idle),
203:const:`TRANS_ACTIVE` (a command is in progress), :const:`TRANS_INTRANS` (idle,
204in a valid transaction block), or :const:`TRANS_INERROR` (idle, in a failed
205transaction block).  :const:`TRANS_UNKNOWN` is reported if the connection is
206bad.  The status :const:`TRANS_ACTIVE` is reported only when a query has been
207sent to the server and not yet completed.
208
209parameter -- get a current server parameter setting
210---------------------------------------------------
211
212.. method:: Connection.parameter(name)
213
214    Look up a current parameter setting of the server
215
216    :param str name: the name of the parameter to look up
217    :returns: the current setting of the specified parameter
218    :rtype: str or None
219    :raises TypeError: too many (any) arguments
220    :raises TypeError: invalid connection
221
222Certain parameter values are reported by the server automatically at
223connection startup or whenever their values change.  This method can be used
224to interrogate these settings.  It returns the current value of a parameter
225if known, or *None* if the parameter is not known.
226
227You can use this method to check the settings of important parameters such as
228`server_version`, `server_encoding`, `client_encoding`, `application_name`,
229`is_superuser`, `session_authorization`, `DateStyle`, `IntervalStyle`,
230`TimeZone`, `integer_datetimes`, and `standard_conforming_strings`.
231
232Values that are not reported by this method can be requested using
233:meth:`DB.get_parameter`.
234
235.. versionadded:: 4.0
236
237date_format -- get the currently used date format
238-------------------------------------------------
239
240.. method:: Connection.date_format()
241
242    Look up the date format currently being used by the database
243
244    :returns: the current date format
245    :rtype: str
246    :raises TypeError: too many (any) arguments
247    :raises TypeError: invalid connection
248
249This method returns the current date format used by the server.  Note that
250it is cheap to call this method, since there is no database query involved
251and the setting is also cached internally.  You will need the date format
252when you want to manually typecast dates and timestamps coming from the
253database instead of using the built-in typecast functions.  The date format
254returned by this method can be directly used with date formatting functions
255such as :meth:`datetime.strptime`.  It is derived from the current setting
256of the database parameter ``DateStyle``.
257
258.. versionadded:: 5.0
259
260fileno -- get the socket used to connect to the database
261--------------------------------------------------------
262
263.. method:: Connection.fileno()
264
265    Get the socket used to connect to the database
266
267    :returns: the socket id of the database connection
268    :rtype: int
269    :raises TypeError: too many (any) arguments
270    :raises TypeError: invalid connection
271
272This method returns the underlying socket id used to connect
273to the database. This is useful for use in select calls, etc.
274
275getnotify -- get the last notify from the server
276------------------------------------------------
277
278.. method:: Connection.getnotify()
279
280    Get the last notify from the server
281
282    :returns: last notify from server
283    :rtype: tuple, None
284    :raises TypeError: too many parameters
285    :raises TypeError: invalid connection
286
287This method tries to get a notify from the server (from the SQL statement
288NOTIFY). If the server returns no notify, the methods returns None.
289Otherwise, it returns a tuple (triplet) *(relname, pid, extra)*, where
290*relname* is the name of the notify, *pid* is the process id of the
291connection that triggered the notify, and *extra* is a payload string
292that has been sent with the notification. Remember to do a listen query
293first, otherwise :meth:`Connection.getnotify` will always return ``None``.
294
295.. versionchanged:: 4.1
296    Support for payload strings was added in version 4.1.
297
298inserttable -- insert a list into a table
299-----------------------------------------
300
301.. method:: Connection.inserttable(table, values)
302
303    Insert a Python list into a database table
304
305    :param str table: the table name
306    :param list values: list of rows values
307    :rtype: None
308    :raises TypeError: invalid connection, bad argument type, or too many arguments
309    :raises MemoryError: insert buffer could not be allocated
310    :raises ValueError: unsupported values
311
312This method allows to *quickly* insert large blocks of data in a table:
313It inserts the whole values list into the given table. Internally, it
314uses the COPY command of the PostgreSQL database. The list is a list
315of tuples/lists that define the values for each inserted row. The rows
316values may contain string, integer, long or double (real) values.
317
318.. warning::
319
320    This method doesn't type check the fields according to the table definition;
321    it just looks whether or not it knows how to handle such types.
322
323get/set_cast_hook -- fallback typecast function
324-----------------------------------------------
325
326.. method:: Connection.get_cast_hook()
327
328    Get the function that handles all external typecasting
329
330    :returns: the current external typecast function
331    :rtype: callable, None
332    :raises TypeError: too many (any) arguments
333
334This returns the callback function used by PyGreSQL to provide plug-in
335Python typecast functions for the connection.
336
337.. versionadded:: 5.0
338
339.. method:: Connection.set_cast_hook(func)
340
341    Set a function that will handle all external typecasting
342
343    :param func: the function to be used as a callback
344    :rtype: None
345    :raises TypeError: the specified notice receiver is not callable
346
347This methods allows setting a custom fallback function for providing
348Python typecast functions for the connection to supplement the C
349extension module.  If you set this function to *None*, then only the typecast
350functions implemented in the C extension module are enabled.  You normally
351would not want to change this.  Instead, you can use :func:`get_typecast` and
352:func:`set_typecast` to add or change the plug-in Python typecast functions.
353
354.. versionadded:: 5.0
355
356get/set_notice_receiver -- custom notice receiver
357-------------------------------------------------
358
359.. method:: Connection.get_notice_receiver()
360
361    Get the current notice receiver
362
363    :returns: the current notice receiver callable
364    :rtype: callable, None
365    :raises TypeError: too many (any) arguments
366
367This method gets the custom notice receiver callback function that has
368been set with :meth:`Connection.set_notice_receiver`, or ``None`` if no
369custom notice receiver has ever been set on the connection.
370
371.. versionadded:: 4.1
372
373.. method:: Connection.set_notice_receiver(func)
374
375    Set a custom notice receiver
376
377    :param func: the custom notice receiver callback function
378    :rtype: None
379    :raises TypeError: the specified notice receiver is not callable
380
381This method allows setting a custom notice receiver callback function.
382When a notice or warning message is received from the server,
383or generated internally by libpq, and the message level is below
384the one set with ``client_min_messages``, the specified notice receiver
385function will be called. This function must take one parameter,
386the :class:`Notice` object, which provides the following read-only
387attributes:
388
389    .. attribute:: Notice.pgcnx
390
391        the connection
392
393    .. attribute:: Notice.message
394
395        the full message with a trailing newline
396
397    .. attribute:: Notice.severity
398
399        the level of the message, e.g. 'NOTICE' or 'WARNING'
400
401    .. attribute:: Notice.primary
402
403        the primary human-readable error message
404
405    .. attribute:: Notice.detail
406
407        an optional secondary error message
408
409    .. attribute:: Notice.hint
410
411        an optional suggestion what to do about the problem
412
413.. versionadded:: 4.1
414
415putline -- write a line to the server socket [DA]
416-------------------------------------------------
417
418.. method:: Connection.putline(line)
419
420    Write a line to the server socket
421
422    :param str line: line to be written
423    :rtype: None
424    :raises TypeError: invalid connection, bad parameter type, or too many parameters
425
426This method allows to directly write a string to the server socket.
427
428getline -- get a line from server socket [DA]
429---------------------------------------------
430
431.. method:: Connection.getline()
432
433    Get a line from server socket
434
435    :returns:  the line read
436    :rtype: str
437    :raises TypeError: invalid connection
438    :raises TypeError: too many parameters
439    :raises MemoryError: buffer overflow
440
441This method allows to directly read a string from the server socket.
442
443endcopy -- synchronize client and server [DA]
444---------------------------------------------
445
446.. method:: Connection.endcopy()
447
448    Synchronize client and server
449
450    :rtype: None
451    :raises TypeError: invalid connection
452    :raises TypeError: too many parameters
453
454The use of direct access methods may desynchronize client and server.
455This method ensure that client and server will be synchronized.
456
457locreate -- create a large object in the database [LO]
458------------------------------------------------------
459
460.. method:: Connection.locreate(mode)
461
462    Create a large object in the database
463
464    :param int mode: large object create mode
465    :returns: object handling the PostgreSQL large object
466    :rtype: :class:`LargeObject`
467    :raises TypeError: invalid connection, bad parameter type, or too many parameters
468    :raises pg.OperationalError: creation error
469
470This method creates a large object in the database. The mode can be defined
471by OR-ing the constants defined in the :mod:`pg` module (:const:`INV_READ`,
472:const:`INV_WRITE` and :const:`INV_ARCHIVE`). Please refer to PostgreSQL
473user manual for a description of the mode values.
474
475getlo -- build a large object from given oid [LO]
476-------------------------------------------------
477
478.. method:: Connection.getlo(oid)
479
480    Create a large object in the database
481
482    :param int oid: OID of the existing large object
483    :returns: object handling the PostgreSQL large object
484    :rtype: :class:`LargeObject`
485    :raises TypeError:  invalid connection, bad parameter type, or too many parameters
486    :raises ValueError: bad OID value (0 is invalid_oid)
487
488This method allows reusing a previously created large object through the
489:class:`LargeObject` interface, provided the user has its OID.
490
491loimport -- import a file to a large object [LO]
492------------------------------------------------
493
494.. method:: Connection.loimport(name)
495
496    Import a file to a large object
497
498    :param str name: the name of the file to be imported
499    :returns: object handling the PostgreSQL large object
500    :rtype: :class:`LargeObject`
501    :raises TypeError: invalid connection, bad argument type, or too many arguments
502    :raises pg.OperationalError: error during file import
503
504This methods allows to create large objects in a very simple way. You just
505give the name of a file containing the data to be used.
506
507Object attributes
508-----------------
509Every :class:`Connection` defines a set of read-only attributes that describe
510the connection and its status. These attributes are:
511
512.. attribute:: Connection.host
513
514    the host name of the server (str)
515
516.. attribute:: Connection.port
517
518    the port of the server (int)
519
520.. attribute:: Connection.db
521
522    the selected database (str)
523
524.. attribute:: Connection.options
525
526    the connection options (str)
527
528.. attribute:: Connection.user
529
530    user name on the database system (str)
531
532.. attribute:: Connection.protocol_version
533
534    the frontend/backend protocol being used (int)
535
536.. versionadded:: 4.0
537
538.. attribute:: Connection.server_version
539
540    the backend version (int, e.g. 90305 for 9.3.5)
541
542.. versionadded:: 4.0
543
544.. attribute:: Connection.status
545
546    the status of the connection (int: 1 = OK, 0 = bad)
547
548.. attribute:: Connection.error
549
550    the last warning/error message from the server (str)
Note: See TracBrowser for help on using the repository browser.