[Tutor] sqlite3 COMMIT directive

[Alex Kleider]

I'm just beginning to enter into the field of database and SQLite in
particular.

Python 2.7 on Ubuntu

The problem code is a class method defined as follows:


def insert(self, DataBase=DataBase, Table=Table):
"""
Insert instance of ClassMate into <DataBase> <Table>.
"""
con = sqlite3.connect(DataBase)
with con:
cur.execute("""INSERT INTO %s VALUES
(NULL, '%s', '%s', '%s', '%s', '%s', '%s');""" % \
(Table, self.first, self.last, self.partner,
self.address, self.phone, self.email, ) )
try:
cur.execute("COMMIT;")
except:
pass

And the problem = \
"""
If the 'cur.execute("COMMIT;")' statement is left by itself, outside of
a
"try/except" clause, I get an error with the following trace back:
"
Traceback (most recent call last):
File "./uwomeds68.py", line 119, in <module>
go_on.insert()
File "./uwomeds68.py", line 64, in insert
cur.execute("COMMIT;")
sqlite3.OperationalError: cannot commit - no transaction is active
"

Without the COMMIT, there's no error but the data doesn't get added to
the data base.
"""

I've been reading about the COMMIT directive but apart from its
relationship with ROLLBACK, I haven't been able to figure out how it is
relevant to my problem here.

Any advice/explanation would of course be appreciated.

Alex Kleider

[Dominik George]

Hi,

Post by Alex Kleider
sqlite3.OperationalError: cannot commit - no transaction is active

That means that you have to execute "BEGIN TRANSACTION" before using atomic commits.

http://www.sqlite.org/lang_transaction.html
http://sqlite.org/transactional.html

Post by Alex Kleider
I've been reading about the COMMIT directive but apart from its
relationship with ROLLBACK, I haven't been able to figure out how it is
relevant to my problem here.

It isn't. What is relevant is whether you use transactions or not.

- -nik

[Alan Gauld]

Post by Alex Kleider
And the problem = \
"""
If the 'cur.execute("COMMIT;")' statement is left by itself, outside of a
"
File "./uwomeds68.py", line 119, in <module>
go_on.insert()
File "./uwomeds68.py", line 64, in insert
cur.execute("COMMIT;")
sqlite3.OperationalError: cannot commit - no transaction is active

You don't normally need to use COMMIT when programming SQLite from
Python, the database starts and ends transactions automatically from
within the execute() function. You only need to manually commit if you
manually create the transaction using BEGIN...

Post by Alex Kleider
Without the COMMIT, there's no error but the data doesn't get added to
the data base.

That suggests that you are opening the wrong database file or you have a
permissions problem or there is some other error. But you should not
need a COMMIT.

BTW. I notice you are using an implicit cursor within the with block.
Now while that may work, my personal preference is for explicit
variables, I just find it keeps the code clearer to explicitly create
the cursor rather than have a variable appear in my code with no
definition. It's OK in short snippets but as the code gets bigger
it can get confusing. But that's just my own preference...

[Alan Gauld]

Post by Alan Gauld
BTW. I notice you are using an implicit cursor within the with block.
Now while that may work, my personal preference is for explicit
variables, ...

Actually I'm not sure how it is working. I've just read the docs again
and it seems to me that your code should be referencing 'con' not 'cur'.
That may be part of the problem...

Post by Alan Gauld

Post by Alex Kleider
con = sqlite3.connect(DataBase)
cur.execute("""INSERT INTO %s VALUES
(NULL, '%s', '%s', '%s', '%s', '%s', '%s');""" % \
(Table, self.first, self.last, self.partner,
self.address, self.phone, self.email, ) )

HTH

[Tim Golden]

Post by Alan Gauld
You don't normally need to use COMMIT when programming SQLite from
Python, the database starts and ends transactions automatically from
within the execute() function. You only need to manually commit if you
manually create the transaction using BEGIN...

Not so, I'm afraid. If you want autocommit, you need to send an
isolation_level of None to the .connect function. If you don't, the
database will rollback any uncommitted transactions on exit.

The code below will only output (1,) as that value was committed. The
later row is lost when the cursor/db closes.

<code>
import os, sys
import sqlite3

dbname = "no-commit.db"
if os.path.exists(dbname):
os.unlink(dbname)

db = sqlite3.connect(dbname)
q = db.cursor()
q.execute("CREATE TABLE test(a INT)")
q.execute("INSERT INTO test(a) VALUES (1)")
db.commit()
q.execute("INSERT INTO test(a) VALUES (2)")
q.close()
db.close()

db = sqlite3.connect(dbname)
q = db.cursor()
for row in q.execute("SELECT * FROM test").fetchall():
print(row)
q.close()
db.close()

</code>

[Alan Gauld]

Post by Tim Golden

Post by Alan Gauld
You don't normally need to use COMMIT when programming SQLite from
Python, the database starts and ends transactions automatically from
within the execute() function.

Not so, I'm afraid. If you want autocommit, you need to send an
isolation_level of None to the .connect function.

Hmm, I didn't express that as I should but I was meaning within
a 'with' block (which the OP was using) based on this comment
in the docs:

"""
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:
"""

Now I admit I didn't test it but I read that to imply that the with
would autocommit. Am I mistaken?

[Tim Golden]

Post by Alan Gauld

Post by Tim Golden

Post by Alan Gauld
You don't normally need to use COMMIT when programming SQLite from
Python, the database starts and ends transactions automatically from
within the execute() function.

Not so, I'm afraid. If you want autocommit, you need to send an
isolation_level of None to the .connect function.

Hmm, I didn't express that as I should but I was meaning within
a 'with' block (which the OP was using) based on this comment
"""
Connection objects can be used as context managers that automatically
commit or rollback transactions. In the event of an exception, the
"""
Now I admit I didn't test it but I read that to imply that the with
would autocommit. Am I mistaken?

You're sort-of correct. What happens is that the database doesn't enter
autocommit mode (you'll still need to specify the right isolation level
on the .connect for that). Rather, the __exit__ method of the
connection-as-context-manager issues the db.commit() call which will
commit anything outstanding.

TJG

[Alan Gauld]

Post by Tim Golden

Post by Alan Gauld

Post by Tim Golden

Post by Alan Gauld
Python, the database starts and ends transactions automatically from
within the execute() function.

Not so, I'm afraid. If you want autocommit, you need to send an
isolation_level of None to the .connect function.

"""
Connection objects can be used as context managers that automatically
commit or rollback transactions.

