SQLite3 there's no such SavePoint when rolling back

[Yun]

I have two functions

def set_Savepoint(): 

  db.execute("savepoint t1bis") 

def rollback_to_sp(): 

  db.execute("rollback to savepoint t1bis")

The first one creates a SavePoint and the second one restores the save point. But unfortunately, every time I call the rollback functions it throws this error:

Traceback (most recent call last): 

  File "main.py", line 194, in <module> main() 

  File "main.py", line 189, in main operaciones_bbdd.rollback_to_sp() 

  File "/Users/yunhaolin/Documents/GitHub/DDSI

        C1_5/Database_DDSI/operaciones_bbdd.py", line 485, in rollback_to_sp    db.execute("rollback to savepoint t1bis") 

sqlite3.OperationalError: no such savepoint: t1bis

Here is how I am calling it from the main:

operaciones_bbdd.set_Savepoint() 

some operations 

operaciones_bbdd.rollback_to_sp()

(operaciones_bbdd is the file where those operations are declared)

I don't see the reason why this is not working, trying to use the commands manually in SQLite3 command line, works fine. Also I tried to use db.isolation_level = None and ended up throwing the same error

In the error message, it says there's no such SavePoint, but I have clearly created the t1bis sp, right?

[Roger Binns]

On Dec 14, 2020, at 07:06, Yun <yunhi...@gmail.com> wrote:
 db.execute("rollback to savepoint t1bis") 

I don't see the reason why this is not working, trying to use the commands manually in SQLite3 command line, works fine. Also I tried to use db.isolation_level = None and ended up throwing the same error

I couldn’t find the python sqlite3 code doing anything with rollback (it usually parses the SQL and tries to make SQLite look more like DBAPI/Postgres). Isolation level should mostly avoid that.

You may want to check out APSW (I am the author).  It implements nested transactions using savepoints behind the scenes.  You can just use as many nested with statements as you want:

https://rogerbinns.github.io/apsw/tips.html#transactions

The only other thing I notice is that apsw uses double quotes around the savepoint names behind the scenes.  Eg rollback to savepoint “t1bis”

Roger