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

Last change on this file since 957 was 957, checked in by cito, 10 months ago

Add documentation for prepared statements

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