You're sort-of correct. What happens is that the database doesn't enter
autocommit mode (you'll still need to specify the right isolation level
on the .connect for that). Rather, the __exit__ method of the
connection-as-context-manager issues the db.commit() call which will
commit anything outstanding.

OK, That makes sense it's a bit like the file close at the end of a with
block. So the docs statement that it automatically commits
*transactions* is slightly misleading as it only commits the full
content of the block which could hold multiple transactions.

Thanks for the clarification Tim.

[Oscar Benjamin]

Post by Alan Gauld
OK, That makes sense it's a bit like the file close at the end of a with
block. So the docs statement that it automatically commits *transactions* is
slightly misleading as it only commits the full content of the block which
could hold multiple transactions.

In the light of this confusion the docs do seem ambiguous. However I
learned to use sqlite3 through those docs and always understood that
the context manager was for committing or rolling back all
transactions within the "with" block. It's actually very useful and a
good use of a context manager (enabling auto-commit would also be
useful but it doesn't really need a context manager).


Oscar

[Mark Lawrence]

Post by Alan Gauld

Post by Tim Golden

Post by Alan Gauld

Post by Tim Golden

Post by Alan Gauld
Python, the database starts and ends transactions automatically from
within the execute() function.

Not so, I'm afraid. If you want autocommit, you need to send an
isolation_level of None to the .connect function.

"""
Connection objects can be used as context managers that automatically
commit or rollback transactions.

You're sort-of correct. What happens is that the database doesn't enter
autocommit mode (you'll still need to specify the right isolation level
on the .connect for that). Rather, the __exit__ method of the
connection-as-context-manager issues the db.commit() call which will
commit anything outstanding.

OK, That makes sense it's a bit like the file close at the end of a with
block. So the docs statement that it automatically commits
*transactions* is slightly misleading as it only commits the full
content of the block which could hold multiple transactions.
Thanks for the clarification Tim.

I haven't followed this too closely as my SQL skills (like many others)
are sadly out of date, but is it worth the hassle of raising an issue on
the bug tracker to get the relevant docs changed, or is "slightly
misleading" actually close enough?

[Tim Golden]

Post by Tim Golden
You're sort-of correct. What happens is that the database doesn't enter
autocommit mode (you'll still need to specify the right isolation level
on the .connect for that). Rather, the __exit__ method of the
connection-as-context-manager issues the db.commit() call which will
commit anything outstanding.

Re-reading, we may be using the term "autocommit" in two different ways.

I'm using it to refer to a database-internal option where each SQL
operation is committed as soon as it completes. This mode of operation
is initiated (from Python) by passing an isolation_level of "None" when
creating the connection.

The "with sqlite3.connect..." approach could be considered a
Python-level autocommit where all the operations within the block are
committed by virtue of the Python context-manager mechanism which
invokes the usual database "COMMIT" operation on exit.

In case it helps...

TJG

[Mark Lawrence]

Post by Alex Kleider
cur.execute("COMMIT;")
pass
And the problem = \
"""
If the 'cur.execute("COMMIT;")' statement is left by itself, outside of a
"
File "./uwomeds68.py", line 119, in <module>
go_on.insert()
File "./uwomeds68.py", line 64, in insert
cur.execute("COMMIT;")
sqlite3.OperationalError: cannot commit - no transaction is active
"

As you've already had some sound advice I'd just like to point out that
a bare except is considered bad form, e.g. it would mask any attempt to
use CTRL-C to halt a rouge program that's inadvertantly gone into an
infinite loop. An obvious one to use here could be the given
sqlite3.OperationalError. Common problems that are caught include
NameError, IndexError and ScrewedUpCompletelyError :)

