My System:

$ python
Python 2.7.5 (default, May 15 2013, 22:43:36) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.6.21'

Test Script:

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('PRAGMA foreign_keys = ON')
fk = (conn.execute("PRAGMA foreign_keys").fetchone()[0])
print 'version = %s, foreign keys = %r' % (sqlite3.sqlite_version, bool(fk))
if not fk:
  raise Exception('No foreign keys!?')

c = conn.cursor()
c.executescript('''
create table if not exists main.one (resource_id TEXT PRIMARY KEY, data TEXT);
create table if not exists main.two (node_id INTEGER PRIMARY KEY, data TEXT);
create table if not exists main.mapping (node_id INTEGER REFERENCES two, resource_id TEXT REFERENCES one);
insert into main.one(resource_id, data) values('A', 'A one thing');
insert into main.two(node_id, data) values(1, 'A two thing');
insert into main.mapping(resource_id, node_id) values('A', 1);
insert into main.one(resource_id, data) values('B', 'Another one thing');
insert into main.two(node_id, data) values(2, 'Another two thing');
insert into main.mapping(resource_id, node_id) values('B', 2);
insert into main.one(resource_id, data) values('C', 'Yet another one thing');
''')
for tbl in 'one', 'two', 'mapping':
  print 'TABLE main.%s:\n%s\n' % (tbl, '\n'.join(repr(r) for r in c.execute('select * from main.%s' % tbl).fetchall()))

del_cmd = """delete from main.one where resource_id='B'"""
print 'Attempting: %s' % (del_cmd,)
try:
  c.execute(del_cmd)
except Exception, e:
  print 'Failed to delete: %s' % e

cmd = """delete from main.one where resource_id='C'"""
print 'Attempting: %s' % (cmd,)
c.execute(cmd)

cmd = """delete from main.mapping where resource_id='B' AND node_id=2"""
print '\nAttempting: %s' % (cmd,)
c.execute(cmd)

for tbl in 'one', 'two', 'mapping':
  print 'TABLE main.%s:\n%s\n' % (tbl, '\n'.join(repr(r) for r in c.execute('select * from main.%s' % tbl).fetchall()))

print 'Attempting: %s' % (del_cmd,)
c.execute(del_cmd)

This fails with "sqlite3.IntegrityError: foreign key constraint failed". Original report comes from SO: http://stackoverflow.com/questions/9342763/sqlite3-foreign-keys-remembered

The proposed solution (to upgrade sqlite) is not possible on Windows as it comes bundled with Python.
So please update the bundled sqlite version where this bug is solved.

It's certainly possible to update SQLite without rebuilding Python. Just replace sqlite3.dll with a newer version.

Ah that's great. The issue is resolved with SQLite 3.8.1.

However, would be great if a newer SQLite version comes already bundled with Python.

FYI 3.4.0 Windows was shipped with SQLite 3.8.3.1 see bpo-20465.

I don't maintain Python 2.7 anymore, so removing myself.

I don't know enough about the SQLite API to determine whether we can safely upgrade from 3.6.21 in Python 2.7, but since this doesn't appear to be a security issue I don't see any solid justification for doing it anyway.

If someone else does it, I'll build it, but I'm not taking responsibility for the change :)

IMO, what third-party libraries are included with the Windows and OS X binary installers, what versions of them, and when to update them are questions without firm established policy answers, say in a PEP. In many ways, for the installers we are performing the role of a third-party distributor / integrator. In many other such situations, the third-party libraries would be dynamically linked from the platform OS's or distributor's version and would be independently updated, assuming ABI compatibility is maintained. For the python.org installers, I think the updating has been mostly at the discretion of the installer developers with occasional instigation by the release managers or other core developers in the cases of security issues. FWIW, the most recent 2.7.x installers for OS X ship with SQLite 3.8.3.1. The current version of SQLite is 3.8.5 and that project has a very good reputation for its well-tested, regression-free releases. So I would not see a problem in upgrading the library but I would get a pronouncement from the release manager.

I've now run various and partially complex applications including SQLAlchemy against SQLite 3.8.1 for months without having any issues. Right now I have run extensive database test suites against the current SQLite release 3.8.6 without any issues, too.

