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

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

Implement "upsert" method for PostgreSQL 9.5

A new method upsert() has been added to the DB wrapper class that
nicely complements the existing get/insert/update/delete() methods.

File size: 11.9 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`` code of
65this 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
113fileno -- returns the socket used to connect to the database
114------------------------------------------------------------
115
116.. method:: Connection.fileno()
117
118   Return the socket used to connect to the database
119
120   :returns: the socket id of the database connection
121   :rtype: int
122   :raises TypeError: too many (any) arguments
123   :raises TypeError: invalid connection
124
125This method returns the underlying socket id used to connect
126to the database. This is useful for use in select calls, etc.
127
128getnotify -- get the last notify from the server
129------------------------------------------------
130
131.. method:: Connection.getnotify()
132
133    Get the last notify from the server
134
135    :returns: last notify from server
136    :rtype: tuple, None
137    :raises TypeError: too many parameters
138    :raises TypeError: invalid connection
139
140This method tries to get a notify from the server (from the SQL statement
141NOTIFY). If the server returns no notify, the methods returns None.
142Otherwise, it returns a tuple (triplet) *(relname, pid, extra)*, where
143*relname* is the name of the notify, *pid* is the process id of the
144connection that triggered the notify, and *extra* is a payload string
145that has been sent with the notification. Remember to do a listen query
146first, otherwise :meth:`Connection.getnotify` will always return ``None``.
147
148.. versionchanged:: 4.1
149    Support for payload strings was added in version 4.1.
150
151inserttable -- insert a list into a table
152-----------------------------------------
153
154.. method:: Connection.inserttable(table, values)
155
156    Insert a Python list into a database table
157
158    :param str table: the table name
159    :param list values: list of rows values
160    :rtype: None
161    :raises TypeError: invalid connection, bad argument type, or too many arguments
162    :raises MemoryError: insert buffer could not be allocated
163    :raises ValueError: unsupported values
164
165This method allows to *quickly* insert large blocks of data in a table:
166It inserts the whole values list into the given table. Internally, it
167uses the COPY command of the PostgreSQL database. The list is a list
168of tuples/lists that define the values for each inserted row. The rows
169values may contain string, integer, long or double (real) values.
170
171.. warning::
172
173    This method doesn't type check the fields according to the table definition;
174    it just look whether or not it knows how to handle such types.
175
176get/set_notice_receiver -- custom notice receiver
177-------------------------------------------------
178
179.. method:: Connection.get_notice_receiver()
180
181    Get the current notice receiver
182
183    :returns: the current notice receiver callable
184    :rtype: callable, None
185    :raises TypeError: too many (any) arguments
186
187This method gets the custom notice receiver callback function that has
188been set with :meth:`Connection.set_notice_receiver`, or ``None`` if no
189custom notice receiver has ever been set on the connection.
190
191.. versionadded:: 4.1
192
193.. method:: Connection.set_notice_receiver(proc)
194
195    Set a custom notice receiver
196
197    :param proc: the custom notice receiver callback function
198    :rtype: None
199    :raises TypeError: the specified notice receiver is not callable
200
201This method allows setting a custom notice receiver callback function.
202When a notice or warning message is received from the server,
203or generated internally by libpq, and the message level is below
204the one set with ``client_min_messages``, the specified notice receiver
205function will be called. This function must take one parameter,
206the :class:`Notice` object, which provides the following read-only
207attributes:
208
209    .. attribute:: Notice.pgcnx
210
211        the connection
212
213    .. attribute:: Notice.message
214
215        the full message with a trailing newline
216
217    .. attribute:: Notice.severity
218
219        the level of the message, e.g. 'NOTICE' or 'WARNING'
220
221    .. attribute:: Notice.primary
222
223        the primary human-readable error message
224
225    .. attribute:: Notice.detail
226
227        an optional secondary error message
228
229    .. attribute:: Notice.hint
230
231        an optional suggestion what to do about the problem
232
233.. versionadded:: 4.1
234
235putline -- write a line to the server socket [DA]
236-------------------------------------------------
237
238.. method:: Connection.putline(line)
239
240    Write a line to the server socket
241
242    :param str line: line to be written
243    :rtype: None
244    :raises TypeError: invalid connection, bad parameter type, or too many parameters
245
246This method allows to directly write a string to the server socket.
247
248getline -- get a line from server socket [DA]
249---------------------------------------------
250
251.. method:: Connection.getline()
252
253    Get a line from server socket
254
255    :returns:  the line read
256    :rtype: str
257    :raises TypeError: invalid connection
258    :raises TypeError: too many parameters
259    :raises MemoryError: buffer overflow
260
261This method allows to directly read a string from the server socket.
262
263endcopy -- synchronize client and server [DA]
264---------------------------------------------
265
266.. method:: Connection.endcopy()
267
268    Synchronize client and server
269
270    :rtype: None
271    :raises TypeError: invalid connection
272    :raises TypeError: too many parameters
273
274The use of direct access methods may desynchronize client and server.
275This method ensure that client and server will be synchronized.
276
277locreate -- create a large object in the database [LO]
278------------------------------------------------------
279
280.. method:: Connection.locreate(mode)
281
282    Create a large object in the database
283
284    :param int mode: large object create mode
285    :returns: object handling the PostGreSQL large object
286    :rtype: :class:`LargeObject`
287    :raises TypeError: invalid connection, bad parameter type, or too many parameters
288    :raises pg.OperationalError: creation error
289
290This method creates a large object in the database. The mode can be defined
291by OR-ing the constants defined in the :mod:`pg` module (:const:`INV_READ`,
292:const:`INV_WRITE` and :const:`INV_ARCHIVE`). Please refer to PostgreSQL
293user manual for a description of the mode values.
294
295getlo -- build a large object from given oid [LO]
296-------------------------------------------------
297
298.. method:: Connection.getlo(oid)
299
300    Create a large object in the database
301
302    :param int oid: OID of the existing large object
303    :returns: object handling the PostGreSQL large object
304    :rtype: :class:`LargeObject`
305    :raises TypeError:  invalid connection, bad parameter type, or too many parameters
306    :raises ValueError: bad OID value (0 is invalid_oid)
307
308This method allows to reuse a formerly created large object through the
309:class:`LargeObject` interface, providing the user have its OID.
310
311loimport -- import a file to a large object [LO]
312------------------------------------------------
313
314.. method:: Connection.loimport(name)
315
316    Import a file to a large object
317
318    :param str name: the name of the file to be imported
319    :returns: object handling the PostGreSQL large object
320    :rtype: :class:`LargeObject`
321    :raises TypeError: invalid connection, bad argument type, or too many arguments
322    :raises pg.OperationalError: error during file import
323
324This methods allows to create large objects in a very simple way. You just
325give the name of a file containing the data to be used.
326
327Object attributes
328-----------------
329Every :class:`Connection` defines a set of read-only attributes that describe
330the connection and its status. These attributes are:
331
332.. attribute:: Connection.host
333
334   the host name of the server (str)
335
336.. attribute:: Connection.port
337
338   the port of the server (int)
339
340.. attribute:: Connection.db
341
342   the selected database (str)
343
344.. attribute:: Connection.options
345
346   the connection options (str)
347
348.. attribute:: Connection.user
349
350    user name on the database system (str)
351
352.. attribute:: Connection.protocol_version
353
354   the frontend/backend protocol being used (int)
355
356.. versionadded:: 4.0
357
358.. attribute:: Connection.server_version
359
360   the backend version (int, e.g. 90305 for 9.3.5)
361
362.. versionadded:: 4.0
363
364.. attribute:: Connection.status
365
366   the status of the connection (int: 1 = OK, 0 = bad)
367
368.. attribute:: Connection.error
369
370   the last warning/error message from the server (str)
Note: See TracBrowser for help on using the repository browser.