Changeset 735 for trunk


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
Files:
4 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
  • trunk/pg.py

    r734 r735  
    514514                raise KeyError('Class %s has no primary key' % cl)
    515515            if len(pkey) > 1:
    516                 pkey = frozenset([k[0] for k in pkey])
     516                pkey = frozenset(k[0] for k in pkey)
    517517            else:
    518518                pkey = pkey[0][0]
     
    625625        if cl.endswith('*'):  # scan descendant tables?
    626626            cl = cl[:-1].rstrip()  # need parent table name
    627         # build qualified class name
    628         # To allow users to work with multiple tables,
    629         # we munge the name of the "oid" key
    630         qoid = _oid_key(cl)
    631627        if not keyname:
    632628            # use the primary key by default
     
    639635        param = partial(self._prepare_param, params=params)
    640636        col = self.escape_identifier
    641         # We want the oid for later updates if that isn't the key
     637        # We want the oid for later updates if that isn't the key.
     638        # To allow users to work with multiple tables, we munge
     639        # the name of the "oid" key by adding the name of the class.
     640        qoid = _oid_key(cl)
    642641        if keyname == 'oid':
    643642            if isinstance(arg, dict):
     
    649648            where = 'oid = %s' % param(arg[qoid], 'int')
    650649        else:
    651             if isinstance(keyname, basestring):
    652                 keyname = (keyname,)
     650            keyname = [keyname] if isinstance(
     651                keyname, basestring) else sorted(keyname)
    653652            if not isinstance(arg, dict):
    654653                if len(keyname) > 1:
     
    656655                arg = dict((k, arg) for k in keyname)
    657656            what = ', '.join(col(k) for k in attnames)
    658             where = ' AND '.join(['%s = %s'
    659                 % (col(k), param(arg[k], attnames[k])) for k in keyname])
     657            where = ' AND '.join('%s = %s' % (
     658                col(k), param(arg[k], attnames[k])) for k in keyname)
    660659        q = 'SELECT %s FROM %s WHERE %s LIMIT 1' % (
    661660            what, self._escape_qualified_name(cl), where)
    662661        self._do_debug(q, params)
    663         res = self.db.query(q, params).dictresult()
     662        q = self.db.query(q, params)
     663        res = q.dictresult()
    664664        if not res:
    665665            raise _db_error('No such record in %s where %s' % (cl, where))
     
    689689
    690690        """
    691         qoid = _oid_key(cl)
     691        if 'oid' in kw:
     692            del kw['oid']
    692693        if d is None:
    693694            d = {}
     
    699700        names, values = [], []
    700701        for n in attnames:
    701             if n != 'oid' and n in d:
     702            if n in d:
    702703                names.append(col(n))
    703704                values.append(param(d[n], attnames[n]))
     
    707708            self._escape_qualified_name(cl), names, values, ret)
    708709        self._do_debug(q, params)
    709         res = self.db.query(q, params)
    710         res = res.dictresult()[0]
    711         for n, value in res.items():
     710        q = self.db.query(q, params)
     711        res = q.dictresult()
     712        if not res:
     713            raise _int_error('insert did not return new values')
     714        for n, value in res[0].items():
    712715            if n == 'oid':
    713                 n = qoid
     716                n = _oid_key(cl)
    714717            elif attnames.get(n) == 'bytea' and value is not None:
    715718                value = self.unescape_bytea(value)
     
    727730
    728731        """
    729         # Update always works on the oid which get returns if available,
     732        # Update always works on the oid which get() returns if available,
    730733        # otherwise use the primary key.  Fail if neither.
    731         # Note that we only accept oid key from named args for safety
     734        # Note that we only accept oid key from named args for safety.
    732735        qoid = _oid_key(cl)
    733736        if 'oid' in kw:
     
    743746        if qoid in d:
    744747            where = 'oid = %s' % param(d[qoid], 'int')
    745             keyname = ()
     748            keyname = []
    746749        else:
    747750            try:
     
    749752            except KeyError:
    750753                raise _prg_error('Class %s has no primary key' % cl)
    751             if isinstance(keyname, basestring):
    752                 keyname = (keyname,)
     754            keyname = [keyname] if isinstance(
     755                keyname, basestring) else sorted(keyname)
    753756            try:
    754                 where = ' AND '.join(['%s = %s'
    755                     % (col(k), param(d[k], attnames[k])) for k in keyname])
     757                where = ' AND '.join('%s = %s' % (
     758                    col(k), param(d[k], attnames[k])) for k in keyname)
    756759            except KeyError:
    757                 raise _prg_error('Update needs primary key or oid.')
     760                raise _prg_error('update needs primary key or oid')
     761        keyname = set(keyname)
     762        keyname.add('oid')
    758763        values = []
    759764        for n in attnames:
     
    767772            self._escape_qualified_name(cl), values, where, ret)
    768773        self._do_debug(q, params)
    769         res = self.db.query(q, params)
    770         res = res.dictresult()[0]
    771         for n, value in res.items():
    772             if n == 'oid':
    773                 n = qoid
    774             elif attnames.get(n) == 'bytea' and value is not None:
    775                 value = self.unescape_bytea(value)
    776             d[n] = value
     774        q = self.db.query(q, params)
     775        res = q.dictresult()
     776        if res:  # may be empty when row does not exist
     777            for n, value in res[0].items():
     778                if n == 'oid':
     779                    n = qoid
     780                elif attnames.get(n) == 'bytea' and value is not None:
     781                    value = self.unescape_bytea(value)
     782                d[n] = value
     783        return d
     784
     785    def upsert(self, cl, d=None, **kw):
     786        """Insert a row into a database table with conflict resolution.
     787
     788        This method inserts a row into a table, but instead of raising a
     789        ProgrammingError exception in case a row with the same primary key
     790        already exists, an update will be executed instead.  This will be
     791        performed as a single atomic operation on the database, so race
     792        conditions can be avoided.
     793
     794        Like the insert method, the first parameter is the name of the
     795        table and the second parameter can be used to pass the values to
     796        be inserted as a dictionary.
     797
     798        Unlike the insert und update statement, keyword parameters are not
     799        used to modify the dictionary, but to specify which columns shall
     800        be updated in case of a conflict, and in which way:
     801
     802        A value of False or None means the column shall not be updated,
     803        a value of True means the column shall be updated with the value
     804        that has been proposed for insertion, i.e. has been passed as value
     805        in the dictionary.  Columns that are not specified by keywords but
     806        appear as keys in the dictionary are also updated like in the case
     807        keywords had been passed with the value True.
     808
     809        So if in the case of a conflict you want to update every column that
     810        has been passed in the dictionary d , you would call upsert(cl, d).
     811        If you don't want to do anything in case of a conflict, i.e. leave
     812        the existing row as it is, call upsert(cl, d, **dict.fromkeys(d)).
     813
     814        If you need more fine-grained control of what gets updated, you can
     815        also pass strings in the keyword parameters.  These strings will
     816        be used as SQL expressions for the update columns.  In these
     817        expressions you can refer to the value that already exists in
     818        the table by prefixing the column name with "included.", and to
     819        the value that has been proposed for insertion by prefixing the
     820        column name with the "excluded."
     821
     822        The dictionary is modified in any case to reflect the values in
     823        the database after the operation has completed.
     824
     825        Note: The method uses the PostgreSQL "upsert" feature which is
     826        only available since PostgreSQL 9.5.
     827
     828        """
     829        if 'oid' in kw:
     830            del kw['oid']
     831        if d is None:
     832            d = {}
     833        attnames = self.get_attnames(cl)
     834        params = []
     835        param = partial(self._prepare_param,params=params)
     836        col = self.escape_identifier
     837        names, values, updates = [], [], []
     838        for n in attnames:
     839            if n in d:
     840                names.append(col(n))
     841                values.append(param(d[n], attnames[n]))
     842        names, values = ', '.join(names), ', '.join(values)
     843        try:
     844            keyname = self.pkey(cl)
     845        except KeyError:
     846            raise _prg_error('Class %s has no primary key' % cl)
     847        keyname = [keyname] if isinstance(
     848            keyname, basestring) else sorted(keyname)
     849        try:
     850            target = ', '.join(col(k) for k in keyname)
     851        except KeyError:
     852            raise _prg_error('upsert needs primary key or oid')
     853        update = []
     854        keyname = set(keyname)
     855        keyname.add('oid')
     856        for n in attnames:
     857            if n not in keyname:
     858                value = kw.get(n, True)
     859                if value:
     860                    if not isinstance(value, basestring):
     861                        value = 'excluded.%s' % col(n)
     862                    update.append('%s = %s' % (col(n), value))
     863        if not values and not update:
     864            return d
     865        do = 'update set %s' % ', '.join(update) if update else 'nothing'
     866        ret = 'oid, *' if 'oid' in attnames else '*'
     867        q = ('INSERT INTO %s AS included (%s) VALUES (%s)'
     868            ' ON CONFLICT (%s) DO %s RETURNING %s') % (
     869                self._escape_qualified_name(cl), names, values,
     870                target, do, ret)
     871        self._do_debug(q, params)
     872        try:
     873            q = self.db.query(q, params)
     874        except ProgrammingError:
     875            if self.server_version < 90500:
     876                raise _prg_error('upsert not supported by PostgreSQL version')
     877            raise  # re-raise original error
     878        res = q.dictresult()
     879        if res:  # may be empty with "do nothing"
     880            for n, value in res[0].items():
     881                if n == 'oid':
     882                    n = _oid_key(cl)
     883                elif attnames.get(n) == 'bytea':
     884                    value = self.unescape_bytea(value)
     885                d[n] = value
     886        elif update:
     887            raise _int_error('upsert did not return new values')
     888        else:
     889            self.get(cl, d)
    777890        return d
    778891
     
    815928        # One day we will be testing that the record to be deleted
    816929        # isn't referenced somewhere (or else PostgreSQL will).
    817         # Note that we only accept oid key from named args for safety
     930        # Note that we only accept oid key from named args for safety.
    818931        qoid = _oid_key(cl)
    819932        if 'oid' in kw:
     
    832945            except KeyError:
    833946                raise _prg_error('Class %s has no primary key' % cl)
    834             if isinstance(keyname, basestring):
    835                 keyname = (keyname,)
     947            keyname = [keyname] if isinstance(
     948                keyname, basestring) else sorted(keyname)
    836949            attnames = self.get_attnames(cl)
    837950            col = self.escape_identifier
    838951            try:
    839                 where = ' AND '.join(['%s = %s'
    840                     % (col(k), param(d[k], attnames[k])) for k in keyname])
     952                where = ' AND '.join('%s = %s'
     953                    % (col(k), param(d[k], attnames[k])) for k in keyname)
    841954            except KeyError:
    842                 raise _prg_error('Delete needs primary key or oid.')
     955                raise _prg_error('delete needs primary key or oid')
    843956        q = 'DELETE FROM %s WHERE %s' % (
    844957            self._escape_qualified_name(cl), where)
    845958        self._do_debug(q, params)
    846         return int(self.db.query(q, params))
     959        res = self.db.query(q, params)
     960        return int(res)
    847961
    848962    def notification_handler(self, event, callback, arg_dict={}, timeout=None):
  • trunk/tests/test_classic_dbwrapper.py

    r732 r735  
    133133            'unescape_bytea',
    134134            'update',
     135            'upsert',
    135136            'use_regtypes',
    136137            'user',
     
    868869        s = update(table, r)
    869870        self.assertEqual(s, r)
    870         r = query('select t from "%s" where n=2' % table
    871                   ).getresult()[0][0]
     871        q = 'select t from "%s" where n=2' % table
     872        r = query(q).getresult()[0][0]
    872873        self.assertEqual(r, 'u')
    873874        query('drop table "%s"' % table)
     
    885886        self.assertRaises(pg.ProgrammingError, update,
    886887                          table, dict(t='b'))
    887         self.assertEqual(update(table, dict(n=2, t='d'))['t'], 'd')
    888         r = query('select t from "%s" where n=2' % table
    889                   ).getresult()[0][0]
     888        s = dict(n=2, t='d')
     889        r = update(table, s)
     890        self.assertIs(r, s)
     891        self.assertEqual(r['n'], 2)
     892        self.assertEqual(r['t'], 'd')
     893        q = 'select t from "%s" where n=2' % table
     894        r = query(q).getresult()[0][0]
    890895        self.assertEqual(r, 'd')
     896        s.update(dict(n=4, t='e'))
     897        r = update(table, s)
     898        self.assertEqual(r['n'], 4)
     899        self.assertEqual(r['t'], 'e')
     900        q = 'select t from "%s" where n=2' % table
     901        r = query(q).getresult()[0][0]
     902        self.assertEqual(r, 'd')
     903        q = 'select t from "%s" where n=4' % table
     904        r = query(q).getresult()
     905        self.assertEqual(len(r), 0)
    891906        query('drop table "%s"' % table)
    892907        table = 'update_test_table_2'
     
    903918        self.assertEqual(update(table,
    904919                                dict(n=2, m=2, t='x'))['t'], 'x')
    905         r = [r[0] for r in query('select t from "%s" where n=2'
    906             ' order by m' % table).getresult()]
     920        q = 'select t from "%s" where n=2 order by m' % table
     921        r = [r[0] for r in query(q).getresult()]
    907922        self.assertEqual(r, ['c', 'x'])
    908923        query('drop table "%s"' % table)
     
    931946        self.assertEqual(r['Questions?'], 'When?')
    932947        query('drop table "%s"' % table)
     948
     949    def testUpsert(self):
     950        upsert = self.db.upsert
     951        query = self.db.query
     952        table = 'upsert_test_table'
     953        query('drop table if exists "%s"' % table)
     954        query('create table "%s" ('
     955            "n integer primary key, t text) with oids" % table)
     956        s = dict(n=1, t='x')
     957        try:
     958            r = upsert(table, s)
     959        except pg.ProgrammingError as error:
     960            if self.db.server_version < 90500:
     961                self.skipTest('database does not support upsert')
     962            self.fail(str(error))
     963        self.assertIs(r, s)
     964        self.assertEqual(r['n'], 1)
     965        self.assertEqual(r['t'], 'x')
     966        s.update(n=2, t='y')
     967        r = upsert(table, s, **dict.fromkeys(s))
     968        self.assertIs(r, s)
     969        self.assertEqual(r['n'], 2)
     970        self.assertEqual(r['t'], 'y')
     971        q = 'select n, t from "%s" order by n limit 3' % table
     972        r = query(q).getresult()
     973        self.assertEqual(r, [(1, 'x'), (2, 'y')])
     974        s.update(t='z')
     975        r = upsert(table, s)
     976        self.assertIs(r, s)
     977        self.assertEqual(r['n'], 2)
     978        self.assertEqual(r['t'], 'z')
     979        r = query(q).getresult()
     980        self.assertEqual(r, [(1, 'x'), (2, 'z')])
     981        s.update(t='n')
     982        r = upsert(table, s, t=False)
     983        self.assertIs(r, s)
     984        self.assertEqual(r['n'], 2)
     985        self.assertEqual(r['t'], 'z')
     986        r = query(q).getresult()
     987        self.assertEqual(r, [(1, 'x'), (2, 'z')])
     988        s.update(t='y')
     989        r = upsert(table, s, t=True)
     990        self.assertIs(r, s)
     991        self.assertEqual(r['n'], 2)
     992        self.assertEqual(r['t'], 'y')
     993        r = query(q).getresult()
     994        self.assertEqual(r, [(1, 'x'), (2, 'y')])
     995        s.update(t='n')
     996        r = upsert(table, s, t="included.t || '2'")
     997        self.assertIs(r, s)
     998        self.assertEqual(r['n'], 2)
     999        self.assertEqual(r['t'], 'y2')
     1000        r = query(q).getresult()
     1001        self.assertEqual(r, [(1, 'x'), (2, 'y2')])
     1002        s.update(t='y')
     1003        r = upsert(table, s, t="excluded.t || '3'")
     1004        self.assertIs(r, s)
     1005        self.assertEqual(r['n'], 2)
     1006        self.assertEqual(r['t'], 'y3')
     1007        r = query(q).getresult()
     1008        self.assertEqual(r, [(1, 'x'), (2, 'y3')])
     1009        s.update(n=1, t='2')
     1010        r = upsert(table, s, t="included.t || excluded.t")
     1011        self.assertIs(r, s)
     1012        self.assertEqual(r['n'], 1)
     1013        self.assertEqual(r['t'], 'x2')
     1014        r = query(q).getresult()
     1015        self.assertEqual(r, [(1, 'x2'), (2, 'y3')])
     1016        query('drop table "%s"' % table)
     1017
     1018    def testUpsertWithCompositeKey(self):
     1019        upsert = self.db.upsert
     1020        query = self.db.query
     1021        table = 'upsert_test_table_2'
     1022        query('drop table if exists "%s"' % table)
     1023        query('create table "%s" ('
     1024            "n integer, m integer, t text, primary key (n, m))" % table)
     1025        s = dict(n=1, m=2, t='x')
     1026        try:
     1027            r = upsert(table, s)
     1028        except pg.ProgrammingError as error:
     1029            if self.db.server_version < 90500:
     1030                self.skipTest('database does not support upsert')
     1031            self.fail(str(error))
     1032        self.assertIs(r, s)
     1033        self.assertEqual(r['n'], 1)
     1034        self.assertEqual(r['m'], 2)
     1035        self.assertEqual(r['t'], 'x')
     1036        s.update(m=3, t='y')
     1037        r = upsert(table, s, **dict.fromkeys(s))
     1038        self.assertIs(r, s)
     1039        self.assertEqual(r['n'], 1)
     1040        self.assertEqual(r['m'], 3)
     1041        self.assertEqual(r['t'], 'y')
     1042        q = 'select n, m, t from "%s" order by n, m limit 3' % table
     1043        r = query(q).getresult()
     1044        self.assertEqual(r, [(1, 2, 'x'), (1, 3, 'y')])
     1045        s.update(t='z')
     1046        r = upsert(table, s)
     1047        self.assertIs(r, s)
     1048        self.assertEqual(r['n'], 1)
     1049        self.assertEqual(r['m'], 3)
     1050        self.assertEqual(r['t'], 'z')
     1051        r = query(q).getresult()
     1052        self.assertEqual(r, [(1, 2, 'x'), (1, 3, 'z')])
     1053        s.update(t='n')
     1054        r = upsert(table, s, t=False)
     1055        self.assertIs(r, s)
     1056        self.assertEqual(r['n'], 1)
     1057        self.assertEqual(r['m'], 3)
     1058        self.assertEqual(r['t'], 'z')
     1059        r = query(q).getresult()
     1060        self.assertEqual(r, [(1, 2, 'x'), (1, 3, 'z')])
     1061        s.update(t='n')
     1062        r = upsert(table, s, t=True)
     1063        self.assertIs(r, s)
     1064        self.assertEqual(r['n'], 1)
     1065        self.assertEqual(r['m'], 3)
     1066        self.assertEqual(r['t'], 'n')
     1067        r = query(q).getresult()
     1068        self.assertEqual(r, [(1, 2, 'x'), (1, 3, 'n')])
     1069        s.update(n=2, t='y')
     1070        r = upsert(table, s, t="'z'")
     1071        self.assertIs(r, s)
     1072        self.assertEqual(r['n'], 2)
     1073        self.assertEqual(r['m'], 3)
     1074        self.assertEqual(r['t'], 'y')
     1075        r = query(q).getresult()
     1076        self.assertEqual(r, [(1, 2, 'x'), (1, 3, 'n'), (2, 3, 'y')])
     1077        s.update(n=1, t='m')
     1078        r = upsert(table, s, t='included.t || excluded.t')
     1079        self.assertIs(r, s)
     1080        self.assertEqual(r['n'], 1)
     1081        self.assertEqual(r['m'], 3)
     1082        self.assertEqual(r['t'], 'nm')
     1083        r = query(q).getresult()
     1084        self.assertEqual(r, [(1, 2, 'x'), (1, 3, 'nm'), (2, 3, 'y')])
     1085        query('drop table "%s"' % table)
     1086
     1087    def testUpsertWithQuotedNames(self):
     1088        upsert = self.db.upsert
     1089        query = self.db.query
     1090        table = 'test table for upsert()'
     1091        query('drop table if exists "%s"' % table)
     1092        query('create table "%s" ('
     1093            '"Prime!" smallint primary key,'
     1094            '"much space" integer, "Questions?" text)' % table)
     1095        s = {'Prime!': 31, 'much space': 9009, 'Questions?': 'Yes.'}
     1096        try:
     1097            r = upsert(table, s)
     1098        except pg.ProgrammingError as error:
     1099            if self.db.server_version < 90500:
     1100                self.skipTest('database does not support upsert')
     1101            self.fail(str(error))
     1102        self.assertIs(r, s)
     1103        self.assertEqual(r['Prime!'], 31)
     1104        self.assertEqual(r['much space'], 9009)
     1105        self.assertEqual(r['Questions?'], 'Yes.')
     1106        q = 'select * from "%s" limit 2' % table
     1107        r = query(q).getresult()
     1108        self.assertEqual(r, [(31, 9009, 'Yes.')])
     1109        s.update({'Questions?': 'No.'})
     1110        r = upsert(table, s)
     1111        self.assertIs(r, s)
     1112        self.assertEqual(r['Prime!'], 31)
     1113        self.assertEqual(r['much space'], 9009)
     1114        self.assertEqual(r['Questions?'], 'No.')
     1115        r = query(q).getresult()
     1116        self.assertEqual(r, [(31, 9009, 'No.')])
    9331117
    9341118    def testClear(self):
Note: See TracChangeset for help on using the changeset viewer.