python sqlite3 insert command

Question

any idea what I'm doing wrong?

I'm creating a table called General:

 conn = sqlite3.connect(self.dbLocation)
 c = conn.cursor() 
 sql = "create table if not exists General (id integer NOT NULL,current char[20] NOT NULL,PRIMARY KEY (id))"
 c.execute(sql)
 c.close()
 conn.close()

I'm then using max(id) to see if the table is empty. If it is, I create a table called Current1 and insert a row in General (id, 'Current1'). id is autoincrementing integer:

 self.currentDB = "Current1"
 self.currentDBID = "1"
 #create the table
 sql = "create table %s (id integer NOT NULL,key char[90] NOT NULL,value float NOT NULL,PRIMARY KEY (id))" % (str(self.currentDB))
 c.execute(sql)
 c.close()
 conn.close()
 conn = sqlite3.connect(self.dbLocation)
 c = conn.cursor()
 sql = "insert into General(current) values('%s')" % (str(self.currentDB))
 print "sql = %s" % (str(sql)) ---> *sql = insert into General(current) values('Current1')*
 c.execute(sql)
 print "executed insert Current"
 c.execute ("select max(id) from General")
 temp = c.next()[0]
 print "temp = %s" % (str(temp)) ---> *temp = 1*
 c.close()
 conn.close()

The problem is that if I open the database, I do not find any rows in the General table. Current1 table is being created, but the insert statement into General does not seem to be doing anything. What am I doing wrong? Thanks.

Answer 1

You have to commit the changes before closing the connection:

conn.commit()

check the example in the docs : http://docs.python.org/2/library/sqlite3.html

• user2623934: Just found the answer on another page before I refreshed this one and found your answers. You are right, I also tried committing with variable c but didn't work...it has to be on conn. Thanks a lot :) Will mark yours as the right answer since yours is the first one, even though @Maxime Lorant is right too!

Answer 2

Or you can use the connection as a context manager: http://docs.python.org/2/library/sqlite3.html#using-the-connection-as-a-context-manager

Connection objects can be used as context managers that automatically commit or rollback transactions. In the event of an exception, the transaction is rolled back; otherwise, the transaction is committed

Example:

import sqlite3
conn = sqlite3.connect('current')
with conn:
 conn.execute("create table if not exists General (id integer NOT NULL,current char[20] NOT NULL,PRIMARY KEY (id))")
 conn.execute("insert into General(current) values('{0}')".format("some data"))
with conn:
 q = conn.execute("select max(id) from General")
q.fetchone()[0]

Answer 3

You need to commit changes in the database with

conn.commit()

This will write on the disk changes you made in your database. If you close your database without doing it, you lost your modification (INSERT/UPDATE/DELETE)