classification
Title: Strange issue : cursor.commit() with sqlite
Type: behavior Stage:
Components: Versions: Python 2.6
process
Status: closed Resolution: not a bug
Dependencies: Superseder:
Assigned To: ghaering Nosy List: ghaering, lakshmipathi
Priority: normal Keywords:

Created on 2009-12-24 10:18 by lakshmipathi, last changed 2009-12-29 11:27 by ghaering. This issue is now closed.

Messages (4)
msg96851 - (view) Author: lakshmipathi (lakshmipathi) Date: 2009-12-24 10:18
Hi all,
I'm new user to python and sqlite, let me know if i'm wrong or it's
sqlite issue.When I uncomment the line from below program it works -as
expected. But even when it's commented It works for first time.-As per
doc,without commit -all changes will be lost- am i right?

Issue : Running the program once without commit works for the first time
but not the next time.

Try changing the return value from 'OSS' to 'GNU' and comment/uncomment
the commit line.
----------------------------------
Here is my program:
-------------------------------------
import sqlite3
def loopy():
	return 'OSS'
#get connection object 
conn = sqlite3.connect("/tmp/example")
#get curson obj. and invoke execute
cur = conn.cursor()

conn.create_function("loopy",0,loopy)

cur.execute("insert into stk values (loopy())""")
#commit changes.
#conn.commit()  # ******Uncomment this line and run again*******

# read values
cur.execute("select * from stk")
for row in cur:
	print row
	
cur.close()
-------------------------
msg96873 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2009-12-25 00:03
Please change your test case so that it works with an in-memory database
":memory:". Then you'll also need to include a schema creation command
"create table", which is missing here.

Please also state which behaviour you see and which one you expect. Best
is to provide a test case that asserts the expected behaviour in code.

For example if you expect a certain SELECT statement to return more than
one value, you can use code like this:

{{{
cur.execute("select ...")
rows = cur.fetchall()
assert len(rows) > 1
}}}
msg96992 - (view) Author: lakshmipathi (lakshmipathi) Date: 2009-12-29 10:43
ok..let me put this question ,in different manner.
Here is my code (example2)
------------------
import sqlite3
def loopy():
	return 'GNU'
#get connection object 
conn = sqlite3.connect("/tmp/example2")
#get curson obj. and invoke execute
cur = conn.cursor()
cur.execute('''create table stk (txt text)''')
#insert a single record
conn.create_function("loopy",0,loopy)
t=(loopy(),)
cur.execute("insert into stk values (?)",t)
#I have close it without committing. -So my record length == 0
cur.close()
#But when  i open again new cursor cur1
cur1=conn.cursor()
cur1.execute("select * from stk")
row = cur1.fetchall()
# i expect this assert to pass - since there is no record 
assert len(row) < 1

-------------------------------------------------------
I used sqlite3 /tmp/example2 and select * from stk returned zero records
as expected.
--------------------------
So commit refers to committing to Database not with in the program.
msg96993 - (view) Author: Gerhard Häring (ghaering) * (Python committer) Date: 2009-12-29 11:27
You confuse two things here: cursors and connections.

Indeed closing a connection without calling commit() on it will do an
implicit rollback, i. e. any changes on the database will not be persisted.

Closing cursors, however does nothing except invalidating the cursor
object for further usage. Cursors are basically only used for executing
SQL and looping over resultsets. You can reuse them or you can close
them and open new ones. But they have no relationship whatsoever with
transactions.

I hope this clears things up. I'm closing the issue now.
History
Date User Action Args
2009-12-29 11:27:47ghaeringsetstatus: open -> closed
resolution: not a bug
messages: + msg96993
2009-12-29 11:21:34ghaeringsettitle: Strabge issue : cursor.commit() with sqlite -> Strange issue : cursor.commit() with sqlite
2009-12-29 10:43:06lakshmipathisetmessages: + msg96992
2009-12-25 00:03:49ghaeringsetmessages: + msg96873
2009-12-24 15:35:59benjamin.petersonsetassignee: ghaering

nosy: + ghaering
2009-12-24 10:18:36lakshmipathicreate