Python Sqlite3 executescript(sql_script) without COMMIT transaction

Question

From the documentation of Python Sqlite3

executescript(sql_script)

This is a nonstandard convenience method for executing multiple SQL statements at once. It issues a COMMIT statement first, then executes the SQL script it gets as a parameter. This method disregards isolation_level; any transaction control must be added to sql_script.

My problem: I am executing a series of sql scripts with multiples statements, e.g.:

db = sqlite3.connect(my_db_path)
for sql_file in files:
  f = open(sql_file)
  update_script = f.read()
  f.close()
  
  db.executescript(update_script)

The executescript() method makes a commit before executing so I can't have a transaction control over all files to rollback to the initial state if it fails in the middle of the process because it commits on each iteration.

I need a way to commit only at the end after the for loop and not at the end of each iteration. I didn't manage to make the method execute() work because it doesn't support multiple statements and I also did not manage to make it work with executemany() method because it would fail if my file happens to have only one statement.

Any suggestions?

DinoCoderSaurus · Accepted Answer · 2021-11-16 18:15:58Z

1

If all the scripts need to be executed as one transaction, instead of read, execute, read, execute, ..... try read, read, read.....execute. In other words, read the contents of all the files into update_script, then execute it.

answered Nov 16, 2021 at 18:15

DinoCoderSaurus

6,5502 gold badges13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

gvn Over a year ago

Good idea. I am trying this approach at the moment. In my collection of files I have in total 7 statements. I introduced I typo on the 7th to check that if it failed I could rollback all the 7 statements. But it did not. It executed until the 6th. I did try: db.executescript(update_script) except: db.rollback()

DinoCoderSaurus Over a year ago

Don't forget "any transaction control must be added to sql_script.", Are you starting (BEGIN) a transaction in the script?

DinoCoderSaurus Over a year ago

Upon further reflection, the original approach should work with a "BEGIN TRANSACTION;" too.

gvn Over a year ago

Using your idea. I got to the following solution. I introduce an sqlparser to get all my statements from all files and c.execute in a loop instead of executescript. But before executing the statements I started c.execute("BEGIN TRANSACTION updatedb") and in case of failure I did: c.execute("ROLLBACK TRANSACTION updatedb"). It worked as I needed. Thanks

Collectives™ on Stack Overflow

Python Sqlite3 executescript(sql_script) without COMMIT transaction

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related