Changeset 735 for trunk/docs


Ignore:
Timestamp:
Jan 13, 2016, 4:49:35 PM (4 years ago)
Author:
cito
Message:

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.

Location:
trunk/docs/contents/pg
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • trunk/docs/contents/pg/connection.rst

    r710 r735  
    169169values may contain string, integer, long or double (real) values.
    170170
    171 .. note::
    172 
    173     **Be very careful**:
     171.. warning::
     172
    174173    This method doesn't type check the fields according to the table definition;
    175174    it just look whether or not it knows how to handle such types.
  • trunk/docs/contents/pg/db_wrapper.rst

    r734 r735  
    218218    :returns: A dictionary - the keys are the attribute names,
    219219      the values are the row values.
     220    :raises ProgrammingError: no primary key or missing privilege
    220221
    221222This method is the basic mechanism to get a single row. It assumes
     
    232233--------------------------------------------
    233234
    234 .. method:: DB.insert(table, [d,] [key = val, ...])
     235.. method:: DB.insert(table, [d], [col=val, ...])
    235236
    236237    Insert a row into a database table
     
    238239    :param str table: name of table
    239240    :param dict d: optional dictionary of values
    240     :returns: the inserted values
     241    :returns: the inserted values in the database
    241242    :rtype: dict
     243    :raises ProgrammingError: missing privilege or conflict
    242244
    243245This method inserts a row into a table.  If the optional dictionary is
     
    255257------------------------------------------
    256258
    257 .. method:: DB.update(table, [d,] [key = val, ...])
     259.. method:: DB.update(table, [d], [col=val, ...])
    258260
    259261    Update a row in a database table
     
    261263    :param str table: name of table
    262264    :param dict d: optional dictionary of values
    263     :returns: the new row
     265    :returns: the new row in the database
    264266    :rtype: dict
     267    :raises ProgrammingError: no primary key or missing privilege
    265268
    266269Similar to insert but updates an existing row.  The update is based on the
     
    273276either in the dictionary where the OID must be munged, or in the keywords
    274277where it can be simply the string 'oid'.
     278
     279upsert -- insert a row with conflict resolution
     280-----------------------------------------------
     281
     282.. method:: DB.upsert(table, [d], [col=val, ...])
     283
     284    Insert a row into a database table with conflict resolution
     285
     286    :param str table: name of table
     287    :param dict d: optional dictionary of values
     288    :returns: the new row in the database
     289    :rtype: dict
     290    :raises ProgrammingError: no primary key or missing privilege
     291
     292This method inserts a row into a table, but instead of raising a
     293ProgrammingError exception in case a row with the same primary key already
     294exists, an update will be executed instead.  This will be performed as a
     295single atomic operation on the database, so race conditions can be avoided.
     296
     297Like the insert method, the first parameter is the name of the table and the
     298second parameter can be used to pass the values to be inserted as a dictionary.
     299
     300Unlike the insert und update statement, keyword parameters are not used to
     301modify the dictionary, but to specify which columns shall be updated in case
     302of a conflict, and in which way:
     303
     304A value of `False` or `None` means the column shall not be updated,
     305a value of `True` means the column shall be updated with the value that
     306has been proposed for insertion, i.e. has been passed as value in the
     307dictionary.  Columns that are not specified by keywords but appear as keys
     308in the dictionary are also updated like in the case keywords had been passed
     309with the value `True`.
     310
     311So if in the case of a conflict you want to update every column that has been
     312passed in the dictionary `d` , you would call ``upsert(cl, d)``. If you don't
     313want to do anything in case of a conflict, i.e. leave the existing row as it
     314is, call ``upsert(cl, d, **dict.fromkeys(d))``.
     315
     316If you need more fine-grained control of what gets updated, you can also pass
     317strings in the keyword parameters.  These strings will be used as SQL
     318expressions for the update columns.  In these expressions you can refer
     319to the value that already exists in the table by writing the table prefix
     320``included.`` before the column name, and you can refer to the value that
     321has been proposed for insertion by writing ``excluded.`` as table prefix.
     322
     323The dictionary is modified in any case to reflect the values in the database
     324after the operation has completed.
     325
     326.. note::
     327
     328    The method uses the PostgreSQL "upsert" feature which is only available
     329    since PostgreSQL 9.5. With older PostgreSQL versions, you will get a
     330    ProgrammingError if you use this method.
     331
     332.. versionadded:: 5.0
    275333
    276334query -- execute a SQL command string
Note: See TracChangeset for help on using the changeset viewer.