[Alex Kleider]

Thank you to respondents.

That the code worked at all is something I don't understand.
As Alan G pointed out, I had forgotten to include the "cur =
con.cursor()" line.
After this was corrected and the try/except statement deleted, it worked
as I expected.

Thanks to "-nick" for pointing me to the references regarding
transactions. That's not easy stuff.
I was aware that 'bare except' statements are to be eschewed but now,
thanks to Mark Lawrence, have a better idea of why.

Much appreciated.

alex

[Alex Kleider]

The following class method
"""

def insert(self, DataBase=DataBase, Table=Table):
"""
Insert instance of ClassMate into <DataBase> <Table>.
"""
with sqlite3.connect(DataBase) as con:
cur = con.cursor()
row = \
('NULL', self.first, self.last, self.partner,
self.address, self.phone, self.email, )
directive = "INSERT INTO %s VALUES ?;" % (Table, )
cur.execute(directive, row)
"""
gives the following error:

"""
Traceback (most recent call last):
File "./v_temp.py", line 155, in <module>
go_on.insert()
File "./v_temp.py", line 70, in insert
cur.execute(directive, row)
sqlite3.OperationalError: near "?": syntax error
"""
(explanation: 'go_on' is an instance of my class in the __main__)

It was working fine when I was using string formatting but I can't seem
to get the syntax right using the qmark method which the documentation
suggests is the better way to do it.

Can anyone spot what should be changed?
(Python 2.7 on Ubuntu )

Thanks in advance.
Alex

[bob gailer]

Post by Alex Kleider
The following class method
"""
"""
Insert instance of ClassMate into <DataBase> <Table>.
"""
cur = con.cursor()
row = \
('NULL', self.first, self.last, self.partner,
self.address, self.phone, self.email, )
directive = "INSERT INTO %s VALUES ?;" % (Table, )
cur.execute(directive, row)
"""
"""
File "./v_temp.py", line 155, in <module>
go_on.insert()
File "./v_temp.py", line 70, in insert
cur.execute(directive, row)
sqlite3.OperationalError: near "?": syntax error
"""
(explanation: 'go_on' is an instance of my class in the __main__)
It was working fine when I was using string formatting but I can't
seem to get the syntax right using the qmark method which the
documentation suggests is the better way to do it.
Can anyone spot what should be changed?

Try: directive = "INSERT INTO %s VALUES (?,?,?,?,?,?,?);" % (Table, )

Post by Alex Kleider
(Python 2.7 on Ubuntu )
Thanks in advance.
Alex
_______________________________________________
https://mail.python.org/mailman/listinfo/tutor

