Re: [sqlite] Autocommit in "with" query: bug or feature?

[big stone]

Curiousity question :

Why are you basing your SQLfast next book on Python 2.7.6 rather than
Python 3.3 (or 3.4) ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autocommit in "with" query: bug or feature?

[Simon Slavin]

On 7 Jul 2014, at 6:49am, big stone  wrote:

> If I understood well, the "autocommit" idea was to not "hold" the database
> file by default, and ease multi-user access to the file database.

Not executing "BEGIN" is also the most frequent error that SQL programmers 
make.  Just like even the most experienced C programmer forgets a semicolon 
occasionally, even the most experienced SQL programmer forgets a "BEGIN".

SQLite does provide a way to check if you have manually started a transaction:



and this can be used in interfaces and drivers which need to know whether they 
should be implementing their own transaction for a statement.  But I don't 
remember seeing one that uses it that way.

It's also useful if you have received an error from a SQL command and are 
trying to work out how serious is it.  If you're back in autocommit mode then 
you know your entire trasaction -- not just the statement -- has been 
terminated.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autocommit in "with" query: bug or feature?

[big stone]

Hi again,

To answer the question in the title :
- yes, the "autocommit" bug is buggy with "with",
  http://bugs.python.org/issue21718
- it is also buggy with comments at the beginning of the 'select'.

The "autocommit" feature was a false good idea, but as sqlite3 arrived like
that in the standard library, they can't "change" this default.

If I understood well, the "autocommit" idea was to not "hold" the database
file by default, and ease multi-user access to the file database.


I tested your example with Python 2.7.5 and SQLite3.8.4, works nicely.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autocommit in "with" query: bug or feature?

[Keith Medcalf]

ie, this will work:  isolation_level = None and explicitly "BEGIN" transactions 
...

import sqlite3

def displayDBcontents():
query = "select * from PERSON"
c.execute(query)
print
for row in c: print '%-4s %-10s' % (row[0],row[1])

# We create and fill the STAFF database
conn = sqlite3.connect('STAFF.db', isolation_level=None)
c = conn.cursor()
c.execute("drop table if exists PERSON")
c.execute("create table PERSON (PID char(4),Name char(10))")

c.execute('BEGIN')
c.execute("insert into PERSON values ('p1','Smith'), ('p2','Dermiez')")
conn.commit()

# We check the contents of table PERSON
displayDBcontents()

# We insert Jones and we check the contents of PERSON

c.execute('BEGIN')
c.execute("insert into PERSON values('p3','Jones')")
displayDBcontents()

# We execute a simple "with" query
c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
print
for row in c: print row[0]

#  We cancel the last insertion (Jones should disappear)
#  and we check the contents of PERSON
conn.rollback()
displayDBcontents()

# Surprise: Jones still is in the DB

c.close()
conn.close()

>test

p1   Smith
p2   Dermiez

p1   Smith
p2   Dermiez
p3   Jones

1
2

p1   Smith
p2   Dermiez

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Jean-Luc Hainaut
>Sent: Saturday, 5 July, 2014 13:56
>To: sqlite-users@sqlite.org
>Subject: [sqlite] Autocommit in "with" query: bug or feature?
>
>Hi,
>
>Context: Python 2.7.6, Windows XP, SQLite v3.8.5.
>
>The following test program suggest that "with" queries automatically
>execute a commit, as if they were DDL statements. I hope this is a bug,
>otherwise, this side effect considerably reduces the interest of this
>query.
>
>Best regards
>
>Jean-Luc Hainaut
>
>
># -*- coding: UTF8 -*-
>import sqlite3
>
>def displayDBcontents():
>query = "select * from PERSON"
>c.execute(query)
>print
>for row in c: print '%-4s %-10s' % (row[0],row[1])
>
># We create and fill the STAFF database
>conn = sqlite3.connect('STAFF.db')
>c = conn.cursor()
>c.execute("drop table if exists PERSON")
>c.execute("create table PERSON (PID char(4),Name char(10))")
>c.execute("insert into PERSON values ('p1','Smith'),('p2','Dermiez')")
>conn.commit()
>
># We check the contents of table PERSON
>displayDBcontents()
>
># We insert Jones and we check the contents of PERSON
>c.execute("insert into PERSON values('p3','Jones')")
>displayDBcontents()
>
># We execute a simple "with" query
>c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
>print
>for row in c: print row[0]
>
>#  We cancel the last insertion (Jones should disappear)
>#  and we check the contents of PERSON
>conn.rollback()
>displayDBcontents()
>
># Surprise: Jones still is in the DB
>
>c.close()
>conn.close()
>
>Prof. Jean-Luc Hainaut
>Faculté d'Informatique
>University of Namur
>Rue Grandgagnage, 21
>B-5000 - Namur (Belgium)
>Phone (direct) : +32 (81) 72 49 96
>Phone (secret.): +32 (81) 72 49 64
>Fax: +32 (81) 72 49 67
>E-mail : jlhain...@info.fundp.ac.be
>http://www.info.fundp.ac.be/libd
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autocommit in "with" query: bug or feature?

