Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sqlite3 module gives SQL logic error only in transactions #49245

Closed
alsadi mannequin opened this issue Jan 19, 2009 · 7 comments
Closed

sqlite3 module gives SQL logic error only in transactions #49245

alsadi mannequin opened this issue Jan 19, 2009 · 7 comments
Labels
extension-modules C modules in the Modules dir type-bug An unexpected behavior, bug, or error

Comments

@alsadi
Copy link
Mannequin

alsadi mannequin commented Jan 19, 2009

BPO 4995
Nosy @pitrou

Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2009-01-19.12:58:22.387>
created_at = <Date 2009-01-19.02:12:54.788>
labels = ['extension-modules', 'type-bug']
title = 'sqlite3 module gives SQL logic error only in transactions'
updated_at = <Date 2009-01-20.18:52:30.701>
user = 'https://bugs.python.org/alsadi'

bugs.python.org fields:

activity = <Date 2009-01-20.18:52:30.701>
actor = 'alsadi'
assignee = 'none'
closed = True
closed_date = <Date 2009-01-19.12:58:22.387>
closer = 'pitrou'
components = ['Extension Modules']
creation = <Date 2009-01-19.02:12:54.788>
creator = 'alsadi'
dependencies = []
files = []
hgrepos = []
issue_num = 4995
keywords = []
message_count = 7.0
messages = ['80143', '80145', '80159', '80163', '80220', '80249', '80264']
nosy_count = 2.0
nosy_names = ['pitrou', 'alsadi']
pr_nums = []
priority = 'normal'
resolution = 'rejected'
stage = None
status = 'closed'
superseder = None
type = 'behavior'
url = 'https://bugs.python.org/issue4995'
versions = ['Python 2.5']
@alsadi
Copy link
Mannequin Author

alsadi mannequin commented Jan 19, 2009

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>
>>>

@alsadi alsadi mannequin added extension-modules C modules in the Modules dir type-bug An unexpected behavior, bug, or error labels Jan 19, 2009
@pitrou
Copy link
Member

pitrou commented Jan 19, 2009

What if you commit before the insert?

@alsadi
Copy link
Mannequin Author

alsadi mannequin commented Jan 19, 2009

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);')

@pitrou
Copy link
Member

pitrou commented Jan 19, 2009

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.

@pitrou pitrou closed this as completed Jan 19, 2009
@alsadi
Copy link
Mannequin Author

alsadi mannequin commented Jan 19, 2009

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')

@pitrou
Copy link
Member

pitrou commented Jan 20, 2009

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)

@alsadi
Copy link
Mannequin Author

alsadi mannequin commented Jan 20, 2009

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>
>>>

@ezio-melotti ezio-melotti transferred this issue from another repository Apr 10, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
extension-modules C modules in the Modules dir type-bug An unexpected behavior, bug, or error
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@pitrou