Changeset 995 for trunk


Ignore:
Timestamp:
Apr 25, 2019, 10:10:20 AM (3 months ago)
Author:
cito
Message:

Support autocommit attribute on pgdb connections

Location:
trunk
Files:
4 edited

Legend:

Unmodified
Added
Removed
  • trunk/docs/contents/changelog.rst

    r994 r995  
    3333    - Added new connection attributes `socket`, `backend_pid`, `ssl_in_use`
    3434      and `ssl_attributes` (the latter need PostgreSQL >= 9.5 on the client).
     35- Changes in the DB-API 2 module (pgdb):
     36    - Connections now have an `autocommit` attribute which is set to `False`
     37      by default but can be set to `True` to switch to autocommit mode where
     38      no transactions are started and calling commit() is not required. Note
     39      that this is not part of the DB-API 2 standard.
    3540
    3641Vesion 5.0.7 (2019-mm-dd)
  • trunk/docs/contents/pgdb/connection.rst

    r844 r995  
    88These connection objects respond to the following methods.
    99
    10 Note that ``pgdb.Connection`` objects also implement the context manager protocol,
    11 i.e. you can use them in a ``with`` statement.
     10Note that ``pgdb.Connection`` objects also implement the context manager
     11protocol, i.e. you can use them in a ``with`` statement. When the ``with``
     12block ends, the current transaction will be automatically committed or
     13rolled back if there was an exception, and you won't need to do this manually.
    1214
    1315close -- close the connection
     
    3537    :rtype: None
    3638
    37 Note that connections always use a transaction, there is no auto-commit.
     39Note that connections always use a transaction, unless you set the
     40:attr:`Connection.autocommit` attribute described below.
    3841
    3942rollback -- roll back the connection
     
    8992
    9093This can be used for getting more information on the PostgreSQL database
    91 types or changing the typecast functions used for the connection.  See the
     94types or changing the typecast functions used for the connection. See the
    9295description of the :class:`TypeCache` class for details.
    9396
    9497.. versionadded:: 5.0
     98
     99.. attribute:: Connection.autocommit
     100
     101    A read/write attribute to get/set the autocommit mode
     102
     103Normally, all DB-API 2 SQL commands are run inside a transaction. Sometimes
     104this behavior is not desired; there are also some SQL commands such as VACUUM
     105which cannot be run inside a transaction.
     106
     107By setting this attribute to ``True`` you can change this behavior so that no
     108transactions will be started for that connection. In this case every executed
     109SQL command has immediate effect on the database and you don't need to call
     110:meth:`Connection.commit` explicitly. In this mode, you can still use
     111``with con:`` blocks to run parts of the code using the connection ``con``
     112inside a transaction.
     113
     114By default, this attribute is set to ``False`` which conforms to the behavior
     115specified by the DB-API 2 standard (manual commit required).
     116
     117.. versionadded:: 5.1
  • trunk/pgdb.py

    r989 r995  
    10481048        sql = "BEGIN"
    10491049        try:
    1050             if not self._dbcnx._tnx:
     1050            if not self._dbcnx._tnx and not self._dbcnx.autocommit:
    10511051                try:
    10521052                    self._src.execute(sql)
     
    10551055                except Exception:
    10561056                    raise _op_error("Can't start transaction")
    1057                 self._dbcnx._tnx = True
     1057                else:
     1058                    self._dbcnx._tnx = True
    10581059            for parameters in seq_of_parameters:
    10591060                sql = operation
     
    14641465        self.type_cache = TypeCache(cnx)
    14651466        self.cursor_type = Cursor
     1467        self.autocommit = False
    14661468        try:
    14671469            self._cnx.source()
     
    14731475
    14741476        The runtime context can be used for running transactions.
     1477
     1478        This also starts a transaction in autocommit mode.
    14751479        """
     1480        if self.autocommit:
     1481            try:
     1482                self._cnx.source().execute("BEGIN")
     1483            except DatabaseError:
     1484                raise  # database provides error message
     1485            except Exception:
     1486                raise _op_error("Can't start transaction")
     1487            else:
     1488                self._tnx = True
    14761489        return self
    14771490
     
    15151528                    self._cnx.source().execute("COMMIT")
    15161529                except DatabaseError:
    1517                     raise
     1530                    raise  # database provides error message
    15181531                except Exception:
    1519                     raise _op_error("Can't commit")
     1532                    raise _op_error("Can't commit transaction")
    15201533        else:
    15211534            raise _op_error("Connection has been closed")
     
    15291542                    self._cnx.source().execute("ROLLBACK")
    15301543                except DatabaseError:
    1531                     raise
     1544                    raise  # database provides error message
    15321545                except Exception:
    1533                     raise _op_error("Can't rollback")
     1546                    raise _op_error("Can't rollback transaction")
    15341547        else:
    15351548            raise _op_error("Connection has been closed")
  • trunk/tests/test_dbapi20.py

    r969 r995  
    3535from uuid import UUID as Uuid
    3636
    37 try:
     37try:  # noinspection PyUnresolvedReferences
    3838    long
    3939except NameError:  # Python >= 3.0
     
    11811181        self.assertEqual(con.NotSupportedError, pgdb.NotSupportedError)
    11821182
     1183    def test_transaction(self):
     1184        table = self.table_prefix + 'booze'
     1185        con1 = self._connect()
     1186        cur1 = con1.cursor()
     1187        self.executeDDL1(cur1)
     1188        con1.commit()
     1189        con2 = self._connect()
     1190        cur2 = con2.cursor()
     1191        cur2.execute("select name from %s" % table)
     1192        self.assertIsNone(cur2.fetchone())
     1193        cur1.execute("insert into %s values('Schlafly')" % table)
     1194        cur2.execute("select name from %s" % table)
     1195        self.assertIsNone(cur2.fetchone())
     1196        con1.commit()
     1197        cur2.execute("select name from %s" % table)
     1198        self.assertEqual(cur2.fetchone(), ('Schlafly',))
     1199        con2.close()
     1200        con1.close()
     1201
     1202    def test_autocommit(self):
     1203        table = self.table_prefix + 'booze'
     1204        con1 = self._connect()
     1205        con1.autocommit = True
     1206        cur1 = con1.cursor()
     1207        self.executeDDL1(cur1)
     1208        con2 = self._connect()
     1209        cur2 = con2.cursor()
     1210        cur2.execute("select name from %s" % table)
     1211        self.assertIsNone(cur2.fetchone())
     1212        cur1.execute("insert into %s values('Shmaltz Pastrami')" % table)
     1213        cur2.execute("select name from %s" % table)
     1214        self.assertEqual(cur2.fetchone(), ('Shmaltz Pastrami',))
     1215        con2.close()
     1216        con1.close()
     1217
    11831218    def test_connection_as_contextmanager(self):
    11841219        table = self.table_prefix + 'booze'
    1185         con = self._connect()
    1186         try:
    1187             cur = con.cursor()
    1188             cur.execute("create table %s (n smallint check(n!=4))" % table)
    1189             with con:
    1190                 cur.execute("insert into %s values (1)" % table)
    1191                 cur.execute("insert into %s values (2)" % table)
     1220        for autocommit in False, True:
     1221            con = self._connect()
     1222            con.autocommit = autocommit
    11921223            try:
     1224                cur = con.cursor()
     1225                if autocommit:
     1226                    cur.execute("truncate %s" % table)
     1227                else:
     1228                    cur.execute(
     1229                        "create table %s (n smallint check(n!=4))" % table)
    11931230                with con:
    1194                     cur.execute("insert into %s values (3)" % table)
    1195                     cur.execute("insert into %s values (4)" % table)
    1196             except con.IntegrityError as error:
    1197                 self.assertTrue('check' in str(error).lower())
    1198             with con:
    1199                 cur.execute("insert into %s values (5)" % table)
    1200                 cur.execute("insert into %s values (6)" % table)
    1201             try:
     1231                    cur.execute("insert into %s values (1)" % table)
     1232                    cur.execute("insert into %s values (2)" % table)
     1233                try:
     1234                    with con:
     1235                        cur.execute("insert into %s values (3)" % table)
     1236                        cur.execute("insert into %s values (4)" % table)
     1237                except con.IntegrityError as error:
     1238                    self.assertTrue('check' in str(error).lower())
    12021239                with con:
    1203                     cur.execute("insert into %s values (7)" % table)
    1204                     cur.execute("insert into %s values (8)" % table)
    1205                     raise ValueError('transaction should rollback')
    1206             except ValueError as error:
    1207                 self.assertEqual(str(error), 'transaction should rollback')
    1208             with con:
    1209                 cur.execute("insert into %s values (9)" % table)
    1210             cur.execute("select * from %s order by 1" % table)
    1211             rows = cur.fetchall()
    1212             rows = [row[0] for row in rows]
    1213         finally:
    1214             con.close()
    1215         self.assertEqual(rows, [1, 2, 5, 6, 9])
     1240                    cur.execute("insert into %s values (5)" % table)
     1241                    cur.execute("insert into %s values (6)" % table)
     1242                try:
     1243                    with con:
     1244                        cur.execute("insert into %s values (7)" % table)
     1245                        cur.execute("insert into %s values (8)" % table)
     1246                        raise ValueError('transaction should rollback')
     1247                except ValueError as error:
     1248                    self.assertEqual(str(error), 'transaction should rollback')
     1249                with con:
     1250                    cur.execute("insert into %s values (9)" % table)
     1251                cur.execute("select * from %s order by 1" % table)
     1252                rows = cur.fetchall()
     1253                rows = [row[0] for row in rows]
     1254            finally:
     1255                con.close()
     1256            self.assertEqual(rows, [1, 2, 5, 6, 9])
    12161257
    12171258    def test_cursor_connection(self):
Note: See TracChangeset for help on using the changeset viewer.