[big stone]

Hi Jean-Luc,


All your problem is that you must use conn.isolation_level = None
With conn.isolation_level = "" , the default of sqlite3, nothing related
with transaction will work.

Just try with :
* this program :
https://github.com/stonebig/sqlite_bro/blob/master/sqlite_bro.py
(or pip install sqlite_bro)

* your example slightly reworked :

-- use conn.isolation_level = None , when you want to manipulate
transactions
drop table if exists PERSON;
create table PERSON (PID char(4),Name char(10));
insert into PERSON values ('p1','Smith'),('p2','Dermiez');

BEGIN TRANSACTION;
-- We check the contents of table PERSON
select * from PERSON;

-- We insert Jones and we check the contents of PERSON
insert into PERSON values('p3','Jones');
select * from PERSON;

-- We execute a simple "with" query
with CTE(A) as (values (1),(2)) select A from CTE;

-- We cancel the last insertion (Jones should disappear)
-- and we check the contents of PERSON
ROLLBACK;
select * from PERSON;

-- No Surprise: Jones IS NO MORE in the DB
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autocommit in "with" query: bug or feature?

[Keith Medcalf]

Python 2.7.8 cannot run your program using the shipped version of SQLite.  It 
does not support either multiple valued inserts nor the WITH statement.  Did 
you just "drop in" a new version of the sqlite3.dll?

While this (dropping a new version of sqlite3.dll) will work fine mostly, the 
dbapi2 compatible layer diddles around with transactions in order to support 
its version of autocommit.  

In order to do so, the _sqlite3.pyd module must detect the "statement type".  
Contained within cursor.c is the following code:

if (!strcmp(buf, "select")) {
return STATEMENT_SELECT;
} else if (!strcmp(buf, "insert")) {
return STATEMENT_INSERT;
} else if (!strcmp(buf, "update")) {
return STATEMENT_UPDATE;
} else if (!strcmp(buf, "delete")) {
return STATEMENT_DELETE;
} else if (!strcmp(buf, "replace")) {
return STATEMENT_REPLACE;
} else {
return STATEMENT_OTHER;
}

You will note that this code classifies statements that begin with the word 
"WITH" as belonging to class "OTHER".  "OTHER" effectively means a DDL 
statement and an autocommit operation is done after DDL statements execute.  
You need to change this to something like the following:

if (!strcmp(buf, "select")) {
return STATEMENT_SELECT;
} else if (!strcmp(buf, "with")) {
return STATEMENT_SELECT;
} else if (!strcmp(buf, "insert")) {
return STATEMENT_INSERT;
} else if (!strcmp(buf, "update")) {
return STATEMENT_UPDATE;
} else if (!strcmp(buf, "delete")) {
return STATEMENT_DELETE;
} else if (!strcmp(buf, "replace")) {
return STATEMENT_REPLACE;
} else {
return STATEMENT_OTHER;
}

Once this change is made (and Python and the extension compiled and installed), 
your code executes as expected.

You can either download pysqlite from the PPI here 
https://pypi.python.org/pypi/pysqlite and build your own extension with the 
above modification; ask the python developers if they can fix this in their 
distribution when they include the new version of SQLite supporting the WITH 
statement; or extract and fix the _sqlite3 extension in Python yourself.

When I tested this it was with the current head of trunk for SQLite3 and the 
currently available pysqlite from above.  I made a few changes to the 
distribution of pysqlite to include the above change, and also so that you can 
simply

import pysqlite2 as sqlite3

instead of

import pysqlite2.dbapi2 as sqlite3

