1

I am using the following code in order to insert data in 2 tables:

  1. Variations
  2. Var_entity
    #begin transaction
    cur.execute("begin")
    id_var = 0
    for red in prop_redirect["results"]["bindings"]:
        var_res = red["x"]["value"].split('/')[-1]
        var_type = "t1"
        #insert data in table VARIATIONS
        cur.execute("INSERT INTO VARIATIONS (ID, NAME, TYPE) VALUES (?, ?, ?)", (str(id_var) + "_" + str(ID), var_res, var_type))
        #insert data in table VAR_ENTITY
        cur.execute("INSERT INTO VAR_ENTITY(ID_ENTITY, ID_VAR, LANGUAGE) VALUES(?, ?, ?)", (str(ID), str(id_var) + "_" + str(ID), "en" ) )
        id_var = id_var + 1 
    #commit after for loop
    cur.execute("commit")

From what I understood, using "begin transaction" and "commit" allows a faster access to the database Bulk insert huge data into SQLite using Python

Are the cur.execute("begin") and cur.execute("commit") statements placed correctly?

Previous questions that I found on stackoverflow:

Community
  • 1
  • 1
Hani Goc
  • 2,371
  • 5
  • 45
  • 89

1 Answers1

1

If the statements are at the same level as the for, then they are at the correct place but you also need to set the isolation_level to something else than None or the driver will do a commit every time you call cur.execute().

You also shouldn't use cur.execute("commit") but conn.commit() so the driver can notice what you're doing.

The begin isn't really necessary either. The database will automatically start a transaction for you if there isn't one, yet. So this would be better:

conn.isolation_level = '...' # anything but none
with conn: # let the driver handle transaction management
   cur = conn.cursor()
   for ...
       cur.execute(...)
Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • isolation_level = “DEFERRED”, “IMMEDIATE” or “EXCLUSIVE. I have to check the meaning of these. But any of them will make the driver not commit everytime i call cur.execute() – Hani Goc Mar 16 '15 at 13:24