Insert into table in sqlite3 and python. The magic of the "with" keyword

Question

I am following this sqlite3 for python tutorial.

I am trying to insert a row into a table with this script:

conn = sqlite3.connect(database)

sql = ''' INSERT INTO projects(name,begin_date,end_date)
              VALUES(?,?,?) '''    
project = ('X', '2015-01-01', '2015-01-30');

cur = conn.cursor()
cur.execute(sql,project)

conn.close()

I can run it with no errors but the row is not inserted.

Although, the row is inserted when using with:

with conn:
    sql = ''' INSERT INTO projects(name,begin_date,end_date)
                  VALUES(?,?,?) '''    
    project = ('X', '2015-01-01', '2015-01-30');

    cur = conn.cursor()
    cur.execute(sql,project)

Does anyone know whats happening?

UPDATE

I found in docs.python.org that after one or more execute statements one does commit as in

cur.execute(sql, project)
conn.commit()

and everything goes fine.

But still, I would much appreciate some technical explanation of with keyword

I mean use the [`import pdb; pdb.set_trace()`](https://docs.python.org/3/library/pdb.html) statement for debug your script and see what's happen... — R. García, Mar 07 '19 at 08:47
Maybe there is something wrong with your date formatt... Who knows ? Without any error and any debug :) — R. García, Mar 07 '19 at 08:50
Possible duplicate of [What is the python "with" statement designed for?](https://stackoverflow.com/questions/3012488/what-is-the-python-with-statement-designed-for) — snakecharmerb, Mar 08 '19 at 08:11
Did you read my question? If not, please read this first: https://en.oxforddictionaries.com/definition/duplicate. — prometeu, Mar 08 '19 at 08:26

Thomas G · Accepted Answer · 2019-03-07T10:59:50.350

But still, I would much appreciate some technical explanation of with keyword

The reason why you should not commit() when using the database connection as a context manager (with keyword), is because there's a mechanism that auto-commit in such case, if the transaction succeed (meaning that there's no exception raised).

It is explained in the sqlite3 API doc:

Using the connection as a context manager

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:
import sqlite3

con = sqlite3.connect(":memory:")
con.execute("create table person (id integer primary key, firstname varchar unique)")

# Successful, con.commit() is called automatically afterwards
with con:
    con.execute("insert into person(firstname) values (?)", ("Joe",))

# con.rollback() is called after the with block finishes with an exception, the
# exception is still raised and must be caught
try:
    with con:
        con.execute("insert into person(firstname) values (?)", ("Joe",))
except sqlite3.IntegrityError:
    print("couldn't add Joe twice")

Insert into table in sqlite3 and python. The magic of the "with" keyword

1 Answers1

Using the connection as a context manager