by modifying the __init__.py in addition to the cursor.c change.  Importing 
pysqlite2.dbapi2 as sqlite3 will still work, of course.

I do not know how this version of pysqlite (2.6.3) compares to the one included 
with python (version 2.6.0).  However, you can download the bits here:

http://www.dessus.com/files/pysqlite-2.6.3.win32-py2.7.exe
http://www.dessus.com/files/pysqlite-2.6.3.zip

Note that the prebuilt extension may not work for you, but it works for me.  
You may have to download the extension source (zip), update the sqlite3.lib and 
sqlite3.h with the files corresponding to the version of sqlite3 you want to 
support, and build the extension yourself.

Alternatively, you may want to look into APSW by Roger Binns, who is also on 
this list.  APSW is a Pythonic wrapper for SQLite3.  It is not an dbapi2 
complianct replacement since it exposes all the capabilities of SQLite3 and 
does not limit it to the lowest common denominator of functionality.  APSW can 
be found here:

https://github.com/rogerbinns/apsw

Make sure you read the documentation -- it is not the same as the dbapi2 
interface even though it may appear to be.  Roger generally updates APSW with 
each release of SQLite3 to include new features found in SQLite.

I may extract the code from the Python distribution and fork a pysqlite that 
mimics the _sqlite3.pyd extension in the Python distribution with the above 
StatementKind fix.  I'll have to see how doable that is.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Jean-Luc Hainaut
>Sent: Saturday, 5 July, 2014 13:56
>To: sqlite-users@sqlite.org
>Subject: [sqlite] Autocommit in "with" query: bug or feature?
>
>Hi,
>
>Context: Python 2.7.6, Windows XP, SQLite v3.8.5.
>
>The following test program suggest that "with" queries automatically
>execute a commit, as if they were DDL statements. I hope this is a bug,
>otherwise, this side effect considerably reduces the interest of this
>query.
>
>Best regards
>
>Jean-Luc Hainaut
>
>
># -*- coding: UTF8 -*-
>import sqlite3
>
>def displayDBcontents():
>query = "select * from PERSON"
>c.execute(query)
>print
>for row in c:

Re: [sqlite] Autocommit in "with" query: bug or feature?

[RSmith]

On 2014/07/05 21:55, Jean-Luc Hainaut wrote:

Hi,

Context: Python 2.7.6, Windows XP, SQLite v3.8.5.

The following test program suggest that "with" queries automatically execute a 
commit, as if they were DDL statements. I hope this is a bug, otherwise, this side effect 
considerably reduces the interest of this query.


Bonjour Jean-Luc,

The assumption is not true I think, there is nothing in a "with" query that 
causes committal - but let's see the example.


# -*- coding: UTF8 -*-
import sqlite3

def displayDBcontents():
 query = "select * from PERSON"
 c.execute(query)
 print
 for row in c: print '%-4s %-10s' % (row[0],row[1])

# We create and fill the STAFF database
conn = sqlite3.connect('STAFF.db')
c = conn.cursor()
Right about here should be the start of a transaction somewhere... but I do not see any, where did  you want to roll back to? If you 
are using pysqlite it will probably have an auto-open transaction, but that should be avoided, I'm not sure exactly how since I 
don't use it. Either way, you should try do it manually.

c.execute("drop table if exists PERSON")
c.execute("create table PERSON (PID char(4),Name char(10))")
c.execute("insert into PERSON values ('p1','Smith'),('p2','Dermiez')")
conn.commit()
This Commit is a no-op... no explicit transaction was opened, so every one of the above "c.execute()" commands started AND ended an 
implicit transaction.
And so we continue without an open transaction... or... maybe a transaction was opened in the background, but now you committed it, 
and so it is not open anymore.. or maybe a new one is opened automatically - you need to find out how the wrapper works exactly.

# We check the contents of table PERSON
displayDBcontents()

# We insert Jones and we check the contents of PERSON
c.execute("insert into PERSON values('p3','Jones')")
displayDBcontents()

# We execute a simple "with" query
c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
print
for row in c: print row[0]

#  We cancel the last insertion (Jones should disappear)
#  and we check the contents of PERSON
conn.rollback()

Roll back to where? there is no open transaction still... every single execute 
was a transaction by itself.

displayDBcontents()

# Surprise: Jones still is in the DB

Yep, but not a surprise at all, very much expected.


c.close()
conn.close()


