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

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

Add typecasting of dates, times, timestamps, intervals

So far, PyGreSQL has returned these types only as strings (in various
formats depending on the DateStyle? setting) and left it to the user
to parse and interpret the strings. These types are now properly cast
into the corresponding detetime types of Python, and this works with
any setting of DatesStyle?, even if you change DateStyle? in the middle
of a database session.

To implement this, a fast method for getting the datestyle (cached and
without roundtrip to the database) has been added. Also, the typecast
mechanism has been extended so that typecast functions can optionally
also take the connection as argument.

The date and time typecast functions have been implemented in Python
using the new typecast registry and added to both pg and pgdb. Some
duplication of code in the two modules was unavoidable, since we don't
want the modules to be dependent of each other or install additional
helper modules. One day we might want to change this, put everything
in one package and factor out some of the functionality.

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