Foreign key support was added in SQLite version 3.6.19, Oct 14th 2009 (Release History of SQLite). There is no point trying to use this feature if you have an earlier version of SQLite. But which version do you have? Here are the relevant commands. NB version is the version of pysqlite, not sqlite. So 2.6.0 does not refer to the SQLite version, which is 3.7.4 i.e. very up-to-date as at the time of posting.


>>> import sqlite3 as sqlite
>>> sqlite.version
'2.6.0'
>>> sqlite.sqlite_version
'3.7.4'

The default installation of Python 2.6.6 (which I had to stay with for various reasons) installed an older version of SQLite. But this was easily remedied by installing a newer version (2.7.2) alongside 2.6.6 and overwriting the older version of sqlite3.dll in the C:\Python26\DLL folder with the version from C:\Python27\DLL. It was as simple as that.

Now foreign key constraints are disabled by default, so they must be enabled for each connection.

cur.execute("PRAGMA foreign_keys = ON")

Now to check that it is set, run the following:

cur.execute("PRAGMA foreign_keys")

to run the PRAGMA command you need, and

cur.fetchone()

to get the result back from it. Either (0,) or (1,). NB to do the fetchone() after “PRAGMA foreign_keys” not “PRAGMA foreign_keys = ON”. If nothing is returned, you either forgot the previous instruction and didn’t rerun “PRAGMA foreign_keys” before the fetchone() 😉 or SQLite was compiled with the wrong flags set.

Tip: If the command “PRAGMA foreign_keys” returns no data instead of a single row containing “0” or “1”, then the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).
SQLite Foreign Key Support

See also:

SQLITE_OMIT_TRIGGER

Defining this option omits support for TRIGGER objects. Neither the CREATE TRIGGER or DROP TRIGGER commands are available in this case, and attempting to execute either will result in a parse error. This option also disables enforcement of foreign key constraints, since the code that implements triggers and which is omitted by this option is also used to implement foreign key actions.
Compilation Options for SQLite

If you are using SQLite as installed along with Python the most likely explanation for no result is that you made a mistake.

Pyinstaller is a wonderful way of making a single executable that can be run on any windows machine without requiring Python or associated libraries (e.g. wxPython for GUI toolkit) to be installed. It can make things much easier for users. Instead of installing a whole lot of libraries they just put a folder somewhere (even the Desktop is OK) and start using the executable. In my case I needed a simple toolkit for reading, editing, and manipulating secured MS Access databases and reporting results in either HTML or Excel spreadsheets. Python is much nicer to write code in than VB. But I had some major problems with getting win32com working.

If using a script with win32com inside a pyinstaller (made with 1.5 in this case) executable, there can be major problems with win32com.client.Dispatch(). I used two different approaches to get MS Access and Excel working. Using win32com.client.gencache.EnsureDispatch() seemed to work for both initially but that was incorrect. Using either approach (EnsureDispatch() or Dispatch() without the clsctx argument) there was a problem finding the module within PyInstaller. See EnsureDispatch and EnsureModule not working on win32 for a description of the problem even if the solution was not one I was able to use successfully.

Example error:

dbengine = win32com.client.gencache.EnsureDispatch(r'DAO.DBEngine.36')
File "C:\Program Files\exampledev\build\pyi.win32\admin\outPYZ1.pyz/win32com.client.gencache", line 536, in EnsureDispatch
File "C:\Program Files\exampledev\build\pyi.win32\admin\outPYZ1.pyz/win32com.client.gencache", line 520, in EnsureModule
File "C:\Program Files\exampledev\build\pyi.win32\admin\outPYZ1.pyz/win32com.client.gencache", line 287, in MakeModuleForTypelib
File "C:\Program Files\exampledev\build\pyi.win32\admin\outPYZ1.pyz/win32com.client.makepy", line 286, in GenerateFromTypeLibSpec
File "C:\Program Files\exampledev\build\pyi.win32\admin\outPYZ1.pyz/win32com.client.gencache", line 550, in AddModuleToCache
File "C:\Program Files\exampledev\build\pyi.win32\admin\outPYZ1.pyz/win32com.client.gencache", line 629, in _GetModule
File "C:\pyinstaller-1.5\iu.py", line 455, in importHook
ImportError: No module named win32com.gen_py.00025E01-0000-0000-C000-000000000046x0x5x0

For MS Access, I looked in C:\Python26\Lib\site-packages\win32com\genpy folder and found one the modules I had earlier generated by makepy (details on making it later). It started with the following details which made it clear it was the correct version:

# Created by makepy.py version 0.5.00
# By python version 2.6.5 (r265:79096, Mar 19 2010, 21:48:26) [MSC v.1500 32 bit (Intel)]
# From type library 'dao360.dll'
# On Tue Jun 21 21:47:34 2011
"""Microsoft DAO 3.6 Object Library"""

I saved and renamed the module to dao36_from_genpy.py and the starting point was:
dao36_from_genpy.DBEngine()
instead of win32com.client.Dispatch(r'DAO.DBEngine.36')
The idea came from py2exe/pyinstaller and DispatchWithEvents

Returning to using makepy.py:

“There are a couple of different ways to run makepy. Start Pythonwin, and from the menu select Tools->Com Makepy Utility. You should see a list of registered typelibs. Select “Microsoft Word x.y Object Library” and hit Ok. This can also be done programatically by initiating Word with
win32com.client.gencache.EnsureDispatch(‘Word.Application’) win32com.client.constants – AttributeError

