python - with keyword and committing transactions

Question

I came across some code on

https://zetcode.com/db/sqlitepythontutorial/#:~:text=executescript(%22%22%22%20DROP%20TABLE%20IF%20EXISTS%20cars%3B%20CREATE,SQL%20code%20in%20one%20step.

There it was insinuated that the presence of the 'with' keyword does not require a committing of transactions. Sample code below (copied from URL mentioned above)

#!/usr/bin/env python2
# -*- coding: utf-8 -*-
import sqlite3 as lite

cars = (
        (1, 'Audi', 52642),
        (2, 'Mercedes', 57127),
        (3, 'Skoda', 9000),
        (4, 'Volvo', 29000),
        (5, 'Bentley', 350000),
        (6, 'Hummer', 41400),
        (7, 'Volkswagen', 21600)
       )

con = lite.connect('test.db')

with con:

    cur = con.cursor()

    cur.execute("DROP TABLE IF EXISTS cars")
    cur.execute("CREATE TABLE cars(id INT, name TEXT, price INT)")
    cur.executemany("INSERT INTO cars VALUES(?, ?, ?)", cars)

Then there is this (also from the same source:

#!/usr/bin/env python2
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

try:
    con = lite.connect('test.db')

    cur = con.cursor()

    cur.executescript("""
                         DROP TABLE IF EXISTS cars;
                         CREATE TABLE cars(id INT, name TEXT, price INT);
                         INSERT INTO cars VALUES(1,'Audi',52642);
                         INSERT INTO cars VALUES(2,'Mercedes',57127);
                         INSERT INTO cars VALUES(3,'Skoda',9000);
                         INSERT INTO cars VALUES(4,'Volvo',29000);
                         INSERT INTO cars VALUES(5,'Bentley',350000);
                         INSERT INTO cars VALUES(6,'Citroen',21000);
                         INSERT INTO cars VALUES(7,'Hummer',41400);
                         INSERT INTO cars VALUES(8,'Volkswagen',21600);
                       """)

    con.commit()

except lite.Error, e:

     if con:
        con.rollback()

        print "Error {}:".format(e.args[0])
        sys.exit(1)

finally:

    if con:
        con.close()

The use of 'with' this way, and the claim that using it removes the need to call the 'commit()' is it right or wrong? Then what could be the drawbacks of using with this way?

question from:https://stackoverflow.com/questions/66050003/with-keyword-and-committing-transactions

Answer 1

From the doc

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed:

See the doc for example and more details. I don't know if there are drawbacks.