[Alex Kleider]

Post by bob gailer

Post by Alex Kleider
The following class method
"""
"""
Insert instance of ClassMate into <DataBase> <Table>.
"""
cur = con.cursor()
row = \
('NULL', self.first, self.last, self.partner,
self.address, self.phone, self.email, )
directive = "INSERT INTO %s VALUES ?;" % (Table, )
cur.execute(directive, row)
"""
"""
File "./v_temp.py", line 155, in <module>
go_on.insert()
File "./v_temp.py", line 70, in insert
cur.execute(directive, row)
sqlite3.OperationalError: near "?": syntax error
"""
(explanation: 'go_on' is an instance of my class in the __main__)
It was working fine when I was using string formatting but I can't
seem to get the syntax right using the qmark method which the
documentation suggests is the better way to do it.
Can anyone spot what should be changed?

Try: directive = "INSERT INTO %s VALUES (?,?,?,?,?,?,?);" % (Table, )

Post by Alex Kleider
_______________________________________________
https://mail.python.org/mailman/listinfo/tutor

Thanks, I discovered that when I went I read further in the
documentation.
This is the part that lead me a stray:
"""
# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'IBM', 500, 53.00),
]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
"""
I made the incorrect assumption that individual question marks
represented tuples, (as they do here with 'executemany') but I guess not
so with 'execute'.

[Alan Gauld]

Thanks, I discovered that when I went I read further in the documentation.
"""
# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'IBM', 500, 53.00),
]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
"""
I made the incorrect assumption that individual question marks
represented tuples, (as they do here with 'executemany') but I guess not
so with 'execute'.

I'm not sure you got that right.

There are 5 question marks there, one for each value item to be inserted.
There are 5 values in each tuple in purchases.
Therefore the question marks do not represent the tuples, they represent
the values in the tuples. Just like in execute()

executemany() performs an execute() on each item in the purchases
list, passing the tuple content into the execute.

HTH

[Alex Kleider]

Post by Alan Gauld

Post by Alex Kleider
Thanks, I discovered that when I went I read further in the
documentation.
"""
# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
('2006-04-06', 'SELL', 'IBM', 500, 53.00),
]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
"""
I made the incorrect assumption that individual question marks
represented tuples, (as they do here with 'executemany') but I guess not
so with 'execute'.

I'm not sure you got that right.
There are 5 question marks there, one for each value item to be inserted.
There are 5 values in each tuple in purchases.
Therefore the question marks do not represent the tuples, they
represent the values in the tuples. Just like in execute()
executemany() performs an execute() on each item in the purchases
list, passing the tuple content into the execute.
HTH

Ah, Ha!
Thanks for the clarification.
Now it is much more consistent (as I should have known that it would
be.)

[Alex Kleider]

Post by Alex Kleider
The following class method
"""
"""
Insert instance of ClassMate into <DataBase> <Table>.
"""
cur = con.cursor()
row = \
('NULL', self.first, self.last, self.partner,
self.address, self.phone, self.email, )
directive = "INSERT INTO %s VALUES ?;" % (Table, )
cur.execute(directive, row)
"""
"""
File "./v_temp.py", line 155, in <module>
go_on.insert()
File "./v_temp.py", line 70, in insert
cur.execute(directive, row)
sqlite3.OperationalError: near "?": syntax error
"""
(explanation: 'go_on' is an instance of my class in the __main__)
It was working fine when I was using string formatting but I can't
seem to get the syntax right using the qmark method which the
documentation suggests is the better way to do it.
Can anyone spot what should be changed?
(Python 2.7 on Ubuntu )
Thanks in advance.
Alex

Sorry to have bothered the list;
I found the problems (there were two!)
Here's the corrected version in case it's of interest to anyone:
"""
def insert(self, DataBase=DataBase, Table=Table):
"""
Insert instance of ClassMate into <DataBase> <Table>.
"""
with lite.connect(DataBase) as con:
cur = con.cursor()
row = \
(self.first, self.last, self.partner,
self.address, self.phone, self.email, )
directive = \
"INSERT INTO %s VALUES (NULL, ?, ?, ?, ?, ?, ?)" \
% (Table, )
cur.execute(directive, row)
"""