Changeset 735 for trunk/docs

Jan 13, 2016, 4:49:35 PM (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.

2 edited


  • trunk/docs/contents/pg/connection.rst

    r710 r735  
    169169values may contain string, integer, long or double (real) values.
    171 .. note::
    173     **Be very careful**:
     171.. warning::
    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
    221222This method is the basic mechanism to get a single row. It assumes
    234 .. method:: DB.insert(table, [d,] [key = val, ...])
     235.. method:: DB.insert(table, [d], [col=val, ...])
    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
    243245This method inserts a row into a table.  If the optional dictionary is
    257 .. method:: DB.update(table, [d,] [key = val, ...])
     259.. method:: DB.update(table, [d], [col=val, ...])
    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
    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'.
     279upsert -- insert a row with conflict resolution
     282.. method:: DB.upsert(table, [d], [col=val, ...])
     284    Insert a row into a database table with conflict resolution
     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
     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.
     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.
     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:
     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`.
     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))``.
     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.
     323The dictionary is modified in any case to reflect the values in the database
     324after the operation has completed.
     326.. note::
     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.
     332.. versionadded:: 5.0
    276334query -- execute a SQL command string
Note: See TracChangeset for help on using the changeset viewer.