May I suggest a slightly altered script:

import sqlite3

def displayDBcontents():
query = "select * from PERSON"
c.execute(query)
print
for row in c: print '%-4s %-10s' % (row[0],row[1])

# We create and fill the STAFF database
conn = sqlite3.connect('STAFF.db')
c = conn.cursor()
c.execute("BEGIN TRANSACTION;")   # This might wee have a defined automatic 
implemetnation in the object, such as c.beginTransaction() or such.
c.execute("drop table if exists PERSON")
c.execute("create table PERSON (PID char(4),Name char(10))")
c.execute("insert into PERSON values ('p1','Smith'),('p2','Dermiez')")
c.execute("COMMIT;")# btw - Why did you refer back to the "Conn" here in 
your version? c.commit should work just fine.

# Now the last open transaction was committed, there is no longer an open 
transaction, so we need to start a new one.
c.execute("BEGIN TRANSACTION")   # Or c.beginTransaction() etc.

# We check the contents of table PERSON
displayDBcontents()

# We insert Jones and we check the contents of PERSON
c.execute("insert into PERSON values('p3','Jones')")
displayDBcontents()

# We execute a simple "with" query
c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
print
for row in c: print row[0]

#  We cancel the last insertion (Jones should disappear)
#  and we check the contents of PERSON
c.execute("ROLLBACK;")   # or c.rollback() or such...
displayDBcontents()

# NOW if jones is still here, it would be indeed a huge Surprise - but he won't 
be, because SQL cannot lie. :)

I still do not know if the transactions can be used explicit like this in the wrapper, but that is not a SQLite question really. The 
above in a normal SQLite (or any other SQL engine) will work as advertised and no happy random committing could be taking place.




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autocommit in "with" query: bug or feature?

[Clemens Ladisch]

Jean-Luc Hainaut wrote:
> Context: Python
>
> The following test program suggest that "with" queries automatically
> execute a commit, as if they were DDL statements.

SQLite itself does not differentiate between DDL and DML statements;
when there are no explicit transactions, every statement gets wrapped
into an automatic transaction.

Python tries to be clever and automatically starts transactions that are
not committed as long as you keep executing DML statements.  However,
it is not possible to detect DML reliably (as you have seen).

> I hope this is a bug, otherwise, this side effect considerably reduces
> the interest of this query.

I consider pysqlite's default transaction handling to be a bug.

To disable it (and handle transactions manually), use something like
this:

  connection.isolation_level = None

or use a different SQLite driver, such as apsw.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Autocommit in "with" query: bug or feature?

[Jean-Luc Hainaut]

Hi,

Context: Python 2.7.6, Windows XP, SQLite v3.8.5.

The following test program suggest that "with" queries automatically execute a 
commit, as if they were DDL statements. I hope this is a bug, otherwise, this 
side effect considerably reduces the interest of this query.

Best regards

Jean-Luc Hainaut


# -*- coding: UTF8 -*-
import sqlite3

def displayDBcontents():
query = "select * from PERSON"
c.execute(query)
print
for row in c: print '%-4s %-10s' % (row[0],row[1])

# We create and fill the STAFF database
conn = sqlite3.connect('STAFF.db')
c = conn.cursor()
c.execute("drop table if exists PERSON")
c.execute("create table PERSON (PID char(4),Name char(10))")
c.execute("insert into PERSON values ('p1','Smith'),('p2','Dermiez')")
conn.commit()

# We check the contents of table PERSON
displayDBcontents()

# We insert Jones and we check the contents of PERSON
c.execute("insert into PERSON values('p3','Jones')")
displayDBcontents()

# We execute a simple "with" query
c.execute("with CTE(A) as (values (1),(2)) select A from CTE")
print
for row in c: print row[0]

#  We cancel the last insertion (Jones should disappear)
#  and we check the contents of PERSON
conn.rollback()
displayDBcontents()

# Surprise: Jones still is in the DB

c.close()
conn.close()

Prof. Jean-Luc Hainaut
Faculté d'Informatique
University of Namur
Rue Grandgagnage, 21
B-5000 - Namur (Belgium)   
Phone (direct) : +32 (81) 72 49 96
Phone (secret.): +32 (81) 72 49 64
Fax: +32 (81) 72 49 67
E-mail : jlhain...@info.fundp.ac.be
http://www.info.fundp.ac.be/libd

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users