pssst …

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.

Don’t get me wrong – I’m really looking forwards to what Unity will become (if Canonical plays its cards right). But it is a mixed experience for users at the moment. A friend of mine upgraded his laptop from Maverick to Natty and had some serious problems. Unity had worked at first (although he preferred the old interface) but then it stopped. The launcher and the panel vanished and he needed some help escaping Unity and using the Ubuntu Classic (Gnome 2) interface. Unfortunately, he wasn’t able to simply log in and change to Ubuntu Classic as per HowTo: Disable Ubuntu Unity Interface.

The first thing I suggested was that he follow the instructions at Missing top and side panels in Unity, Natty Troubleshooting. Great! It worked and now he could interact with the system much easier. The sharks were backing off (See http://xkcd.com/349/).

But how could he get the system to let him log in using his name and password? For that I needed the control panel. The easiest way to get that seemed to be via the terminal with the command:

gnome-control-center

Once there I could select the default log in session:

Success!

While I’m at it I should add a couple of opinions about Unity. Why not? Everyone else is

1. Canonical needs to give users the option of Ubuntu Classic (Gnome 2) for a few releases more than intended. Depending on how Unity works with dual widescreen monitors I may need Gnome 2 for quite a while.
2. Users need to have a few configuration options easily available – e.g. how to remove the overlay scroll bars**
3. Unity could be awesome and I think it will be a great option within a couple of releases. See a reasonably balanced review here – Riding the Narwhal: Ars reviews Unity in Ubuntu 11.04 I am already trying out Oneiric Unity on my notebook so I’m not a hater.

**
At the moment you have to do the following:
sudo su
echo "export LIBOVERLAY_SCROLLBAR=0" > /etc/X11/Xsession.d/80overlayscrollbars
from How To Disable The Overlay Scrollbars In Ubuntu 11.04 [Quick Tip]

My mother runs Ubuntu (Lucid) on her laptop and she needed to make her NZ Telecom USB modem work. Getting an XT USB Modem working in Ubuntu provided a great starting point but there were a few things I had to do differently. I recommend printing the Rob the Geek article and then following this post for extra guidance.

The key issue is:

[these particular devices] respond to the system by default as a CD drive or USB hub so Ubuntu (with the stick in it’s native form) doesn’t see it as a modem device.
Getting an XT USB Modem working in Ubuntu

Fortunately, I had the option of dual booting into Windows XP so I could modify the modem (thus enabling Ubuntu to detect it as such). The missing step in the documentation for me was that I needed to install the modem under Windows first before I could see it in the Device Manager. In Windows XP, it was Control Panel>Classic View>System>Hardware Device Manager. I discovered the relevant port in my mother’s case was COM5. Yours may be different.

I used Hyperterminal for the next step. It wasn’t exactly intuitive to me but I got it to work. My main problem was I couldn’t see the interface responding as I typed in the command – at least not until it was entered. The command IIRC was AT+ZCDRUN=8

I received:

>> Close autorun
state result (0: FAIL 1: SUCCESS): 1
>> OK

I could then reboot into Ubuntu. The Ubuntu steps (look at Rob the Geek’s instructions) “Just Worked” and the modem just works. I was also successful getting another Telecom XT mobile going for my father-in-law on his eeebuntu netbook. So this was not a fluke.

In conclusion, there was only one small thing stopping everything else from working. Once fixed – complete success and an easy and intuitive user interface.

“wxPython 2.8 Application Development Cookbook” is out and my first impressions are very positive. Although I already have a heavily-used copy of “wxPython In Action” I didn’t hesitate to add this new book to my collection. My open source SOFA Statistics application (36,000 downloads to date) is based on wxPython and is running successfully on Windows XP, Windows 7, Mac OS X, Ubuntu, Fedora, Linux Mint, Mandriva, Arch Linux and OpenSuse. This would not have been possible without a good cross-platform GUI toolkit and a helpful community to go with it. Good reference material has often proved essential as well and I look forward to putting this book to work in coming months.

SOFA Statistics is a user-friendly open source reporting, analysis and reporting package. The project goals are ease of use, learn as you go, and beautiful output. And after 34,000 downloads of early releases, the first general release of SOFA Statistics is out. See it in action here. You can get it for Windows, Mac, Ubuntu, and (other) Linux distributions here.

SOFA Statistics is 100% free and open source but affordable commercial support is available to those who would like that here. The latest version is the culmination of several years work and we hope you like it!

Installation note if upgrading: if upgrading from 0.9.24 or below, you will need to rename your /home/username/sofa folder to /home/username/sofastats if you wish to work with old data or settings. If you are using Ubuntu, you must also manually uninstall previous versions of SOFA first so that “sofastats” can overwrite the application icon put there originally by “sofa”.

Using OpenShot I was able to make videos, edit them, insert new sections, add soundtracks, make a groovy 3D title sequence (using Blender behind the scenes). I exported output as AVIs and MP4s, was able to select a size format that perfectly suited my target (www.showmedo.com), and was able to work it all out without reference to the documentation. So this is a big vote of confidence in a brilliant project with a bright future. The developer is very talented (and not just as a developer, but also as a promoter etc) and I think it is safe to hitch my wagon to that program.

The few glitches I experienced were not very large and will probably not survive for too long.

As an example of what I was able to do with OpenShot, check out the Quick Overview video here:

There was a persistent bug in SOFA Statistics regarding the Preferences bitmap button (wxPython). You would click the button and it would not activate. The associated image would change when you hovered the mouse over the button and the button would appear to have the focus but no click event would fire. Pressing the Enter key or space bar would activate it. Aha! I knew of a similar bug that had affected eclipse on Ubuntu (see What GDK_NATIVE_WINDOWS=1 means) – and setting the env variable GDK_NATIVE_WINDOWS to true was the workaround there. Would that work? Well it did under some circumstances and not others. Very frustrating. But also very interesting now that I know the underlying cause of the bug – namely, a missing event.Skip() at the end of the event triggered by moving the mouse over the Preferences bitmap button. That’s all it was. Nothing whatsoever to do with GDK_NATIVE_WINDOWS really. Adding that missing line solved the problem.

I needed to shift the contents, bit for bit, from a 1TB HDD to a 1.5TB HDD. The process takes several hours and may be best completed as an over-nighter.

Boot into a Ubuntu Live CD
Choose to Try It (not Install It)
Applications>Accessories>Terminal
sudo su (so we don’t have to use sudo all the time)
cfdisk /dev/sda (so we can see which HDD is which – it is VERY important to get the source and destination details the right way around)
cfdisk /dev/sdb (in my case it was 1.5TB and had no partitions so sdb was the destination for me. NB to get this the right way around)
dd if=/dev/sda of=/dev/sdb bs=30M & (‘dd’ for disk duplicator; ‘if’ is the source – ‘if’ for input file; ‘of’ is the destination – ‘of’ for output file; bs is block size and I chose 30M; & so the process runs in the background without feedback)

To get feedback as the process runs we use the scary command “kill”, but only as USR1. All this does is say Hi to the process and we hear back how long it has been running and how much it has processed.

My process number was 4569 but yours will be different:

kill -USR1 4569

At the end there will be a message about the task, and the kill command will be told the process is not running any more.

In my case I also wanted to change to ext4, delete the swap partition on the new (1.5TB) HDD, resize the partition on the new HDD, resize the file system on the new HDD, and create a swapfile.

The best and easiest way to do this is probably to use gparted from within the live CD.

System>Administration>GParted Partition Editor

From this point on, use advice at own risk:

OK – the destination disk is potentially 1.5 TB but it is currently only making 1TB available. We need to delete the partition, then create a new one (which should start at the same position).

cfdisk /dev/sdb (the destination, which might be a in your case)

d for Delete to remove the appropriate partition
n for New to make a new one
p for primary
Enter to accept size suggested
b to set the disk as bootable (will show up under flags)
A capital W for write and then yes to confirm (not y but yes)
q to quit

NB make sure the drive you want to modify is unmounted (to prevent severe damage).

May need to reboot to read everything properly.

e2fsck -f /dev/sdb1 (the destination, which might be a in your case)

Re: e2fsck see e2fsck(8) – Linux man page
-f Force checking even if the file system seems clean.
-y might also be useful: Assume an answer of ‘yes’ to all questions; allows e2fsck to be used non-interactively

resize2fs /dev/sdb1 (the destination, which might be a in your case)

The next instructions must be treated with caution as they are my recollection of the steps taken.

Open and edit /etc/fstab

The column headings are: file system, mount point, type, options, dump, pass

Where I saw ext3 I changed it to ext4:

UUID= .../ ext4 relatime,errors=remount-ro 0 1

I commented out the swap UUID:

#UUID=... none swap sw 0 0

And added a swapfile line:

/swapfile none swap sw 0 0

After Ubuntu upgraded the kernel later on I needed to reboot into the old kernel and run:

sudo update-grub

to be able to boot in an ext4 system.

Everything is working well and there were no issues when I upgraded from Lucid to Maverick a few weeks later.