when I use transactions I got errors I won't get with sqlite3 cli

[alsadi@pc1 ~]$ python
Python 2.5.2 (r252:60911, Sep 30 2008, 15:41:38) 
[GCC 4.3.2 20080917 (Red Hat 4.3.2-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> cn=sqlite3.connect(':memory:')
>>> c=cn.cursor()
>>> c.execute('BEGIN TRANSACTION')
<sqlite3.Cursor object at 0xb7efada0>
>>> c.execute('create temp table tmp_main (id integer, b text)')
<sqlite3.Cursor object at 0xb7efada0>
>>> c.execute('insert into tmp_main (id) values (10);')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: SQL logic error or missing database
>>> 


[alsadi@pc1 ~]$ sqlite3 ':memory:'
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> BEGIN TRANSACTION;
sqlite> create temp table tmp_main (id integer, b text);
sqlite> insert into tmp_main (id) values (10);
sqlite> select * from tmp_main;
10|
sqlite> 

and if the transaction line removed I get no error

[alsadi@pc1 ~]$ python
Python 2.5.2 (r252:60911, Sep 30 2008, 15:41:38) 
[GCC 4.3.2 20080917 (Red Hat 4.3.2-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> cn=sqlite3.connect(':memory:')
>>> c=cn.cursor()
>>> c.execute('create temp table tmp_main (id integer, b text)')
<sqlite3.Cursor object at 0xb7ff6da0>
>>> c.execute('insert into tmp_main (id) values (10);')
<sqlite3.Cursor object at 0xb7ff6da0>
>>>

What if you commit before the insert?

same thing

[alsadi@pc1 ~]$ python
Python 2.5.2 (r252:60911, Sep 30 2008, 15:41:38) 
[GCC 4.3.2 20080917 (Red Hat 4.3.2-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> cn=sqlite3.connect(':memory:')
>>> c=cn.cursor()
>>> c.execute('BEGIN TRANSACTION')
<sqlite3.Cursor object at 0xb7f99da0>
>>> c.execute('create temp table tmp_main (id integer, b text)')
<sqlite3.Cursor object at 0xb7f99da0>
>>> cn.commit()
>>> c.execute('insert into tmp_main (id) values (10);')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: SQL logic error or missing database
>>> 

to make it easy for you to try it yourself

import sqlite3
cn=sqlite3.connect(':memory:')
c=cn.cursor()
c.execute('BEGIN TRANSACTION')
c.execute('create temp table tmp_main (id integer, b text)')
cn.commit() # this was added on your request
c.execute('insert into tmp_main (id) values (10);')

Ok, I've just tried. While it fails in Python 2.5.2, it works in 2.6.1
and in trunk (what will become 2.7). So I suggest you upgrade to 2.6.1,
or simply drop the "BEGIN TRANSACTION" statement in this particular case.

can you please tell me how to detect the version so that my application
will not crash on Python 2.5.2 (r252:60911, Sep 30 2008, 15:41:38) 

which of these values should I use
    apilevel = '2.0'
    sqlite_version = '3.5.9'
    sqlite_version_info = (3, 5, 9)
    version = '2.3.2'
    version_info = (2, 3, 2)
    x = '9'

that are different in your python 2.6
for example
import sqlite3
if sqlite3.version_info<(3,0,0): c.execute('BEGIN TRANSACTION')

Well I'm no sqlite expert, but here are the values I get on the 2.6 branch:

>>> sqlite3.apilevel
'2.0'
>>> sqlite3.sqlite_version
'3.6.1'
>>> sqlite3.sqlite_version_info
(3, 6, 1)
>>> sqlite3.version
'2.4.1'
>>> sqlite3.version_info
(2, 4, 1)

a fedora-devel fellow gave me a solution

cn=sqlite3.connect(':memory:', isolation_level=None)

and it worked
I hope that does not affect other versions


$ python
Python 2.5.2 (r252:60911, Sep 30 2008, 15:41:38) 
[GCC 4.3.2 20080917 (Red Hat 4.3.2-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> cn=sqlite3.connect(':memory:', isolation_level=None)
>>> c=cn.cursor()
>>> c.execute('BEGIN TRANSACTION')
<sqlite3.Cursor object at 0x9cb9c20>
>>> c.execute('create temp table tmp_main (id integer, b text)')
<sqlite3.Cursor object at 0x9cb9c20>
>>> c.execute('insert into tmp_main (id) values (10);')
<sqlite3.Cursor object at 0x9cb9c20>
>>> c.execute('END TRANSACTION')
<sqlite3.Cursor object at 0x9cb9c20>
>>>