For Excel, the solution was to use win32com.client.Dispatch(“Excel.Application”, clsctx = pythoncom.CLSCTX_LOCAL_SERVER). Remember to import pythoncom of course. I wasn’t able to get the other approach working very quickly because instead of a single module there was a folder, an extensive __init__.py, and a lot of submodules e.g. Workbook.py.

For more on Pyinstaller see Pyinstaller 1.5 with Python 2.6 (Round 3)

Round 1 with Pyinstaller 1.2 and Python 2.4 was here: PyInstaller1.2
Round 2 with Pyinstaller 1.3 and Python 2.5 was here: PyInstaller Round 2

Overview:

1. Put latest copy of pyinstaller under the appropriate python folder e.g. Python26
2. Run Configure.py e.g.

   C:\Python26\python.exe C:\Python26\Pyinstaller-1.5\Configure.py

3. Make a dev folder and put main script in there and a copy of the most recent spec file made for any other project. Use as a starting point.
4. Make a bat file to produce the executable using the spec file to configure the end result e.g.

   "C:\Python26\python.exe" "C:\Python26\pyinstaller-1.5\Build.py" "myproj.spec"
pause

   Include the pause so any errors can be spotted and fixed

5. Make another batch script which runs the executable and has the pause command at the end so you can spot any errors and fix them (so screen doesn’t flash on and off without giving you a chance to read the error messages if any). NB no python.exe involved – just the exe you made e.g.

   myproj.exe
pause

6. Once testing is complete, remake the exe with live spec settings i.e. set console to False and debug to False.
7. Distribute 🙂

Mistakes to avoid:

• Testing the exe with a batch file is good. Having the batch file try to run the exe with python, not so good (in fact, it is dumb 😉 ). Just run the exe on its own. Otherwise spurious error about encoding problem with line 1 of the exe. See Why this SyntaxError
• If making a script which execs everything else it needs (reason: to keep things flexible), the main script e.g. “exec_script2exec.py” MUST import anything needed by scripts it is likely to call otherwise they will not appear in the exe. Pyinstaller can’t read minds or predict the future 😉 . So import wx and import win32com.client are probably required for my MS Access project. Otherwise will get an error about no module called wx etc.

The last Kiwi Python Convention (PyCon) was brilliant and the 2010 convention will be in beautiful Waitangi, Bay of Islands, New Zealand. Here is the release from the organising committee:

Hi all,

We’ve got another Kiwi PyCon lined up, and we’re all expecting it to be as
good as last year’s event. This year it will be staged in lovely Paihia/Waitangi in the sunny Bay of Islands. It is on the weekend 20-21
November.

Find a link to the “Call for Participation” below. We are encouraging everybody to come and to participate. Do not feel intimidated, this is a community conference, and we are hosting talks and sessions for all knowledge levels. Be it a tiny demo or show and tell (by doing a 5 minute Lightning Talk), or up to running an hour long tutorial. Or are you interested in the dynamic evolution of a session? Then the un-conference like Open Spaces will give you just the platform for interaction …

Also … make sure to spread the word. Tell everybody else about it. Blog about it, post it on Twitter/Identi.ca, etc.

http://nz.pycon.org/2010/talks-cfp/call-for-papers/

…

Code and enjoy,

The Kiwi PyCon Committee
By Tim Knapp, Plone Developer at Emerge Technology

The first release for series 0.9 of SOFA Statistics has just been launched. The main focus of series 0.9 will be the “learn-as-you-go” goal. To this end, there will be plenty of support graphics and decision-support documentation. See 0.9.0 can examine normality of variables when selecting main test to use. Matplotlib is being used for the support charting.

Support graphing in the new series of SOFA Statistics releases

One of the things I like about Python is the way I can hold so much more in my brain without having to look stuff up. If you know the tools for working with sequences (strings, lists, tuples, dictionaries etc), you know what to do with anything that is a sequence. Data from a database can be handled using the standard tools for processing lists and tuples etc etc etc. You don’t need to know how ADO differs from DAO etc. This is no accident – it was by design. Anyway, it was nice to see that a prominent programmer feels the same way about Python:

Bruce Eckel: They say you can hold seven plus or minus two pieces of information in your mind. I can’t remember how to open files in Java. I’ve written chapters on it. I’ve done it a bunch of times, but it’s too many steps. …

The other issue is the effect of an interruption. If you are really deep into doing something and you have an interruption, it’s quite a number of minutes before you can get back into that deeply focused state. With programming, imagine you’re flowing along. You’re thinking, “I know this, and I know this, and I know this,” and you are putting things together. And then all of a sudden you run into something like, “I have to open a file and read in the lines.” All the clutter in the code you have to write to do that in Java can interrupt the flow of your work.

Another number that used to be bandied about is that programmers can produce an average of ten working lines of code per day. Say I open up a file and read in all the lines. In Java, I’ve probably already used up my ten working lines of code for that day. In Python, I can do it in one line. I can say, “for line in file(‘filename’).readlines():,” and then I’m ready to process the lines. And I can remember that one liner off the top of my head, so I can just really flow with that.
http://www.artima.com/intv/aboutmeP.html