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: [email protected] [mailto:sqlite-users-
>[email protected]] On Behalf Of Jean-Luc Hainaut
>Sent: Saturday, 5 July, 2014 13:56
>To: [email protected]
>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 : [email protected]
>http://www.info.fundp.ac.be/libd
>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
