Sqlite insert query not working with python?

Question

I have been trying to insert data into the database using the following code in python:

import sqlite3 as db
conn = db.connect('insertlinks.db')
cursor = conn.cursor()
db.autocommit(True)
a="asd"
b="adasd"
cursor.execute("Insert into links (link,id) values (?,?)",(a,b))
conn.close()

The code runs without any errors. But no updation to the database takes place. I tried adding the conn.commit() but it gives an error saying module not found. Please help?

• Hyperboreus: I am not sure: is it possible that you need to set auto-commit before obtaining the cursor?
• Saurabh: tried that as well. get the same error 'module' object has no attribute 'autocommit'
• Martijn Pieters: AttributeError: 'module' object has no attribute 'autocommit'. There is no such method on the sqlite3 module, setting autocommit would work quite differently.
• Saurabh: @MartijnPieters What should I do if i cant use autocommit?

Answer 1

You do have to commit after inserting:

cursor.execute("Insert into links (link,id) values (?,?)",(a,b))
conn.commit()

or use the connection as a context manager:

with conn:
 cursor.execute("Insert into links (link,id) values (?,?)", (a, b))

or set autocommit correctly by setting the isolation_level keyword parameter to the connect() method to None:

conn = db.connect('insertlinks.db', isolation_level=None)

See Controlling Transactions.

• Saurabh: commit is giving an error. However, the second code worked fine. Thank you so much.
• Hyperboreus: Does the __exit__ call commit? Is this part of the DB-API spec?
• Martijn Pieters: @Hyperboreus: Yes, the context manager __exit__ method calls commit(). This is an extension, there were no context managers when the API was written, see Is __enter__ and __exit__ behaviour for connection objects specified in the Python database API?

Answer 2

It can be a bit late but set the autocommit = true save my time! especially if you have a script to run some bulk action as update/insert/delete...

Reference: https://docs.python.org/2/library/sqlite3.html#sqlite3.Connection.isolation_level

it is the way I usually have in my scripts:

def get_connection():
 conn = sqlite3.connect('../db.sqlite3', isolation_level=None)
 cursor = conn.cursor()
 return conn, cursor
def get_jobs():
 conn, cursor = get_connection()
 if conn is None:
 raise DatabaseError("Could not get connection")

I hope it helps you!