1

I've an application, where I use sqlite3's autocommit feature everywhere which usually works fine. The app includes an database scheme updater.

Basically it's just a set of SQL commands for each version upgrade, which should be called in a single transaction. I implemented this by using the .executemany() call which worked until now. Now is the first time, I want to alter a table description using this method (I made a short example, as the original table is fairly big):

Think of

-- the Table in the current Version:
table: foo (foo_id INTEGER PRIMARY KEY, quantity INTEGER, 
            single_price REAL, all_price REAL)
-- where I want to end:
table: foo (foo_id INTEGER PRIMARY KEY, quantity INTEGER, 
            single_price REAL, total_price REAL)
(so renaming the 4th column)

I'm not talking about indexes here, problem is the same without :)

What I try to run in a single executemany() is:

ALTER TABLE foo RENAME TO foo_PREv2;
CREATE TABLE foo (foo_id INTEGER PRIMARY KEY, quantity INTEGER, 
            single_price REAL, total_price REAL);
INSERT INTO foo (foo_id, quantity, 
            single_price, total_price)
SELECT foo_id, quantity, 
            single_price, all_price
FROM foo_PREv2;
DROP TABLE foo_PREv2; -- <<<--- here it fails with a database locked error

Even if I move the DROP to a second executemany() call it doesn't work. I have to restart my app before I could DROP.

As I understood, executemany() handles the BEGIN TRANSACTION ... COMMIT stuff for me. What do I miss?

Thanks in advance, king regards, Florian.

Florian Lagg
  • 751
  • 2
  • 8
  • 21
  • Did you actually [set autocommit mode](http://docs.python.org/2/library/sqlite3.html#sqlite3-controlling-transactions)? – CL. Jun 08 '13 at 15:57
  • Yes, as stated above, autcommit is on. Which is default, not set by me. But executemany() should do a transaction, right? Maybe I'm doing it wrong, but I don't get it :) – Florian Lagg Jun 08 '13 at 19:02
  • The [documentation](http://docs.python.org/2/library/sqlite3.html#sqlite3-controlling-transactions) states that autocommit is *not* the default, and `executemany` is not different from a series of `execute` calls. – CL. Jun 08 '13 at 21:10
  • Than I misunderstood the english docs, I'll re-read. Thanks so far. – Florian Lagg Jun 09 '13 at 11:44
  • Hi, I don't get it working. I used 'with' for my connection, tried executescript(), removed my 'isolation_level=None', tried it in BEGIN ... COMMIT statements, any combination I can think of. I give up for now. I'll look into it again if I need it again :/ – Florian Lagg Jun 18 '13 at 15:52

0 Answers0