So I would say it's save to bundle a newer SQLite version in the next Python 2.7.x. Who would be responsible for this? Is there anything I can do to get progress on this issue?

Steve is responsible for the Windows builds, but he always answered to your request above.

A work around is already given in msg201796 and is reinforced by this https://mail.python.org/pipermail/python-list/2014-October/679369.html

Yes, but this is no practical solution. Telling *all* my clients to update the sqlite3.dll after *every* Python update is just not feasible and will just not work out in practice.

What would be the required steps to update the *.dll in the build? Just update the external.bat to fetch another version or I am missing something?

I would suggest synchronizing it with Python 3, which means SQLite 3.8.3.1. Or what do you think?

The main risk is if SQLite 3.8 changes some behaviours compared to older versions. This is why we generally choose to only apply bugfix releases of external libraries in our owbn bugfix branches.

The question is whether this issue can be worked around without upgrading SQLite.

Well, OSX release ships with 3.8.3.1, too:

https://hg.python.org/cpython/file/2.7/Mac/BuildScript/build-installer.py#l290

As I said, I think it's really Steve's choice here (as long as he is doing the Windows release builds).

I'm ambivalent on whether to do it, but Marc is right that it's just a version update in Tools/buildbot/external-common.bat and PCbuild/pyproject.vsprops.

Apparently we are shipping an old version in 3.5b4 (3.8.3.1 and the latest release is 3.11), so this should probably be upgraded before rc.

I agree, and I know that backwards compatibility is a very high priority especially now that SQLite is shipping as part of Windows 10. While we *could* take a dependency on this binary (winsqlite3.dll, for those who have it), I don't think it's actually any different from the main release.

In short, let's update the SQLite version for 3.5 and put it through its paces.

I'm only +0 on doing it for Python 2.7, and that's assuming we'll do an RC before releasing 2.7.11. Given we just revised the entire build project, I think an RC would be appropriate.

Updating to 3.8.11.0 now.

I'm still +1 for an update in 2.7 as there are a lot of important bug fixes and a new SQLite version is 100% backwards compatible for my medium to big size (SQLAlchemy) SQLite applications.

Looks like Python 2.7 is still on SQLite 3.6.21, so the RM (Benjamin) will have to make a call on whether we take the upgrade.

New changeset 1003746ee7a0 by Steve Dower in branch '3.4':
Issue bpo-19450: Update Windows builds to use SQLite 3.8.11.0
https://hg.python.org/cpython/rev/1003746ee7a0

New changeset 4b37e81fcc54 by Steve Dower in branch '3.5':
Issue bpo-19450: Update Windows builds to use SQLite 3.8.11.0
https://hg.python.org/cpython/rev/4b37e81fcc54

New changeset efb8132a7a22 by Steve Dower in branch 'default':
Issue bpo-19450: Update Windows builds to use SQLite 3.8.11.0
https://hg.python.org/cpython/rev/efb8132a7a22

New changeset 33dbcde76b3f by Ned Deily in branch '3.4':
Issue bpo-19450: Update OS X installer builds to use SQLite 3.8.11.
https://hg.python.org/cpython/rev/33dbcde76b3f

New changeset ebe72984c1b9 by Ned Deily in branch '3.5':
Issue bpo-19450: merge from 3.4
https://hg.python.org/cpython/rev/ebe72984c1b9

New changeset 1f4ef305b658 by Ned Deily in branch 'default':
Issue bpo-19450: merge from 3.5
https://hg.python.org/cpython/rev/1f4ef305b658

This wasn't decided yet for 2.7 so I'm reopening it until a decision is made.

I'm still +1 for an update to 3.8.3.1 on Windows so that this is on par with the version on OSX and the version in Python 3.

Assigning to Benjamin to make the call for 2.7. Either close or assign it back if you want the update.

Let's make Windows use the same version as the Mac installer.

New changeset fa68df1d5e65 by Steve Dower in branch '2.7':
Issue bpo-19450: Update Windows builds to use SQLite 3.8.11.0.
https://hg.python.org/cpython/rev/fa68df1d5e65

Done. The test_sqlite tests were fine, but that's all I checked.