classification
Title: sqlite3 module gives SQL logic error only in transactions
Type: behavior Stage:
Components: Extension Modules Versions: Python 2.5
process
Status: closed Resolution: rejected
Dependencies: Superseder:
Assigned To: Nosy List: alsadi, pitrou
Priority: normal Keywords:

Created on 2009-01-19 02:12 by alsadi, last changed 2009-01-20 18:52 by alsadi. This issue is now closed.

Messages (7)
msg80143 - (view) Author: Muayyad Alsadi (alsadi) Date: 2009-01-19 02:12
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>
>>>
msg80145 - (view) Author: Antoine Pitrou (pitrou) * (Python committer) Date: 2009-01-19 02:22
What if you commit before the insert?
msg80159 - (view) Author: Muayyad Alsadi (alsadi) Date: 2009-01-19 11:06
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);')
msg80163 - (view) Author: Antoine Pitrou (pitrou) * (Python committer) Date: 2009-01-19 12:58
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.
msg80220 - (view) Author: Muayyad Alsadi (alsadi) Date: 2009-01-19 23:22
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')
msg80249 - (view) Author: Antoine Pitrou (pitrou) * (Python committer) Date: 2009-01-20 11:54
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)
msg80264 - (view) Author: Muayyad Alsadi (alsadi) Date: 2009-01-20 18:52
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>
>>>
History
Date User Action Args
2009-01-20 18:52:30alsadisetmessages: + msg80264
2009-01-20 11:54:06pitrousetmessages: + msg80249
2009-01-19 23:22:45alsadisetmessages: + msg80220
2009-01-19 12:58:22pitrousetstatus: open -> closed
resolution: rejected
messages: + msg80163
2009-01-19 11:06:37alsadisetmessages: + msg80159
2009-01-19 02:22:56pitrousetnosy: + pitrou
messages: + msg80145
2009-01-19 02:13:22alsadisettype: behavior
2009-01-19 02:12:54alsadicreate